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

Understanding the time a session spends waiting inside of SQL Server is an incredibly important part of performance tuning and diagnosing problems during normal troubleshooting. 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.

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

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.

Download1960 downloads