避免参数嗅探带来的资源消耗

            近日发现一个服务器上的TRACE文件中记录了大量同一类型的SQL，而这个SQL看上去貌似没什么问题。

     exec sp_executesql N'        SELECT UserID, UserCategory, UserName, PartnerID, PartnerName, CreatedTime FROM [table] WITH(NOLOCK)        WHERE UserCategory = @UserCategory AND PartnerID = @PartnerID       ',N'@UserCategory nvarchar(50),@PartnerID nvarchar(50)',@UserCategory=N'fee3c20d30964637809c7cc68c45243b',@PartnerID=N'5DFAEB3BE6377159FE3548DA39E9936F'

(1 行受影响)
表 ''。扫描计数 1，逻辑读取 18131 次，物理读取 1 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。


(1 行受影响)


 SQL Server 执行时间:
   CPU 时间 = 1451 毫秒，占用时间 = 1478 毫秒。


 SQL Server 执行时间:
   CPU 时间 = 1466 毫秒，占用时间 = 1890 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。


 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

    相应的索引在表上也存在，应该执行的比较快才对，并且对CPU和IO 的消耗也不应该如此多。观察了此SQL 的执行计划，发现在执行索引查找的时候，选择的是范围查询，与预想中的等值查询不一致。从执行计划可以看出，SQL SERVER在执行查询的时候，发生了参数嗅探。原因可能是SQL 语句中有强制参数化的行为.（强制参数化只是执行计划发生参数嗅探的一个因素，是否出现参数嗅探还和表中的数据量等其它因素有关。）

既然原因大致明了了，改进的方向大致也有了，SQL SERVER在编译SQL 的时候会进行简单参数化，大可不必因担心执行计划不可以重用而将SQL强制参数化。

将这个查询改成了一个存储过程，目的是使这个SQL的执行计划在编译的时候简单参数化，避免出现查询谓词范围查询。

CREATE PROCEDURE [dbo].[GetPartnerUsers_test]  
 -- Add the parameters for the stored procedure here  
 @UserCategory nvarchar(50),  
 @PartnerID nvarchar(50)  
AS  
BEGIN  
   
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.  
 SET NOCOUNT ON;  
    declare @sql nvarchar(max)  
    -- Insert statements for procedure here  
 set @sql='SELECT UserID, UserCategory, UserName, PartnerID, PartnerName, CreatedTime FROM [table] WITH(NOLOCK)'  
    +' WHERE UserCategory =''' +@UserCategory+''' AND PartnerID = '''+@PartnerID+''''  
    --print @sql  
    exec(@sql)  
END  

新的执行计划如下：

表 ''。扫描计数 0，逻辑读取 7 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。


 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。


 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。


 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 2 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。


 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

资源消耗对比可以看出，前者比后者至少多三个数量级。