Announcement

Collapse
No announcement yet.

What steps should be taken to improve the performance SQL server database?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • What steps should be taken to improve the performance SQL server database?

    Often it is seen that as the SQL databases behind web applications and web sites gets bigger in size and are utilized more, it slows down the performance of the SQL server and hence the website may get affected by it. In such a case, it becomes necessary for the application developers and architects to figure out a way to scale the database before it gets too late and the application becomes useless under heavy load.

  • #2
    In order to avoid such scenarios it is necessary to plan from the beginning. In such a case, it is better not to store everything in the same database. The scaling becomes much easier when you categorize the data into logical groups and stores them in databases based on function. This way a web application that is properly designed would easily be able to handle and display errors to the users, as well as log the error to the database. Also, nowadays there are various things websites keep a track of such as the way visitors use the website, things they click on, etc.… so, such things need to be saved by the application to the database.

    Also, in many cases, it has been noticed that most of the commercial applications store such kind of data in the same database which usually drives the application. Due to this, it increases the size of the database and resulting in the increased expenditure needed to host and maintain these additional databases.

    Another way to get rid of such heavy workload problems is to go for a SQL Server Load Balancing solution. Basically, the load balancing solution is built to deal with such kind of issues.

    Comment


    • #3
      Absolutely, it doesn’t matter at all for the businesses who opt for a load balanced server, as it is a solution which connect more than one servers together to offer optimum utilisation of resources and helps to improve the performance as well as reliability for your web sites and web applications. In simple words, the load balancing solution works by distributing the workload of one dedicated server between two or multiple servers in order to serve the users request quickly an in the same amount of time.

      Comment


      • #4
        Advantages of Separating the Logging & Event-Capturing Data from Application database

        If you separate the logging and event capturing data from the SQL database, it can offer you significant advantages such as if in some cases your database server becomes too heavy that it cannot manage the load, you can easily move the logging databases to another server. In addition, you can also distribute the databases across multiple servers to distribute the load. By separating the logging and even capturing data the application database can remain small in size, hence becoming easier to manage, monitor or moving a copy to various environments easily.

        Sometimes, when a server becomes busy due to heavy workload and traffic, in such a condition usually applications have a tendency to record more errors because of the timeouts and other problems. And, if the server keeps on logging the errors obviously it will increase more load on the server and can make the situation worse. If you decide to run the logging databases on a different server, then you may not require the same high-availability as required for the databases that drives the application as well as you will be able to go for a Standard Edition of SQL Server 2008.
        Last edited by Mackay; 14-06-13, 07:02 AM.

        Comment


        • #5
          Hey Mackay, separating the data and storing in a different database by function does make sense; however the distribution of the databases across multiple servers you are saying, don't you think that it would be more expensive? Also, would be the cost below a load balancing solution?

          Comment


          • #6
            Well Haddin, it is hard to confirm about the cost unless we know your current as well as future requirements such as databases and its total size, bandwidth, required CPU, RAM, etc.… as well as the cost you are paying currently. If you can mention all of your requirements we would be able to provide you with the best deal and solution.

            Comment


            • #7
              Okay. Actually, I can’t as I am not in a need of a solution, the reason I have raised the thread is because a few days ago someone on the Twitter social networking site asked for the tips about how to maximize the performance of SQL Server Database. That’s ok, I can understand that without the requirements no one would be able to predict the cost of a solution.

              In addition to the above tips, what else can be done to maximize the performance of SQL Server database?

              Comment


              • #8
                Moreover, a purging strategy may also help you in maximizing your SQL Server database performance. If there is a huge traffic to your website and application, definitely it will record a lot of logging data and create a situation where it will gather logging data in terabytes. In such a condition, it will become hard for you to manage and remove this huge amount of data. Hence, it would be better to develop a purging strategy, categorise the events and conclude that how long would you like to store each kind of logged data. After that, simply execute a purging job which will eliminate the rows from the database according to the defined threshold.

                Comment


                • #9
                  Great, thanks for providing such a useful information about SQL server database. One more thing, are there any sql server management tools that could be used to manage the databases? It would be more helpful if you could provide detailed information about it such as features, how it works, compatible with the versions, etc...

                  Comment


                  • #10
                    Yes, there are some popular SQL management tools available to manage the SQL database. These management tools are very important for an organization in order to manage the enterprise data. In fact, using these management tools allows administrators to expand their capabilities, while some tools from general companies can create a mess, mistakes, and poor database administrator productivity. Hence, it is necessary to choose the tools carefully from a trustworthy vendor and according to the requirements.

                    Comment


                    • #11
                      Absolutely, it is necessary to have a good knowledge of the tools you are going to use to manage the SQL database. To deal with several issues and demands of enterprise database, Microsoft has released few graphical management tools that can help database administrators and web developers to build, manage, and maintain the SQL server solution efficiently. These tools also help to resolve difficult issues related to the SQL performance and configuration. Some of these tools are:

                      SQL Server Management Studio (SSMS) – is a tool through which you can manage multiple sql servers. With the SSMS, the database administrator can manage various sql services that include server reporting, analysis, integration, database engine, and database on multiple servers.

                      SQL Server Configuration Manager (SSCM) – is a tool through which you can cope SQL associated services such as configuration and management of installed client and communication protocols.

                      Microsoft SQL Server Profiler (MSSP) - is a tool with graphical user interface through which database administrators can monitor various activities such as the SQL server or analysis services.

                      Comment


                      • #12
                        All the tools listed Mackay are pretty much all you need to address any performance issue with databases...! Most of the time it issue is with some of the query causing the load instead of the server hardware itself.
                        Also the SQL version you are using as different version uses different number of CPU cores.

                        Comment


                        • #13
                          Well, the high-load SQL statements may be the reason which can impact the performance of queries. In such a case, the SQL Query tuning and caching might help in reducing and improving the workload and performance respectively.

                          Comment


                          • #14
                            Originally posted by Mackay View Post
                            Well, the high-load SQL statements may be the reason which can impact the performance of queries. In such a case, the SQL Query tuning and caching might help in reducing and improving the workload and performance respectively.
                            Exactly..! before zeroing on hardware issues, sorting the query itself and fine tuning it would save lot of time and cost involved for hardware upgrade.

                            Comment


                            • #15
                              Originally posted by JamesC View Post
                              Exactly..! before zeroing on hardware issues, sorting the query itself and fine tuning it would save lot of time and cost involved for hardware upgrade.
                              Always works to check the query instead of just hopping onto to upgrade the hardware.
                              Only I can live forever.

                              Comment

                              Working...
                              X