Determine objects consuming the largest amount of space in the SQL Server buffer pool


By:   |   Updated: 2007-10-15   |   Comments   |   Related: More > 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!

 



Last Updated: 2007-10-15


get scripts

next tip button



About the author
MSSQLTips author Chad Boyd Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools