7 Tips for Highly Effective Monitoring

Use NT's Performance Monitor to track SQL Server's performance

However, you already have a tool that gives you the most comprehensive overview of your SQL Server system's behavior: Windows NT Performance Monitor. When you install NT Server, you automatically install Performance Monitor. Because you install SQL Server on top of NT, Performance Monitor tracks your SQL Server system's performance.

You have several options available for integrating SQL Server with Performance Monitor. You also have five counters that are predefined when you start Performance Monitor from the SQL Server folder or group. I will explain your options and then look at common misunderstandings about using the five counters. As a prerequisite, you need to know how to add a new counter and configure its scale, how to change the format of the graph, and how to use the four different views available through Performance Monitor. You also need to know how to define and use .pmc (chart) and .pmw (workspace) files for saving the counters you define. (If you're unfamiliar with using Performance Monitor in the NT environment, see Michael D. Reilly, "The Windows NT Performance Monitor," March 1997.)

Your Monitoring Options
Because installation of SQL Server automatically integrates it with Performance Monitor, SQL Server is immediately collecting information for Performance Monitor. When Performance Monitor is running, it connects to the specified SQL Server and uses the login name, probe, under standard security or the login name, sa, under integrated security. (To determine what security mode you are currently running under, you can right-click the name of your server in Enterprise Manager and select Configure. The second tab of the configuration dialog box is the security tab, which identifies the type of security you are configured for.) The login account will query SQL Server to determine the values for the specific counters and objects that you've set and will then display the data in Performance Monitor.

SQL Server gathers information in two ways: Direct Response Mode or On Demand Mode. By default, SQL Server uses Direct Response Mode, which means that SQL Server gathers data automatically, without prompting from Performance Monitor. This data is available immediately to Performance Monitor, so response time is good. However, Performance Monitor displays the data one period (i.e., update interval) behind the data that SQL Server gathers. To change the update interval, choose the view you are working with from the Options menu in Performance Monitor. You will see the Update Time box, which lets you select the interval between automatic updates. You can also select to update the display manually. The default update interval is different for each of the different views. In the chart view, the default interval is every 3 seconds.

With the alternative method, On Demand Mode, Performance Monitor requests and waits for the latest data from SQL Server during each Performance Monitor update interval. With this option, you get the latest data, but response time is not as good as with Direct Response Mode.

You can change the default option after installation by running SQL Server setup.exe. When the window in Screen 1, page 208, appears, you must select the option to Set Server Options. Clicking Continue brings up the view in Screen 2, page 208. From this screen, you can change the data collection method from Direct Response Mode to On Demand Mode. A similar configuration screen is available through the SQL Enterprise Manager if you right-click the name of a SQL Server and select Configure.

If you don't want to use Performance Monitor, you can disable it (i.e., SQL Server will not accumulate data for display by Performance Monitor) in the Set Server Options screen in SQL Server setup.exe. Simply clear the SQL PerfMon Integration check box. You cannot disable Performance Monitor through SQL Enterprise Manager.

The Predefined Counters
Whether you invoke Performance Monitor from the SQL Server folder or the NT Administrative Tools folder, the executable is the same. The only difference is that one argument of the shortcut in the SQL Server folder is the file C:mssqlbinnsqlctrs.pmc, which has predefined counters for Performance Monitor to display. When you invoke Performance Monitor from the NT Administrative Tools folder, you will see a blank display. But when you invoke Performance Monitor from the SQL Server folder, you will see a display that includes five counters: Cache Hit Ratio (CHR), I/O - Transactions/sec, I/O - Page Reads/sec, I/O - Single Page Writes/sec, and User Connections. Screen 3 shows this display. By default, Performance Monitor displays these five counters. However, more than 300 counters are available.

The Cache Hit Ratio
Performance Monitor displays the CHR as a percent. Although many administrators believe that the CHR is the best overall measurement of SQL Server performance, this value isn't as useful as they think. To understand why the CHR isn't very useful, you need to understand how Performance Monitor calculates it.

One of the most time-consuming operations that SQL Server can perform is reading data from disk. SQL Server often reads data on a page-by-page basis, as needed. (A page in SQL Server is always 2KB.) You can reduce the number of disk reads by caching any pages you need in the area reserved for SQL Server's memory needs. The bigger the data cache is, the fewer pages SQL Server must read from disk.

When SQL Server executes a query, some of the data pages it needs might already be in the cache (cache reads--CR) and some pages might need to be read from a disk (disk reads--DR). The total number of pages that SQL Server accesses (logical reads--LR) is the sum of CR and DR. The CHR is the ratio of CR to LR. Performance Monitor expresses this ratio as a percent, using this formula: CHR = CR/(CR + DR) * 100. A low CHR means few of the needed pages were in the cache; a high CHR means most of the needed pages were in the cache.

To track SQL Server's performance over time, Performance Monitor averages the CR and DR values before substituting them into the formula and then graphs the resulting CHR values. Each update interval provides a new data point.

You must keep several facts in mind when looking at the value Performance Monitor reports for the CHR. When you first start SQL Server, the CHR will be low, because the cache is empty. After SQL Server has been running for a long time, you won't see many fluctuations in the graph because the displayed value is the CHR since the Performance Monitor session began. At every update interval, Performance Monitor computes the charted value for the CHR display, using the number of cache reads and the number of disk reads since the performance monitoring session started.

In addition, several background activities can affect the CHR, giving a misleading impression. These background activities are SQL Server's read-ahead threads and NT's Virtual Memory Manager.

Starting with version 6.0, SQL Server uses auxiliary read-ahead threads to help process certain kinds of queries. These threads anticipate the pages that SQL Server will need, and the threads bring those pages into the data cache before they're needed. In other words, read-ahead threads are performing disk reads for a particular query. However, Performance Monitor doesn't consider read-ahead threads when computing the CHR. If Performance Monitor finds a page already in the cache, it counts that page as a cache read, no matter how that page got into the cache. (Counters are available to monitor read-ahead threads, but because they are not predefined, I will not discuss them here.)

Another background activity that affects the CHR's reliability is NT's Virtual Memory Manager. The amount of memory that SQL Server has available is configurable, and you can configure SQL Server to use more physical memory than NT can really afford. NT's Virtual Memory Manager lets SQL Server think it has all the memory it needs for its cache. NT then takes care of the details by bringing SQL Server's cache in and out of RAM. As a result, SQL Server thinks that it is doing lots of cache reads from its big cache, but NT is really taking care of the disk reads and writes. Performance Monitor will then report an artificially high CHR.

As mentioned, a high CHR is better than a low one. But what kind of value should you strive for? The SQL Server documentation says that a CHR between 90 percent and 94 percent is good, and a CHR of 95 percent or higher is terrific. Do these numbers mean that you can relax once you reach 95 percent because performance does not increase appreciably between, a 95 percent CHR and a 99 percent CHR?

To answer this question, you need to look at the value from a different perspective. A 99 percent CHR means that 1 percent of all page reads are from disk; a 95 percent CHR means that 5 percent of page reads are from disk. In other words, you have a 400 percent increase in disk activity when the CHR drops only a few percentage points!

I/O - Page Reads/sec
The I/O - Page Reads/sec counter lets you see the number of disk reads that SQL Server thinks it is doing. Once again, you must keep in mind that if you have configured SQL Server's memory too high, NT will be performing disk reads and writes through the Virtual Memory Manager--and SQL Server doesn't include these disk reads in the I/O - Page Reads/sec counter.

I/O - Transactions/sec
In spite of its name, the I/O - Transactions/sec counter is not counting transactions. If you look at the Explain box for this counter in Performance Monitor, you will see this definition: "The number of Transact-SQL (T-SQL) command batches executed per second." In other words, this counter is keeping track of batches, which are not the same as transactions.

A batch in SQL Server is one or more commands sent to SQL Server as a group to be parsed and executed together. Every time you click the green arrow in the Query Tool, you are sending a command batch to SQL Server. A batch can result in one transaction or multiple transactions.

A transaction is a change in the state of a database or a set of actions that completely succeeds or completely fails. You can use any INSERT, UPDATE, or DELETE statement to produce a transaction. You can also use the commands BEGIN TRAN and COMMIT TRAN to combine multiple statements to produce a user-defined transaction. The commands for a user-defined transaction can be in one batch or can span several batches.

For example, suppose you want to make two changes to your database that contains publishing information. First, you want to change the location of all the authors to HI because they're all moving to Hawaii. Second, you want to delete any publishers with the location of CA because you want to get rid of all the California publishers. As Listing 1 shows, you can use the Query Tool to produce one user-defined transaction that spans four command batches. If you were to read the I/O - Transactions/sec counter in Performance Monitor after these commands ran, it would read 4 and not 1.

Now let's look at one batch that contains multiple transactions. Suppose you want to perform multiple updates on all the books listed in your publishing database. Specifically, you want to update all the prices by 10 percent, and you want to do that 100 times.

As Listing 2 shows, you can use the Query Tool to create a command batch. Without the BEGIN TRAN and COMMIT TRAN keywords, the UPDATE statement is a transaction by itself, so this batch will execute 100 transactions. However, the I/O - Transactions/sec counter in Performance Monitor will read 1.

I/O - Single Page Writes/sec
The I/O - Single Page Writes/Sec counter measures single-page physical writes. You need to get this counter as close to 0 as possible, but achieving this goal doesn't mean that SQL Server will never write to disk. SQL Server writes log records to disk whenever a transaction commits, but the I/O - Single Page Writes/sec counter doesn't include those writes. Another counter, I/O - Log Writes/sec, keeps track of writes to the transaction log.

SQL Server will also write to disk when the Lazywriter process puts pages on the free list and when SQL Server activates the checkpoint process and writes all dirty (changed) pages to disk. Both the Lazywriter and the checkpoint processes perform block writes instead of single-page writes. If the Lazywriter and the checkpoint processes are not doing their job, SQL Server will not have any free pages available when a process needs to read a data page into cache. In this case, SQL Server will need to write out a single page to make room for a new page to be read in.

User Connections
A User Connection is not a person or an application. You can make multiple connections with just one application.

For example, when you use the Query Tool from Enterprise Manager, you initiate a separate connection to SQL Server every time you click the New Query icon. Similarly, when you use an Open Database Connectivity (ODBC)-based client application, every call to SQLConnect( ) will start a new connection. The User Connections counter monitors these connections.

An Important Step
Performance Monitor is a convenient, easy-to-use tool for monitoring the performance of your SQL Server. To take full advantage of its problem-detection and resolution capabilities, you must have a thorough knowledge of SQL Server functions. You have taken an important first step by learning how to configure SQL Server to work with Performance Monitor, what each predefined counter tells you, and what you must be aware of when using each counter.