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 will be dealing with a system that someone else has 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 the hardware you have.

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

The next step is determining how each system is required to perform. Are there any performance-related Service Level Agreements (SLAs)? If there are any performance guidelines 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 getting worse?

The production DBA needs to understand all of this, and then know how to identify bottlenecks and resolve them to get the system performing at the required level again.

The tools that the production DBA uses to perform these tasks are as follows:

• Task Manager: Gives a quick, high-level view of server performance and use of resources

• Performance Monitor: Provides a much more detailed view of server performance

• SQL Profiler: Enables workload traces to be captured, and can report on long-running queries

• Query Analyzer: Enables long-running queries to be analyzed, bottlenecks found, and solutions developed

These tools are covered in more detail in Chapter 13.