what tables and indexes are using the most memory in the buffer cache

Question

Post reply

what tables and indexes are using the most memory in the buffer cache

akhamoshioke

Ten Centuries

Points: 1110
More actions
May 1, 2014 at 6:11 pm

#288375

Comments posted to this topic are about the item what tables and indexes are using the most memory in the buffer cache

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 1

Contrary to the title, the query does not show the indexes.

Below is an enhanced version (courtesy of Aaron Bertrand)

;WITH src AS

(

SELECT

[Object] = o.name,

[Type] = o.type_desc,

[Index] = COALESCE(i.name, ''),

[Index_Type] = i.type_desc,

p.[object_id],

p.index_id,

au.allocation_unit_id

FROM sys.partitions AS p

INNER JOIN sys.allocation_units AS au

ON p.hobt_id = au.container_id

INNER JOIN sys.objects AS o

ON p.[object_id] = o.[object_id]

INNER JOIN sys.indexes AS i

ON o.[object_id] = i.[object_id]

AND p.index_id = i.index_id

WHERE au.[type] IN (1,2,3) AND o.is_ms_shipped = 0

)

SELECT

src.[Object],

src.[Type],

src.[Index],

src.Index_Type,

buffer_pages = COUNT_BIG(b.page_id),

buffer_mb = COUNT_BIG(b.page_id) / 128

FROM src

INNER JOIN

sys.dm_os_buffer_descriptors AS b

ON src.allocation_unit_id = b.allocation_unit_id

WHERE b.database_id = DB_ID()

GROUP BY

src.[Object],

src.[Type],

src.[Index],

src.Index_Type

ORDER BY

buffer_pages DESC;

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

phkaushik Grasshopper Points: 11 More actions · Answer 2

after result what to do?

is this help in any performance of databases

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 3

SQLQuest29 (5/27/2014)
Contrary to the title, the query does not show the indexes.
Below is an enhanced version (courtesy of Aaron Bertrand)

Thanks for the enhancement.

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 4

Iwas Bornready (10/28/2015)
SQLQuest29 (5/27/2014)
Contrary to the title, the query does not show the indexes.
Below is an enhanced version (courtesy of Aaron Bertrand)
Thanks for the enhancement.

Thank you Aaron for a script that gives us the index name, uses a CTE, and determines buffer space using values in a column instead of the number of rows in the result.

😎

jpratt-797544 SSC Eights! Points: 899 More actions · Answer 5

Great scripts! To show memory buffer MB with a decimal value, I changed "128" to "128.00". Since many of my indexes use less than 1 MB, they all showed as 0 without the decimal included in the division formula.

J Pratt