SQLServer:Buffer Cache Hit ratio is a good indication of POSSIBLE memory bottleneck?

Question

I am looking for an indication (if any) that would either disregard the memory bottleneck option, or accept it, or make me investigate further.

for instance:

page life expectancy:

SELECT [object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

enter image description here

ON this occasion, on my current environment, I have a server that has 8 GB of RAM, I would ask management for more memory. I believe this is a memory bottleneck.

what is using the RAM would be another investigation.

is this email below valid to identify possible memory bottlenecks?

we need to look at SQLServer:Buffer Cache Hit ratio

If that ratio is less than 95% than the server is under memory pressure

Hope this helps,

enter image description here

• Shanky: Please dont base your suggestion about memory on Buffer Cache hit ratio. Please read Great SQL Server debate about BCHR as fact I never rely on this counter. You can rely on PLE but you must see PLE for all NUMA nodes(if you have)
• Shanky: The PLE value seems to be very less but other counters are fine. You tagged question with two versions, I can give you list of counters to monitor memory usage but since there are 2 versions involved, their is bit difference. What version are you looking at exactly

Answer 1

We need to look at SQLServer:Buffer Cache Hit ratio.If that ratio is less than 95% than the server is under memory pressure

Stop looking at Buffer Cache hit ratio to determine memory pressure. This is because with read ahead mechanism in SQL Server more than enough pages are alredy in buffer pool to satisfy the query so BCHR does not gives accurate figure about memory pressure. You might even see that BCHR even would not drop when there is memory pressure. All these has been explained in Great SQL Server Debate about BCHR

PLE output you posted seems really low but we cannot just use one counter to gauge memory pressure. PLE is more indication of I/O activity on server. It could be possible that due to heavy I/O activity PLE plummeted. If you note Target and Total server memory still remains same. Which is good sign.

For Edition upto 2008 R2. You can use below counters

1. SQLServer:Buffer Manager--CheckpointPages/sec:

2. SQLServer:Buffer Manager--Memory Grants Pending:

3. SQLServer:Buffer Manager--Target Server Memory:

4. SQLServer:Buffer Manager--Total Server memory

5. SQLServer:Buffer Manager--Free Pages

6. SQLServer:Buffer Manager--Free List Stall/sec

7. SQLServer:Buffer Manager--Page Life expectancy

For SQL Server 2012 onwards few of Buffer Pool counters are deprecated and so we have to use Memory Manger counters

1. SQL Server: Memory Manager-- Target Server Memory (KB)

2. SQL Server: Memory Manager--Total Server Memory (KB)

3. SQL Server: Memory Manager- Free Memory (KB)

4. SQL Server: Memory Manager--Database Cache Memory (KB)

5. SQLServer:Buffer Manager--Free Pages

6. SQLServer:Buffer Manager--Free List Stall/sec

7. SQLServer:Buffer Manager--Page Life expectancy

Note if you have heavy disk activity do not forget to refer Disk related counters as well. Create a data collector set and allow it to run for 4-5 hrs when load on system is at peak and then add the snapshot of data collector in your question. Then we can determine whether SQL Server needs more memory or not.

Personally 8G is a bit of less RAM considering workload and OS requirements these days. At the back of your head you should always think about increasing RAM.

Answer 2

we need to look at SQLServer:Buffer Cache Hit ratio

If that ratio is less than 95% than the server is under memory pressure

Hope this helps,

This is not which will help you in deciding the memory bottleneck on its own for sure. Rather what i would prefer is to gather the data for below counters at least for a day during the heavy load/business hours.

Memory – Available MBytes
SQLServer: Buffer Manager – Page Life Expectancy
SQLServer: Memory Manager – Memory Grants Pending
SQLServer: Memory Manager – Target Server Memory
SQLServer: Memory Manager – Total Server Memory
SQLServer: SQL Statistics – Batch Requests/sec
SQLServer: SQL Statistics – Compilations/sec

The best would be Baseline the SQL server as suggested Back to Basics: Capturing Baselines on Production SQL Servers which will also collect the require perfmon counters along with wait stats for any problem..

Also, 8 GB RAM may not be that Suitable as per todays env, but again it depends what is the load on system along with size of databases hosted on the instance\instances.