Home Forums SQL Server 2008 T-SQL (SS2K8) Troubleshooting\Tuning sql query taking more than 40 secs RE: Troubleshooting\Tuning sql query taking more than 40 secs

  • Your move of the join changed the way the optimiser 'wandered' through the search space, resulting in it finding a different 'good enough' plan.

    I say not to do it, because it's in no way deterministic, you could change something else (swap two columns in the group by) and have the same effect. If the optimiser did a complete search of the plan space, this would never happen, but since no one wants to spend days waiting for a plan, it can.

    Remember the optimiser is NEVER looking for the best plan, it's looking for a good plan fast. In one case if found a good plan that was actually crap, in the other it found a good plan that was good. In that case, I'd be looking over the query to see if there's anything in it which is prone to cause the optimiser problems.

    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