SQL Server Performance Tuning Using Wait Statistics: A Beginner’s Guide

This beginner's guide by Jonathan Kehayias and Erin Stellato covers the basics of using wait statistics; topics include the SQLOS scheduler, performance tuning, baselines, and more.

1965-waitstatspaper.png

Download the free PDF

SQL Server Performance Tuning Using Wait Statistics: A Beginner’s Guide

By Jonathan Kehayias and Erin Stellato
Brought to you by SQL Skills and Simple-Talk

Introduction

When a user application submits to SQL Server a request for data, the biggest element of SQL Server’s total response time would, ideally, be the CPU processing time. In other words, the time it takes the CPU to pull together the required data, slice, dice, and sort it according to the query specifications and send it back. However, in a busy database system, with hundreds or thousands of user requests competing for the limited resources of the database server, there will be times when a request is waiting to proceed, rather than actively processing. For example, Session A’s request may be waiting for Session B to release a lock on a resource to which it needs access.

Every time a request is forced to wait, SQL Server records the length of the wait, and the cause of the wait, a.k.a. the wait type, which generally indicates the resource on which the request was waiting. These are the wait statistics, and SQL Server exposes them primarily through two Dynamic Management Views:

  • sys.dm_os_wait_stats (or sys.dm_db_wait_stats on Windows Azure SQL Database) – aggregated wait statistics for all wait types
  • sys.dm_os_waiting_tasks – wait statistics for currently-executing requests that are experiencing resource waits

The basis of performance tuning SQL Server using wait statistics is simply that we interrogate these statistics to find out the primary reasons why requests are being forced to wait, and focus our tuning efforts on relieving those bottlenecks.

Table of Contents

  1. Introduction
  2. The SQLOS scheduler and thread scheduling
  3. Using wait statistics for performance tuning
  4. Investigating active-but-blocked requests using sys.dm_os_waiting_tasks
  5. Analyzing historical wait statistics using sys.dm_os_wait_stats
  6. Common wait types
  7. Wait Statistics baselines
  8. Summary
  9. Further reading
  10. About the authors
  11. About the technical editor

Download the free PDF