Wait Stats over Time

Looking at Wait Stats over time while a specific piece of work is taking place can be a very helpful method for troubleshooting performance issues. SQL Server exposes wait stats so you can see what specific resources are causing contention. Waits include things like waiting on memory, CPU, disk, access to tables, and a vast array of other resources.

    Ok, I’m waiting!

I use the following code to obtain stats over a given period; by default 10 minutes.

Microsoft provides great documentation on wait stats, including:

  • https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql
  • https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-wait-statistics-object
  • https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store
  • https://blogs.msdn.microsoft.com/psssql/2009/11/02/the-sql-server-wait-type-repository/

This Duck Duck Go search also turns up a fair amount of reading directly from Microsoft.

This post is part of our series on SQL Server Tools, where we cover helpful scripts that we use day-to-day.