Wait Stats capture script

Performance troubleshooting should begin with capturing wait stats so we can understand where SQL Server is busy. The script below captures wait stats into a table in tempdb; the script should be ran via a SQL Server Agent job or some other scheduling mechanism.

The dbo.WaitDefs table contains a listing of all documented wait stats – for comprehensive information regarding specific wait types, check Paul Randal’s wait-types library.

The dbo.wsIgnore table contains a list of wait stats that you might decide to ignore from analysis – add or remove items from that list as appropriate to your environment. The dbo.ws_header table lists a single row per execution with the RowNum, date stamp, and the number of batch requests per second. The below query displays a list of waits that occurred during the most recent two executions of the script above: