Optimizing SQL Server Wait Types

26 Mar
March 26, 2012

Optimizing SQL Server’s Wait Types can be a daunting task, there are so many interdependencies to account for, and most of the time optimising a Wait Type could mean sacrificing other system resources (such as disk space) or SQL tasks (such as INSERTs on the underlying table through index creation).

This post will give you a quick reference and overview on all the SQL Server Wait Types, as well a quick way to optimize each single or class of Wait Types (such as optimizing PAGEIOLATCH_EX, ASYNC_NETWORK_IO or WRITELOG etc.).

In order to identify what SQL Server Wait Type is holding up a particular query from completion, you can look at SQL Server’s Activity Monitor, which will list all active sub-tasks a particular SQL Server Session ID has spawned, and the Wait Type status of each of these sub-tasks. Here you will be identify the Wait Type you will need to optimize the query (or system) for.

You can easily reach SQL Server’s Activity Monitor through SSMS, by right-clicking on the instance node and selecting the Activity Monitor option, as shown in the image below. Clearly this needs to be done while you are running the SQL query you are trying to diagnose and optimize.

This will also give you the Session ID under which the query is running, this can also be used in the next query to retrieve the Wait Type being experienced by this Session ID.

    CASE wait_time WHEN 0 THEN 'Memory/CPU' ELSE wait_type END AS wait_type
FROM sys.dm_exec_requests
WHERE session_id=<session-id>

The table below will give you a quick reference on each Wait Type and how to optimize for that particular Wait Type. Obviously this is not a comprehensive list and as a particular SQL Server eco-system grows, we start noticing dependencies that might not be immediately obvious.

Wait Class (Group of Wait Types) Wait Type Optimization Strategy
General Wait Type

Optimising SQL Memory/CPU Wait Types

  • Waiting to read data from memory or executing
    CPU intensive code
  • Tune queries to read less data, i.e. use an index
  • Avoid excessive use of inline user-defined functions
User I/O


  • Tune indexes
  • Tune disks
  • Increase buffer cache

Optimising SQL CXPACKET Wait Type

  • Reduce parallelism at query level (MAXDOP)
  • Reduce parallelism at server level
  • Turn off hyperthreading

Optimising SQL ASYNC_NETWORK_IO Wait Type

  • Reduce amount of data query is returning
  • Speed up network between server and client

Optimising SQL WRITELOG Wait Type

  • Tune applications to commit data less often
  • Tune disks where transaction logs exist
  • Set isolation level appropriately
  • Size transaction log properly

Optimising SQL MSQL_XP Wait Type

  • Waiting on extended stored procedure
  • Tune extended stored procedure

Optimising SQL LCK_M_S, LCK_M_X, LCK_M_IX, LCK_M_* Wait Type

  • Tune blocking queries so locks are release faster
  • Ensure proper error handling so locks are released
  • Code applications to fetch all result rows
  • Use proper isolation levels
  • Check for orphaned processes holding locks

Optimising SQL LATCH_EX Wait Type

  • More than one session is trying to access same page of memory
  • Tune queries so they read less data from memory

Courtesy of Dean Richards, Senior DBA in Confio from Ignite

Wait Types optimization and analysis on SQL Server should be used in tandem with other SQL Server performance analysis tools, such as query performance analysis and optimization through SQL Server’s Execution Plan, SQL Server Profiler or Data Collectors.

Optimizing SQL Server Wait Types rated 5 out of 5 by 1 readers

Optimizing SQL Server Wait Types , 5.0 out of 5 based on 1 ratings

1 vote
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>