-
Notifications
You must be signed in to change notification settings - Fork 51
/
Breakdown of buffer cache usage for a specific database.sql
42 lines (40 loc) · 1.48 KB
/
Breakdown of buffer cache usage for a specific database.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- Breakdown of buffer cache usage for a specific database
-- Part of the SQL Server DBA Toolbox at https://github.com/DavidSchanzer/Sql-Server-DBA-Toolbox
-- This script analyses which clustered and non-clustered indexes have been using how much of the buffer cache memory for this database.
-- From https://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/
USE [<DatabaseName>];
GO
;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;