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:\mssql\binn\sqlctrs.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.)
Prev. page
[1]
2
next page 
