准备工作
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 行受影响)
说明长度发生变化无法重用计划