How can I avoid this bad plan
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
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
Ed
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.
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.
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
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.
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.