SQL Server wait statistics

Troubleshooting SQL Server performance issues is a huge subject, but I often start by analysing the wait (sys.dm_os_wait_stats) and virtual file statistics (sys.dm_io_virtual_file_stats) in parallel.

If the performance issues are not blocking, then it is always a good idea to clear the wait statistics and then let server run for a set period (Hour, Day, Week) giving you a good set of data to work with.

Wait stats are cleared by running:

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)

However Virtual File Stats cannot be cleared. SELECT * FROM sys.fn_virtualfilestats(NULL, NULL) will display all virtual file statistics since server installation and there is no way to clear these in debugging situations.

Once you’ve resolved the performance issues, don’t forget to clear the wait statistics again – your next issue may be a blocking issue in which you need to troubleshoot using the existing wait statistics, so you’ll want to make sure the set of data is as clean as possible.

One of the best SQL Server performance troubleshooting blog posts I’ve come along is a post by  on Simple Talk, I still refer to it quite often when trying to remind myself of the best route for diagnosing SQL Server performance issues: