sys.dm_os_buffer_descriptors aggregations - Sql Stuff - Site Home

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

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

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 http://www.microsoft.com/info/cpyright.htm.