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.htmlBefore 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_idsmallint
Session ID.
-- session_id maps to session_id in sys.dm_exec_sessions.
database_idsmallint
Database ID.
user_objects_alloc_page_countbigint
Number of pages reserved or allocated for user objects by this session.
user_objects_dealloc_page_countbigint
Number of pages deallocated and no longer reserved for user objects by this session.
internal_objects_alloc_page_countbigint
Number of pages reserved or allocated for internal objects by this session.
internal_objects_dealloc_page_countbigint
Number of pages deallocated and no longer reserved for internal objects by this session.
First we will talk about user objects
User ObjectsThe 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 =
@@spidValues 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 =
@@spiduser_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 =
@@spidValues 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 =
@@spidValues 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 =
@@spidValues 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 ObjectsInternal 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 =
@@spidValues 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 1105Any session must allocate space in tempdb.
3959The version store is full. This error usually appears after a 1105 or 1101 error in the log.
3967The version store is forced to shrink because tempdb is full.
3958 or 3966A 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
has written a SSIS Connectivity Whitepaper(still a draft)
in 1980 which would become the fourth best-selling album in the United States, tied with Billy Joel's Greatest Hits Vol. 1 & II with 21 million copies sold
.
.
