SQL Server Wait Types Library
Following the release of SQL Server Wait Types Library by Paul Randal via SQLSkills.com, I have updated my query which finds the most prolific wait types to include a link to the waits library for that particular wait type. The handy thing about the way Paul has named the posts is that they all end with the exact wait type name so this name can be pulled out from a query and appended to a fixed URL. This means we can copy the contents of the cell in the help_url column into our browser to find the entry in the SQL Server Wait Types Library.
More about the query
As sys.dm_os_wait_stats is reset when SQL server is restarted, it’s worth knowing the start time of the instance so you can get information on averages per minute or hour (or some other time scale). This is included in the query below. Please bear in mind that the contents of this DMV can be cleared out by running DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR); so server start time is not completely reliable as a measure for working out averages.
SQL server wait statistics query
[SQL]
— Declare variables
DECLARE
@sqlserver_start_time DATETIME,
@current_time DATETIME,
@mins INT,
@hours INT;
— Set variables
SET @sqlserver_start_time = (SELECT sqlserver_start_time FROM sys.dm_os_sys_info); — Needed to work when collection in dm_os_wait_stats started
SET @current_time = (SELECT SYSDATETIME());
SET @mins = (SELECT DATEDIFF(MINUTE,@sqlserver_start_time,@current_time));
SET @hours = (SELECT @mins/60 AS Integer);
— Uncomment PRINT statements for more info
— PRINT @sqlserver_start_time
— PRINT @current_time
— PRINT @mins
— PRINT @hours
SELECT TOP 10
wait_type,
wait_time_ms,
waiting_tasks_count,
waiting_tasks_count/@hours as waiting_tasks_count_PerHour,
waiting_tasks_count/@mins as waiting_tasks_count_PerMin,
wait_time_ms/waiting_tasks_count AS avg_wait_time_ms,
‘https://www.sqlskills.com/help/waits/’+wait_type as help_url — SQLSkills resource on waits. If web page not complete, find another source.
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC — Overall time spent waiting by wait type. (Could change to waiting_tasks_count DESC)
[/SQL]
Leave a Reply