Buffer Cache Size when Page Life Expectancy Drops

Question

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...?

Answer 1

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)

I can see from the question that you have SQL Server 2012 SP1. There was a Bug in SQL Server 2012 which forced PLE to plummet but that was fixed in SQL Server 2012 SP1 CU4. Now since ** SQL Server 2012 SP3** has been released I suggest you apply SP3 and see if the issue subsides.

I wanted the check the size of the buffer cache at the exact time of PLE drop,

You can't use perfmon counter Buffer Manager: Database Pages to check buffer size. As per BOL what it signifies is:

Indicates the number of pages in the buffer pool with database content.

So you can see it does not gives information about complete Buffer Pool. When you define SQL Server max server memory the value set in max server memory becomes the buffer pool size. As such from SQL Server 2012 onward the buffer pool has less significance. Previously it was both consumer and the provider of memory. From SQL Server 2012 onward its just the consumer. To see the contents of the buffer pool(to some extent) you have DMV sys.dm_os_buffer_descriptors.

Below query returns cached page count of each database.

SELECT COUNT(*)AS cached_pages_count
    ,CASE database_id 
        WHEN 32767 THEN 'ResourceDb' 
        ELSE db_name(database_id) 
        END AS database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ,database_id
ORDER BY cached_pages_count DESC;  

If you want to get pages cached for each objects in particular database

SELECT COUNT(*)AS cached_pages_count 
    ,name ,index_id 
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            ,index_id ,allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            ,index_id, allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.partition_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id 
ORDER BY cached_pages_count DESC;