Building a Solid Baseline

Abstract

In the previous chapter we spent a great deal of time describing and using the various methods of accessing Wait Statistics information. Most of those methods focused on using that information for detecting performance problems that are presently occurring. While it is possible to find the exact cause of the performance problem using these real-time methods, it requires a deep knowledge of the various Wait Types and—most important—experience in the performance of your SQL Server. If you are managing only one SQL Server instance you can get yourself familiar with the way it reacts under different circumstances relatively quickly. If you are managing hundreds of SQL Server instances, getting yourself familiar with the way they perform is impossible. Because SQL Server Wait Statistics are largely based on the workload of your SQL Server instance, no two SQL Server instances will have the same Wait Times for the same Wait Types. This makes detecting possible problems difficult, because we can’t just say “because the CXPACKET Wait Type has a Wait Time of 20,000 milliseconds we are having a problem.” It all depends on the configuration and workload of your system. One SQL Server instance can have 20,000 milliseconds (20 seconds) of Wait Time every minute spent on the CXPACKET Wait Type and experience no performance problems, while another instance has 1,000 milliseconds of Wait Time and users are constantly complaining about performance.