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

Using SQL DMVs – sys.dm_db_session_space_usage


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

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