Performance debugging with DMVs

SQL Server 2005 introduced a great innovative way of performance debugging – the DMVs. In short, the DMVs collect statistical information behind the scenes and give a very simple way of querying this information from the SSMS. All this happens incrementally, where the start point is the startup of the SQL instance, and the end point is the shutdown / restart. This means that sometimes, depending on how long the instance has been up, the results can have some latency until they catch up with reality. I haven’t said anything new and exciting so far. However, now I will: 🙂 there is a way to reset the collected data for some DMVs, without having to restart the SQL Server. In detail, there is a way to reset the data for only 2 DMVs: sys.dm_os_wait_stats and sys.dm_os_latch_stats.

Here is the syntax:DBCC SQLPERF(“sys.dm_os_wait_stats”,CLEAR);