Category: Performance
Item: Wait statistics
What are SQL Server wait statistics?
They are data about all the waits encountered by SQL Server threads at the instance level.
Using the DMV sys.dm_os_wait_statistics, you can return information that SQL Server is permanently tracking – Why execution threads have to wait.
Why should you care about them?
You should not clear your wait statistics data frequently.
Wait statistics are one of the most important metrics you can use when troubleshooting performance issues in SQL Server.
There are some valid reasons to clear wait stats, but it shouldn’t be done arbitrarily.
How to discover when SQL Server wait stats were cleaned?
You can use the query below to return the date of the last cleaning.
SELECTDATEADD(ms,-[wait_time_ms],getdate()) AS [ClearedDateTime]
FROM [sys].[dm_os_wait_stats]
WHERE [wait_type] = 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
How to fix them?
- Try to figure out if a job or someone is clearing the wait statistics (Probably there is not a good reason).
- The result from the script above will give you initial indications for an analysis.
More information
Microsoft – SQL Server 2005 Waits and Queues.
Microsoft Tech Community – SQL Server Wait Type Repository.
Microsoft – sys.dm_os_wait_stats.
