Zvikas Peer technologies Blog : SQL SERVER Buffer pool size

יום חמישי, 6 ביוני 2013

SQL SERVER Buffer pool size

The following T-SQL query fetch the size of the buffer pool being used by as specific database.

  1:  select database_id, db_buffer_pages = COUNT_BIG(*) ,  db_buffer_MB =  COUNT_BIG(*) / 128
  2:        FROM sys.dm_os_buffer_descriptors
  3:        WHERE DB_NAME([database_id]) = 'AdventureWorks2008'
  4:        GROUP BY database_id

View the Buffer pool size of each object in the DB that was loaded to the memory pool

  1: USE AdventureWorks2008;
  2: GO
  3: 
  4: ;WITH src AS
  5: (
  6:    SELECT
  7:        [Object] = o.name,
  8:        [Type] = o.type_desc,
  9:        [Index] = COALESCE(i.name, ''),
 10:        [Index_Type] = i.type_desc,
 11:        p.[object_id],
 12:        p.index_id,
 13:        au.allocation_unit_id
 14:    FROM
 15:        sys.partitions AS p
 16:    INNER JOIN
 17:        sys.allocation_units AS au
 18:        ON p.hobt_id = au.container_id
 19:    INNER JOIN
 20:        sys.objects AS o
 21:        ON p.[object_id] = o.[object_id]
 22:    INNER JOIN
 23:        sys.indexes AS i
 24:        ON o.[object_id] = i.[object_id]
 25:        AND p.index_id = i.index_id
 26:    WHERE
 27:        au.[type] IN (1,2,3)
 28:        AND o.is_ms_shipped = 0
 29: )
 30: SELECT
 31:    src.[Object],
 32:    src.[Type],
 33:    src.[Index],
 34:    src.Index_Type,
 35:    buffer_pages = COUNT_BIG(b.page_id),
 36:    buffer_mb = COUNT_BIG(b.page_id) / 128
 37: FROM
 38:    src
 39: INNER JOIN
 40:    sys.dm_os_buffer_descriptors AS b
 41:    ON src.allocation_unit_id = b.allocation_unit_id
 42: WHERE
 43:    b.database_id = DB_ID()
 44: GROUP BY
 45:    src.[Object],
 46:    src.[Type],
 47:    src.[Index],
 48:    src.Index_Type
 49: ORDER BY
 50:    buffer_pages DESC;
 51: