FrugalShaun
FrugalShaun

Reputation: 176

SQL Server TempDB - Will a Hash Match operator always cause tempdb allocations?

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!

Upvotes: 0

Views: 944

Answers (0)

Related Questions