SQL Server - Tempdb - Task Space

Question

Today I was going through a few dmv's to check the tempdb consumption.

One of them which caught my attention is dm_db_task_space_usage. I have a query related to same, i tried to look google but didn't get the answer, i was looking for.

So this DMV has two columns user_objects_alloc_page_count and user_objects_dealloc_page_count.

These basically shows allocated\deallocated number of pages to a session. Additionally if we multiply by 8 then divide it by 1024 it shows consumption in MB.

Let's say a single query is running on server which is consuming tempdb. Now

a. If i run the dmv its shows 10 Gb of user allocation space, does it mean 10 gb is size of data it has worked on till now ?

b. if dmv shows 5 Gb of user deallocation space, does it mean to say 5 gb is size of data it has worked on till now and if transaction is not committed it have to rollback 5 gb of data ? or something else.

c. In some of blogs i have seen author subtracting user_objects_dealloc_page_count from user_objects_alloc_page_count for evaluating space used. What does that means ? and then what's the difference between space user_objects_alloc_page_count and spaceused [user_objects_alloc_page_count - user_objects_dealloc_page_count ] ?

Regards

Answer 1

Hi @sourabh tiwari

How about sys.dm_db_file_space_usage, we can know which object the tempdb space is used by, whether it is a user object (user_object_reserved_page_count) or a system object (internal_object_reserved_page_count) or the version store (version_store_reserved_page_count).

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

That's a tricky one. Let's say a process needs 10 GB of temp space for something. Later the process completes everything, drop temp tables etc. Then in theory, the counters should show the same value. But the deallocations may be performed by a system process, which makes the numbers less reliable.

Here is a very good blog post by Sebastian Meine that explains this in more detail: https://sqlity.net/en/671/deferred-deallocations-of-pages-in-tempdb/