0

I've been investigating high write latency figures in tempdb on a data warehouse server (about 600ms!). As part of that investigation, I've identified a query that is causing significant internal object allocation in tempdb (about 8GB worth)

Now, this is a data warehouse query so this isn't entirely surprising (it's doing over 13 million reads). When I examined the plan, I was expecting to see a bunch of sort warnings relating to tempdb spills that would explain the internal object creation. What I'm actually seeing is some pretty hefty Hash Match operators, but no warnings, and nothing that would suggest (to me) heavy tempdb usage.

So my question is, will a hash-match ALWAYS cause tempdb internal object creation, and therefore what I'm seeing in the plan does indeed explain the internal object creation? Or should (as I assumed) this only occur where there are spill warnings (caused by an insufficient memory grant)?

Running SQL Server 2014 SP1

Query Plan: https://www.brentozar.com/pastetheplan/?id=S16ZQET4S

Query for capturing the tempdb allocations: https://www.mssqltips.com/sqlservertip/4356/track-sql-server-tempdb-space-usage/

Thanks in advance!

FrugalShaun
  • 166
  • 7
  • Grouping on so many fields is most always sign of a badly written query. Determine the minimum nr of grouping fields, rewrite the query to do the grouping in a derived table for those fields, and join that derived table with tables to include the other columns for the final query. – TT. Aug 23 '19 at 11:48
  • Thanks for your reply. For this purpose let's assume that re-writing the query isn't possible (I didn't write it incidentally). The main purpose of my question is to understand whether hash-matches will always cause tempdb internal object creation, to try and explain what I'm seeing. – FrugalShaun Aug 27 '19 at 08:01
  • I think it's best to plug this link that describes this, rather than repeating: [Understanding Hash, Sort and Exchange Spill events](http://rusanu.com/2011/10/19/understanding-hash-sort-and-exchange-spill-events/) – TT. Aug 27 '19 at 09:38

0 Answers0