SQL Server Best Practices Article

By Tom Davidson

Updated by: Danny Tambs

Technical Reviewer: Sanjay Mishra

Introduction

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.

Included in This Document

• Introduction

• Overview

• Purpose

• Waits and Queues: A Performance Methodology

• Execution Model (simplified)

• Waiter List and Wait Types

• Performance and Tuning Blue Prints

• Typical resource bottlenecks

• SQL Server 2005 Wait Types and correlation to other Performance information

• QUEUES (Perfmon Counters)

• Memory Issues

• Application Design issues

• Waiter List and Wait Types

• Recommendations

• Conclusion

Download

Performance Tuning Waits Queues.doc
880 KB
Microsoft Word file

Get Office File Viewers