This metric gives the ratio of the data pages found and read from the SQL Server buffer cache. Pages not found in the buffer cache must be read from the disk, which is significantly slower and affects performance.
Ideally, the SQL Server or Oracle Database would read all pages from the buffer cache, and there would be no need to read any from the disk. In this case, the ratio value would be 100. There is a close relationship between this and page life expectancy – see here. Low PLE will always correlate with lower cache hit ratios.
Find out how you can save hours of work when investigating the root cause of this issue.
Symptoms
No longer caching data pages in memory, reads from disk are higher, possible memory bottleneck – The Database server is running slow.
Impact: High
Slow SQL responses will degrade the user experience, resulting in poor efficiency of your organization’s operations.
Expected behavior :
There is no standard metric for the buffer cache hit ratio. Our recommended value for it is over 90. When better performance is needed, the minimal acceptable value is 95. A lower value indicates a memory problem. This alert indicates that your threshold levels have been crossed and must be investigated.
Considering the cache usage by single queries, it should not be more than dozens of MBs.
Possible causes for low ratio
1- Insufficient RAM Priority: High
The more memory your server has, the more cached disk reads and writes it can perform. Lack of system memory can cause high non-cached disk reads and writes. Adding memory to your server can help lower physical disk access.
In SQL Server, data is stored in 8 KB pages. When data is required for input/output, the page is first read from the disk and stored in memory. The area allocated is called the Buffer Pool. Once a page is loaded into the pool, all subsequent access is very fast and efficient, whereas disk I/O is relatively slow.
In Oracle, the equivalent of SQL Server’s Buffer Pool is called the Buffer Cache. The Buffer Cache in Oracle serves a similar purpose: it’s a portion of the system’s global area (SGA) allocated to store data blocks read from data files. The data is stored in data blocks, which are the smallest unit of storage in the database. These data blocks are typically 8 KB in size, similar to SQL Server.
Problem identification:
Check the maximum server memory setting in SQL Server, comparing it with the existing total RAM memory.
Compare the ratio of physical RAM to database size – aim for RAM to be equal to or more than 20% of the largest database.
Unlike SQL Server’s explicit setting for maximum server memory that limits the amount of memory SQL Server can use from the OS, Oracle’s approach is more flexible and dynamic with parameters like SGA_TARGET and PGA_AGGREGATE_TARGET adjusting dynamically based on workload and available resources.
Hands-on approach
- To compare the maximum server memory setting in SQL Server with the existing total RAM memory, you need to enter the SQL Server Management Studio, log in, and look at the instance properties. Parallelly, enter the task manager and view the current performance, which changes every moment. Here, you don’t have historical data on it.
- .Check through an SQL script for the biggest data files.
- Compare the performance of queries using high cache by running a performance analysis. You can use SQL Server Profiler or another similar tool. This action might take at least a few hours. overloads your server and is valid only to the current time; you can’t know what happened days or weeks before.
- Monitor the buffer cache hit ratio using SQL Server performance counters or DMVs (dynamic management views).
Get the answer in just seconds!
Our solution saves you all the investigation of the hands-on approach above.
If the problem is a lack of memory dedicated to the SQL Server, you’ll simply get notified and the source of this issue is clear!
Along with the Buffer Cache Hit alert, a low Page Life Expectancy alert can immediately be seen on the Dashboard.
Recommended action :
Once you have enough RAM memory, SQL Server will be able to store more data in the buffer cache, which can improve the buffer cache hit ratio.
If RAM is less than 20% of the largest database, configure the SQL Server max memory to utilize all the possible memory without impacting other OS processes.
Allocate an optimal proportion of physical RAM to Buffer Pool, but leave sufficient RAM for other Operating System operations. Set a value in the ‘maximum server memory’ option of the ‘server memory options’ page that leaves enough memory for the OS itself. We recommend around 4 GB less than the amount of RAM installed.
If the max SQL Server memory is already the maximum possible, try to enhance SQL Server performance through the further issue analyses presented in this article before adding RAM to your machine, which can be a costly solution.
2-Missing or corrupt indexes Priority: High
Missing indexes means SQL Server is suggesting that your query could run faster with an index. In cases of corruption, 99% of the time, corruptions are disk or disk driver/firmware related.
Problem identification:
Investigate missing or corrupt indexes issues that could improve the buffer cache hit ratio in SQL Server following these steps:
1. Identify the missing or corrupt indexes by running a performance analysis. You can use SQL Server Profiler or other performance analysis tools. This step is complicated, might take hours of work, and you can’t guarantee precise results when checking the online status with no historical events.
2. Think about which appropriate indexes would optimize the queries. You can use the Database Engine Tuning Advisor or other tools to help identify the missing indexes. This mission might be complicated, requiring a highly skilled DBA that can view a complete SQL query plan that might be long and complicated.
3. Check the existing indexes for corruption. If any index corruption is detected, rebuild or repair the indexes.
4. Monitor the buffer cache hit ratio using SQL Server performance counters. You lay on current moments.
5. If the buffer cache hit ratio is still low, you may need to investigate the issue further.
Our solution implements an automatic algorithm on each query session, looking for missing indexes or non-optimal query plans.
You can simply check our recommendation for missing indexes or plan improvement in most Cache-consuming queries.
For each query, you can also easily see their code and download their plan.
Recommended action :
Using our “missing index” and “plan improvement” recommendations, you can refer to your system design team or our expert DBA Team. In case of corrupted indexes, direct your storage management team or our support experts for help. One possible remedy in cases where the corruption is repeating is to clone the database onto a new hardware configuration.
3- Queries saturating the Buffer Pool Priority: Low
These causes of low PLE should be transient, not persisting over long periods. This may be due to :
- getting large memory grants
- displacing large numbers of pages in memory with new ones
- modifying many pages, forcing them to be flushed to disk
- many concurrent (or some substantial) queries
Problem identification:
Try to target which queries are the most memory-consuming, consider looking for larger queries.
1. Identify the queries that are consuming a large amount of memory by running a performance analysis. You can use SQL Server Profiler. This step may be complicated and take hours (or days) of work, and you can’t guarantee precise results when checking online statuses with no historical events.
2. Look for a way to optimize the queries by reducing the amount of data they retrieve or by tuning their execution plans. This mission might be complicated, requiring a highly skilled DBA that can view a complete SQL query plan that might be long and complicated.
3. Try to target which queries are the most memory-consuming, consider looking for larger queries. This can help reduce the amount of memory each query consumes and allow more data to be stored in the buffer pool.
4. Monitor the buffer cache hit ratio.
You’ll be immediately notified if there are long-running queries by our solution!
Along with the Buffer Cache Hit alert, this long-running queries alert will show on the Dashboard.
Simply check our recommendation for missing indexes or plan improvements and save hours of work looking for the source cause of this issue.
Recommended action :
Optimize the most cache-consuming queries’ performance. This can help reduce the amount of memory each query consumes and allow more data to be stored in the buffer pool. The “missing index” and “plan improvement” recommendations found in our platform are a starting point for your expert DBA to evaluate and design improved queries. You can also send this request to our expert DBA team.
In cases where they are re-occurring or showing a tendency to increase in magnitude (lower PLE), investigate the other possible causes below.
4- Insufficient CPU power Priority: High
A continually high usage rate may indicate the need for a CPU upgrade or the addition of multiple processors. Alternatively, high CPU usage may indicate a poorly tuned application or SQL code. Optimizing the application can lower CPU utilization.
As detailed in the CPU utilization article, Business Intelligence applications typically take over 80% of CPU capacity, compared to SQL database applications, where CPU usage below 70% is considered normal.
Problem identification :
Investigate the CPU usage over time and analyze if there are non-optimal queries that could consume less CPU through plan improvements or indexing.
- Use the profiler or another similar tool to analyze if there is a high CPU usage issue. Beware that this method may heavily load the CPU, and you can’t keep it running for too long because it would cause even more slowness, and the file would consume much disk space.
- Identify the queries that are causing the high CPU usage. Use SQL Server Profiler or other performance analysis tools to identify the most CPU-consuming queries.
- Look for a way to optimize the queries by creating appropriate indexes, modifying the query plan, or rewriting the queries. Without any tool that indicates where to focus in order to improve query performance, this task might take hours or days of work.
- Monitor the CPU usage and buffer cache hit ratio over time to ensure that the changes made have improved the performance. Probably you would be able only to view the current status with no way of comparing.
Recommended action :
Optimize the most CPU-consuming queries’ performance. The “missing index” and “plan improvement” recommendations in our platform are a starting point for your expert DBA to evaluate and design improved queries. You can also send this request to our expert DBA team.
5- DBCC CHECKDB running Priority: Low
CHECKDB is very resource intensive – probably one of the most resource-intensive things SQL Server performs. Introducing this process as an additional I/O load on top of the regular SQL Server load means that disk heads will constantly be moving.
Problem identification :
Check if there is a daily schedule running DBCC CHECKDB commands for each Database.
Hands-on approach
- Enter the SQL Server Management Studio, write a script, or check each DataBase manually whether there’s a daily DBCC CHECKDB schedule.
- Else, look for a maintenance plan or a job involving DBCC CHECKDB commands.
- If there is a daily schedule running DBCC CHECKDB commands, check whether these queries cause a low buffer cache hit ratio while this task is running. This might be hard to follow.
Recommended action :
We suggest running any essential CHECKDB processes to a time of minimal SQL Server load, or alternatively on backup copies of the databases offline.
6- Index Rebuilds running Priority: Low
An index rebuild operation will always build a new index, which means extra space is required to build the new index before dropping the old one; a lot of CPU and I/O resources are required, which can overload the system.
Problem identification :
The index rebuild query execution plan is non-optimal to the server’s performance.
Hands-on approach
- Enter the SQL Server Management Studio, go to the maintenance plan, and check for rebuild index task existence.
- Else, go to sql server agent and look for a job doing rebuild index operations.
- Check the routine of the tasks, and check on overload on OS resources commands while this task is running. For this, you may need special scripts and Profiler-like tools which may take long hours.
Recommended action :
If it occurs frequently, it can be corrected by changes in the indexing procedure, for example, by switching to a strategy in which fragmentation is analyzed every night, and only the fragmented indexes are processed. Our SQL Support Center experts are available to analyze and assist.
