sys.dm_db_session_space_usage

Okay so today we will talk about the sys.dm_db_session_space_usage dynamic management view.
This is the third dynamic management view that I have covered; a list if all of them and the date that I covered some of them can be found below

http://sqlservercode.blogspot.com/2006/08/dynamic-management-views-blog-post.html



Before we start here are a couple of remarks

IAM pages are not included in any of the allocation or deallocation counts reported by this view.

Page counters are initialized to zero (0) at the start of a session. The counters track the total number of pages that have been allocated or deallocated for tasks that are already completed in the session. The counters are updated only when a task ends; they do not reflect running tasks.

A session can have multiple requests active at the same time. A request can start multiple threads, tasks, if it is a parallel query.

Here is some information about the columns in this view

session_id
smallint
Session ID.
-- session_id maps to session_id in sys.dm_exec_sessions.

database_id
smallint
Database ID.

user_objects_alloc_page_count
bigint
Number of pages reserved or allocated for user objects by this session.

user_objects_dealloc_page_count
bigint
Number of pages deallocated and no longer reserved for user objects by this session.

internal_objects_alloc_page_count
bigint
Number of pages reserved or allocated for internal objects by this session.

internal_objects_dealloc_page_count
bigint
Number of pages deallocated and no longer reserved for internal objects by this session.


First we will talk about user objects

User Objects
The following objects are included in the user object page counters:

• User-defined tables and indexes
  
• System tables and indexes
  
• Global temporary tables and indexes
  
• Local temporary tables and indexes
  
• Table variables
  
• Tables returned in the table-valued functions
  

SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid


Values in the columns
--------------------------------------
user_objects_alloc_page_count =0
user_objects_dealloc_page_count = 0
internal_objects_alloc_page_count = 0
internal_objects_dealloc_page_count = 0




CREATE TABLE #temp(id VARCHAR(MAX))
INSERT #TEMP VALUES(REPLICATE('a',25000))
INSERT #TEMP VALUES(REPLICATE('b',25000))
INSERT #TEMP VALUES(REPLICATE('c',25000))
INSERT #TEMP VALUES(REPLICATE('d',25000))
INSERT #TEMP VALUES(REPLICATE('e',25000))

SELECT *
FROM #temp

SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid

user_objects_alloc_page_count =5
user_objects_dealloc_page_count = 0
internal_objects_alloc_page_count = 0
internal_objects_dealloc_page_count = 0

DROP TABLE #temp

SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid


Values in the columns
--------------------------------------
user_objects_alloc_page_count =5
user_objects_dealloc_page_count = 5
internal_objects_alloc_page_count = 0
internal_objects_dealloc_page_count = 0


CREATE TABLE #temp(id VARCHAR(MAX))
INSERT #TEMP VALUES(REPLICATE('a',25000))
INSERT #TEMP VALUES(REPLICATE('b',25000))
INSERT #TEMP VALUES(REPLICATE('c',25000))
INSERT #TEMP VALUES(REPLICATE('d',25000))
INSERT #TEMP VALUES(REPLICATE('e',25000))


SELECT *
FROM #temp

SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid


Values in the columns
--------------------------------------
user_objects_alloc_page_count =10
user_objects_dealloc_page_count = 5
internal_objects_alloc_page_count = 0
internal_objects_dealloc_page_count = 0


TRUNCATE TABLE #temp

SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid


Values in the columns
--------------------------------------
user_objects_alloc_page_count =10
user_objects_dealloc_page_count = 10
internal_objects_alloc_page_count = 0
internal_objects_dealloc_page_count = 0



DROP TABLE #temp1


so those were the use objects now we will talk about internal objects

Internal Objects
Internal objects are only in tempdb. The following objects are included in the internal object page counters:

• Work tables for cursor or spool operations and temporary large object (LOB) storage
  
• Work files for operations such as a hash join
  
• Sort runs
  

--Create the tables
CREATE TABLE #temp1(id INT, SomeCol VARCHAR(MAX))
INSERT #TEMP1 VALUES(1,REPLICATE('a',25000))
INSERT #TEMP1 VALUES(2,REPLICATE('b',25000))
INSERT #TEMP1 VALUES(3,REPLICATE('c',25000))
INSERT #TEMP1 VALUES(4,REPLICATE('d',25000))
INSERT #TEMP1 VALUES(5,REPLICATE('e',25000))


CREATE TABLE #temp2(id INT, SomeCol VARCHAR(MAX))
INSERT #TEMP2 VALUES(1,REPLICATE('a',25000))
INSERT #TEMP2 VALUES(2,REPLICATE('b',25000))
INSERT #TEMP2 VALUES(3,REPLICATE('c',25000))
INSERT #TEMP2 VALUES(4,REPLICATE('d',25000))
INSERT #TEMP2 VALUES(5,REPLICATE('e',25000))


--Do a sort operation
SELECT *
FROM#TEMP2 t2
JOIN #TEMP1 t1 ON t1.id = t2.id
ORDER BY t1.id,t1.SomeCol,t2.id,t2.SomeCol


--Check the view
SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@spid


Values in the columns
--------------------------------------
user_objects_alloc_page_count =20
user_objects_dealloc_page_count = 10
internal_objects_alloc_page_count = 16
internal_objects_dealloc_page_count = 8


What is this dmv useful for? You can use it to to help you diagnose and troubleshoot problems caused by insufficient disk space in the tempdb database.

The following error messages indicate insufficient disk space in the tempdb database. These errors can be found in the SQL Server error log, and may also be returned to any running application.

1101 or 1105
Any session must allocate space in tempdb.

3959
The version store is full. This error usually appears after a 1105 or 1101 error in the log.

3967
The version store is forced to shrink because tempdb is full.

3958 or 3966
A transaction cannot find the required version record in tempdb.


Some of these queries that you can use can be found here: Troubleshooting Insufficient Disk Space in tempdb