Sql server 2005 Waits and Queues

səhifə	1/10
tarix	18.07.2016
ölçüsü	0.86 Mb.

1 2 3 4 5 6 7 8 9 10

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

Copyright

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.

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.

Introduction 1

Overview 1

Purpose 1

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

Sys.dm_exec_requests 4

Sys.dm_os_waiting_tasks 5

Sys.dm_os_wait_stats 5

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

Recommendations 69

Conclusion 69

Introduction

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.

Overview

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.

Purpose

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.