Home > Management Views and Functions, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > Using SQL DMVs – sys.dm_db_task_space_usage

Using SQL DMVs – sys.dm_db_task_space_usage

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!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: