Monitor tempdb Storage

No comments

It’s often important to understand what is using your tempdb. For the size:

1
sp_helpdb 'TempDB'

Break out user objects from the tempdb:

1
2
3
4
5
6
7
8
9
10
USE tempdb
GO
 
SELECT
(SUM(unallocated_extent_page_count)/128) AS [Free space (MB)],
SUM(internal_object_reserved_page_count)*8 AS [Internal objects (KB)],
SUM(user_object_reserved_page_count)*8 AS [User objects (KB)],
SUM(version_store_reserved_page_count)*8 AS [Version store (KB)]
FROM sys.dm_db_file_space_usage
WHERE database_id = 2 -- 2=tempdb

View temporary tables created in tempdb:

1
2
3
4
5
6
7
8
9
10
SELECT tb.name AS [Temporary table name],
stt.row_count AS [Number of rows],
stt.used_page_count * 8 AS [Used space (KB)],
stt.reserved_page_count * 8 AS [Reserved space (KB)] FROM tempdb.sys.partitions AS prt
INNER JOIN tempdb.sys.dm_db_partition_stats AS stt
ON prt.partition_id = stt.partition_id
AND prt.partition_number = stt.partition_number
INNER JOIN tempdb.sys.tables AS tb
ON stt.object_id = tb.object_id
ORDER BY tb.name

Leave a Reply