SQLServer Object

The following counters are part of the SQLServer object. These counters provide statistics about global SQL Server performance:

Cache ¾ Ave. Free Page Scan

The average number of data cache buffers scanned by Lazywriter while searching for an unused buffer to replenish the free buffer pool.

Cache ¾ Max. Free Page Scan

The maximum number of data cache buffers scanned by Lazywriter while searching for unused buffers to replenish the free buffer pool.

Cache ¾ Number of Free Buffers

The number of cache buffers currently in the free buffer pool. Running out of free buffers is expensive, so this statistic should not be much lower than the free buffer threshold that is specified by the free buffers parameter of sp_configure. Lazywriter will normally ensure that the number of free buffers does not fall below the threshold. If it does, you may need to either increase the max asyn IO and max lazywrite IO parameters, and/or increase the free buffer threshold.

Cache Hit Ratio

The percentage of time that a request was found in the data cache (instead of being read from disk). Because reading from the cache is much cheaper than reading from disk, you want this ratio to be high. Generally, you can increase the cache hit ratio by increasing the amount of memory reserved for the data cache using the memory parameter of sp_configure. Note that some transactions can cause the cache hit ratio to be "artificially" high (80 - 90 percent). For example, in the course of certain transactions, a page might be requested multiple times, creating the likelihood that, after the first request, all requests would be satisfied by the data cache.

I/O ¾ Batch Average Size

The default maximum is 8. This is the average number of 2K pages written to disk during a batch I/O operation. The checkpoint thread is the primary use of batch I/O.

If you have a good disk I/O subsystem that can handle queued I/O requests (one that supports disk arrays or includes a smart SCSI controller), you can increase this maximum (and the I/O - Batch Max Size and I/O - Batch Writes/sec) using the max async IO option of sp_configure. However, setting the max async IO option too high can result in too much overhead and can be counterproductive, because it could flood the disk subsystem with writes and induce overload.

I/O ¾ Batch Max Size

The default maximum is 8. This is the maximum number of 2K pages written to disk during a batch I/O operation.

See the remarks under I/O - Batch Average Size.

I/O ¾ Batch Writes/sec

It is optimal for Batch Writes/sec to be high. This is the number of 2K pages written to disk per second using batch I/O. The checkpoint process is the primary user of batch I/O.

See the remarks under I/O - Batch Average Size.

I/O ¾ Lazy Writes/sec

The number of 2K pages flushed to disk per second by Lazywriter.

The Lazywriter is a system process whose main task is to flush out batches of dirty, aged buffers (buffers that contain changes that must be written back to disk before the buffer can be reused for a different page) and make them available to user processes by placing them in the free buffer pool.

The Lazywriter eliminates the need to checkpoint frequently for the purpose of creating available buffers. The batch I/O size used by the Lazywriter can be set by using SQL Enterprise Manager or sp_configure to adjust the server's max lazywrite IO parameter. This controls the Lazywriter's batch I/O size, and it can be tuned to maximize the I/O throughput for specific hardware platforms and I/O subsystems.

The maximum number of batched asynchronous I/Os performed by the Lazywriter can be tuned by using SQL Enterprise Manager or sp_configure to adjust the server's max lazywrite IO parameter. This parameter is comparable to max async IO, which controls batch I/O such as BCP and checkpoints, but max lazywrite IO is specific to the Lazywriter. This option should be configured only on systems with multiple disks. This parameter is dynamically configurable up to the value specified by max async IO, which makes it easier to tune this parameter. Setting this too low can hinder performance, while setting it too high could flood the disk subsystem with writes and thus slow disk reads.

Important Do not change the max lazywrite I/O value unless your primary support provider instructs you to do so.

For information on setting the max lazywrite I/O and max async I/O parameters, see the Microsoft SQL Server Transact-SQL Reference.

I/O ¾ Log Writes/sec

The number of log pages physically written to disk per second. Because all changes must be physically logged to disk before a transaction can be considered complete, overall throughput can be limited by the disk drive on which the log is located. If Log Writes/sec reaches the maximum sequential write capability of the disk drive (60 - 70 per second for a typical 12-15 millisecond seek time disk drive and more for disk arrays), the system will be limited by that maximum, so you should consider placing the log on a striped disk set to increase the I/O capacity.. For best performance, the log device should be placed on a disk or logical disk capable of optimal write performance.

I/O ¾ Outstanding Reads

The number of physical reads pending. This statistic gives you an idea of how the I/O subsystem is performing. A large number of outstanding reads over an extended time period can be an indicator that the I/O subsystem is a bottleneck and that changing to faster disks or a disk array would help performance.

I/O ¾ Outstanding Writes

The number of physical writes pending. This statistic gives you an idea of how the I/O subsystem is performing. A large number of outstanding writes over an extended time period can be an indicator that the I/O subsystem is a bottleneck and that changing to faster disks or a disk array would help performance.

I/O ¾ Page Reads/sec

The number of physical page reads per second. This statistic displays the total number of physical page reads per second across all databases. Because physical I/O is very expensive, you may be able to minimize it by using a larger data cache, intelligent indexes, more efficient queries, or by changing the database design.

I/O ¾ Single Page Writes/sec

The number of single page writes performed per second by logging and cache flushes. (Pages written out during the checkpoint process are recorded in the Batch Writes/sec statistic.) This does not include lazywriter batch statistics.

Single-page writes are generally expensive, as opposed to batch-wide IO (used by lazywriter and checkpoint). Reducing single-page write activity is important for optimal tuning. One way is to do this is to ensure that you do not run out of free buffers in the free buffer pool. If you do, single page writes will occur when waiting for an unused cache buffer to flush.

I/O ¾ Trans. per Log Record

The number of transactions that were packed into a log record before the log record was written to disk. Because log records must be physically written to disk before a transaction is considered complete, the rate at which log writes are completed is the ultimate limit on system throughput. If the log writes from many transactions are packed into a single physical write, throughput can be improved. It may be possible to increase the number of transactions per log record by adjusting the logwrite sleep parameter of sp_configure.

I/O ¾ Transactions/sec

The number of Transact-SQL command batches executed per second. This statistic is affected by all constraints (such as I/O, number of users, cache size, complexity of requests, and so on). High Transactions/sec means good throughput. It is important to note that this is command batches per second (not transactions per second).

NET ¾ Command Queue Length

The number of client requests waiting to be handled by the SQL Server worker threads. When this statistic is high, it indicates a backlog of commands waiting to be processed. To decrease the wait times, use sp_configure to increase the number of worker threads.

NET ¾ Network Reads/sec

The number of tabular data stream (TDS) packets read from the network. This statistic is an indicator of network throughput. When this statistic is high, it indicates lots of network traffic. This statistic is best used in conjunction with the other network statistics.

NET ¾ Network Writes/sec

The number of tabular data stream (TDS) packets written to the network. This statistic is an indicator of network throughput. When this statistic is high, it indicates lots of network traffic. This statistic is best used in conjunction with the other network statistics.

RA ¾ Pages Fetched into Cache/sec

Pages prefetched into cache by Read Ahead Manager.

If this counter is monitored in conjunction with the "RA ¾ Pages Found in Cache/sec" counter, can be used to determine read-ahead effectiveness. If "Pages Fetched" is high and "Pages Found" is low, then read-ahead is effective. The less effective read-ahead is, the closer the two counters become. If "Pages Fetched" is less than "Pages Found," then read-ahead is simply overhead and is not helping scan times.

Read-ahead configuration options control "parallel data scan" capabilities, which enable asynchronous read-ahead of data when SQL Server determines that pages are being retrieved in sequential order. With read-ahead (RA), separate background threads will be used to prefetch pages for a given results set. Read-ahead is used by queries, DBCC CHECKTABLE, DBCC CHECKDB, DBCC CHECKALLOC, UPDATE STATISTICS, CREATE INDEX, exporting with BCP, and the retrieval of text and image data. Read-ahead prefetches are configured based on extents. The read-ahead sp_configure options are RA cache hit limit, RA cache miss limit, RA delay, RA pre-fetches, RA slots per thread, and RA worker threads. For more information about read-ahead, see the Microsoft SQL Server Transact-SQL Reference.

RA ¾ Pages Found in Cache/sec

Pages found to be already in cache by Read Ahead Manager. (Shows how many 2K pages were already found to be in the cache.)

RA ¾ Physical Reads/sec

Physical reads (each consisting of 8 2K pages, meaning 16K reads) issued by Read Ahead Manager. Provides a general idea of how busy read-ahead is in terms of actual physical IO.

RA ¾ Slots Used

Total slots used. Shows you how many slots you are using (compared to how many you have configured). Also indicates the number of queries using read-ahead at any given time. If this counter hits the maximum slots ceiling, you should increase the number of slots using sp_configure.

User Connections

The number of user connections. Since each user connection consumes some memory, configuring overly high numbers of user connections could impact throughput. User connections should be set to the maximum expected number of concurrent users.

If a performance slowdown coincides with an increased number of user connections, then you can use sp_configure to restrict user connections, lower the number of worker threads, or increase memory.