question

sp_lock avatar image
sp_lock 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
sql2012query-optimisationplan-cache
badplan.jpg (14.7 KiB)
goodplan.jpg (14.4 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
2 Likes 2 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
2 Likes 2 ·
sp_lock avatar image sp_lock commented ·
@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?
0 Likes 0 ·
sp_lock avatar image sp_lock commented ·
Ah. Now I understand.. With the "bad" statement I get "Parameter List" properties, but not for the RECOMPILE option. You learn something new every day. Thank you!
0 Likes 0 ·
sp_lock avatar image
sp_lock answered
[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

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
Ta. Is this the good or the bad? Whichever it is, can you post the other too?
1 Like 1 ·
sp_lock avatar image sp_lock commented ·
Added the good plan ;-)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.