Cached plans

Last Post 13 Aug 2014 06:07 PM by gunneyk. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
rerichards
New Member
New Member

--
13 Aug 2014 12:40 PM
Please help me understand stored procedure plans that get cached.

For instance, lets say I execute the following one after the other:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
exec dbo.MyReport @WidgetNum = 121212, @DateLow='Aug 11 2014 12:00:00:000AM',@DateHigh='Aug 12 2014 12:00:00:000AM'
exec dbo.MyReport @WidgetNum = 121212, @DateLow='Jan 1 2014 12:00:00:000AM',@DateHigh='Aug 12 2014 12:00:00:000AM'

When I look at use counts after the two executions above I notice that the UseCounts = 2 from the following query:
SELECT usecounts, cacheobjtype, objtype, bucketid, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'

Yet when I look at the execution plan for one of the statements in the stored procedure, the first executable produces a plan that is not parallelized and primarily uses Nested Loop operators. The second executable with the larger date range (for the same statement) produces a plan that uses parallelism and primarily uses Hash Joins.

There is no OPTION(RECOMPILE) on the statement.

My understanding is that only one plan could be in cache for a given stored procedure. Since the usecounts keep incrementing the plan is being reused, yet the plans are different. How can this be?

I am familiar with parameter sniffing, but my understanding is that the first execution with the small date range would produce a plan that may not be optimal for subsequent executions that may use a large date range (as seen in the second execution). Yet, the second execution is not using the small date range plan which was not parallelized and largely used Nested Loop Joins. Yet, the UseCounts are incrementing.

Please help me understand how the usecounts can increment yet different plans are being used.
gunneyk
New Member
New Member

--
13 Aug 2014 06:07 PM
SQL Server decides at compile time (first execution) if the statement(s) are costly enough that they may benefit from parallelism. If that threshold is not met then a single plan is generated and that plan will always be single threaded. However if the cost if high enough and there are multiple cores / schedulers the engine can generate a parallel plan in addition to the single threaded plan. This is because the decision to use 1 to nn many threads comes at each and every execution time based on several factors such as how busy all the schedulers are, available memory, current MAXDOP settings etc. A parallel plan may be more efficient using a specific type of join than a single threaded one so they may not and actually will not be the same in certain ways. If the conditions at the time it is run is conducive to using more than a single thread the parallel plan will be used. However if it decides to use a single thread the single threaded plan will be used but they are both for the same proc. Hope that makes sense.
You are not authorized to post a reply.

Acceptable Use Policy