February 15, 2011 at 10:57 am
Ok so I have seperate hash match (inner join) showing up in my reasonably otherwise blemishless execution plan (actual) for some none-to-complicated three table join query....
The execution plan itself suggests no missing indexes.
I check the Hask Keys build and the hash keys probe entries , check the fields add a nonclustered index to the one field that doesnt have an index, run again ... still hash joins ...
I check the size of the inputs 500 rows apiece going in to the join so guessing its not sticking around due to volume of data
In a moment of desperation I do a sp_updatestats
Still no joy
What am I missing.. I thought hash matches went away when all the join fields where indexed /no large volumns of data .. or is my understanding somewhat floored ?
thanks Simon
February 15, 2011 at 11:24 am
Why do you want it to go away? Have you identified the hash join as a major bottleneck?
Table definitions, index definitions and execution plan please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2011 at 11:29 am
Hash joins are sometimes useful just because they're the fastest option for even relatively small data sets.
Check out http://msdn.microsoft.com/en-us/library/aa178578(v=SQL.80).aspx for more data. It says SQL 2000, but it's applicable to later versions too.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply