sqlserver存储过程与即时查询的计划缓存

最近公司服务器CPU一直很高，没有发现死锁信息，经检查，发现开发人员都是直接拼SQL，因此数据库很少重复利用执行计划，大量的编译消耗很多CPU，所以建议使用参数化查询和存储过程，但老大要求能证明存储过程确实比拼SQL性能要好才会修改代码，因此写了如下代码做说明（此类代码很多，在此只是做下记录，以鼓励自己以后多写博客）

1.首先建立一张空表（在此是copy现有的表结构）

2.分别实现了4个简单的存储过程

3.分别用不同的参数执行代码（方便看出效果）

4.查看缓存信息，并比较

 --Version

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

 

select top 100 * into __WOSN from WO_SNselect * from __WOSNgocreate procedure __usp_cache_1(@sn varchar(30))asbeginselect top 1000 * from __WOSN where SN=@snendgocreate procedure __usp_cache_2(@sn varchar(30))asbegindeclare @sql varchar(1000)set @sql='select top 1000 * from __WOSN where SN='''+@sn+''''exec(@sql)endgocreate procedure __usp_cache_3(@sn varchar(30))asbegindeclare @sql nvarchar(1000)set @sql='select top 1000 * from __WOSN where SN='''+@sn+''''exec sp_executesql @sqlendgocreate procedure __usp_cache_4(@sn varchar(30))asbegindeclare @sql nvarchar(1000)set @sql='select top 1000 * from __WOSN where SN='''+@sn+''''exec sp_sqlexec @sqlendgoselect top 1000 * from __WOSN where SN='0001'select top 1000 * from __WOSN where SN='0002'select top 1000 * from __WOSN where SN='0003'exec __usp_cache_1 '0004'exec __usp_cache_1 '0005'exec __usp_cache_1 '0006'exec __usp_cache_2 '0007'exec __usp_cache_2 '0008'exec __usp_cache_2 '0009'exec __usp_cache_3 '0010'exec __usp_cache_3 '0011'exec __usp_cache_3 '0012'exec __usp_cache_4 '0013'exec __usp_cache_4 '0014'exec __usp_cache_4 '0015'

 

以上代码分别单独执行，然后执行如下代码，查看缓存信息。

SELECT b.text,a.*FROM sys.dm_exec_cached_plans a  CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) bwhere b.text like '%100%WOSN%' or b.text like '%__usp_cache_%'

结果如下：

从上面结果可以看出执行存储过程__usp_cache_1的时候它的执行计划被重复利用了(因为没有出现'0004','0005','0006')，而其他的则没有;特别是对于动态SQL的存储过程，它的效果跟直接执行SQL是一样的，对于每一个参数都需要编译执行计划，并不能重复利用。