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.
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.
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.
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.
- Understanding SQL Server Wait types
- Identify Disk Bottleneck in SQL Server using Perfmon Disk Counters
- Understanding Resource_Semaphore wait type
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.
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018