I have a stored procedure that works fine most of the time but every once in a while it chooses a nested loop instead of a hash match and the query will timeout in the application.
The query pulls information into a temporary table and then uses the temporary table multiple times to get at the information I need. In the bad plan it assumes there is 1 row in the temporary table which is why it chooses the nested loops. I can't seem to figure out how to tell the optimiser that it will have multiple rows only some of the time.
I have tried giving the temporary table a clustering key that is the same as from the base table but that doesn't appear to help.
I will also post the good plan that I get most of the time.
if there is any other information that I need to post please let me know.
Query that inserts into Temp Table
By Chris Wilson 48 asked Jul 31, 2014 at 03:32 PM
You will likely need to update statistics on the temporary table manually and add an
There are (even) more details about temporary table caching here:
By SQLkiwi ♦ 6.6k answered Jul 31, 2014 at 04:58 PM
My immediate thought is that the cached query plan is using bad statistics based on the time the plan was created. As a test, you can add a "with recompile" to the stored procedure to see if it clears the problem. If it does, then you know it is the cached plan hanging up. Do you drop the temporary table and recreate it in the SP? If not, you could also try that. Again, this is what just first popped into my mind.
By Edward Norris 96 answered Jul 31, 2014 at 03:39 PM
Have you tried to use a plan guide? If you know the plan you want to use every time, I'd specify it.
Are there other plans that could potentially be used based on the parameters? You might want other plans as well, in which case it might be better to add some filtered statistics or even indexes...
Hope I'm answering your question?! :) Winter
By Erickson Winter 1 answered Jul 31, 2014 at 03:52 PM