sp_executesql是参数部分的长度定义发生变化能否重用执行计划
准备工作
create table dbo.ta(id varchar(10) ,c char(10))
go
insert dbo.ta
select ROW_NUMBER() over(order by c1.name) id,'x' from sys.columns c1 cross join sys.columns c2
go
create index index1 on dbo.ta(id)
go
dbcc freeproccache
go
go
insert dbo.ta
select ROW_NUMBER() over(order by c1.name) id,'x' from sys.columns c1 cross join sys.columns c2
go
create index index1 on dbo.ta(id)
go
dbcc freeproccache
go
接下来要用select *from ta where id='1'的方式执行两次，看看是否重用了存储过程
dbcc freeproccache
go
DECLARE @value varchar(1);
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
/* Build the SQL string one time.*/
SET @SQLString =
N'select * from dbo.ta
WHERE id = @id';
SET @ParmDefinition = N'@id varchar(1)';
/* Execute the string with the first parameter value. */
SET @value = '1';
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@id = @value;
go
DECLARE @value varchar(2);
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
/* Build the SQL string one time.*/
SET @SQLString =
N'select * from dbo.ta
WHERE id = @id';
SET @ParmDefinition = N'@id varchar(2)';
/* Execute the string with the first parameter value. */
SET @value = '10';
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@id = @value;
go
go
DECLARE @value varchar(1);
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
/* Build the SQL string one time.*/
SET @SQLString =
N'select * from dbo.ta
WHERE id = @id';
SET @ParmDefinition = N'@id varchar(1)';
/* Execute the string with the first parameter value. */
SET @value = '1';
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@id = @value;
go
DECLARE @value varchar(2);
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
/* Build the SQL string one time.*/
SET @SQLString =
N'select * from dbo.ta
WHERE id = @id';
SET @ParmDefinition = N'@id varchar(2)';
/* Execute the string with the first parameter value. */
SET @value = '10';
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@id = @value;
go
查看执行计划
SELECT execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
where st.text not like '%dm_exec_sql_text%'
执行结果如下：
execution_count statement_text
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 select * from dbo.ta
WHERE id = @id
1 select * from dbo.ta
WHERE id = @id
(2 行受影响)
说明长度发生变化无法重用计划
