I'm troubleshooting an issue with one of our SharePoint databases, essentially huge drops in PLE which I think are caused by increased I/O activity (I see increased reads, writes and lazy writes at the same time)
Buffer Cache Hit Ratio at this time is typically 96%..
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Oct 19 2012 13:38:57 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)
I wanted the check the size of the buffer cache at the exact time of PLE drop, so I'm running the following query which I think gives me the size in MB:
DECLARE @total_buffer INT; SELECT @total_buffer = cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE '%Buffer Manager%' AND counter_name = 'Database Pages' SELECT ((@total_buffer * 8) / 1024) AS CacheSizeMB
The size I'm seeing does not make sense (to me, i mean), typically 250-350MB. I was hoping I would be seeing a large size, then I could check which tables have the most pages in the cache, which would hopefully lead me to which LOB objects were being retrieved/uploaded thus causing the problems.
Do I also need to be looking at the plan cache? i.e. could the PLE drop be caused by some heavy table scans?
SQL Server is allocated a MAX memory of 12GB, from an available 16GB. How does that correlate with Buffer Cache Size?
If PLE is dropping to values of 0-5 then surely the Buffer Size should be larger...?