Microsoft SQL Server Buffer Cache Hit Ratio too low

Microsoft SQL Server Buffer Cache Hit Ratio too low

Occurs when the buffer cache hit ratio falls below the Buffer Cache Hit Ratio lower bound threshold. The buffer cache hit ratio is the percentage of pages found in the buffer cache without having to read them from the disk. The percentage is calculated as the total number of successful cache hits divided by the total number of requested cache lookups since the last cycle of the resource model.

Each instance of Microsoft SQL Server has a buffer cache. Data remains in the buffer cache until it has not been referenced for some time and the database needs the buffer area to record more data. Data is written back to disk only if it is modified.

Because reading from the cache is less expensive than reading from disk, you want this percentage to be high. After your system maintains a steady state of operation, this metric should achieve rates of 90 or higher. You can increase the buffer cache hit ratio by increasing the amount of memory available to Microsoft SQL Server.

Microsoft SQL Server uses dynamic memory allocation to tune itself. The ideal scenario is for the database server to run on a dedicated machine with dynamic memory allocation in effect. When needed, Microsoft SQL Server increases memory allocation if there is more than 5 megabytes of unallocated memory available. It concedes memory if unallocated memory available falls below 5 megabytes.

When the indication occurs often enough to trigger an event, the event delivers a message to the Tivoli Enterprise Console in the following format:

<application_label>: Microsoft SQL Server <MSSQLServerName> - Buffer Cache Hit 
Ratio is <MSSQLBufferCacheHitRatio>, which is below threshold of <LowerBound>.

If you have Tivoli Business Systems Manager configured for your system, Tivoli Enterprise Console forwards the message to Tivoli Business Systems Manager.

You can check the health of this resource model in the IBM Tivoli Monitoring Web Health Console. For more information, see the IBM Tivoli Monitoring Web Health Console documentation.

The indication has the following attributes:

application_class

The registered object's Tivoli Management Environment (TME) class.

application_label

The registered object's Tivoli Management Environment (TME) label.

application_oid

The registered object's Tivoli Management Environment (TME) object identifier.

application_version

The managed resource's version; for example, v7, or 2k.

LowerBound

The value set as the minimum allowable for the specified threshold.

MSSQLBufferCacheHitRatio

The percentage of the buffer cache hits to total requests over the lifetime of an instance. The percentage calculates how often a requested page is found in the buffer cache without requiring disk access. The buffer cache is the portion of the instance that holds pages of data. All user processes that are connected to the instance share access to the buffer cache.

MSSQLServerName

The instance name of Microsoft SQL Server.

This indication has the following threshold:

• Buffer Cache Hit Ratio lower bound