Performance-Monitoring Approach

Taking a closer look at the performance-monitoring handles depicted in Figure 37.1, you can see that SQL Server spans all of them. SQL Server must process requests submitted to it via the network, service these requests with one or more processors, and rely on accessing a request's data from both memory/cache and the disk system. If you maximize utilization on these resources from the point of view of SQL Server and the operating system, you will end up with a well- tuned database server.

One area of interest is the amount of network traffic that is handled by SQL Server and the size of these network requests. Another area of interest is the ability of the available processors to service the load presented to them by SQL Server without exceeding certain CPU saturation. This chapter will look at the overall memory utilization of what is available on the server and how effective SQL Server is utilizing the disk system.

In general, you will want to start from the bottom with the network and work your way up into the SQL Server-specific elements. This will quickly serve to isolate certain issues that are paramount in performance tuning. In each of these areas, this chapter will provide you with a minimum list of detail performance handles or counters that can be looked at. This approach can be summarized into the following steps:

1. Understand and monitor network request characteristics as they relate to SQL Server and the machine on which SQL Server has been installed. This will mean a complete profile of what is coming into and sent back out over the network from SQL Server.

2. Understand processor utilization. It might be that the processing power is the biggest issue. You need to get a handle on this early.

3. Understand and monitor memory and cache utilization. This is the next detail step into the overall memory usage at the operating system point of view and into the memory that SQL Server is using for such things as data caching, procedure caching, and so on.

4. Understand and monitor disk system utilization. You are often rewarded for a simple disk configuration or data storage approach. You won't know you have a problem unless you look for it. Techniques that are often used include disk stripping, isolation of logs from data, and so on.

To summarize, repeat steps 1 through 4 on a regular basis. Your continued success and salary increases will reflect your diligence here. For each step, certain tools and facilities will be available to you to use that gather all that is needed to identify and monitor performance issues. The Performance Monitor facility of Windows NT/2000 will be explored first.