Get Database Buffer Cache Size

Applicability:

SQL Server 2000: Not Supported

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: 17 Nov 2010

Description

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.

Code

SELECT

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