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:

Solarwinds
  • 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.
SELECT
st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
st.objectid AS ModuleObjectId,
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(
CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id ,
dmv_tsu.request_id,
dmv_tsu.exec_context_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,
dmv_er.start_time,
dmv_er.command,
dmv_er.open_transaction_count,
dmv_er.percent_complete,
dmv_er.estimated_completion_time,
dmv_er.cpu_time,
dmv_er.total_elapsed_time,
dmv_er.reads,dmv_er.writes,
dmv_er.logical_reads,
dmv_er.granted_query_memory,
dmv_es.HOST_NAME,
dmv_es.login_name,
dmv_es.program_name
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)
CROSS
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)

Solarwinds
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

23 Comments. Leave new

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

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

    Reply
  • 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
    SELECT
    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]
    ,status
    ,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]
    FROM
    sys.dm_db_session_space_usage
    INNER join
    sys.dm_exec_sessions
    ON sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id
    where status = ‘running’

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

    Reply
  • divyasharma545
    May 5, 2015 3:30 pm

    thanks pinal for this brave script helped me alott !!

    Reply
  • sudheer1242dheer
    August 5, 2015 12:01 pm

    Hi Pinal,

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

    Reply
  • 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:

    14594600
    3268792

    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.

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

    Reply
  • Michael Hanna
    July 3, 2016 4:18 pm

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

    Reply
  • 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 ?

    Reply
  • 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?

    Reply
    • SUBSTRING(st.TEXT,
      dmv_er.statement_start_offset/2 + 1,
      (CASE WHEN dmv_er.statement_end_offset = -1
      THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
      ELSE dmv_er.statement_end_offset
      END – dmv_er.statement_start_offset)/2) AS Query_Text,

      Reply
  • Thank you Pinal. The query is very helpful.

    Reply
  • 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

    Reply
  • Naveed Akbar
    May 15, 2018 9:44 pm

    Amazing man you are always super.

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

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

    Reply
  • Hi Pinal,

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

    SELECT
    SPU.session_id AS SESSION_ID
    ,DB_NAME(SPU.database_id) AS DATABASE_Name
    ,S.HOST_NAME AS HOSTNAME
    ,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’
    END AS SESSION_Type
    , 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
    SPU.session_id
    ,DB_NAME(SPU.database_id)
    ,S.HOST_NAME
    ,S.program_name
    ,S.login_name
    ,S.status
    ,S.cpu_time
    ,total_scheduled_time
    ,total_elapsed_time
    ,is_user_process
    , 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

    Reply

Leave a Reply

Menu