SQLSERVER中如何忽略索引提示

SQLSERVER中如何忽略索引提示 当我们想让某条查询语句利用某个索引的时候，我们一般会在查询语句里加索引提示，就像这样 SELECT id,name from TB with ( index (IX_xttrace_bal)) where bal 100 当在生产环境里面，由于这个索引提示的原因，优化器一般不会再

SQLSERVER中如何忽略索引提示

当我们想让某条查询语句利用某个索引的时候，我们一般会在查询语句里加索引提示，就像这样

<span>SELECT</span>  id,name <span>from</span> TB  <span>with</span> (<span>index</span>(IX_xttrace_bal)) <span>where</span> bal<span><span>100</span></span>

 

当在生产环境里面，由于这个索引提示的原因，优化器一般不会再去考虑其他的索引，那有时候这个索引提示可能会导致查询变慢

经过你的测试，发现确实是因为这个索引提示的关系导致查询变慢，但是SQL服务器已经缓存了这条SQL语句的执行计划，如果修改SQL语句的话可能会有影响

而且，可能不单只一条SQL语句用了索引提示，还有其他的SQL语句也用了索引提示，你不可能马上去修改这些SQL语句的时候可以使用SQLSERVER里面的一个trace flag

 

这个trace flag能忽略SQL语句里面的索引提示和存储过程里面的索引提示

不需要修改SQL语句，就可以进行性能排查

 

运行下面脚本创建数据库和相关索引

<span>USE</span><span> master
</span><span>GO</span>

<span>IF</span> <span>DB_ID</span>(<span>'</span><span>Trace8602</span><span>'</span>) <span>IS</span> <span>NOT</span> <span>NULL</span>
    <span>DROP</span> <span>DATABASE</span><span> Trace8602
</span><span>GO</span>

<span>CREATE</span> <span>DATABASE</span><span> Trace8602
</span><span>GO</span>

<span>USE</span><span> Trace8602
</span><span>GO</span>

<span>CREATE</span> <span>TABLE</span><span> xttrace8602
    (
      id </span><span>INT</span> <span>IDENTITY</span>(<span>1</span>, <span>1</span><span>)
             </span><span>PRIMARY</span> <span>KEY</span><span> ,
      bal </span><span>INT</span><span> ,
      name </span><span>VARCHAR</span>(<span>100</span><span>)
    )
</span><span>GO</span>

<span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> IX_xttrace8602_bal_name <span>ON</span><span> xttrace8602(bal,name)
</span><span>GO</span>
<span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> IX_xttrace8602_bal <span>ON</span><span> xttrace8602(bal)
</span><span>GO</span>

<span>INSERT</span>  <span>INTO</span><span> xttrace8602
</span><span>VALUES</span>  ( <span>RAND</span>() <span>*</span> <span>786</span>, <span>'</span><span>cnblogs.com/lyhabc</span><span>'</span><span> )
</span><span>GO</span> <span>10000</span>

<span>CREATE</span> <span>PROC</span><span> uspFirst
</span><span>AS</span>
    <span>SELECT</span><span>  id ,
            name
    </span><span>FROM</span>    xttrace8602 TF <span>WITH</span> ( <span>INDEX</span><span> ( IX_xttrace8602_bal ) )
    </span><span>WHERE</span>   bal <span> <span>100</span>
<span>GO</span></span>

 

现在执行下面代码

<span>--</span><span>没有使用跟踪标致</span>
<span>EXEC</span><span> uspFirst  
</span><span>GO</span>

 

<span>--</span><span>使用了跟踪标志</span>
<span>DBCC</span> TRACEON(<span>8602</span>,<span>-</span><span>1</span><span>)
</span><span>GO</span>
<span>DBCC</span><span> FREEPROCCACHE
</span><span>GO</span>
<span>EXEC</span><span> uspFirst 
</span><span>GO</span>

 

可以看到，打开TRACEON(8602,-1) 跟踪标志之后，SQLSERVER忽略了索引提示，利用复合索引IX_xttrace8602_bal_name 把数据查出来

而不需要额外的键查找

 

这个跟踪标志不需要你修改你的SQL语句就可以让SQLSERVER忽略索引提示

在使用这个8602跟踪标志之前记得先在开发环境测试好，确认是否需要忽略索引提示，以便做成性能问题

 

如有不对的地方，欢迎大家拍砖o(∩_∩)o