What Every DBA Needs to Know about Performance

In this chapter, we lay out a lot of specific hardware recommendations that will enable you to improve the performance of your system. However, shaving milliseconds off of a transaction time isn't always worth the amount of time and hardware budget you spend to accomplish that goal. Frequently, good planning up front is worth more than clever optimization later. The three things you should know above all else are as follows: start tuning early, define performance, and focus on what's important.

The Performance Tuning Cycle

Performance tuning is an iterative process, and ideally starts at the beginning of the design process. Too often performance and optimization are tacked on at the end. Obtaining optimal performance starts with configuring the server, continuing with designing an efficient schema and specifying optimized queries, which leads to optimal index selection. The monitoring and analysis of performance can then feed back to any point in this process, right back to changes to server configuration or schema design. This system is illustrated in Figure 11-1.

Figure 11-1

You start with your first best guess about how you think your application is going to work and what resources you think it's going to use, and plan accordingly. Most often, it's only a best guess. At this point in time, you really don't know exactly how the application is going to work.

In the case of a new application, you don't have an existing system to measure. It is hoped that you will have some metrics from either an existing user base or management predictions regarding who the users will be, what they do on a daily basis, and how that would impact the new application.

In the case of an existing system that you are either moving to a new server or to which you are adding functionality, you can measure specific metrics on system resource usage, and use those as a starting point. Then you can add information about any new functionality. Will this increase the user base? Will it increase the processing load on the server? Will it change the data volume?

All this information enables you to make a good estimate of the new system's impact on resources. Even before you have implemented the new system, while testing is taking place, you have a great opportunity to start to evaluate your estimates against the actual resource requirements, and the performance you get from your test servers.

Defining Good Performance

The fundamental question that every DBA has to answer before refining a system is simple: Do we have good performance? Without either a specific target or some baseline to compare against, you will never know. Planning, sizing, testing, and monitoring will provide you with the information you need to be able to start to answer this question. Identify your critical metrics for CPU, memory, and IO; create a baseline; and then after deploying, monitor your critical metrics to answer the question.

For example, consider how performance requirements can vary in the case of an online store. Here, response time to users is critical to keep them shopping. On a database such as this, there are likely to be very clearly defined response times for the most important queries, and maybe a broad requirement that no query can take longer than two or three seconds. On a different database server delivering management reports on warehouse inventory levels, there may be an expectation that these reporting queries take some time to gather the right information, so response times as long as a few minutes may be acceptable, although there may still be some queries that have much shorter response time requirements. In yet another database the key performance criteria might be the time it takes to back up the database. In another database the key criteria might be the time to load or unload data.

The key performance criteria might be defined as a very high level requirement such as "no query must take longer than x seconds." In another system it might be that long-running operations must complete within a specific time window, such as backup must complete in an overnight window of x hours.

Focus on What's Most Important

The final essential aspect of performance is being able to focus on what's really important, knowing what you can measure, how to measure it, and what the limitations of that measurement might be. Consider a typical system. The end users' experience is the net sum of all performance from their client machine through many layers to the database server and back again. Because the focus of this book is the DBA and SQL Server 2005, we are going to focus on measuring SQL Server performance, but it's worthwhile to have a basic understanding of the big picture, where DBAs fit in, and some of the tools and metrics that may be having an impact on your system.

Figure 11-2 shows a schematic diagram of a typical Web-based architecture. This schematic is typical of any enterprise customer using the Microsoft Windows Server System Reference Architecture to implement their enterprise solution. This may be the first time that many DBAs have looked at something like this, and understood where their puzzle piece (the database) fits into the big picture.

Figure 11-2

One of the first things you notice in the diagram is the number of elements in the bigger picture. When the user calls the help desk and complains of poor performance, finding the culprit involves a lot of possible candidates, so it is important to spend time identifying which piece of the complex system architecture might be guilty.

The important thing to take away from this exercise is an understanding of both the big picture and how to focus in on what's important for you. In this book we are interested in SQL Server, databases, and the server on which SQL runs.