T-SQL Tuesday #004: IO — Where Are My TempDB Objects?

This blog entry is participating in T-SQL Tuesday #004, hosted this month by Mike Walsh. You are invited to visit his blog to join the party and read more blogs participating in this month’s theme: IO.

The question was raised recently in a discussion group about how to tell if your temporary tables and table variables were being maintained in memory or on disk. Here is my attempt to solve that particular puzzle.

We can determine how many pages are being used on disk by mapping sys.allocation_units to sys.partitions. You can get the number of pages in cache for each object by looking at sys.dm_os_buffer_descriptors. Combine the two to get the total of both.

The query:

USE tempDB;

WITH Objs (
    ObjectName,
    ObjectID,
    IndexID,
    AU_ID,
    used_pages,
    AU_Type)
AS (SELECT OBJECT_NAME(object_id) AS ObjectName,
        object_id,
        index_id,
        allocation_unit_id,
        used_pages,
        AU.type_desc
    FROM sys.allocation_units AS AU
    INNER JOIN sys.partitions AS P
        ON AU.container_id = P.hobt_id
            -- IN_ROW_DATA and ROW_OVERFLOW_DATA
            AND AU.type In (1, 3)
    UNION ALL
    SELECT OBJECT_NAME(object_id) AS ObjectName,
        object_id,
        index_id,
        allocation_unit_id,
        used_pages,
        AU.type_desc
    FROM sys.allocation_units AS AU
    INNER JOIN sys.partitions AS P
        ON AU.container_id = P.partition_id
            -- LOB_DATA
            AND AU.type = 2
    )
SELECT ObjectName,
    AU_Type,
    IndexID,
    MAX(used_pages) PagesOnDisk,
    COUNT(*) PagesInCache,
    MAX(used_pages) - COUNT(*) PageAllocationDiff
FROM sys.dm_os_buffer_descriptors AS BD
LEFT JOIN Objs O
    ON BD.allocation_unit_id = O.AU_ID
WHERE database_id = DB_ID()
AND ObjectPropertyEx(ObjectID, 'IsUserTable') = 1
GROUP BY ObjectName, AU_Type, IndexID , used_pages
ORDER BY O.ObjectName, O.AU_Type;

The output:

     ObjectName – Name of table
     AU_Type – Type of allocation
     IndexID – ID of the index
     PagesOnDisk – Number of pages on disk
     PagesInCache – Number of pages in cache
     PageAllocationDiff – Difference in pages between disk and cache

Sample output: