http://www.sqlservercentral.com/blogs/robert_davis/2010/03/09/T_2D00_SQL-Tuesday-_2300_004_3A00_-IO-_2D002D00_-Where-Are-My-TempDB-Objects/

Printed 2014/09/19 10:33AM

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

By Robert Davis, 2010/03/09

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:

 

ObjectName

AU_Type

IndexID

PagesOnDisk

PagesInCache

PageAllocationDiff

#000C8F7D

IN_ROW_DATA

0

2

1

1

#3263D077

IN_ROW_DATA

0

2

1

1

#32CD1974

IN_ROW_DATA

1

2

304

-302

#536FBE87

IN_ROW_DATA

0

2

14

-12

#5379E028

IN_ROW_DATA

0

2

2

0

#54631769

IN_ROW_DATA

0

2

2

0

#54631769

LOB_DATA

0

2

93

-91

#78AB64D7

IN_ROW_DATA

0

2

10

-8

#78D64D60

IN_ROW_DATA

1

2

9903

-9901

#78F648F1

IN_ROW_DATA

0

2

3

-1

#793574BC

IN_ROW_DATA

0

2

1

1

#799F8910

IN_ROW_DATA

0

2

29

-27

#79D4933A

IN_ROW_DATA

0

2

2

0

#79E9A1D3

IN_ROW_DATA

0

2

1

1

#7A148A5C

IN_ROW_DATA

0

2

1

1

#7BB2BA0B

IN_ROW_DATA

0

2

41041

-41039

#7BD1EA45

IN_ROW_DATA

0

2

1

1

#7C31DCF8

IN_ROW_DATA

0

2

1

1

#7C7108C3

IN_ROW_DATA

0

2

23011

-23009

#7C7BF5BB

IN_ROW_DATA

0

2

2

0

#7C9BF14C

IN_ROW_DATA

0

2

1

1

#7CC6D9D5

IN_ROW_DATA

0

2

2

0

#7CD0FB76

IN_ROW_DATA

0

2

1

1

#7D652CFC

IN_ROW_DATA

0

2

459

-457

#parsedOwners___________________________________0000000015B5

IN_ROW_DATA

0

2

2

0

#parsedProperties_________________________________0000000015BE

IN_ROW_DATA

0

2

3

-1

#parsedStatuses__________________________________0000000015A3

IN_ROW_DATA

0

2

4

-2

#queueIds_______________________________________00000000159E

IN_ROW_DATA

0

2

2

0

#resultsTable_____________________________________0000000015C9

IN_ROW_DATA

1

2

379

-377

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.