By: Chad Boyd | Updated: 2007-10-15 | Comments | Related: > Dynamic Management Views and Functions
Lots of customers I visit are frequently interested in determining what objects/structures/files/etc. are consuming the largest amount of space at a given time (or over time) within the Sql Server buffer pool. In Sql 2000, this was a bit complicated to determine to say the least, however with Sql 2005's new dynamic management functions/views, it's become exponentially easier to gain this type of insight; additionally, it's also become easy to aggregate this information for use/display/reporting purposes.
In this scenario, the use of the sys.dm_os_buffer_descriptors DMV optionally correlated against other catalog views within a given database will provide you all the information you need to get this type of information. I'm not going to talk about the DMV or catalog views here, that's done many other places quite well, and also quite sufficiently within Books Online. What I'm going to provide are a couple of utility procedures that wrap this functionallity for ease of use, providing options to aggregate the sum of information per structure/database, query on particular databases or all databases, etc.
The first and simpler of 2 procedures I'll provide will give you aggregated information from the DMV rolled-up on a per database, file, and page type combination (page type being things like data pages vs. index pages vs. PFS pages vs. etc., etc.). It's a simple procedure with no parameters and a single select statement with some grouping, rollup, and sorting...here's the code:
use master
go
if ((object_id('sp_osbufferdescriptors_agg') is not null) and (objectproperty(object_id('sp_osbufferdescriptors_agg'), 'IsProcedure') = 1))
drop proc [dbo].sp_osbufferdescriptors_agg
go
create proc [dbo].sp_osbufferdescriptors_agg
as
/*
SAMPLE EXECUTION:
exec sp_osbufferdescriptors_agg
*/
set nocount on;
set transaction isolation level read uncommitted;
select case when grouping(dbName) = 1 then '--- TOTAL ---' else dbName end as dbName,
case when grouping(fileId) = 1 then '--- TOTAL ---' else fileId end as fileId,
case when grouping(pageType) = 1 then '--- TOTAL ---' else pageType end as pageType,
count(*) as countPages, sum(row_count) as sumRowCount, avg(row_count) as avgRowCount,
sum(freeSpaceBytes) as sumFreeSpaceBytes, avg(freeSpaceBytes) as avgFreeSpaceBytes
from (select case when database_id = 32767 then 'resourceDb' else cast(db_name(database_id) as varchar(25)) end as dbName,
cast(file_id as varchar(10)) as fileId, cast(page_type as varchar(25)) as pageType,
row_count as row_count, free_space_in_bytes as freeSpaceBytes
from sys.dm_os_buffer_descriptors bufferDescriptor with(nolock)) tmp
group by dbName, fileId, pageType with rollup
order by case when grouping(dbName) = 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz' else dbName end,
case when grouping(fileId) = 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz' else fileId end,
case when grouping(pageType) = 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz' else pageType end;
go
The second and more complex of the procedures provides more detailed information for each given database on the system - instead of providing only server-level information (like what database, file, etc. is consuming the buffer pool), it will dig into specific database(s) to provide more targetted information within the given database(s) in regards to specific indexes/tables/views/etc. that are chewing up the most space. Optional parameters are included to target to a specific database, all databases on the system, system level only information, and return only a certain number of results. Here's the code:
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
Feel free to tweak the code to match specific requirements - if you come up with an interesting morph, I'd be very interested to see what you have for additional enhancements. Additionally, as always, be sure to understand the performance impact associated with some possible incarnations of executing these procedures...if you have a large 64-bit system with a large buffer pool, these could get somewhat intensive.
Enjoy!
About the author
Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2007-10-15
