SQL SERVER – Who is Consuming my TempDB Now?

Off late my love for TempDB and writing on topics of tempDB has been a great learning experience. The more I work with tempDB, the more fascinated I am. TempDb is being used by a number of operations inside SQL Server, let me list some of them here:

  • Temporary user objects like temp tables, table variables
  • Cursors
  • Internal worktables for spool and sorting
  • Row Versioning for snapshot isolation
  • Online Index rebuild operations
  • MARS (Multiple Active Resultsets)
  • AFTER Triggers and more

These are some of the ways in which tempdb in our servers get used. When I was talking about this to one of my DBA friends, he always asks some interesting questions. He said the previous blogs I wrote helped in understanding how to read temp tables, how to see table variable data. Now his requirement was different. He wanted to know if there was any script which will let him know who was consuming tempDB resources. If tempDB is used by many inside the SQL Server context, it is important that we know how to get this information from DMVs. There are a number of DMVs we can use:

  • dm_db_file_space_usage – Returns space usage information for each file in tempdb
  • dm_db_session_space_usage – Returns the number of pages allocated and deallocated by each session
  • dm_db_task_space_usage – Returns page allocation and deallocation activity by task
  • We can join these DMV’s with various other DMV’s like sys.dm_exec_sessions, sys.dm_exec_requests, etc and get to the actual TSQL statement and plan responsible for these allocations.

Here is a simple script that will outline the sessions which are using TempDB currently.
st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
st.objectid AS ModuleObjectId,
dmv_er.statement_start_offset/2 + 1,
CASE WHEN dmv_er.statement_end_offset = -1
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id ,
dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
FROM sys.dm_db_task_space_usage dmv_tsu
INNER JOIN sys.dm_exec_requests dmv_er
ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id)
INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st
WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) > 0
ORDER BY (dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) DESC

Have you ever had such requirements to monitor and troubleshoot tempDB in your environments? What have you been using to monitor your tempDB usage? What is the typical output you are getting in your environments? Do let me know as we can learn together.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – The DBA goes to MARS – Maintenance, Architecture, Research and Support – Notes from the Field #063
Next Post
SQL SERVER – How to Bind Resource Governor for InMemory Enabled Databases?

Related Posts

28 Comments. Leave new

  • I use WhoisActive for that. then sort by tempdb space used . it store in a table for analysis use

  • I run your script and the result is empty! it should not be true as this is one of the busiest MS SQL server.

  • Me too I am getting empty result set
    when I ran the below query I am getting 5 results but I cant sense the impact on temp db
    use tempdb
    sys.dm_exec_sessions.session_id AS [SESSION ID]
    ,DB_NAME(database_id) AS [DATABASE Name]
    ,HOST_NAME AS [System Name]
    ,program_name AS [Program Name]
    ,login_name AS [USER Name]
    ,cpu_time AS [CPU TIME (in milisec)]
    ,total_scheduled_time AS [Total Scheduled TIME (in milisec)]
    ,total_elapsed_time AS [Elapsed TIME (in milisec)]
    ,(memory_usage * 8) AS [Memory USAGE (in KB)]
    ,(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)]
    ,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)]
    ,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)]
    ,(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)]
    ,CASE is_user_process
    WHEN 1 THEN ‘user session’
    WHEN 0 THEN ‘system session’
    END AS [SESSION Type], row_count AS [ROW COUNT]
    INNER join
    ON sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id
    where status = ‘running’

  • yah pinal ! I removed the where filter and it works . how to say exactly this program or service has caused the temb deb to grow by this much . Why I am insisting is because we use sql cluster 2008 having lot of databases and our admins wants to which program is exactly consuming tempdb. from your query we see 4 columns reads, writes, logical reads and granted query memory along with cpu time and query elapsed time what exactly will be the first criteria which you can say definitely if this column is high, temp db usage will definitely be very high .

  • divyasharma545
    May 5, 2015 3:30 pm

    thanks pinal for this brave script helped me alott !!

  • sudheer1242dheer
    August 5, 2015 12:01 pm

    Hi Pinal,

    Can you remove the filters and send the script to me..

  • Hi Pinal,
    I am running into one of the issue where my tempDB is growing in exponentially. Last week its size was 100 GB and we have increased to 130 GB and now that also consumed and getting error to increase the space.

    I run your query without where clause but it is not giving me any details through which I can identify who is taking so much space on tempDB?

    I run
    select Top 5 * from tempdb.sys.dm_db_task_space_usage
    order by internal_objects_alloc_page_count desc

    query and output of query reported internal_object_alloc_page_count for two session id is:


    I suspect this is the cause which is eating up the space of tempDB but not able to extract who is using this session and what query is taking this space?

    If you have any idea then please let me know.

  • Hi, Could you pls explain the output fields that come as a result of this query as well.

  • Michael Hanna
    July 3, 2016 4:18 pm

    is there any way to get the query consumption in MB ?

  • Dear Sir,

    If we execute a query that are taking longer time. So i want to know this is using tempDB or our selected user DB ?

  • Thank you it works! Great!
    May I know which part of the columns we can tell a query is consuming much space? Is there any?

      dmv_er.statement_start_offset/2 + 1,
      (CASE WHEN dmv_er.statement_end_offset = -1
      ELSE dmv_er.statement_end_offset
      END – dmv_er.statement_start_offset)/2) AS Query_Text,

  • Thank you Pinal. The query is very helpful.

  • In my query for XML PATH statement is using lot of tempDB. Is there any way to reduce use of tempDB ? I used for XML PATH for concatenating multiple rows into one

    • I too facing the same problem.. SET STATISTICS XML OFF is using lot of tempdb. Why is this causing an issue ?

  • Naveed Akbar
    May 15, 2018 9:44 pm

    Amazing man you are always super.

  • Jitendra Kumar Gupta
    July 22, 2018 4:16 pm

    Does CDC implement effect tempdb effect and any performance issue ??

  • Hi Pinal,

    Thanks for your help. I have modify this bit further. It may help some:

    SPU.session_id AS SESSION_ID
    ,DB_NAME(SPU.database_id) AS DATABASE_Name
    ,S.program_name AS ProgramName
    ,S.login_name AS Login_name
    ,S.status AS Status
    ,(S.cpu_time/1000) AS CPU_TIME_In_Seconds
    ,(S.total_scheduled_time/1000) AS Total_Scheduled_TIME_In_Seconds
    ,(S.total_elapsed_time/1000) AS Elapsed_TIME_In_Seconds
    ,CASE is_user_process
    WHEN 1 THEN ‘user session’
    WHEN 0 THEN ‘system session’
    , S.row_count AS Row_Count

    , ((Sum(S.memory_usage) * 8)/1024) AS Memory_USAGE_In_MB
    ,(((Sum(SPU.user_objects_alloc_page_count) – Sum(SPU.user_objects_dealloc_page_count)) * 8)/1024) AS SPACE_Allocated_USER_Objects_MB
    ,(((Sum(SPU.internal_objects_alloc_page_count) – Sum(SPU.internal_objects_dealloc_page_count)) * 8)/1024) AS SPACE_Allocated_FOR_Internal_Objects_MB
    , IsNUll(( SELECT TEXT FROM sys.dm_exec_sql_text(SP.sql_handle)),”) As SQLCommand
    FROM sys.dm_db_session_space_usage SPU
    LEFT JOIN sys.dm_exec_sessions S ON SPU.session_id = S.session_id
    LEFT JOIN master..sysprocesses SP ON SP.spid = S.session_id
    Group by
    , S.row_count , SP.sql_handle
    Order by (
    (Sum(SPU.user_objects_alloc_page_count) – Sum(SPU.user_objects_dealloc_page_count) )
    + (Sum(SPU.internal_objects_alloc_page_count) – Sum(SPU.internal_objects_dealloc_page_count))
    ) DESC

  • Torben Hansen
    June 29, 2022 2:36 pm

    Hi Pinal.
    Can you help me understand, this..
    sys.dm_db_session_space_usage can tell you how much tempdb (user / Internal) there are requested and deallocated after each request. where dm_db_task_space_usage can give it for each context_id, shoud I then SUM all user_objects_alloc_page_count to get what is allocated, and sum user_objects_dealloc_page_count for how much there are deallocated, but subtract one from the other can give negative values, and don’t tell me
    How much store in TempDB is a session using right NOW.
    I “only” need the actual allocated TempDB at the given moment

  • Hi Pinal, what I am seeing in these days is very very strange.. for me
    I was looking at a Sql Server 2008 R2 which had page writes/sec almost 3K/sec while page reads and read-ahead page/sec almost zero..
    Finding no other way than seeing what db file is accessed for read/write, Tempdb resulted the suspected one.

    Briefly, there is someone running a query on a user db, query rather complex and inefficient (costing more than 400), every minute, not one but three times on the same moment.
    Apart from this last thing, 3 instead of 1, and apart obviously from optimizing query, my question is where is that I can find that the execution plan uses Tempdb in this way, what is the operator responsible for this such a large read/write (when executing this query page writes/sec often reaches 1M..
    I know you are very expert and careful with all what relates to Sql Server and other things.
    Probably you have already seen hundreds of similar cases, for me this is a really odd and strange case..

    Thank you for every tip and trick and reasoning which you could have about what I wrote above
    and many thanks for your good work and sharing


    PS: of course, I know I should study more and more Sql Server execution plans, and it’s what I’m going to do..

  • In case a database has active snapshot isolation, tempdb is used as storage for record-snapshots after a transaction began. In this case you won’t find queries actually “using” the tempdb, instead tempdb is cluttered with versions of all records that were touched since the long running transaction begun. Curiously, running the space-used-report in sqlserver looks like there is no space allocated, which definitely is false. Instead, the transaction-log of the affected database is filled (as the transaction is still running), but the record snapshots are not stored there.
    So when investigating tempdb, also take a look at running transactions.


Leave a Reply