包含动态sql的存储过程的执行计划是否已重用? [英] Execution plan of a stored procedure containing dynamic sql is reused?

问题描述

我有一个没有输入参数的sp.我也有动态sql statemtns构建并执行usign sp_executesql.我的问题是，当我们多次执行sp时..第一次使用的执行计划是否可以重用?
这是代码块:

Hi,

I have a sp which has no input parameters .I also have dynamic sql statemtns build and executed usign sp_executesql.My question is when we execute the sp more than once ..will the execution plan that is generated for the first time is reused or not?
Here is the code block:

CREATE proc uspcachehitchk
as
begin
DECLARE @sal bigint;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
declare @dbsortorder varchar(100)
set @dbsortorder='desc'
declare @cols nvarchar(1000)
set @cols=N'empno,ename,job,mgr,SAL'
declare @size int
set @size=4
SET @SQLString =
   N'select top(@size) '+ @cols + ' from emp where sal>Convert(varchar(100),@sal) order by ename '+ @dbsortorder;
SET @ParmDefinition = N'@sal bigint,@cols nvarchar(100),@size int';
SET @sal= 2000;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
           @sal= @sal,@cols=@cols,@size=@size;
           end

我已经在探查器中使用了SP:cachehit和SP:cachemiss事件来跟踪..，但我只能看到select语句正在触发sP:cachehit ..但没有看到sp.IS，这是检查执行计划重用的正确方法. .或者如果还有其他任何问题，请帮助我.

问候
Chaithanya M

I have used SP:cachehit and SP:cachemiss events in the profiler to trace ..but i could only see the select statement is firing sP:cachehit..but not the sp.IS this the correct way to check for execution plan reuse..or if there exists any other please help me .

Regards
Chaithanya M

推荐答案

根据MSDN:"在执行sp_executesql语句之前，不会编译sp_executesql stmt参数中的Transact-SQL语句或批处理.然后将stmt的内容编译并作为执行计划执行，该执行计划与名为sp_executesql的批处理的执行计划分开执行.

http://msdn.microsoft.com/en-us/library/ms188001.aspx [ ^ ]

According to MSDN: ''The Transact-SQL statement or batch in the sp_executesql stmt parameter is not compiled until the sp_executesql statement is executed. The contents of stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql''

http://msdn.microsoft.com/en-us/library/ms188001.aspx[^]

要在Manas上扩展''答案...请从他提供的链接中查看摘要:

sp_executesql 可以代替存储过程用于多次执行Transact-SQL语句，前提是该语句的参数值更改是唯一的变化. 由于Transact-SQL语句本身保持不变，并且仅参数值发生变化，因此SQL Server查询优化器可能会重用其为首次执行而生成的执行计划..

To expand on Manas'' answer... please see snippet from the link he provided:

sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

这篇关于包含动态sql的存储过程的执行计划是否已重用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！