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

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.

  1. SELECT
  2. DATEADD(ms,-[wait_time_ms],getdate()) AS [ClearedDateTime]
  3. FROM [sys].[dm_os_wait_stats]
  4. WHERE [wait_type] = 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
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

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

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