Free SQL Help for Devs, DBAs, and the Curious

In the world of SQL Server, understanding wait statistics is crucial for effective performance tuning. SQL Server uses wait statistics to track the amount of time a thread has to wait before it can get the resources it needs to execute a task. This data is incredibly valuable, as it allows you to pinpoint bottlenecks and areas of contention in your system, leading to more efficient performance tuning efforts.

To access wait stats in SQL Server 2012 and 2014, one can use the DMV sys.dm_os_wait_stats. This DMV provides a snapshot of the cumulative wait statistics since the last time the SQL Server instance was restarted or the statistics were manually cleared.

SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

However, it's important to remember that these wait stats are cumulative and can sometimes be misleading when diagnosing performance problems. Therefore, it is often beneficial to take periodic snapshots of the wait stats and analyze the differences between these snapshots.

In MySQL 5.6, wait statistics can be accessed through the Performance Schema. The events_waits_summary_by_thread_by_event_name table provides a summary of wait events, grouped by event name and thread.

SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.events_waits_summary_by_thread_by_event_name
ORDER BY SUM_TIMER_WAIT DESC;

In DB2 10.5, you can use the MONREPORT.CURRENTSQL() table function to retrieve the current SQL statement for each application, along with the associated wait time and wait type. This can be particularly useful for identifying queries that are causing significant waits.

SELECT APPLICATION_HANDLE, STMT_TEXT, TOTAL_WAIT_TIME, WAIT_TYPE
FROM TABLE(MONREPORT.CURRENTSQL());

Azure SQL also provides a DMV for wait statistics, sys.dm_os_wait_stats. However, because Azure SQL is a shared service, the output from this DMV includes waits for all databases on the same logical server. To filter out waits for other databases, you can join with sys.dm_exec_requests on the session_id column.

SELECT w.wait_type, w.waiting_tasks_count, w.wait_time_ms, w.max_wait_time_ms, w.signal_wait_time_ms
FROM sys.dm_os_wait_stats AS w
JOIN sys.dm_exec_requests AS r ON w.session_id = r.session_id
WHERE r.database_id = DB_ID();

Understanding and interpreting wait stats is a complex task but it's an essential skill for anyone involved in SQL Server performance tuning. By identifying and focusing on the most significant wait types, you can effectively target your tuning efforts and significantly improve the performance of your SQL Server instances.