T-SQL之变量导致索引无效/何谓SQLSERVER参数嗅探

关于变量导致索引无效的情况，在使用工作中其实经常遇到，我看到一篇写的不错的文章就转载过来丰富我的收藏噶。

转载自：http://www.cnblogs.com/slade/archive/2012/04/29.html

--2014/08/02

今天看到阐述相同问题的文章：

补充转载：http://www.cnblogs.com/lyhabc/archive/2013/03/02/2941144.html

这里说法更加规范，更多列出一种解决方案，这里补充在最下面

T-SQL之变量导致索引无效

 (一)问题提出

1，在开发中是否遇到一个情况，就是在where后写明具体值时可以用到索引，使用变量时却不行了呢？

2，是否开始怀疑MS SQL 出现了编译问题。

（二）测试过程

1，建立测试数据

CREATE TABLE t_order (    orderid     INT   IDENTITY ( 1 , 1 )   PRIMARY KEY ,    ordertime   DATETIME,    productname VARCHAR (50)) GO --创建索引 CREATE INDEX idx_ordertime ON t_order (        ordertime) GO --插入1000000条记录 WITH cte       AS ( SELECT NUMBER + 1 AS NUMBER           FROM   master..spt_values a           WHERE  a.TYPE = 'P'                  AND NUMBER < 1000) INSERT INTO t_order             (ordertime,              productname) SELECT Getdate() - a.NUMBER,         LEFT (Newid(),10) FROM   cte a         CROSS JOIN cte b GO

 

2，分别查询

SET STATISTICS io  ON －－查询一采用变量 DECLARE  @ date DATETIME SET @ date = Getdate() SELECT * FROM   t_order WHERE  ordertime > @ date GO －－查询二采用变量给出具体值 SELECT * FROM   t_order WHERE  ordertime > Getdate()

 3，对比执行计划发现相差太太太太大了。

查询1扫描了整个表，查询2确实很好的一个seek加Look up

（三） 原因分析以及验证

1，原因分析

因为当你使用变量时，查询语句在编译时，并不做SET操作。换句话说，即是SET操作是编译完成后，执行的时候才执行。所以编译的时候MS SQL 并不知道◎date的值，所以不能产生一个正确的执行计划。

2,验证

MS SQL在这种情况总按照一个固定的估计值在产生执行计划（即30%），所以做一个全表扫描更划算。让我们来论证一下，我们对该表插入了1000000条记录，按照30% ，所以预估行数就该是300000,查看执行计划，果然如此（注意红色方框）：

 （四）解决方案

解决方案1：（使用option(RECOMPILE)，在执行时重新编译）：

declare @ date datetime set @ date =GETDATE() select * from T_order where ordertime>@ date option (RECOMPILE)

 解决方案2：给定一个参数提示给该查询

declare @ date datetime set @ date =GETDATE() select * from T_order where ordertime>@ date option (OPTIMIZE FOR (@ date = '2012-04-29' ))

  解决方案3：封装成存储过程，有人就会疑问了，为什么存储过程可以呢？在这里大家别把参数和变量混淆了，在SQL SERVER里面写法都一样，但意义不完全一样。存储过程的编译实在第一次执行的时候才产生执行计划。

--创建存储过程 CREATE PROC Sp_select_t_order             @ date DATETIME AS    SELECT *    FROM   t_order    WHERE  ordertime > @ date GO --执行存储过程 DECLARE  @ date DATETIME SET @ date = Getdate() EXEC Sp_select_t_order @ date

 解决方案4：参数化查询

sp_executesql N 'select * from T_order where ordertime>@date' , N '@date datetime' , @ date = '2012-04-29'

 

以上四种解决方案的执行计划都如下，实际环境推荐封装成存储过程：

--2014/08/02

补充解决方案

（4）Plan Guide

 可以用下面的方法，在原来那个有参数嗅探问题的存储过程“Sniff”上，解决sniffing问题

 USE [AdventureWorks]
 GO
 EXEC [sys].[sp_create_plan_guide]
 @name=N'Guide1',
 @stmt=N'SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])
 FROM [dbo].[SalesOrderHeader_test] a
 INNER JOIN [dbo].[SalesOrderDetail_test] b
 ON a.[SalesOrderID]=b.[SalesOrderID]
 INNER JOIN [Production].[Product] p
 ON b.[ProductID]=p.[ProductID]
 WHERE a.[SalesOrderID]=@i',
 @type=N'OBJECT',
 @module_or_batch=N'Sniff',
 @params=NULL,
 @hints=N'option(optimize for(@i=75124))';
 GO

对于Plan Guide，他还可以使用在一般的语句调优里