SQL Server 2005 Waits and Queues
SQL Server Best Practices Article
Writers: Tom Davidson
Updated by: Danny Tambs
Technical Reviewer: Sanjay Mishra
Published: November 2006
Applies To: Microsoft SQL Server 2005
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions
, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, email address, logo, person, place or event is intended or should be inferred.
2006 Microsoft Corporation. All rights reserved.
Microsoft, SQL Server, Windows, Window Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
Table of Contents
Audience: Who should read this paper 1
Waits and Queues: A Performance Methodology 2
Execution Model (simplified) 2
Waiter List and Wait Types 4
Dynamic Management Views (DMVs) and Functions (DMFs) 4
Track_waitstats_2005 stored procedure 5
Get_waitstats_2005 stored procedure 5
Performance and Tuning Blue Prints 6
OLTP blueprint 6
Common scenarios to avoid in OLTP 6
DataWarehouse blueprint 10
Common Scenarios to avoid with DataWarehousing 10
Typical resource bottlenecks 13
Memory pressure and IO subsystem issues 13
IO Stalls 13
Missing or poorly formed indexes 14
Largest IO queries. 15
Query optimizer, query plans and statistics 16
Query plan reuse and DMVs 16
Query plan reuse and Performance counters 17
Statement level recompilation and DMVs 17
Tracking down blocking issues. 18
Retrieving statements in the waiter list 18
SQL Server 2005 Wait Types and correlation to other Performance information 19
QUEUES (Perfmon Counters) 58
PERFMON Counters, correlation, possible conclusions and actions 58
Interesting PERFMON Ratios and comparisons 65
Memory Issues 67
Comparison of 32-bit memory architecture vs. 64-bit flat memory 67
64-bit flat memory vs. higher 32-bit clock speeds 68
Application Design issues 68
This paper approaches the complex area of Microsoft® SQL Server™ performance tuning using a methodology called Waits and Queues. By using this methodology one can 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. You, or another troubleshooter, can use this to pinpoint problem areas by correlating data from two sources: 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.
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.
We outline a methodology that considers performance from the perspective of application, SQL Server, and correlates this to the system or resource perspective. These perspectives are the primary inputs to the waits and queues methodology.
Be aware that some bottlenecks are more easily correctable than other bottlenecks. For example, a lack of query plan reuse for ad hoc SQL can be resolved by either of two methods: (1) using sp_executesql to parameterize the ad hoc SQL or (2) by replacing ad hoc SQL with stored procedures. However, completing these application changes takes time depending on the extent of the coding and testing requirements.
The purpose of this document is to help developers and database administrators in pinpointing areas in applications and solutions that interact with SQL Server 2005 and can benefit from improved performance. This paper outlines a best practice methodology and provides guidelines and thought processes to identify poor performing applications in addition to providing insight into improvement regimes. It should be noted that
, although the concepts outlined in this paper can apply to all versions of Microsoft SQL Server, the included examples are specific to SQL Server 2005 as they use some new features not available in earlier versions.
This methodology can be put to most effective use in order to discover some of the non-obvious performance issues and help in quickly identifying the root cause. There are many papers and books on performance tuning and optimization techniques for various versions of Microsoft SQL Server. This paper demonstrates features that are specific to SQL Server 2005. It embodies knowledge from a range or sources including the SQL Server development team in addition to specialist consultants working with customers.
Audience: Who should read this paper
This paper is intended for developers
, testers and database administrators (DBAs) that are involved with development or performance optimization of solutions that are based on the Microsoft SQL Server platform. This paper assumes some knowledge of SQL Server commands and a basic foundation in application performance tuning. This methodology is not intended to be a substitute for application performance testing during the development phase nor is it meant to be a substitute for other papers in this area.