Last week a new whitepaper titled SQL Server Performance Tuning Using Wait Statistics: A Beginners Guide was made available through SimpleTalk. This paper is a combination of information from Chapter 1 of my Troubleshooting SQL Server: A Guide for the Accidental DBA book (Amazon|eBook download), also published by SimpleTalk, and a series of articles created by Erin on baseline data collection techniques.
Understanding the time a session spends waiting inside of SQL Server is an incredibly important part of performance tuning and diagnosing problems. As consultants we use the information contained in the wait statistics during health checks of servers for bottleneck identification, and as a part of root cause analysis while troubleshooting problems. This whitepaper will provide you an introduction into the world of performance tuning using wait statistics in SQL Server and will explain the common wait types and what they do and do not mean in the context of performance tuning and troubleshooting. You will also find code for collecting and aggregating the information available in SQL Server DMVs about the waits that have occurred in the past and what sessions are currently waiting for in the paper to simplify getting started with understanding the activity on you SQL Servers.
Table of Contents
- The SQLOS scheduler and thread scheduling
- Using wait statistics for performance tuning
- Investigating active-but-blocked requests using sys.dm_os_waiting_tasks
- Analyzing historical wait statistics using sys.dm_os_wait_stats
- Common wait types
- Wait Statistics baselines
- Further reading
- About the authors
- About the technical editor
A big thanks goes to Tony Davis at Redgate for editing all of the content and merging the two sets of information into a single concise introduction to the world of Performance Tuning SQL Server using Wait Statistics. The whitepaper can be downloaded from the SimpleTalk website, or from the help section of our site through the link below.