Buffer cache per DB (Overview & Detailed)

Ever wondered how much memory each DB is using for buffering data?

/* 
Description 	: Ever wondered how much memory each DB is using for buffering data?
Author		: Theo Ekelmans 
Version/Date	: 1.0 2011-03-23
*/

select	coalesce(db_name(database_id), 'System') as 'DbName'
		,sum(case dirty when 0 then cast((pages * 8192.0 ) / 1024.0  / 1024.0 as numeric (17,2)) else 0 end) as 'MBClean'
		,sum(case dirty when 1 then cast((pages * 8192.0 ) / 1024.0  / 1024.0 as numeric (17,2)) else 0 end) as 'MBDirty'
from 	(
			select 	database_id
					,[is_modified] as 'Dirty'
					,count(page_id) as 'pages'
			from	sys.dm_os_buffer_descriptors
			group by database_id 
					,[is_modified]
		) as t
group by database_id
order by sum(case dirty when 0 then cast((pages * 8192.0 ) / 1024.0  / 1024.0 as numeric (17,2)) else 0 end) desc

And in detail

declare @nRows int 
set @nRows = 25

	set nocount on

	declare @qs_results	table
		(	database_id			int
		,	objectname			sysname null
		,	indexname			sysname null
		,	cache_kb			bigint
		,	free_bytes			bigint
		,	percentage_cache	numeric(10,5) null
		,	size_kb				bigint null
		,	percentage_object	numeric(10,5) null
		,	filegroup			sysname null
		,	indid				int null
		,	dirty_kb			bigint null
		,	percentage_dirty	numeric(10,5) null
		,	schema_name			sysname	null
		,	user_name			sysname	null
		)
		
	declare	@qs_db table
		(	database_id			int
		,	name				sysname null
		,	id					int identity
		)
	
	insert	into @qs_db (database_id, name)
	select	database_id, name
	from	sys.databases 
	where	user_access <> 1		-- NOT SINGLE USER
	and		state = 0               -- ONLINE
	and		has_dbaccess(name) <> 0	-- Have Access.


	declare	@nBufferSize int
	
	select	@nBufferSize = count(*)
	from	sys.dm_os_buffer_descriptors with (readpast)

	declare @sql nvarchar(max)
	declare @n int
	set		@n = 1
	declare @db int
	set		@db = 0

	while 1=1
	begin
		set		@db = null
		select	@db = database_id from @qs_db where id = @n
		set @n = @n + 1
		
		if @db is null --We're done
			break
		
		if @db = 0x7FFF	-- Skip this one.
			continue
		
		set @sql= 'use ' + quotename(db_name(@db)) + '
					select	db_id() database_id
					,	isnull(o.name,''<in-memory-resource>'')	object_name
					,	isnull(i.name,'''')	index_name
					,	8.0*sum(b.cache_pages)	cache_kb
					,	sum(b.free_bytes)		free_bytes
					,	8.0*sum(a.total_pages)	used_kb
					,	(select top 1 name from sys.filegroups fg with (readpast) where fg.data_space_id = a.data_space_id) filegroup
					,	min(i.index_id)	indid
					,	8.0*sum(b.dirty_pages) dirty_kb
					,	min(s.name)	schema_name
					,	min(u.name)	user_name
					from	(
						select	a.database_id
							,	allocation_unit_id
							,	count(*) cache_pages
							,	sum(free_space_in_bytes) free_bytes
							,	sum(case when is_modified=1 then 1 else 0 end) dirty_pages
						from	sys.dm_os_buffer_descriptors a with (readpast) 
						where	a.database_id = db_id()
						group by a.database_id,allocation_unit_id
					)	b
					left outer join	sys.allocation_units	a	with (readpast) on	b.allocation_unit_id = a.allocation_unit_id
					left outer join	sys.partitions			p	with (readpast) on	(a.container_id = p.hobt_id		 and a.type in (1,3) )
																or	 (a.container_id = p.partition_id and a.type = 2 )
					left outer join sys.objects				o	with (readpast) on p.object_id = o.object_id 
					left outer join sys.indexes				i	with (readpast) on p.object_id = i.object_id  and p.index_id = i.index_id
					left outer join sys.schemas				s	with (readpast) on o.schema_id = s.schema_id
					left outer join	sys.database_principals u	with (readpast) on s.principal_id = u.principal_id
					where	database_id = db_id()
					and		a.data_space_id is not null
					group by a.data_space_id, isnull(o.name,''<in-memory-resource>''), isnull(i.name,'''')
					option (keepfixed plan)'
		insert into @qs_results
			(	database_id
			,	objectname
			,	indexname
			,	cache_kb
			,	free_bytes
			,	size_kb
			,	filegroup
			,	indid
			,	dirty_kb
			,	schema_name
			,	user_name
			)
		exec(@sql)
	end
	
	insert into @qs_results (database_id, schema_name, user_name, cache_kb, free_bytes, dirty_kb)
		select	a.database_id
			, 'system'
			, 'system'
			,	8.0*count(*) cache_pages
			,	sum(free_space_in_bytes) free_bytes
			,	sum(case when is_modified=1 then 1 else 0 end) dirty_pages
		from	sys.dm_os_buffer_descriptors a with (readpast) 
		where	a.database_id = 0x7FFF
		group by a.database_id,allocation_unit_id
		option (keepfixed plan)
	
	set rowcount @nRows
	set nocount off
	
	select	DBName
		,	TBOwner
		,	TBName
		,	IXName
		,	SizeInCacheKB
		--,	Pinned
		--,	PinnedSizeKB
		,	case
			when PercentageOfCache > 100 then 100
			when PercentageOfCache < 0 then 0
			else PercentageOfCache
			end PercentageOfCache
		,	ObjectSizeKB
		,	case
			when PercentageOfObject > 100 then 100
			when PercentageOfObject < 0 then 0
			else PercentageOfObject
			end PercentageOfObject
		,	FileGroup
		,	indid
		,	DirtyKB
		,	case
			when PercentageObjectDirty > 100 then 100
			when PercentageObjectDirty < 0 then 0
			else PercentageObjectDirty
			end PercentageObjectDirty
		,	AllocateCacheUnusedKB
	from	(
		select	case when database_id = 0x7FFF then 'mssqlsystemresource' else db_name(database_id) end	DBName
			,	isnull(user_name,'system') TBOwner
			,	objectname				TBName
			,	indexname				IXName
			,	cache_kb				SizeInCacheKB
			--,	null					Pinned
			--,	null					PinnedSizeKB
			,	case when @nBufferSize = 0 then 0.0 else 100.0*(cache_kb/8.0)/@nBufferSize end	PercentageOfCache
			,	size_kb					ObjectSizeKB
			,	case when size_kb = 0 then 0.0 else ((cache_kb*1.0)*100.0)/size_kb end	PercentageOfObject
			,	filegroup				FileGroup
			,	indid					
			,	dirty_kb				DirtyKB
			,	case when size_kb = 0 then 0.0 else dirty_kb*100.0/size_kb end PercentageObjectDirty
			,	free_bytes/1024.0		AllocateCacheUnusedKB	-- NEWCOlumn
		from @qs_results
	)	x
	order by PercentageOfCache desc

Leave a Reply

Your email address will not be published. Required fields are marked *