Search code examples
sql-server-2012waitdatabase-performancedbcc

How safe is to clear Wait stats


I have been facing performance issues in the production server; and while reading it about on internet I came across @Brent Ozar article about wait stats.

I want to try that but I am not sure how safe is it to run. My production environment is constantly occupied with SSIS jobs and I don't want to kill any job or server. So, I have few questions

  • Is it safe to run when queries or SQL Jobs are running on server

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

DBCC SQLPREF("sys.dm_os_latch_stats",CLEAR);

  • What is the difference between update stats and clearing wait stats?

Solution

  • Clearing wait stats has no affect on performance of SQL Server. It would just remove information related to accumulates wait stats. Now you should have a valid reason to do it and believe me lot of DBA's and SQL Server users do it quite often when troubleshooting performance issue. Only issue is that you loose valuable information about wait stats. But there is a way to get over with it before clearing wait stats run sys.dm_os_wait_stats and get current output of wait stats now clear it and start your monitoring. Atleast you would have statistics before clearing.

    •What is the difference between update stats and clearing wait stats?

    They both are not related to each other in any way. Statistics (one which you are referring via update stats) is distribution of data. It is how SQL Server data is distributed and is used by SQL Server in cardinality estimation and helps optimizer to prepare cost bases GOOD plan for a query. Clearing wait stats(statistics about on what resource the query was waiting) would not affect SQL Server data distribution statistics.