Recently, I was asked to do an extensive sql server performance audit and review.  I will be sharing some of the information, scripts and documents that I referenced when I completed this thorough analysis.  One of the most important things in performance tuning, is the knowledge that goes along with best practices on SQL Server Waits and Queues.  The article is quite in-depth, but contains a treasure trove of valuable performance tuning information, especially with respect to waits and queues. 

In this whitepaper, you will find examples and explanations of every kind of Wait Type, and how to correlate it to a particular performance resource issue or bottleneck.  You will readily be able to identify the cause of these waits, and learn how to optimize the performance of your sql server.  A user typically experiences poor performance as a series of waits. Whenever a SQL query is able to run but is waiting on another resource, it records details about the cause of the wait. These details can be accessed using the sys.dm_os_wait_stats DMV.  One can examine the accumulated cause of all the waits, based on the output of the DMV, as displayed below:

So, just to take the first wait type that shows up as the highest % of waiting, on the server, CXPACKET.

This wait occurs when trying to synchronize the query processor exchange iterator. Consider lowering the degree of parallelism if contention on this wait type becomes a problem.  Parallel process waits can sometimes occur when data is skewed. In such cases, one parallel thread may process a larger number of rows while another may process a smaller number of rows and so on.

In an OLTP environment, excessive CXPACKET waits can affect the throughput of other OLTP traffic.  In a data warehouse environment, CXPACKET waits are expected for multiple proc environments.

Therefore, for OLTP workloads, you may want to consider limiting parallelism by setting max degree of parallelism to some number less than the total number of CPUs, and other than the default '0', meaning ALL processors.  Please see the document for definitions on other wait types.

In another example, I already authored some articles on IO usage, statistics, and scripts. High I/O usage alone is not necessarily an indication of a problem, or I/O pressure.  In fact, IO_stalls, is the total cumulative time, in milliseconds, that users waited for I/O to be completed on the file since the last restart of SQL Server.  One DMV, sys.dm_io_virtual_file_stats provides a breakdown of SQL Server reads, writes, and io_stalls for a particular database or transaction log file.

There is a complete section dedicated to IO_Stalls, which is one type of wait that can help identify performance bottlenecks.  However, this is just a tiny bit in a large pool of useful information contained in the doc.

Here's a summary of this document, which can be downloaded directly from Microsoft, by clicking on the link below.

SQL Server 2005 Performance Tuning using the Waits and Queues is a proven methodology that allows one to identify the best opportunities to improve performance, the so called “biggest bang for the buck”. These performance improvements are likely to have a significant return on the performance tuning time investment. The methodology helps identify the areas of slow performance by looking at the problem from two directions called Waits and Queues. An analysis of Waits indicates where SQL Server is spending lots of time waiting. In addition, the biggest waits point out the most important or relevant Queues (that is, Performance Monitor counters and other data) for this workload. The cross validation of the waits analysis enables us to eliminate all except the most significant performance counters, and provides a strong indication of pressure on specific resources.

Application performance can be easily explained by looking at SQL Server waits and System or Resource queues. In SQL Server 2005, the dynamic management view (DMV) sys.dm_os_wait_stats provides a valuable source of wait information from an application perspective. The system or resource perspective uses Performance Monitor counters and other data sources to provide a breakdown of system resource usage according to resource queues. Taken together, the value of the application and resource perspectives used by the waits and queues methodology enables us to narrow in on the bottleneck and eliminate the irrelevant performance data.

In sum, Performance Tuning using the Waits and Queues methodology is an effective way to quickly identify and resolve application performance problems because it lets the user discover new and potentially unexpected problem areas, within applications or solutions without the typical guesswork that can accompany such work.

Performance tuning of applications and solutions has been around for many years. The performance of SQL Server 2005 database applications should be evaluated from several different perspectives. Each perspective tells a different section of the complete performance story. Together they paint a detailed performance picture of the whole and also cross validate observations in each specific perspective.

Although, the document was drafted for SQL Server 2005, it is certainly applicable to SQL Server 2008, (some new DMV's may have been introduced).

Without further adieu, to download this whitepaper entitled, SQL Server 2005 Waits and Queues - SQL Server Best Practice Article, authored by Tom Davidson, updated by Danny Tambs, and technically reviewed by Sanjay Mishra (full accreditation goes to these gentlemen), goto:

http://msdn.microsoft.com/en-us/library/cc966413.aspx --> Scroll down to the bottom to get the doc.  I hope your internet connection is good and doesn't keep you WAITing 😉