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] 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] 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. : /storage/temp/1433-badplan.jpg : /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.