Sql-server – SQLServer:Buffer Cache Hit ratio is a good indication of POSSIBLE memory bottleneck

memoryperformancesql serversql server 2014sql-server-2008-r2

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

Best Answer

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.

Related Question