Please help me understand stored procedure plans that get cached.
For instance, lets say I execute the following one after the other:
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
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.