tempdb-Heavy Queries: Finding Them in Query Store

Tempdb-heavy queries are easy to miss, because the work is over before you look. Query Store keeps a record of how many tempdb pages each plan used. Rank that history first, then check what is running right now.

Why the live view usually misses it

You get the page at 3 AM: tempdb is nearly full. By the time you log in, the culprit has finished and released its space. The live views show a calm server. Nobody is guilty, and everybody is nervous.

Query Store records page use per plan, per time interval, in sys.query_store_runtime_stats. The columns are avg_tempdb_space_used and max_tempdb_space_used, counted in 8 KB pages. They describe past work, not the current size of your tempdb files.

The demo creates the SqlAuthorityDemo database, turns Query Store on with one-minute intervals, and drops the database at the end. The first block confirms the store is writable and the columns exist.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
ALTER DATABASE SqlAuthorityDemo
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL, INTERVAL_LENGTH_MINUTES = 1);
GO
USE SqlAuthorityDemo;
GO
SELECT actual_state_desc, query_capture_mode_desc
FROM sys.database_query_store_options;

SELECT name
FROM sys.all_columns
WHERE object_id = OBJECT_ID(N'sys.query_store_runtime_stats')
  AND name IN (N'avg_tempdb_space_used', N'max_tempdb_space_used')
ORDER BY name;

Create some tempdb-heavy work

We need a table, and two ways to use tempdb. One query sorts every row with a tiny memory grant, so the sort spills into tempdb. I cap the grant on purpose. In real life spills come from bad estimates. The other query copies the whole table into a temp table.

CREATE TABLE dbo.SortDemo
(Id int IDENTITY PRIMARY KEY, Category int NOT NULL, Pad char(200) NOT NULL);

INSERT dbo.SortDemo (Category, Pad)
SELECT TOP (200000) ABS(CHECKSUM(NEWID())) % 1000, REPLICATE('x', 200)
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;
GO
SELECT MAX(rn) AS SortedRows
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Pad, Category, Id) AS rn
      FROM dbo.SortDemo) AS x
OPTION (MAX_GRANT_PERCENT = 0.01);
GO
SELECT * INTO #Copy FROM dbo.SortDemo;
GO
SELECT COUNT(*) AS LightCount FROM dbo.SortDemo WHERE Category = 5;
GO 3
EXEC sys.sp_query_store_flush_db;

The last line asks Query Store to write its in-memory data to disk, so the next queries can see it. Without the flush, a fresh test often looks empty.

Rank by weighted page use

Multiply the average pages by the execution count, then add those up over your time window. Do not add up averages alone. A plan that ran once with a big average and a plan that ran a thousand times are different amounts of work.

The query below looks at the last day, successful executions only, and groups by query and plan. I keep only rows that used pages, so the quiet queries stay out of the way.

DECLARE @from datetimeoffset = DATEADD(day, -1, SYSUTCDATETIME());

SELECT TOP (20) p.query_id, p.plan_id,
       SUM(CONVERT(float, r.count_executions) * r.avg_tempdb_space_used) AS EstimatedTotalPages,
       MAX(r.max_tempdb_space_used) AS LargestExecutionPages,
       SUM(r.count_executions) AS RecordedExecutions
FROM sys.query_store_runtime_stats AS r
JOIN sys.query_store_runtime_stats_interval AS i
  ON i.runtime_stats_interval_id = r.runtime_stats_interval_id
JOIN sys.query_store_plan AS p ON p.plan_id = r.plan_id
WHERE i.end_time > @from AND r.execution_type = 0
GROUP BY p.query_id, p.plan_id
HAVING SUM(r.avg_tempdb_space_used) > 0
ORDER BY EstimatedTotalPages DESC, p.query_id, p.plan_id;

Two rows come back. The temp table copy is on top, with thousands of pages. The spilling sort is second, with a few hundred. The exact numbers will differ on your server, but the shape should look the same.

Attach the query text, then open the plan

Rank first, then join the text. That keeps the descriptive joins from multiplying your rows. Keep plan_id, because one query can have several plans with very different appetites.

DECLARE @from datetimeoffset = DATEADD(day, -1, SYSUTCDATETIME());

WITH RankedPlans AS
(SELECT TOP (5) p.query_id, p.plan_id,
        SUM(CONVERT(float, r.count_executions) * r.avg_tempdb_space_used) AS EstimatedTotalPages
 FROM sys.query_store_runtime_stats AS r
 JOIN sys.query_store_runtime_stats_interval AS i
   ON i.runtime_stats_interval_id = r.runtime_stats_interval_id
 JOIN sys.query_store_plan AS p ON p.plan_id = r.plan_id
 WHERE i.end_time > @from AND r.execution_type = 0
 GROUP BY p.query_id, p.plan_id
 HAVING SUM(r.avg_tempdb_space_used) > 0
 ORDER BY EstimatedTotalPages DESC, p.query_id, p.plan_id)
SELECT rp.query_id, rp.plan_id, rp.EstimatedTotalPages, t.query_sql_text
FROM RankedPlans AS rp
JOIN sys.query_store_query AS q ON q.query_id = rp.query_id
JOIN sys.query_store_query_text AS t ON t.query_text_id = q.query_text_id
ORDER BY rp.EstimatedTotalPages DESC, rp.query_id, rp.plan_id;

Now you can read the statements. Add p.query_plan to the select list when you want the plan itself. Look for sorts, hashes, spools and temp table access. A big number tells you who, but not which operator. And the cures differ. A spill needs better estimates or more memory. A temp table needs fewer rows, or a different design.

Compare with what is running now

sys.dm_db_task_space_usage shows tempdb pages allocated and released by each running task. The query below keeps only tasks that have allocated something.

SELECT session_id, request_id,
       SUM(user_objects_alloc_page_count - user_objects_dealloc_page_count) AS NetUserPages,
       SUM(internal_objects_alloc_page_count - internal_objects_dealloc_page_count) AS NetInternalPages
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id
HAVING SUM(user_objects_alloc_page_count + internal_objects_alloc_page_count) > 0
ORDER BY NetInternalPages DESC, session_id, request_id;

Run it here and it shows nothing worth reading. Our workload has finished, so the live view has no memory of it. Query Store does. Also remember that tempdb files keep their size after the work is released, so a large file does not mean a busy tempdb.

When you save a ranking, save the time window, the execution type and the date too. The weighted total is an estimate built from averages. Call it that.

DROP TABLE IF EXISTS #Copy;
GO
USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Next time tempdb fills up at night, let Query Store tell you who was there.

Tempdb history is not current occupancy, it is past demand.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.