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
