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%'