In a previous post I showed how you can collect information on what is held in the data cache. The data collected here was just a simple summary of how much space each database was consuming. While useful we will need more detailed information on what is inside the cache to get a proper handle on things.

The below query has been modified from the post: What’s swimming in your buffer pool? This procedure will tell us exactly what objects are contained within the data cache.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Rhys Campbell
-- Create date: 2010-09-28
-- Description:	Gets info on objects in the data
-- cache broken by database.
-- =============================================
CREATE PROCEDURE [dbo].[GetDataCacheObjects]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT @@SERVERNAME AS 'Server',
		   GETDATE() AS collected_at,
		   DB_NAME(database_id) AS 'database',
		   COUNT(*)AS cached_pages_count,
		   obj.name AS objectname,
		   ind.name AS indexname,
		   obj.index_id AS indexid
	FROM sys.dm_os_buffer_descriptors AS bd
    INNER JOIN
    (
        SELECT object_id AS objectid,
                           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_id AS objectid,
                           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
	LEFT OUTER JOIN sys.indexes ind
		ON obj.objectid = ind.object_id
	AND obj.index_id = ind.index_id
	WHERE bd.page_type IN ('data_page', 'index_page')
	GROUP BY DB_NAME(database_id), obj.name, ind.name, obj.index_id
	ORDER BY cached_pages_count DESC

END

GO

This will produce a dataset similar to below.

objects in the sql server data cache

This proc can be called by the Powershell script in the previous post (nice and easy to run against multiple servers) and will produce a csv file looking like below. Just change the line

$proc = "EXEC dbo.GetDataCache";

to

$proc = "EXEC dbo.GetDataCacheObjects";

This is ready to be imported into a database for future analysis.

"Server","collected_at","database","cached_pages_count","objectname","indexname","indexid"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","","1167","sysobjvalues","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","","304","syscolpars","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","","190","tweet_followersIds","PK __tweet_fo__ 3214EC0735DCF99B","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","","56","sysschobjs","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","test","32","sysobjvalues","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","","27","sysobjkeycrypts","cl","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","msdb","25","sysschobjs","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","master","22","sysobjvalues","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","msdb","14","sysobjvalues","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","test","13","syscolpars","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","msdb","13","syscolpars","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","","12","sysrscols","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","msdb","12","sysrscols","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","master","11","sysrscols","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","msdb","10","sysobjkeycrypts","cl","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","","9","sysschobjs","nc2","3"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","","8","sysidxstats","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","model","8","sysobjvalues","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","model","8","sysrscols","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","tempdb","8","sysobjvalues","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","tempdb","8","sysrscols","clst","1"
"RHYS-VAIO\SQLEXPRESS","29/09/2010 14:58:32","test","8","sysrscols","clst","1"

Once several snapshots of the data cache have been made this should help us with performance problem diagnosis and estimate the “warm-up time” after a reboot.