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.

SELECT
DATEADD(ms,-[wait_time_ms],getdate()) AS [ClearedDateTime]
FROM [sys].[dm_os_wait_stats]
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.

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

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

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.