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.
End of Article
Prev. page
1
[2]
next page -->
