-1

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.

J. Mini
  • 109
  • 2
  • 15
  • why are you confident? – nbk Feb 17 '24 at 01:05
  • @nbk sp_blitzcache reports on it. – J. Mini Feb 17 '24 at 01:06
  • sys.dm_exec_query_stats contains spill stats SQL Server 2017 CU3 + : https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-ver16 – Mitch Wheat Feb 17 '24 at 04: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 Feb 17 '24 at 09:37
  • @MitchWheat That's helpful, but I'm pretty sure that's got nothing to do with Query Store. – J. Mini Feb 17 '24 at 14:20
  • It hasn't. why do you care where the info comes from? AFAIK it is not available from QS currently. – Mitch Wheat Feb 17 '24 at 14:35
  • @MitchWheat Because Query Store contains far more long-lasting information than the plan cache. – J. Mini Feb 17 '24 at 14:43
  • FWIW, sp_blitzcache also (in your own words) "has nothing to do with query store". If you're satisfied with it, then you should be equally satisfied with what Mitch presented you. – J.D. Feb 17 '24 at 15:01
  • @J.Mini : usually when one is looking at TempDb spills, its related about something that is happening now or very recently, not a month ago. – Mitch Wheat Feb 18 '24 at 01:02

1 Answers1

4

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.

Erik Darling
  • 40,781
  • 14
  • 130
  • 456