I’m currently building a CMDB at work and I wanted to include a bit of performance data in the information collected. The data cache is probably the biggest consumer of memory within SQL Server so It makes sense to collect this for future analysis. As a start I’m gathering the amount of space each database consumes in the data cache. I’ve created the below proc from a query in the excellent Pro SQL Server 2008 Internals & Troubleshooting.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Rhys Campbell
-- Create date: 2010-09-28
-- Description:	Provides a summary of how much
-- space each database is using in the data cache
-- =============================================
CREATE PROCEDURE GetDataCache
AS
BEGIN

	SET NOCOUNT ON;

    SELECT COUNT(*) * 8 / 1024 AS 'Cache Size (MB)',
		   CASE database_id
				WHEN 32767 THEN 'ResourceDb'
				ELSE DB_NAME(database_id)
		   END AS 'Database',
		   @@SERVERNAME AS 'Server',
		   GETDATE() AS 'collected_at'
	FROM sys.dm_os_buffer_descriptors
	GROUP BY DB_NAME(database_id), database_id
	ORDER BY 'Cache Size (MB)' DESC;

END
GO

This will produce a dataset similar to below.

sql data cache use by database

I’ve written a Powershell script to make it easy to query multiple servers. The script assumes that each sql instance to be queried contains the same database with the above proc. This script should be able to call any procedure as it simply exports the resultset to a csv file.

# array of sql server instances to query
$instances = @("localhost\sqlexpress", "RHYS-VAIO\sqlexpress");
# assumes proc is located in the same db on all servers
$database = "test";
# proc to call
$proc = "EXEC dbo.GetDataCache";
# Datetime stamp for filename
$dt = Get-Date -Format "yyyyMMddHHmmss";

# Query each instance
foreach($sql_instance in $instances)
{
	$con = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sql_instance;Integrated Security=true;Initial Catalog=$database");
	$resultset = New-Object "System.Data.DataSet" "myDs";
	$data_adap = new-object "System.Data.SqlClient.SqlDataAdapter" ($proc, $con);
	$data_adap.Fill($resultset);
	# Replace any '\' chars from instance names to use in the filename
	$tmp_srv = $sql_instance -replace "\\", "_";
	# Add data to csv file
	$resultset.Tables[0] | Export-Csv -NoTypeInformation -Path "$Env:USERPROFILE\$tmp_srv`_sql_data_cache_$dt.csv";
	# Clean up
	$resultset.Dispose();
	$con.Close();
}

The script will spit out a csv file for each sql instance ready to be imported into the CMDB.

"Cache Size (MB)","Database","Server","collected_at"
"0","master","RHYS-VAIO\SQLEXPRESS","28/09/2010 13:12:52"
"0","msdb","RHYS-VAIO\SQLEXPRESS","28/09/2010 13:12:52"
"1","test","RHYS-VAIO\SQLEXPRESS","28/09/2010 13:12:52"
"20","TweetSQLV3","RHYS-VAIO\SQLEXPRESS","28/09/2010 13:12:52"
"1","tweetsql_31","RHYS-VAIO\SQLEXPRESS","28/09/2010 13:12:52"
"0","ResourceDb","RHYS-VAIO\SQLEXPRESS","28/09/2010 13:12:52"