Performance - Get Database Buffer Cache Size


                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        


Author:    Unknown
Date:        17 Nov 2010


Retrieves the number of pages with database content in the SQL Server Buffer Pool.  Utilizes the dmv sys.dm_os_performance_counters, which is an upgrade to the SQL 2000 SysPerfInfo function.

I found this useful in the past when tracing memory and performance problems in a multi-instance SQL Server environment where an XML index was being used inappropriately to service a range type query.  I wrote a small batch job to capture snapshots of this counter regularly and helped to determine that the entire index (50GB) was being loaded into cache each morning and then aged out of cache overnight.


[Object_Name]       AS PerformanceObjectName,
Counter_Name        AS PerformanceCounterName,  
Instance_Name       AS CounterInstanceName,
Cntr_Value          AS CacheSizePages,
Cntr_Value * 8        AS CacheSizeBytes
FROM sys.dm_os_performance_counters (nolock)
WHERE OBJECT_NAME LIKE '%buffer manager%'
AND Counter_Name LIKE '%database pages%'

Andy Hughes,
Aug 23, 2012, 11:52 AM