sys.dm_db_task_space_usage contains details for allocation/de-allocation pages for activity tasks for database.

It returns below columns:

1. session_id – session id

2. request_id – request id within the session, i.e. execution batch within a session

3. database_id – will always be 2, id of tempdb

4. user_objects_alloc_page_count – number pages allocated for the request

5. user_objects_dealloc_page_count – number of pages de-allocated for the request.

for other columns returned, refer BOL…

sys.dm_db_task_space_usage returns only active tasks:

-- © 2011 – Vishal (http://SqlAndMe.com)
 
SELECT      session_id, request_id,
            user_objects_alloc_page_count,
            user_objects_dealloc_page_count
FROM        sys.dm_db_task_space_usage

Partial Result Set:

session_id request_id  user_objects_alloc_page_count user_objects_dealloc_page_count
———-       ———–        —————————–                    ——————————-
1          0           0                             0
2          0           0                             0
3          0           0                             0
4          0           0                             0
5          0           0                             0
6          0           0                             0
7          0           1                             0
8          0           0                             0

When a query is executed which requires storage in tempdb, this DMV will return the number of pages allocated for the request.

For example, below query will require tempdb storage:

-- Session ID = 56
 
USE AdventureWorks2008R2
 
SELECT      A.*
INTO        #TempTable
FROM        HumanResources.Employee A
CROSS APPLY HumanResources.Employee B
CROSS APPLY HumanResources.Employee C

You can check sys.dm_db_task_space_usage while the above query is running to check the pages allocated to the request.

SELECT      session_id, request_id,
            user_objects_alloc_page_count,
            user_objects_dealloc_page_count
FROM        sys.dm_db_task_space_usage
WHERE       session_id = 56

Result Set:

session_id request_id  user_objects_alloc_page_count user_objects_dealloc_page_count
———-       ———–        —————————–                    ——————————-
56         0           8193                          0
 
(1 row(s) affected)

Once the request is completed this row will no longer be available.

To check pages allocated to a session you can use sys.dm_db_session_space_usage.

Hope This Helps! Cheers!