Professional Microsoft® SQL Server® 2008 Administration

11.3. What the Production DBA Needs to Know about Performance

The production DBA's life is considerably different from that of the developer DBA, in that a production DBA is dealing with a system that someone else may have designed, built, and handed over, either as a new or an already running system. The production DBA may also face challenges with performance on very old systems running legacy applications on old outdated hardware. In this case, the scenario changes from designing an efficient system to making the system you have been given work as well as possible on that limited hardware.

The starting point for this process has to be an understanding of what the hardware can deliver; what hardware resources the system needs; and what are the expectations of the users in terms of user response time. The key elements of understanding the hardware are processor speed, type, and cache size. Next comes memory: How much is there? What is the bus speed? Then comes I/O: How many disks? How are they configured? How many NICs are there? Having answers to these questions is just the start.

The next step is determining how each component of the system is required to perform. Are there any performance-related service level agreements (SLAs)? If any performance guidelines are specified anywhere, are you meeting them, exceeding them, or failing them? In all cases, you should also know the trend. Have you been maintaining the status quo, getting better, or, as is most often the case, slowly ...