How can I avoid this bad plan

Chris Wilson 2014-07-31 15:32:15

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
SQLkiwi 2014-07-31 16:58:00
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

Chris Wilson 2014-07-31 18:56:41
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.

Edward Norris 2014-07-31 15:39:33
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

Chris Wilson 2014-07-31 15:50:00
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.

Aaron Bertrand 2014-08-01 12:53:09
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).
Chris Wilson 2014-08-01 15:04:30
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.
SQLkiwi 2014-07-31 16:51:09
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
Edward Norris 2014-07-31 16:55:39
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! ๐Ÿ™
Erickson Winter 2014-07-31 15:52:55
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

Chris Wilson 2014-07-31 16:00:04
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.