Execution Plan reuse

Question

Consider the following "code"

define stmt1 = 'insert into T(a, b) values(1, 1);
define stmt2 = 'select * from T';
MSSqlCommand.Execute( stmt1;stmt2 );
MSSqlCommand.Execute( stmt2 );

Investigating the cached query-plans using:

SELECT [cp].[refcounts] 
, [cp].[usecounts] 
, [cp].[objtype] 
, [st].[dbid] 
, [st].[objectid] 
, [st].[text] 
, [qp].[query_plan] 
FROM sys.dm_exec_cached_plans cp 
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st 
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp ;

My impression is that the first "Execute" generates a composite execution plan instead of two singular execution plans, thereby disabling the second "Execute" reusing any execution plan generated in the first Execute.

Am I right?

Answer 1

Yes, you're right. To reuse the the second part of the execution plan you need to split the first statement into 2 separate execution plans. You can do this either by executing them with separate MSSqlCommand.Execute calls or by using two calls to sp_executesql in one query (this adds one level of indirection). It would look something like this (in pseudocode):

MSSqlCommand.Execute('exec sp_executesql stmt1; exec sp_executesql stmt2");

Answer 2

Thanks!

Could you please confirm the answer to the question provided at http://stackoverflow.com/questions/2027386/set-context-info-select-context-info-locking ?

I need a second opinion / confimation of truth!

Thanks!

/Jens