sys.dm_os_buffer_descriptors aggregations
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
/*
NOTE: Use of this procedure requires the existence of the following procedures/functions as well:
1. <NA>
-- 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, max(p.row_count) as indexRowCount,
sum(b.row_count) as loadedRowCount, max(p.in_row_used_page_count) as inRowUsedPages,
max(p.in_row_data_page_count) as inRowDataPages, max(p.in_row_reserved_page_count) as inRowReservedPages,
max(p.lob_used_page_count) as lobUsedPages, max(p.lob_reserved_page_count) as lobReservedPages,
max(p.row_overflow_used_page_count) as rowOverflowUsedPages,
max(p.row_overflow_reserved_page_count) as rowOverflowReservedPages,
max(p.used_page_count) as totalUsedPages, max(p.reserved_page_count) as totalReservedPages
from sys.dm_db_partition_stats 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,
sum(b.row_count) as loadedRows
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, max(p.row_count) as indexRowCount,
sum(b.row_count) as loadedRowCount, max(p.in_row_used_page_count) as inRowUsedPages,
max(p.in_row_data_page_count) as inRowDataPages, max(p.in_row_reserved_page_count) as inRowReservedPages,
max(p.lob_used_page_count) as lobUsedPages, max(p.lob_reserved_page_count) as lobReservedPages,
max(p.row_overflow_used_page_count) as rowOverflowUsedPages,
max(p.row_overflow_reserved_page_count) as rowOverflowReservedPages,
max(p.used_page_count) as totalUsedPages, max(p.reserved_page_count) as totalReservedPages
from sys.dm_db_partition_stats 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
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!
Chad Boyd ~~~ This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.
Comments
- Anonymous
March 29, 2007
yet another incredible article from chad boyd's blog a quote: "Lots of customers I visit are frequently - Anonymous
April 10, 2007
Chad,Great and very helpful query, but aggregations are a bit wrong. They show incorrect number of pages (number of pages in index multiplied on number of pages allocated from this index etc).I've also added SUM(b.row_count) to see how many rows are loaded for the index.Correct aggregation is below.count(*) as bufferCount,
RegardsAlexander Tarasuldb_name() as dbName, object_name(p.object_id) as objectName, isnull(i.name,''HEAP'') as indexName, max(p.partition_number) as partitionCount, AVG(p.rows) as indexRowCount, AVG(a.total_pages) as auTotalPages, AVG(a.used_pages) as auUsedPages, AVG(a.data_pages) as auDataPages,SUM(b.row_count) as loadedrows - Anonymous
April 19, 2007
Hi Alexander - thanks for the catch! I completely revamped that particular procedure, as I noticed there were even still some wrong aggregations in certain scenarios on larger machines. I'm posting the updated copy of the data above in the original post, but it is going to take advantage of the dm_db_partition_stats dmv instead of aggregating things manually...Thanks again,Chad