Tuning Overview

From SQLServerPedia

Jump to: navigation, search
See Also: Main_Page - Monitoring & Tuning

Performance optimization and tuning are very popular database development and support topics. Many organizations tend to spend more time and resources on tuning the database than on the original development. In addition, developing Transact-SQL code is very different from developing front-end applications and middle-tier components. Transact-SQL code developed by front-end or middle-tier experts can often be optimized. Finally, there is a lot of room for optimizing databases when the volume of operations grows and the application that worked great with 15 users no longer lives up to 1500 users' expectations. 

Tuning applications might involve efforts of more than one individual or team. Although the Tuning section concentrates on database design and Transact-SQL code optimization, there are other areas such as the following, that might also have to be optimized:
  1. Front-end user interface applications
  2. Network hardware and topology
  3. Client workstations where the application runs
  4. Database server hardware and operating system
  5. Middle-tier components
  6. Application architecture


A common place where most people like to start tuning applications is the database hardware. Most developers that are unhappy with the application performance will demand adding more memory to the server. For some memory-intensive tasks this makes much sense, however, this approach has a couple of drawbacks:
  1. You can only add so much memory to the server. Although memory is inexpensive, you can't add more RAM than is supported by the computer manufacturer. 
     
  2. Extending the memory will only help optimize the performance up to a certain point. In other words, if you keep adding memory and do not tune the application in any other way, you will reach the point where additional memory produces marginal or no performance improvement. 


The same disadvantages hold true with tuning just the operating system - you can upgrade to the latest operating system with the latest service packs and move all other applications to a different server. You can also adjust the memory used by SQL Server as opposed to the memory available to the Windows NT or Windows 2000. Still, the majority of the performance improvement should come from better logical and physical database design and optimized code. 

SQL Server has a built-in query optimizer that decides how to execute each submitted query. Understanding how query optimizer works is often the key to tuning query performance. 

Steps Involved in Tuning



In general tuning applications involves the following steps:
  1. Determining what constitutes the acceptable performance
  2. Identifying the bottleneck(s) that causes most problems
  3. Determining the most critical queries
  4. Compromising other factors - improvement in one phase will often cause degradation or sacrifice in another


The application performance is ultimately rated by its users. Your queries may be running in record time, but if users have to make 20 clicks and provide several pieces of information repeatedly before they can see the data they need, they may not be happy. That is why it is so important to gather feedback from the unhappy users and determine what constitutes the appropriate performance. Often the issues users have with the application have little or nothing to do with database design or code. However, if the application "hangs" each time a particular query is submitted and users have to wait several minutes to get the results, then you might have some work ahead of you. 

It is also important to realize that improvement in one area often means compromising others. If you can optimize 95% of the critical queries by slowing down the performance of other 5% it might be well worth of your time. Sometimes you can improve the performance of online transactions by increasing the response time with the expense of reducing concurrency. Or you can improve the response time at the expense of throughput. Most of the time, you will also have limited budget to fight the fires - you won't always be able to invest into a brand new top of the line hardware or operating system. Therefore it is important to determine the application performance requirements. 

It is common to spend more time identifying the cause of the problem then actually troubleshooting and fixing it. If all other areas of application are working properly and you can be sure there is a problem with the database code, then you need to investigate your code modules and decide which one is causing problems. Many times improvement in only one stored procedure or trigger can fix the issues. Click here for more information on testing and debugging to find out about problems in your code. 

One of the often-overlooked approaches to optimization is balancing the workload on the database server. If you could move all the batch jobs and maintenance tasks to the periods of limited user activity your code might not have to be changed at all. If those tasks compete for the server resources with the users trying to insert and modify data, then shifting the execution of the tasks will provide significant performance improvements without any coding efforts.
Retrieved from "http://sqlserverpedia.com/wiki/Tuning_Overview"