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.

Answer 1

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:

• Doesn't contain any spooling operators
• Does contain operators which may spill (sort, hash, etc.)

See Why would a SELECT query cause writes? for more details.

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.