CollectWaitStats
Collect wait statistics from SQL Server's system views.
The three system views:
- sys.dm_os_wait_stats
- sys.dm_os_latch_stats
- sys.dm_os_spinlock_stats
have useful information which is aggregated over time. These scripts create a job to collect this information into a table every minute. That information can be used to explore how that data changes over time.
Instructions
Run CreateObjects.sql in a database on a server whose stats you want to collect.
A table will be created to store the statistics. A procedure and a sql agent job are created to collect statistics and store them into that table every minute. Data older than a week is deleted.
Another procedure s_WaitStatsHistogram is created which compares the wait_time_ms of two samples and reports the difference. This default bucket size of the histogram is 60 minutes.
To clean up the objects and jobs created here, run DropObjects.sql