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: