SQL Server Objects In Memory VS Disk

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

Leave a Reply

Your email address will not be published. Required fields are marked *