asked
inefficient plan use when not using RECOMPILE
Dear All I was wondering if some could help to explain the strange workings (IMO) of the optimizer. DECLARE @StartDate DATETIME = '2013-11-02' DECLARE @Enddate DATETIME = '2013-11-05' SELECT pc.CreatedOn FROM [dbo].ActivityPointerBase AS pc INNER JOIN [dbo].[Contactbase] c ON c.contactid = pc.regardingobjectid AND pc.ActivityTypeCode = 4210 WHERE pc.CreatedOn BETWEEN @StartDate AND @enddate The query above generates the plan below. ![alt text][1] As you can see SQL used in the this case an Index scan on the ContactBase table (the said index has only the telephone number as a key column), but I know I have an index that best suits the query being executed. Now, my confusion comes from the fact I know that my indexes are not heavily fragmented (between 2-5%) and stats for both tables were updates yesterday evening. The reason for mentioning this is because by adding **OPTION (RECOMILE)** to the query I get a more efficient plan and IO stats (see below). ![alt text][2] I have also been able to reproduce this in our test system with the same outcome (this is MS CRM 2011 if you are wondering). I have also cleared the CACHE to no avail. So my question is (i guess), why is SQL only selecting an efficient plan when I force a recompile even though the cache is clear? Any advise would be greatly appreciated. Thanks JL P.S. I would also like to avoid using a table hint. [1]: /storage/temp/1433-badplan.jpg [2]: /storage/temp/1434-goodplan.jpg
These are both using local variables. So, with a recompile you're seeing variable sniffing, like parameter sniffing. Take a look at the SELECT operator properties. I'll bet you have compile time values for the variables in the good plan, but not in the other. So, a solution would be to parameterize the query, either as a stored procedure or using sp_executesql to call it.
Local variables are not sniffed the way parameters are. Until you put that recompile statement in. Then, it can look at the values for the local variables. Weird I know, but that's how it works. So you're basically setting up parameter sniffing for your local variables and proving that parameter sniffing is generally a good thing.
And by compile time values, I should have been more specific, not the literal time it takes to compile, but the parameter values have a compile time value as well as a run time value.
@Grant thank you for the response... The Compile time for the 2 queries are very similar. Bad CompileCPU 29 ComplileMemory 928 CompileTime 29 Good CompileCPU 27 ComplileMemory 912 CompileTime 27 Adding the query to a SP or sp_executesql does force the correct plan as you correctly mentioned. I appreciated your help on this, but I'm just can't get my head around what SQL is doing in terms of the local value. Why would it do this query when there is nothing in the cache?
[link text][1]@Kev Riley I have attached the XML at your request. [link text][2] [1]: /storage/temp/1438-good+execution+plan.txt [2]: /storage/temp/1437-execution+plan.txt
Ta. Is this the good or the bad? Whichever it is, can you post the other too?
15 People are following this question.
