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.

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