SQL Buffers Test

This internal test also monitors the usage of buffer memory of a Microsoft SQL server.

Target of the test : A Microsoft SQL server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every Microsoft SQL server monitored

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed
  2. Host – The IP address of the Microsoft SQL server.
  3. Port - The port number through which the Microsoft SQL server communicates. The default port is 1433.
  4. ssl - By default, the SSL flag is set to No, indicating that the target Microsoft SQL server is not SSL-enabled by default. To enable the test to connect to an SSL-enabled Microsoft SQL server, set the SSL flag to Yes.
  5. instance – The name of a specific Microsoft SQL instance to be monitored. The default value of this parameter is “default”. To monitor a Microsoft SQL instance named “CFS”, enter this as the value of the “instance” parameter.
  6. ISPASSIVE – If the value chosen is yes, then the Microsoft SQL server under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.
  7. useperfmon – By default, this flag is set to Yes, indicating that this test uses the Windows Perfmon utility by default to pull out the metrics of interest. To instruct the test to use queries for metrics collection and not Perfmon, set this flag to No. Typically, when monitoring a Microsoft SQL server in an agent-based manner, its best to go with the default setting – i.e., use Perfmon for metrics collection. However, when monitoring the Microsoft SQL server in an agentless manner, its ideal to use queries instead of Perfmon to collect the required metrics. In such cases, set this flag to No
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Buffer cache hit ratio:

This value indicates the percentage of pages that are served from the server’s buffer cache (i.e., without requiring a read from the disk).

Percent

This value is the ratio of the total number of cache hits to the total number of cache lookups since the server was started. Because reading from the cache is much less expensive than reading from disk, this ratio should ideally be high. Generally, one can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server.

Page reads:

This indicates the number of database pages that were physically read per second. 

Pages/Seconds

Because physical I/O is expensive, one may be able to minimize the cost, either by using a larger data cache, intelligent indexes, more efficient queries, or by changing the database design.

Page writes:

This indicates the number of database page writes that are issued per second.

Writes/Seconds

Page writes are generally expensive. Reducing page-write activity is important for optimal tuning. One way to do this is to ensure that you do not run out of free buffers in the free buffer pool. If you do, page writes will occur while waiting for an unused cache buffer to flush.

Target Pages:

The ideal number of pages that should be in the buffer pool for optimal performance

Number

 

Total pages:

The current total number of pages in the buffer pool. This value includes the number of pages in the database pool, free pool, and stolen list.

Number

For optimum performance, the total pages must be close to the target pages value.

This measure will not be available for Microsoft SQL Server 2012.

 

Free pages:

The total number of pages currently in all free lists

Number

This measure will not be available for Microsoft SQL Server 2012.

 

Stolen pages:

The number of buffer pool pages used to satisfy other server memory requests

Number

This measure will not be available for Microsoft SQL Server 2012.

 

Lazy writes rate:

Indicates the number of lazy writes per second.

Writes/Seconds

Lazy writes are buffers written by the lazy writer.  The lazy writer is a system process that flushes 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 makes them available to user processes. The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers.

Keeping the number of lazy writes low can enhance performance. A supply of buffers available for immediate use keeps the number of lazy writes low. Before a requested page can be brought into memory, a free buffer must be available in the buffer pool. If no free buffers are available, an existing buffer must be reused. When an existing buffer has to be reused, many buffer pages might have to be searched in order to locate a buffer to reclaim for use. If the buffer found is marked as dirty or modified, the buffer manager must first write the changes to disk before the page can be reused and assigned to the requesting process. This results in a wait for the requesting process. Waiting processes can degrade performance.

Page life expectancy:

Indicates the number of seconds a page will stay in the buffer pool without references.

Secs

A high value of this measure indicates that your page stays longer in the buffer pool (area of the memory cache), thereby leading to higher performance. This is because, every time a request comes in, the probability of that request been served by the cache is higher; this  in turn significantly reduces direct disk accesses, which are relatively more expensive operations.

A value too low for your system indicates that pages are being flushed from the buffer pool too quickly.

The target on an OLTP system should be at least 300 seconds (5min). When under 300 seconds, this may indicate poor index design (leading to increased disk I/O and less effective use of memory) or, simply, a potential shortage of memory.

Free list stalls:

Indicates the number of requests per second that had to wait for a free page.

Stalls/Sec

Free list stall rates of 3 or 4 per second indicate too little SQL memory available.

Page lookups

Indicates the number of requests per second to find a page in the buffer pool during the last measurement period.

Lookups/sec

When a query needs data that is not already in memory (buffer pool), SQL Server performs a "page lookup". This involves locating the required data page on disk, reading it into the buffer pool, and then accessing the necessary rows or information from that page.

A high value of this measure can slow down query performance because fetching data from disk is slower compared to accessing it from memory.