Archive

Posts Tagged ‘sys.dm_db_session_space_usage’

Using SQL DMVs – sys.dm_db_task_space_usage

08.06.2011 No comments

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

Using SQL DMVs – sys.dm_db_session_space_usage

07.06.2011 No comments

While you can use sys.dm_db_file_space_usage to get the free space available in tempdb. you can use sys.dm_db_session_space_usage to get the used space by each session.

sys.dm_db_session_space_usage contains following columns:

1. session_id – session_id

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

3. user_objects_alloc_page_count – number of pages allocated/reserved by the session

4. user_objects_dealloc_page_count – number of pages that are de-allocated by user objects for the session

for other columns returned by sys.dm_db_session_space_usage, refer BOL…

 

sys.dm_db_session_space_usage returns each row per session as below:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      session_id, user_objects_alloc_page_count,

            user_objects_dealloc_page_count

FROM        sys.dm_db_session_space_usage

Partial ResultSet:

session_id user_objects_alloc_page_count user_objects_dealloc_page_count

———- —————————– ——————————-

1          0                             0

2          0                             0

3          0                             0

4          0                             0

5          0                             0

6          0                             0

7          0                             0

8          0                             0

Now, if I create a new object I should be able to see the same in the view:

USE AdventureWorks2008R2

 

SELECT      *

INTO        #TempTable

FROM        HumanResources.Employee

 

SELECT      @@spid

Result Set:

(290 row(s) affected)

 

——

52

 

(1 row(s) affected)

Now, if we check the DMV for session id = 52, we should be able to see the usage for that session:

SELECT      session_id, user_objects_alloc_page_count,

            user_objects_dealloc_page_count

FROM        sys.dm_db_session_space_usage

WHERE       session_id = 52

Result Set:

session_id user_objects_alloc_page_count user_objects_dealloc_page_count

———- —————————– ——————————-

52         99                            90

 

(1 row(s) affected)

 

 

 

(1 row(s) affected)

After dropping the #TempTable the counts will change as below:

session_id user_objects_alloc_page_count user_objects_dealloc_page_count

———- —————————– ——————————-

52         99                            99

 

(1 row(s) affected)

 

 

 

(1 row(s) affected)

You can see here that de-allocated page count changed from 90 to 99.

Hope This Helps!

Vishal