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.

  • why are you confident?
    – nbk
    Commented Feb 17 at 1:05
  • @nbk sp_blitzcache reports on it.
    – J. Mini
    Commented Feb 17 at 1:06
  • sys.dm_exec_query_stats contains spill stats SQL Server 2017 CU3 + : learn.microsoft.com/en-us/sql/relational-databases/… Commented Feb 17 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 Commented Feb 17 at 9:37
  • @MitchWheat That's helpful, but I'm pretty sure that's got nothing to do with Query Store.
    – J. Mini
    Commented Feb 17 at 14:20

1 Answer 1



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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.