I am working with MS SQL Server 2008 R2. I have a stored procedure called rpt_getWeeklyScheduleData strong>. This is the query that I used to search for 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 several times, so I believe that SQL Server builds a new execution plan for it whenever it has been modified and started. Is this the correct explanation? If not, how can you explain this?
Can you also see when each plan was created? If so, how?
UPDATE:
This is the stored signature of proc:
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 procedure is long; has only 2 conditions, as for the @a_flag parameter.
if @a_flag = 0
begin
...
end
if @a_flag = 1
begin
...
end