Can Query Store Show Spills To Tempdb?

Question

I know that Query Store can show tempdb usage as of SQL Server 2017, but can it show spills to tempdb? I'm highly confident that the plan cache can.

sys.dm_exec_query_stats contains spill stats SQL Server 2017 CU3 + : learn.microsoft.com/en-us/sql/relational-databases/… — Mitch Wheat
– Mitch Wheat, Commented Feb 17, 2024 at 4:33
If it was going to store that aggregated information it would be in sys.query_store_runtime_stats - and no such columns there - so looks like not persisted to query store — Martin Smith
– Martin Smith, Commented Feb 17, 2024 at 9:37
@MitchWheat That's helpful, but I'm pretty sure that's got nothing to do with Query Store. — J. Mini
– J. Mini, Commented Feb 17, 2024 at 14:20

Erik Darling · Accepted Answer · 2024-02-17 16:14:12Z

nope

But you may be able to infer that a select spilled to disk if values for average or total writes are "high", and the query plan:

If you're on SQL Server 2017+, sys.query_store_runtime_stats has columns for tempdb space used, but this is not specific to spills.

number of pages used in tempdb for the query plan within the aggregation interval (expressed as a number of 8-KB pages).

They have the standard min, max, last, etc. prefixes appended to _tempdb_space_used.