SQL Server Performance Tuning Tips – Wait Statistics
Wait statistics are one of commonly overlooked ways to quickly find out what is causing your SQL Server to be slow. One of the reasons that they are commonly overlooked is that without a monitoring tool, or your own custom monitoring tools, it is difficult to see how they are trending over time.
When someone reports that the database was slow yesterday at 2:00pm, do you know how to determine what was causing it to be slow? You can check the logs, you can look at the history of running jobs, and you might even ask around to see if anyone was doing anything unusual at that time. If you are tracking wait statistics, it is quick and easy to zoom in on a point in time and see exactly what queries were slow and why they were slow.
You might be thinking the following:
- Did someone run a slow ad-hoc query?
- Was there something wrong with the network?
- Did someone run an unscheduled SSIS ETL package?
- Was an index being rebuilt?
- Did someone change application code?
- Was there a hardware failure of some kind?
- Was DBCC CheckDB running?
What are Wait Statistics
Whenever SQL Server is waiting for something to happen it logs that information as a WAIT. If your SQL Server is waiting on I/O, that gets logged. If it’s waiting on network traffic to another SQL Server, that gets logged. If your database is waiting on a transaction to complete, that gets logged as well. Most everything that SQL Server waits on gets logged. However this information doesn’t stay around for long.