SQL Server: Buffer Manager

3 4

This object contains a number of counters related to the SQL Server buffer cache. These counters include the following:

NOTE


In the following list, "AWE" refers to the Address Windowing Extensions. AWE is used to support SQL Server's use of physical memory above 4 gigabytes (GB). SQL Server makes special system calls in order to use this much memory.

  • AWE Lookup Maps/sec Number of AWE map calls made per second for pages in the buffer pool. Because AWE uses a window of memory where the actual pages are held, mappings of that window must be done. This counter represents the number of times per second those maps are called.
  • AWE Stolen Maps/sec Number of AWE map calls made per second for pages stolen from the buffer pool.
  • AWE Unmap Calls/sec Number of AWE unmap calls made per second. At each call, one or more buffers can be unmapped.
  • AWE Unmap Pages/sec Number of AWE pages unmapped per second.
  • AWE Write Maps/sec Number of AWE map calls per second that map to a dirty buffer and that cause writing data to disk.
  • Buffer Cache Hit Ratio Percentage of pages that were found in memory, thus not requiring a physical I/O operation. This is your indicator of how well the SQL Server buffer cache is performing.
  • Checkpoint Pages/sec Number of pages written to disk by the checkpoint process. The checkpoint process causes all dirty pages to be flushed to disk. This process will reduce the recovery time and is controlled by the recovery interval configuration parameter. If this counter consistently shows a high value, your system might be experiencing checkpoints too frequently. You should configure the frequency of checkpoints based on your needs. Frequent checkpoints guarantee faster recovery time; less frequent checkpoints offer better performance. In addition, this counter monitors the number of pages written to disk on behalf of other operations that require all dirty pages to be flushed.
  • Database Pages Number of pages that currently make up the SQL Server cache. Because the SQL Server buffer cache is dynamic, this counter is one way to determine how big the cache is. You can monitor this counter to see how the size of the cache constantly changes. If the cache size changes significantly a number of times during the day, you might want to fix the cache size by using the min server memory and max server memory parameters. This is due to the fact that dynamic memory allocation and deallocation uses significant system resources such as CPU and I/O.
  • Free List Stalls/sec Number of requests per second that had to wait for a page to be freed before they could continue.
  • Free Pages Number of pages on all of the free lists. A free list is a linked list of available pages. This is the number of free (unused, but allocated) buffers available in the buffer cache. Don't worry if the number reported by this counter seems low. Remember that SQL Server dynamically creates buffers when they are needed.
  • Lazy Writes/sec Number of buffers per second written by the lazy writer. The lazy writer frees up dirty buffers by using the LRU (least recently used) algorithm.
  • Page Life Expectancy Estimated number of seconds a page will stay in the buffer pool before it is written out (if not referenced).
  • Page Lookups/sec Number of requests to find a page in the buffer pool made per second.
  • Page Reads/sec Number of physical database page reads issued per second.
  • Page Writes/sec Number of physical database page writes issued per second.
  • Procedure Cache Pages Number of pages used for the procedure cache. The procedure cache stores compiled queries.
  • Readahead Pages/sec Number of pages per second that SQL Server reads in anticipation of a user request. SQL Server anticipates requests based on previous requests.
  • Reserved Pages Number of reserved pages in the buffer cache.
  • Stolen Pages Number of pages that have been stolen from the buffer cache to satisfy a memory request.
  • Target Pages Optimal number of pages (according to SQL Server) in the buffer pool.
  • Total Pages Total number of pages in the buffer pool.


Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net