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.

Jose

Jose

I am an Enterprise SQL Server Database Administrator with 10+ years of experience working with complex transactional environments.

Leave a Reply

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

Call Us Now

Or, Let Us Know How We Can Help

Tight On Time?

Schedule A Call: