Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

Track SQL Server TempDB Space Usage


By:   |   Last Updated: 2016-06-30   |   Comments (3)   |   Related Tips: More > System Databases

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 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.

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) T

On 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


Last Updated: 2016-06-30


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, February 21, 2018 - 5:42:40 AM - pl80 Back To Top

 

 

Daniel,

 

What exactly is space deallocated by a query?  Is this something that cannot be used by another query?


Tuesday, August 15, 2017 - 10:28:40 PM - Daniel Farina Back To Top

Hi Steve!

Yes, you have to install the client tools of those versions.

 

Best Regards!


Tuesday, August 15, 2017 - 1:13:27 PM - Steve Hall Back To Top

 Is there a way to update the sqlcmd that came from SQl 2005 to the one used by 2008R2 or 2012?

 


Learn more about SQL Server tools