SQL Server:DMV table differences(sys.dm_db_task_space_usage, tempdb.sys.dm_db_file_space_usage)

Question

I'm trying to check the space of tempdb.

I want to check the space of tempdb, including tempdb usage, remaining space, and tempdb usage per query.

When I ran the two queries below, the results were different.

SELECT sum(user_object_reserved_page_count+internal_object_reserved_page_count), sum(allocated_extent_page_count) FROM tempdb.sys.dm_db_file_space_usage SELECT sum(user_objects_alloc_page_count+user_objects_dealloc_page_count+internal_objects_alloc_page_count+internal_objects_dealloc_page_count) FROM sys.dm_db_task_space_usage

Even if you search for that column separately, the results are different.

SELECT user_object_reserved_page_count, internal_object_reserved_page_count, allocated_extent_page_count FROM tempdb.sys.dm_db_file_space_usage SELECT user_objects_alloc_page_count, user_objects_dealloc_page_count, internal_objects_alloc_page_count, internal_objects_dealloc_page_count FROM sys.dm_db_task_space_usage

How should I look it up?

Which is correct?

Answer 1

This is no trivial matter, and I don't have a final answer. But there are a few observations I like to make. For the first query, you may be able to get the two columns to match, if you change query to:

SELECT sum(user_object_reserved_page_count + 
           internal_object_reserved_page_count + 
           version_store_reserved_page_count + 
           mixed_extent_page_count), 
           sum(allocated_extent_page_count) 
FROM   tempdb.sys.dm_db_file_space_usage 
WHERE  database_id = 2

I say "may", because they match on my system at home, but this instance is idle. A busy production system may yield different results. If you want to know the overall usage in tempdb, this is probably the query to use.

When it comes to other query, you have an error: the dealloc columns should have a minus:

SELECT sum(user_objects_alloc_page_count - 
           user_objects_dealloc_page_count + 
           internal_objects_alloc_page_count - 
           internal_objects_dealloc_page_count) 
FROM sys.dm_db_task_space_usage

Observe that this DMV only give you the usage from tasks, that is running requests. So if an idle session is sitting with a huge temp table, it will not show up here. So there is no surprise if the sum here does not match the first query.

If you want to know the usage per session, the DMV sys.dm_db_session_space_usage is better. Here is a SUM query:

SELECT sum(user_objects_alloc_page_count - 
           user_objects_dealloc_page_count + 
           internal_objects_alloc_page_count - 
           internal_objects_dealloc_page_count - 
           user_objects_deferred_dealloc_page_count) 
FROM sys.dm_db_session_space_usage

Note the last column. When a session drops a temp table that is of any size, the deallocation may not occur directly, but it is deferred and performed by a background task. And that background task is always active, so that deallocation number will remain in sys.dm_db_task_usage, which makes the total from that view even less reliable.