ben.mcintyre (9/4/2009)
Gail, I am still interested in what is going on here, specifically that the single change in an index from unique to nonunique can flip the query plan on its head.I'm also interested in how you analyse the plan for this. I suspect there is not any way to 'nudge' the query plan back, but if you worked one out I would be most impressed.
Same way you'd analyse any other plan, and same fix as for any other case of poor plans - rewrite the query, maybe add hints
If you feel it's a better use of time, I could give you my skype ID and do a verbal rundown rather than try and type it all.
I don't use skype, don't have the bandwidth to support it.
But it seems that the hints are exactly that - only hints which may be ignored - and what I'm trying to find out is whether there is some way to do this more reliably rather than hint and pray, ie: am I missing something ?
In my experience, hints are directives that the optimiser has to obey. That said, with tonnes of view and extra tables, the hints will probably have to be applied in the views, not in the outer query, plus it will be very hard to work out an optimal set of hints, especially when working with just the ones available in SQL 2000.
I have to deal with instantiating and dropping possibly several temp tables with dynamic names (since I have multiple sessions and each name needs to have the sessionid added to the end).
Why? SQL gives temp tables dynamic names itself. There's no need for you to do its work for it.
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