When was the last time your SQL Server wait stats was cleared?

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.

By the way, this check is a part of our SQL Server Health Check service.

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.

  2. DATEADD(ms,-[wait_time_ms],getdate()) AS [ClearedDateTime]
  3. FROM [sys].[dm_os_wait_stats]
Figure 1 – Query output.

How to fix them?

  1. Try to figure out if a job or someone is clearing the wait statistics (Probably there is not a good reason).
  2. 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.

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *