Sorry if I was too cryptic/concise...
1) Here's the query I run to check exec plan reuse:
SELECT ECP.usecounts,ECP.refcounts,ECP.cacheobjtype,ECP.objtype,EST.[text]
FROM
sys.dm_exec_cached_plans AS ECP
CROSS APPLY
sys.dm_exec_sql_text(ECP.plan_handle) AS EST
ORDER BY
ECP.usecounts DESC,EST.[text]
;
One row of output:
usecounts refcounts cacheobjtype objtype text
540253 2 Compiled PlanProc Create Proc mt_amstas30 @SourceID varchar(3),TaskID int,....
Other rows of output (there are thousands of these, just the parameters different):
usecounts refcounts cacheobjtype objtype text
1 2 Compiled PlanPrepared exec mt_amstas30 'AMS','77985984',...
1 2 Compiled PlanPrepared exec mt_amstas30 'AMS','77985995',...
My goal is to reuse query plans whenever possible. If I'm interpreting the results of the query correctly, the exec plan for the SP mt_amstas30 is being reused, but the EXEC statements that "start" the SP are not. If this is true, is there a simple way to "start" the SP that does not create a new exec plan when the parameter strings change? My research on MSDN turns up RCP and ODBC calls that are far too complex...
Thanks in advance for any guidance / help you can provide.