Should We Clear Wait Statistics Data from DMV sys.dm_os_wait_stats in SQL Server?

Should we clear or reset Performance Wait Statistics Data present in DMV sys.dm_os_wait_stats in SQL Server? The answer to this question is depends on case to case. Ideally, you should not clear your wait statistics data frequently. Best way is Do Not clear wait stats data until it is required to gather information for any performance issue. Although, clearing wait stats has no impact on SQL Server performance. It will just remove information related to accumulated wait stats.

Should We Clear Wait Stats Data in SQL Server?

We do not recommend to clear the wait stats data frequently because all valuable information about wait statistics will be vanished if you clear them. Always try to capture wait statistics information before clearing wait stats. This way you would have both before and after data. You can use sys.dm_os_wait_stats DMV to get the SQL Server wait statistics information.

Generally, we reset or clear wait stats information to capture actual data during a given time duration because this DMV does not show only current data. The DMV sys.dm_os_wait_stats accumulates all the counts and wait times since last time SQL Server started or its stats got cleared by dbcc sqlperf command.

Here I will show you how to clear the SQL Server wait statistics and the difference between data captured before clearing the wait stats and after clearing the wait stats.

Get Wait Statistics Data

The DMV sys.dm_os_wait_stats provides essential metrics for diagnosing SQL Server performance problems. This DMV captures all the details like waiting requests counts, signal waits and waiting time since last time SQL Server instance got started or last time you have cleared your wait stats using DBCC SQLPERF command. Run below command to get performance wait stats details of your SQL Server instance.

--Get Wait Statistics using DMV sys.dm_os_wait_stats
SELECT * FROM sys.dm_os_wait_stats
Order by wait_time_ms desc

You can see the details about all the wait types on your SQL Server instance. You can also see the total wait time in milliseconds. This data is very important if you are dealing with any performance issue but as i discussed above details like total wait time, total waiting tasks counts are the accumulated/incremental values.

Get SQL Server Wait Stats Data

Reset or CLEAR Wait Statistics

Now you have captured wait stats information since your SQL Server was started. If you want to get only current waiting task counts or waiting time then you need to reset or clear wait statistics data using dbcc sqlperf command. Run below command to clear all wait stats data on your SQL Server Instance.

DBCC SQLPERF(“sys.dm_os_wait_stats”, CLEAR)

You can see I have successfully executed this command in below screenshot.

Clear Wait Stats

Now we will again run same command to get the wait statistics data. I ran it and I get below information that is showing different than the one showing before clearing wait stats.

Get wait stats after clearing its older data

You can see how you will lose valuable information if you will clear your wait stats without capture its earlier data. This way you can also better analyze your database wait types, waiting tasks to reach on any conclusion to fix the issue.

Related Articles

Now you understand that we should not reset or clear wait stats frequently because wait stats data is very crucial to deal with any performance issue. You can reset or clear the wait stats but make sure to capture the data before clearing the wait stats. I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:
Summary
Should We Clear Wait Statistics Data in SQL Server?
Article Name
Should We Clear Wait Statistics Data in SQL Server?
Description
Should we clear or reset Performance Wait Statistics Data in SQL Server? The answer to this question is depends on case to case. Ideally, you should not clear your wait statistics data frequently. Best way is Do Not clear wait stats data until it is required to gather information for a particular time duration to fix any performance issue. Although, clearing wait stats has no impact on SQL Server performance. It would just remove information related to accumulates wait stats.
Author
Publisher Name
www.techyaz.com

You may also like...

Leave a Reply

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