How to identify tempdb space usage

Sometimes our tempdb gets full and fills the disk. Hence it cant expand and due to its a lots of our operation either stucks or starting failing.
At this time we immediately need to find which process is taking so much space in tempdb and which type of  operation by that process is taking space to troubleshoot the issue ASAP

Lets see how we can get this details


FROM   sys.dm_db_task_space_usage
ORDER  BY ( user_objects_alloc_page_count + internal_objects_alloc_page_count ) DESC

Returns information for the pages allocated and deallocated by the running task 

Here we can see that session 164 is taking highest space and its taking most of space due to internal objects.So either worktables or sorting operation are reason for it. Immediately we can stop this process and optimize it to reduce load on tempdb

From BOL


User-defined tables and indexes
System tables and indexes
Global temporary tables and indexes
Local temporary tables and indexes
Table variables
Tables returned in the table-valued functions

Internal Objects

Work tables for cursor or spool operations and temporary large object (LOB) storage
Work files for operations such as a hash join
Sort runs

It shows  pages used for version store in database.

There are some other system tables also which can give us additional information 


USE tempdb

 FROM   sys.dm_db_file_space_usage(nolock)

Here it gives space used by different objects in database file
 We have to look at user_object_reserved_page_count ,version_store_reserved_page_count,internal_object_reserved_page_count
 By looking at this value we can get idea what is taking space in tempdb

Here for above results the space is taken mostly by internal objectsSo its done by either work tables for the query or sort operations.

No comments:

Post a Comment

Popular Posts