Sql Server Waits Queues

[pic]


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 asof 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 THEINFORMATION 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 expresswritten 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 otherintellectual 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

Introduction 1
Overview 1
Purpose 1
Audience: Who should read this paper 2
Waitsand Queues: A Performance Methodology 2
Execution Model (simplified) 3
Waiter List and Wait Types 5
Dynamic Management Views (DMVs) and Functions (DMFs) 5
Sys.dm_exec_requests 5
Sys.dm_os_waiting_tasks 5
Sys.dm_os_wait_stats 6
Track_waitstats_2005 stored procedure 6
Get_waitstats_2005 stored procedure 6
Performance and Tuning Blue Prints 7OLTP blueprint 7
Common scenarios to avoid in OLTP 7
DataWarehouse blueprint 12
Common Scenarios to avoid with DataWarehousing 12
Typical resource bottlenecks 16
Memory pressure and IO subsystem issues 16
IO Stalls 16
Missing or poorly formed indexes 17
Largest IO queries. 18
Query optimizer, query plans and statistics 18
Queryplan reuse and DMVs 19
Query plan reuse and Performance counters 19
Statement level recompilation and DMVs 20
Tracking down blocking issues. 21
Retrieving statements in the waiter list 22
SQL Server 2005 Wait Types and correlation to other Performance information 22
QUEUES (Perfmon Counters) 79
PERFMON Counters, correlation, possible conclusions and actions79
Interesting PERFMON Ratios and comparisons 89
Memory Issues 91
Comparison of 32-bit memory architecture vs. 64-bit flat memory 91
64-bit flat memory vs. higher 32-bit clock speeds 92
Application Design issues 93
Recommendations 93
Conclusion 93

Introduction

This paper approaches the complex area of Microsoft® SQL Server™ performance tuning using a...