Who’s Using All that Space in tempdb, and What’s their Plan?

Whatcha Doing in My TempDb???

This post contains a script that I adapted from the fantastic SQL Server Storage Engine Blog.

It comes in handy in my job all the time! Sometimes tempdb is filling up, but sometimes I just want to monitor the amount of tempdb and check out execution plans of heavy tempdb users while watching performance on a server. It just really comes in handy more frequently than I would have thought before I started using it.

Note: This script returns space used in tempdb only, regardless of the db context it’s run in, and it only works for tempdb.

The Sample Code

and here it is…

--Modified from http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/12/tempdb-monitoring-and-troubleshooting-out-of-space.aspx

select
    t1.session_id
    , t1.request_id
    , task_alloc_GB = cast((t1.task_alloc_pages * 8./1024./1024.) as numeric(10,1))
    , task_dealloc_GB = cast((t1.task_dealloc_pages * 8./1024./1024.) as numeric(10,1))
    , host= case when t1.session_id <= 50 then 'SYS' else s1.host_name end
    , s1.login_name
    , s1.status
    , s1.last_request_start_time
    , s1.last_request_end_time
    , s1.row_count
    , s1.transaction_isolation_level
    , query_text=
        coalesce((SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
          (CASE WHEN statement_end_offset = -1
              THEN LEN(CONVERT(nvarchar(max),text)) * 2
                   ELSE statement_end_offset
              END - t2.statement_start_offset)/2)
        FROM sys.dm_exec_sql_text(t2.sql_handle)) , 'Not currently executing')
    , query_plan=(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle))
from
    (Select session_id, request_id
    , task_alloc_pages=sum(internal_objects_alloc_page_count +   user_objects_alloc_page_count)
    , task_dealloc_pages = sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count)
    from sys.dm_db_task_space_usage
    group by session_id, request_id) as t1
left join sys.dm_exec_requests as t2 on
    t1.session_id = t2.session_id
    and t1.request_id = t2.request_id
left join sys.dm_exec_sessions as s1 on
    t1.session_id=s1.session_id
where
    t1.session_id > 50 -- ignore system unless you suspect there's a problem there
    and t1.session_id <> @@SPID -- ignore this request itself
order by t1.task_alloc_pages DESC;
GO

,

6 Responses to Who’s Using All that Space in tempdb, and What’s their Plan?

  1. James December 2, 2015 at 9:34 am #

    How can i historically save all queries that hit my tempdb? My tempdb keeps filling up?

    • Kendra Little December 2, 2015 at 9:39 am #

      Great question. There isn’t a great way to do it by trace, which is part of why I’d originally written this query.

      You could trace all queries and look for anything that uses a temp table or table variable of any sort. But that’s a lot of overhead, and it also won’t catch things that use tempdb behind the scenes like hash joins, etc.

      This gets interesting with the 2016 Query Store feature. There’s not an obvious way to do it looking at tempdb proper (see https://www.brentozar.com/archive/2015/11/the-case-for-query-store-in-tempdb/).

      But you could potentially look for longrunning queries in a user database that had temp tables or table variables in query store. (You’ll have to have it on, and there’s going to be overhead, but it’s got more potential uses than a trace.)

  2. Eric81 January 18, 2017 at 12:25 pm #

    Kendra,

    Great article, and the script is awesome. I have a single procedure that is consuming over 300 GB of tempdb space all of a sudden. It uses temp tables and I can’t find an execution plan for it in plan cache nor can I let it run to completion since it consumes all of my tempdb space. The other issue is its made up of a lot of t-sql code so I can’t pinpoint the specific code.

    Every run into this before?

    • Kendra Little January 23, 2017 at 10:02 am #

      I haven’t run into this before. When the used space in tempdb starts to grow, can you see the queries running and get the plan with sp_whoisactive, or alternatively do you have a monitoring tool that watches queries running in the server?

      • eric81 January 23, 2017 at 8:18 pm #

        I ended up using your script from this article to trap the t-sql statement and identify where the issue was in the code. Turns out it was pulling in to many records using one of the inner joins, but took a bit of time to figure it out since I couldn’t generate an execution plan. This was a first time for me as well.

Trackbacks/Pingbacks

  1. SQL Server – Tempdb – v2012 | Learning in the Open - December 5, 2016

    […] Kendra Little Who’s Using All that Space in tempdb, and What’s their Plan? August 27, 2009 Who’s Using All that Space in tempdb, and What’s their Plan? */ ;with cteTaskSpaceUsage ( session_id , request_id , task_alloc_pages , task_dealloc_pages ) as ( […]

Leave a Reply