Problem
You have a SQL Server instance on which the TempDB database is starting to grow very fast. In this tip I will show you two Dynamic Management Views that give us information required to determine how much TempDB space is being used by each session.
Solution
There are two Dynamic Management Views that aid us when troubleshooting SQL Server TempDB usage. These are sys.dm_db_session_space_usage and sys.dm_db_task_space_usage. The first one returns the number of pages allocated and deallocated by each session, and the second returns page allocation and deallocation activity by task.
Before starting to discuss these DMVs I must add that there is another Dynamic Management View related to SQL Server TempDB usage which is sys.dm_db_file_space_usage, but it only shows how the space is being used, not who is using it or from what query.
Both sys.dm_db_session_space_usage and sys.dm_db_task_space_usage views look similar if we compare columns and this shouldn’t surprise us. The reason behind this is because sys.dm_db_session_space_usage contains summarized data of sys.dm_db_task_space_usage. When a session executes a query, SQL Server creates one or more tasks to service that request. So if you query the sys.dm_db_task_space_usage view for that session when the query is still running you will get the space being used for each active task. On the other hand, if you query sys.dm_db_session_space_usage view while the session is still running you won’t see any change on the view results.
On the next table I copied from the MSDN page the columns in common of both sys.dm_db_session_space_usage and sys.dm_db_task_space_usage system views.
| Column name | Data type | Description |
|---|---|---|
session_id | smallint | Session ID. |
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 |
internal_objects_alloc_page_count | bigint | Number of pages reserved or allocated for internal objects by this |
internal_objects_dealloc_page_count | bigint | Number of pages deallocated and no longer reserved for internal objects |
The extra columns of sys.dm_db_task_space_usage are request_id and exec_context_id that adds information about the active request and its tasks.
Notice that both views distinguish between user and internal objects. This distinction will give you some insight into where the problem lies, but first let me give you a brief explanation about what those kinds of objects are: User objects are temporary tables (both session and global), table variables, the result of table valued functions which in fact are an implicit table variable. Internal objects are those created to process a query like a hash join, a sort spilling into TempDB or a spool operation.
When you see that a session has too much TempDB usage, you should take a look if it’s caused by internal or user objects. If there are too many pages allocated to user objects, probably the session has big temporary tables. But if the page allocation for internal objects is big, then you should take a look at the query plans, find the responsible query and try to optimize it.
SQL Server TempDB Usage Queries
The following query uses sys. dm_db_session_space_usage view to show the total and net allocation of both user and internal objects and the last query executed by the session. Notice that in order to get the space allocated in megabytes we need to divide the number of pages by 128. That’s because the page size is 8 kilobytes resulting in 128 pages per megabyte.
SELECT SS.session_id , SS.database_id ,
CAST(SS.user_objects_alloc_page_count / 128 AS DECIMAL(15, 2)) [Total Allocation User Objects MB] ,
CAST(( SS.user_objects_alloc_page_count
- SS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15, 2)) [Net Allocation User Objects MB] ,
CAST(SS.internal_objects_alloc_page_count / 128 AS DECIMAL(15, 2)) [Total Allocation Internal Objects MB] ,
CAST(( SS.internal_objects_alloc_page_count
- SS.internal_objects_dealloc_page_count ) / 128 AS DECIMAL(15,
2)) [Net Allocation Internal Objects MB] ,
CAST(( SS.user_objects_alloc_page_count
+ internal_objects_alloc_page_count ) / 128 AS DECIMAL(15, 2)) [Total Allocation MB] ,
CAST(( SS.user_objects_alloc_page_count
+ SS.internal_objects_alloc_page_count
- SS.internal_objects_dealloc_page_count
- SS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15, 2)) [Net Allocation MB] ,
T.text [Query Text]
FROM sys.dm_db_session_space_usage SS
LEFT JOIN sys.dm_exec_connections CN ON CN.session_id = SS.session_id
OUTER APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) TOn the other hand, the next query uses sys.dm_db_task_space_usage view to show the total and net allocation of user and internal objects as well as the query being executed by each active task.
SELECT TS.session_id ,
TS.request_id ,
TS.database_id ,
CAST(TS.user_objects_alloc_page_count / 128 AS DECIMAL(15, 2)) [Total Allocation User Objects MB] ,
CAST(( TS.user_objects_alloc_page_count
- TS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15, 2)) [Net Allocation User Objects MB] ,
CAST(TS.internal_objects_alloc_page_count / 128 AS DECIMAL(15, 2)) [Total Allocation Internal Objects MB] ,
CAST(( TS.internal_objects_alloc_page_count
- TS.internal_objects_dealloc_page_count ) / 128 AS DECIMAL(15,
2)) [Net Allocation Internal Objects MB] ,
CAST(( TS.user_objects_alloc_page_count
+ internal_objects_alloc_page_count ) / 128 AS DECIMAL(15, 2)) [Total Allocation MB] ,
CAST(( TS.user_objects_alloc_page_count
+ TS.internal_objects_alloc_page_count
- TS.internal_objects_dealloc_page_count
- TS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15, 2)) [Net Allocation MB] ,
T.text [Query Text]
FROM sys.dm_db_task_space_usage TS
INNER JOIN sys.dm_exec_requests ER ON ER.request_id = TS.request_id
AND ER.session_id = TS.session_id
OUTER APPLY sys.dm_exec_sql_text(ER.sql_handle) T
But it doesn’t end here, because we can merge both queries to get space usage by session including the current requests.
SELECT COALESCE(T1.session_id, T2.session_id) [session_id] , T1.request_id ,
COALESCE(T1.database_id, T2.database_id) [database_id],
COALESCE(T1.[Total Allocation User Objects], 0)
+ T2.[Total Allocation User Objects] [Total Allocation User Objects] ,
COALESCE(T1.[Net Allocation User Objects], 0)
+ T2.[Net Allocation User Objects] [Net Allocation User Objects] ,
COALESCE(T1.[Total Allocation Internal Objects], 0)
+ T2.[Total Allocation Internal Objects] [Total Allocation Internal Objects] ,
COALESCE(T1.[Net Allocation Internal Objects], 0)
+ T2.[Net Allocation Internal Objects] [Net Allocation Internal Objects] ,
COALESCE(T1.[Total Allocation], 0) + T2.[Total Allocation] [Total Allocation] ,
COALESCE(T1.[Net Allocation], 0) + T2.[Net Allocation] [Net Allocation] ,
COALESCE(T1.[Query Text], T2.[Query Text]) [Query Text]
FROM ( SELECT TS.session_id ,
TS.request_id ,
TS.database_id ,
CAST(TS.user_objects_alloc_page_count / 128 AS DECIMAL(15,
2)) [Total Allocation User Objects] ,
CAST(( TS.user_objects_alloc_page_count
- TS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15,
2)) [Net Allocation User Objects] ,
CAST(TS.internal_objects_alloc_page_count / 128 AS DECIMAL(15,
2)) [Total Allocation Internal Objects] ,
CAST(( TS.internal_objects_alloc_page_count
- TS.internal_objects_dealloc_page_count ) / 128 AS DECIMAL(15,
2)) [Net Allocation Internal Objects] ,
CAST(( TS.user_objects_alloc_page_count
+ internal_objects_alloc_page_count ) / 128 AS DECIMAL(15,
2)) [Total Allocation] ,
CAST(( TS.user_objects_alloc_page_count
+ TS.internal_objects_alloc_page_count
- TS.internal_objects_dealloc_page_count
- TS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15,
2)) [Net Allocation] ,
T.text [Query Text]
FROM sys.dm_db_task_space_usage TS
INNER JOIN sys.dm_exec_requests ER ON ER.request_id = TS.request_id
AND ER.session_id = TS.session_id
OUTER APPLY sys.dm_exec_sql_text(ER.sql_handle) T
) T1
RIGHT JOIN ( SELECT SS.session_id ,
SS.database_id ,
CAST(SS.user_objects_alloc_page_count / 128 AS DECIMAL(15,
2)) [Total Allocation User Objects] ,
CAST(( SS.user_objects_alloc_page_count
- SS.user_objects_dealloc_page_count )
/ 128 AS DECIMAL(15, 2)) [Net Allocation User Objects] ,
CAST(SS.internal_objects_alloc_page_count / 128 AS DECIMAL(15,
2)) [Total Allocation Internal Objects] ,
CAST(( SS.internal_objects_alloc_page_count
- SS.internal_objects_dealloc_page_count )
/ 128 AS DECIMAL(15, 2)) [Net Allocation Internal Objects] ,
CAST(( SS.user_objects_alloc_page_count
+ internal_objects_alloc_page_count ) / 128 AS DECIMAL(15,
2)) [Total Allocation] ,
CAST(( SS.user_objects_alloc_page_count
+ SS.internal_objects_alloc_page_count
- SS.internal_objects_dealloc_page_count
- SS.user_objects_dealloc_page_count )
/ 128 AS DECIMAL(15, 2)) [Net Allocation] ,
T.text [Query Text]
FROM sys.dm_db_session_space_usage SS
LEFT JOIN sys.dm_exec_connections CN ON CN.session_id = SS.session_id
OUTER APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) T
) T2 ON T1.session_id = T2.session_id
Next Steps
- In the following tip you can see how to troubleshoot a sort spilling to TempDB: Correct SQL Server TempDB Spills in Query Plans Caused by Outdated Statistics.
- It’s very important to monitor TempDB growth, the following tip will teach you how to implement an alert when TempDB is growing: SQL Server Alert for TempDB Growing Out of Control.
- In case you need to track Buffer Pool space usage the following tip will guide you: Determine objects consuming the largest amount of space in the SQL Server buffer pool
- If you also need to Determine SQL Server memory use by database and object, this tip is the one for you.
- You don’t know what Dynamic Management Views are? Don’t worry; this tip explain this to you: Dynamic Management Views.
- Check out the SQL Server Dynamic Management Views and Functions Tip category.
