For a few months I’ve been meaning to do an extensive post on a SQL Server query that would show the amount of each index/table in memory (Data Cache). I haven’t had the time to do this write-up, so I’m jut providing the query at this time. Hopefully in the future I will have time to describe this better.
The following query has been extremely beneficial at my work. It has help to identify indexes that should not be in memory and performance issues. This query doesn’t usually identify a root-cause, but it does seem to point you into the correct direction.
In the past there was a situation where a Cluster Index Scan was being used on a 20 GB table. This caused major performance problems for our system. The Cluster Index was loaded into memory (data cache) which forced other important data out of the data cache. By using this query to show indexes in memory, it was easy to identify the table that was being queried and inturn find the query that was causing the problem.
IF OBJECT_ID('tempdb..#PhysicalBufferSize') IS NOT NULL
BEGIN
PRINT 'DROP TABLE tempdb..#PhysicalBufferSize'
DROP TABLE #PhysicalBufferSize
END
SELECT
PhysicalSize.TableName
,PhysicalSize.IndexName
,PhysicalSize.Index_MB
,BufferSize.Buffer_MB
,CASE
WHEN Index_MB != 0 AND Buffer_MB != 0 THEN
CAST(Buffer_MB AS Float) / CAST(Index_MB AS Float)
ELSE 0
END IndexInBuffer_Percent
INTO #PhysicalBufferSize
FROM
(
--Index Disk Allocation per file
SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
SUM(a.used_pages) / 128 AS 'Index_MB'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON
p.OBJECT_ID = i.OBJECT_ID
AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON
a.container_id = p.partition_id
WHERE
i.object_id > 100
GROUP BY i.OBJECT_ID,i.index_id,i.name
--ORDER BY 8 * SUM(a.used_pages) DESC--OBJECT_NAME(i.OBJECT_ID),i.index_id
) PhysicalSize
LEFT JOIN
(
--Index Memory Allocations per file
SELECT
obj.[name] TableName,
i.[name] IndexName,
obj.[index_id] IndexID,
i.[type_desc],
count_BIG(*)AS Buffered_Page_Count ,
count_BIG(*) /128 as Buffer_MB --8192 / (1024 * 1024)
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p ON
au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3 OR au.type = 2)
) AS obj ON
bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on
i.object_id = obj.object_id
AND i.index_id = obj.index_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
--ORDER BY Buffered_Page_Count DESC
) BufferSize ON
PhysicalSize.TableName = BufferSize.TableName
AND PhysicalSize.IndexID = BufferSize.IndexID
ORDER BY Buffer_MB DESC
--*****************************************************************
-- Queries that work of Temp Table
--*****************************************************************
SELECT *
FROM #PhysicalBufferSize
ORDER By Buffer_MB DESC--TableName, IndexName DESC
SELECT TableName, SUM(Index_MB) Index_MB, SUM(Buffer_MB) Buffer_MB,
CASE
WHEN SUM(Index_MB) != 0 AND SUM(Buffer_MB) != 0 THEN
CAST(SUM(Buffer_MB) AS Float) / CAST(SUM(Index_MB) AS Float)
ELSE 0
END IndexInBuffer_Percent
FROM #PhysicalBufferSize
GROUP BY TableName
ORDER BY Buffer_MB DESC
SELECT SUM(Index_MB) Index_MB, SUM(Buffer_MB) Buffer_MB,
CASE
WHEN SUM(Index_MB) != 0 AND SUM(Buffer_MB) != 0 THEN
CAST(SUM(Buffer_MB) AS Float) / CAST(SUM(Index_MB) AS Float)
ELSE 0
END IndexInBuffer_Percent
FROM #PhysicalBufferSize
SELECT *
FROM #PhysicalBufferSize
ORDER By Index_MB DESC
The following grid shows an example output from AdventureWorks database. The grid identifes the size of the index on disk (Index MB) and the amount of the index in the Data Cache.
| Table Name |
Index Name | Index MB | Buffer MB | Percent |
| Person | PK_Person_BusinessEntityID | 29 | 29 | 1.00 |
| SalesOrderDetail | PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID | 9 | 9 | 1.00 |
| TransactionHistory | PK_TransactionHistory_TransactionID | 6 | 6 | 1.00 |
| DatabaseLog | NULL | 6 | 6 | 1.00 |
| WorkOrderRouting | PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence | 5 | 5 | 1.00 |
| SalesOrderHeader | PK_SalesOrderHeader_SalesOrderID | 5 | 5 | 1.00 |
| WorkOrder | PK_WorkOrder_WorkOrderID | 4 | 4 | 1.00 |
| TransactionHistoryArchive | PK_TransactionHistoryArchive_TransactionID | 5 | 4 | 0.80 |
| Address | PK_Address_AddressID | 2 | 2 | 1.00 |
| CreditCard | PK_CreditCard_CreditCardID | 1 | 1 | 1.00 |
| EmailAddress | PK_EmailAddress_BusinessEntityID_EmailAddressID | 1 | 1 | 1.00 |
| Password | PK_Password_BusinessEntityID | 1 | 1 | 1.00 |
| PersonPhone | PK_PersonPhone_BusinessEntityID_PhoneNumber_PhoneNumberTypeID | 1 | 1 | 1.00 |
| Address | IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode | 1 | 1 | 1.00 |
| Shift | AK_Shift_StartTime_EndTime | 0 | 0 | 0.00 |
Resource:
SQLTEAM - What Data is in SQL Server's Memory?
MSDN - sys.dm_os_buffer_descriptors (Transact-SQL)
Glenn Berry - SQL Server Performance 1
Glenn Berry - SQL Server Performance 2
