How can I avoid this bad plan

Good morning,

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.

EDIT:

Query that inserts into Temp Table

 /**********************************************************************/
 CREATE TABLE #Table10(
     Col1 [varchar](50) NOT NULL,
     Col2 [varchar](50) NOT NULL,
     Col3 [varchar](50) NOT NULL,
     Col4 [varchar](50) NOT NULL,
     Col5 [int] NULL,
     Col6 [uniqueidentifier] NULL,
     Col7 [varchar](75) NOT NULL,
     Col8 [int] NULL,
     Col9 [uniqueidentifier] NULL,
     Col10 [varchar](75) NOT NULL,
     Col11 [varchar](30) NOT NULL,
     Col12 [varchar](200) NOT NULL,
     Col13 [varchar](200) NOT NULL,
     Col14 [varchar](200) NOT NULL,
     Col15 [varchar](75) NOT NULL,
     Col16 [varchar](25) NOT NULL,
     Col17 [varchar](50) NOT NULL,
     Col18 [varchar](75) NOT NULL,
     Col19 [varchar](50) NOT NULL,
     Col20[varchar](50) NOT NULL,
     Col21 [varchar](30) NOT NULL,
     Col22 [varchar](150) NOT NULL,
     Col23 [varchar](75) NOT NULL,
     Col24 [varchar](50) NOT NULL,
     Col25 [varchar](50) NOT NULL,
     Col26 [varchar](30) NOT NULL,
     Col27 [varchar](150) NOT NULL,
     Col28 [varchar](100) NOT NULL,
     Col29 [varchar](50) NOT NULL,
     Col30 [varchar](25) NOT NULL,
     Col31 [varchar](30) NOT NULL,
     Col32 [smalldatetime] NOT NULL,
     Col33 [bit] NOT NULL,
     Col34 [varchar](30) NOT NULL,
     Col35 [smalldatetime] NOT NULL,
     Col36 [uniqueidentifier] NOT NULL,
     Col37 [money] NOT NULL,
     Col38 [int] NOT NULL,
     Col39 tinyint NOT NULL,
     Col40 [datetime] NOT NULL,
     Col41 [int] NULL,
     Col42 [int] NOT NULL,
     Col43 [int] NOT NULL,
     Col44 [int] NULL,
     Col45 [varchar](50) NOT NULL,
     Col46 [varchar](50) NOT NULL,
     Col47 [int] NULL,
     Col48 [int] NOT NULL,
     Col49 [varchar](max) NOT NULL,
     Col50 [varchar](max) NOT NULL,
     Col51 [varchar](max) NOT NULL,
     Col52 [varchar](max) NOT NULL,
     Col53 [varchar](250) NULL,
     Col54 [int] NULL,
     Col55 [bit] NOT NULL,
     Col56 [varchar](50) NULL,
     Col57 [int] NULL,
     Col58 [int] NULL,
     Col59 [int] NULL,
     Col60 [int] NOT NULL,
     Col61 varbinary(8) NOT NULL,
     Col62 [varchar](250) NULL
 )
     
 create clustered index [ix_temp_wox_orders_broadcaster_int_order_int] on #wox_orders
 (
     Col60 asc,
     Col48 asc
 )
     
 -- insert data into the temp table
 if @XMLIntParam is not null
 begin
     INSERT INTO #Table10
     SELECT 
         Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22,Col23,Col24,Col25,Col26,
         Col27,Col28,Col29,Col30,Col31,Col32,Col33,Col34,Col35,Col36,Col37,Col38,Col39,Col40,Col41,Col42,Col43,Col44,Col45,Col46,Col47,Col48,Col49,Col50,Col51,
         Col52,Col53,Col54,Col55,Col56,Col57,Col58,Col59,Col60,Col61,Col62
     FROM table10 --Actual table 
     WHERE col60 = @col60 AND 
             Col42 IN (SELECT IntValue FROM [dbo].[get_ints_from_xml](@col60))
 end
 else
 begin
             INSERT INTO #Table10
     SELECT 
         Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22,Col23,Col24,Col25,Col26,
         Col27,Col28,Col29,Col30,Col31,Col32,Col33,Col34,Col35,Col36,Col37,Col38,Col39,Col40,Col41,Col42,Col43,Col44,Col45,Col46,Col47,Col48,Col49,Col50,Col51,
         Col52,Col53,Col54,Col55,Col56,Col57,Col58,Col59,Col60,Col61,Col62
     FROM table10 --Actual table 
     WHERE col60 = @col60 
     AND (@param1 = 1 OR order_end_date >= @maxAge)
     AND ((@include_inactive_orders = 1) or (col32 >= @EndDate or Col32 is null))
     and Col45 <> ''            
 end
avatar image By Chris Wilson 48 asked Jul 31, 2014 at 03:32 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

3 answers: sort oldest

You will likely need to update statistics on the temporary table manually and add an OPTION (RECOMPILE) hint to the problematic query that uses the temporary table to get the behaviour you would expect in all cases. I describe the reasoning in the following article:

http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx

There are (even) more details about temporary table caching here:

http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx

avatar image By SQLkiwi ♦ 6.6k answered Jul 31, 2014 at 04:58 PM
more ▼
(comments are locked)
avatar image Chris Wilson Jul 31, 2014 at 06:56 PM

Paul,

Thank you for response! Sorry for the lateness of my response but it took me a bit to read all that and then implement some changes.

It definitely removed the "bad" plan from showing up but the shape of the plan isn't as speedy as the previous "good" plan. On average it is 2 to 3 seconds slower. I am going to mark your response as the answer as that is what my problem is but I am going to need to try and tune the new plan to be speedier.

10|10000 characters needed characters left

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.

Ed

avatar image By Edward Norris 96 answered Jul 31, 2014 at 03:39 PM
more ▼
(comments are locked)
avatar image Chris Wilson Jul 31, 2014 at 03:50 PM

I have added WITH RECOMPILE to the stored procedure and used it in the execute (exec sroc with recompile) and the problem is still intermittent. I can run it 20 times and not have a problem and then on the 21st it will create this plan. The next execution of it won't have the same problem.

As a note statement 7 is the insert into the temp table and statement 8 is the query. It estimates that the cost of the temp table will be 96% and the query 4% but it is the opposite for actual costs.

I don't explicitly drop the temp table in the stored procedure. Should I be doing so? I do explicitly create the temp table and don't use a SELECT INTO.

The insert is wrapped in an IF statement and depends on if a parameter is passed on. I will anonymize that part of the query and post it as well. For some reason I thought the stored procedure would be copied.

avatar image Aaron Bertrand ♦ Aug 01, 2014 at 12:53 PM

Chris, IMHO you should put OPTION (RECOMPILE) on the troublesome statement, not use the WITH RECOMPILE option on the whole procedure (or add the option on the call to the procedure).

avatar image Chris Wilson Aug 01, 2014 at 03:04 PM

Aaron, That is what I ended up doing. I added an update statistics #temp under where I inserted to the table but before the problem statement and then added OPTION(RECOMPILE) to the problem statement. I am guessing that since I added OPTION(RECOMPILE) the query could now look at some local variables and made some different choices for the plan so now it isn't as performant as it was most times before.

avatar image SQLkiwi ♦ Jul 31, 2014 at 04:51 PM

FYI dropping a temporary table created in a stored procedure has no effect http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx

avatar image Edward Norris Jul 31, 2014 at 04:55 PM

I realized that afterwards, that it was all contained in one procedure, so the plan has already been created. 0 for 2 on this one. Doh! :(

10|10000 characters needed characters left

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

avatar image By Erickson Winter 1 answered Jul 31, 2014 at 03:52 PM
more ▼
(comments are locked)
avatar image Chris Wilson Jul 31, 2014 at 04:00 PM

I have thought about adding a plan guide which will work in the short term but there are potentially other parameters that would change the shape of the plan and the guide would then be the problem. I would like to let the optimizer choose.

How do I get the statistics on the temporary table? There are already statistics because of the clustered index on the temp table but I don't think the optimizer is using them because it hasn't been inserted to before it creates the plan.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x117

asked: Jul 31, 2014 at 03:32 PM

Seen: 231 times

Last Updated: Aug 01, 2014 at 03:04 PM