How can a stored proc have multiple execution plans?

Question

I am working with MS SQL Server 2008 R2. I have a stored procedure named rpt_getWeeklyScheduleData. This is the query I used to look up its execution plan in a specific database:

select 
   *
from 
   sys.dm_exec_cached_plans cp
      CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
where 
   OBJECT_NAME(st.objectid, st.dbid) = 'rpt_getWeeklyScheduleData' and 
   st.dbid = DB_ID()

The above query returns me 9 rows. I was expecting 1 row.

This stored procedure has been modified multiple times so I believe SQL Server has been building a new execution plan for it whenever it was modified and run. Is it correct explanation? If not then how can you explain this?

Also is it possible to see when each plan was created? If yes then how?

UPDATE:

This is the stored proc's signature:

CREATE procedure [dbo].[rpt_getWeeklyScheduleData]
(
   @a_paaipk        int,
   @a_location_code int,
   @a_department_code   int,
   @a_week_start_date   varchar(12),
   @a_week_end_date varchar(12),
   @a_language_code int,
   @a_flag      int 
)
as
begin
   ...
end

The stored proc is long; has only 2 if conditions both for @a_flag parameter.

if @a_flag = 0
begin
   ...
end 

if @a_flag = 1
begin
   ...
end

Answer 1

Depending on the nature of the stored procedure (which wasn't provided) this is very possible for any number of reasons (most likely not limited to below):

1. Does the proc use a lot of if this then this select, else this select/update
2. Does the proc contain dynamic sql?
3. Are you executing the SP from both web and SSMS? Then you're likely executing the SP with different connection settings.
4. Does the stored proc have parameters? Sometimes a difference in parameters can cause one execution plan to be terrible for a specific set, so a different plan is used.

Going to try an analogy which might help... maybe...

---

Say you have a stored procedure for your weekend shopping. You typically need to get groceries, sometimes an air filter, and even less often a big pack of something that needs replacing 4 times a year.

1. The grocery store can handle groceries, and is the closest to your house (5 minutes).
2. Target can handle the air filter and groceries, but add 25 minutes travel time.
3. "Big place of everything" has everything you'd possibly need, but is an hours drive away.

So here, depending on your parameters @needsAirFilter and @needsBigPackOfSomething could vastly change your "execution plan" of your stored procedure of "shopping".

If @needsAirFilter and @needsBigPackOfSomething is false, there's no reason to make the 30 minute or hour drive, as everything you need is at the grocery store.

One a month, @needsAirFilter is true, in that case we need to go to Target, as the grocery store's execution plan is insufficient.

4 times a year @needsBigPackOfSomething is true, and we need to make the hour drive to get the big pack of something, while grabbing groceries, and airfilter since we're there.

Sure... we could make the hour drive every time to get groceries, and the other things when needed (imagine single execution plan). But this is in no way the most efficient way to do it. In instances like this, we have different execution plans for what information/goods are actually needed.

No idea if that helps... but I had fun :D

Answer 2

Typically SQL Server will generate a new query plan depending on the values of the parameters being passed in (this can determine what indexes, if any, it will use) and if indexes are added, changed or updated (on the tables/views being used in the proc) so SQL Server may decide that it is more effective to use one or more indexes that it previously ignored. The more involved the SQL in the proc will also kick off more work on SQL Server side as it attempts to optimize the query. If the data changes (suddenly you have many more customers in NJ and there is a query and index for states) it may decide that its going to use that index and the query plan is changed. If any of the tables or views involved in the query change (schema change) will also invalidate an existing plan and result in a new plan being generated.