Hash Join ~ Damn thing wont just go away

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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