As a database professional, you may often encounter situations where end-users complain about slow reports or queries. In such cases, it becomes crucial to identify the root cause of the performance issue and take appropriate actions to resolve it. One effective technique for troubleshooting performance issues in SQL Server is by analyzing wait stats.

SQL Server can be considered as a mini operating system that handles multiple concurrent users and operations. To support concurrency, SQL Server uses a three-node mechanism consisting of Running, Runnable, and Suspended nodes. The Running node represents queries that are currently executing on a CPU, the Runnable node contains queries waiting for their turn to run, and the Suspended node holds queries that are waiting for resources or other processes.

By analyzing the Suspended list, which contains queries waiting for resources, you can gain insights into the reasons for delays and the duration of the delays. The sys.dm_os_waiting_tasks DMV (Dynamic Management View) provides details such as process ID, wait type, duration, and if a query is blocked by another process. Additionally, other DMVs like sys.dm_exec_sessions, sys.dm_exec_requests, sys.dm_exec_sql_text, and sys.dm_exec_query_plan can be used to retrieve the query and execution plan associated with the suspended queries.

Here is an example query that retrieves the details of processes in the suspended list:

SELECT owt.session_id, owt.exec_context_id, owt.wait_duration_ms, owt.wait_type, owt.blocking_session_id, owt.resource_description, est.text, es.program_name, eqp.query_plan, es.cpu_time, es.memory_usage
FROM sys.dm_os_waiting_tasks owt
INNER JOIN sys.dm_exec_sessions es ON owt.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) est
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) eqp
WHERE es.is_user_process = 1
ORDER BY owt.session_id, owt.exec_context_id

By executing this query during a troubleshooting event, you can obtain a list of user queries in the suspended list along with additional details such as the query plan, program name, CPU time, and memory usage.

Let’s consider an example to illustrate the troubleshooting process using wait stats. In the example, most of the processes are blocked by process ID 209, which is in turn blocked by session ID 67. By further examining session ID 67, it is determined that it is not blocked by any other process. The root cause of the performance issue is identified as the WRITELOG wait type, indicating a delay in writing to the log file. Further analysis reveals that the data and log files are located on the same drive, causing contention in writing. Moving the log file to a different drive resolves the problem.

It is important to note that troubleshooting using wait stats can help identify performance issues that are not related to the query itself. By focusing on query tuning and other resources without considering wait stats, valuable time and effort may be wasted.

During the troubleshooting process, it is essential to be aware of common wait types and take appropriate actions. For example, the CXPACKET wait type often indicates a lack of proper indexes, and avoiding parallelism should be avoided. The ASYNC_NETWORK_IO wait type may require rewriting the application code to address delays in data acknowledgment by the client.

While the above table provides a few common wait types, there are numerous other wait types that may be encountered during troubleshooting. A comprehensive list of wait types can be found on the SQL Server wait types page.

In conclusion, troubleshooting performance issues in SQL Server using wait stats is a valuable technique for database administrators. By analyzing wait stats and taking appropriate actions based on the identified issues, you can effectively resolve performance problems and improve the overall performance of your SQL Server environment.