use master
go
 
if ((object_id('sp_osbufferdescriptors') is not null) and (objectproperty(object_id('sp_osbufferdescriptors'), 'IsProcedure') = 1))
drop proc [dbo].sp_osbufferdescriptors
go
 

create proc [dbo].sp_osbufferdescriptors
	@top int = 0, -- Limits the result set to the top # specified - if null/default/0, all records are returned
	@opts int = 0 -- Option values for execution - bit flags:
-- <no opts> - If no opts are set, database level information is
-- returned for the database context we're executing in
-- 1 bit - If set, system level os_buffer information is returned
-- only - no db level information is returned
-- 2 bit - If set, and the 1 bit is NOT set, all db specific 
-- information is gathered by iterating through all 
-- databases on the system and gathering info

as

/*
-- Get database level information for the current db only
exec sp_osbufferdescriptors; 
-- Only the top 20 results
exec sp_osbufferdescriptors @top = 20; 
-- Get system level information only
exec sp_osbufferdescriptors @opts = 1; 
-- Only top 5 results
exec sp_osbufferdescriptors @top = 5, @opts = 1; 
-- Get database level information for all db's on the system
exec sp_osbufferdescriptors @opts = 2; 
-- Only top 20 results
exec sp_osbufferdescriptors @top = 20, @opts = 2;
*/
 
set nocount on;
set transaction isolation level read uncommitted;

declare @sql nvarchar(4000);

-- Format incoming data
select	@opts = isnull(@opts,0),
		@top = case when @top > 0 then @top else 0 end;

-- If no options were specified, we get the data for the current db and exit
if @opts = 0 begin
	-- Get largest buffer consumers for the given database
	select @sql = N'
			select ' + case when @top > 0 then N'top ' + cast(@top as nvarchar(20)) else N'' end + N' count(*) as bufferCount,
			db_name() as dbName,
			object_name(p.object_id) as objectName, isnull(i.name,''HEAP'') as indexName,
			max(p.partition_number) as partitionCount, sum(p.rows) as indexRowCount,
			sum(a.total_pages) as auTotalPages, sum(a.used_pages) as auUsedPages, sum(a.data_pages) as auDataPages
			from sys.partitions p with(nolock)
			join sys.allocation_units a with(nolock)
			on p.partition_id = a.container_id
			join sys.dm_os_buffer_descriptors b with(nolock)
			on a.allocation_unit_id = b.allocation_unit_id
			join sys.indexes i with(nolock)
			on p.object_id = i.object_id
			and p.index_id = i.index_id
			where b.database_id = db_id()
			group by p.object_id, i.name
			order by count(*) desc, p.object_id, i.name;';
	exec (@sql);
	return;
end
 

-- If 1 bit is set, we get system level information only...
if @opts & 1 = 1 begin
	-- Get largest buffer consumers for the system
	select @sql = N'
			select ' + case when @top > 0 then N'top ' + cast(@top as nvarchar(20)) else N'' end + N' count(*) as bufferCount,
			case when grouping(b.database_id) = 1 then ''--- TOTAL ---'' else
			case when b.database_id = 32767 then ''resourceDb'' else db_name(b.database_id) end end as dbName
			from sys.dm_os_buffer_descriptors b with(nolock)
			group by b.database_id with rollup
			order by case when grouping(b.database_id) = 1 then 0 else count(*) end desc;';
	exec (@sql);
	return;
end
 

-- If the 2 bit is set, we get database level information for multiple db's as appropriate
if @opts & 2 = 2 begin
	-- Create a temp object for storage
	create table #osBufferDescriptorsDbData (
		bufferCount bigint, 
		dbName nvarchar(250), 
		objectName nvarchar(250), 
		indexName nvarchar(250),
		partitionCount int, 
		indexRowCount bigint, 
		auTotalPages bigint, 
		auUsedPages bigint, 
		auDataPages bigint);

	-- Gather up the appropriate data from each database on the server (not system db except tempdb)
	select @sql = N'use [?];
				if ''?'' in (''master'',''model'',''msdb'') return;
				insert #osBufferDescriptorsDbData (bufferCount, dbName, objectName, indexName, partitionCount, indexRowCount, auTotalPages, auUsedPages, auDataPages)
				select ' + case when @top > 0 then N'top ' + cast(@top as nvarchar(20)) else N'' end + N' count(*) as bufferCount,
					db_name() as dbName,
					object_name(p.object_id) as objectName, isnull(i.name,''HEAP'') as indexName,
					max(p.partition_number) as partitionCount, sum(p.rows) as indexRowCount,
					sum(a.total_pages) as auTotalPages, sum(a.used_pages) as auUsedPages, sum(a.data_pages) as auDataPages
				from sys.partitions p with(nolock)
				join sys.allocation_units a with(nolock)
				on p.partition_id = a.container_id
				join sys.dm_os_buffer_descriptors b with(nolock)
				on a.allocation_unit_id = b.allocation_unit_id
				join sys.indexes i with(nolock)
				on p.object_id = i.object_id
				and p.index_id = i.index_id
				where b.database_id = db_id()
				group by p.object_id, i.name;';
				 
	exec sp_MSforeachdb @sql;
 
	-- Return the results
	select @sql = N'
			select ' + case when @top > 0 then N'top ' + cast(@top as nvarchar(20)) else N'' end + N' *
			from #osBufferDescriptorsDbData with(nolock)
			order by bufferCount desc, dbName, objectName;';
	exec (@sql);
 
	-- Cleanup
	drop table #osBufferDescriptorsDbData;
end
go