Post: Who's Using All that Space in tempdb, and What's their Plan?

comments migrated from wordpress

James December 2, 2015 9:34 am How can i historically save all queries that hit my tempdb? My tempdb keeps filling up?

Reply Kendra Little December 2, 2015 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.)

Reply SQL Server – Tempdb – v2012 | Learning in the Open December 5, 2016 6:18 pm […] 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 ( […]

Reply Eric81 January 18, 2017 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?

Reply Kendra Little January 23, 2017 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?

Reply eric81 January 23, 2017 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.

Reply John G Hohengarten April 28, 2020 4:09 pm Thanks for the script, Kendra! With some guidance from Adam Machanic for the finding the right DMV, this query could be enhanced by adding this, perhaps to just under task_dealloc_GB:

,(SELECT CAST( SUM(unallocated_extent_page_count) * 8./1024./1024. AS NUMERIC(10,1)) FROM sys.dm_db_file_space_usage WHERE database_id = 2 ) AS available_tempdb_GB — JGH 4/28/2020 added

LitKnd / littlekendracomments

Post: Who's Using All that Space in tempdb, and What's their Plan? #21