Optimizing SQL Server Wait Types
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.
STATUS,
start_time,
sql_handle,
plan_handle,
statement_start_offset,
statement_end_offset,
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
|
| User I/O |
Optimising SQL PAGEIOLATCH_SH, PAGEIOLATCH_EX Wait Types
|
| Parallelism |
Optimising SQL CXPACKET Wait Type
|
| Network |
Optimising SQL ASYNC_NETWORK_IO Wait Type
|
| Commit |
Optimising SQL WRITELOG Wait Type
|
| System |
Optimising SQL MSQL_XP Wait Type
|
| Concurrency |
Optimising SQL LCK_M_S, LCK_M_X, LCK_M_IX, LCK_M_* Wait Type
|
| Concurrency |
Optimising SQL LATCH_EX Wait Type
|
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.
Leave a Reply
Want to join the discussion?Feel free to contribute!