是什么引起执行计划变得极其糟糕? 应该使用更新统计信息来解决它吗？

考虑这种情况：

在大多数时间里你的存储过程运行良好，但是有时非常差，性能仿佛从天下掉到地下，有人会说肯定是统计信息更新不及时，而且当你手动运行它并查看执行计划，你会发现预估行数和实际行数有很大差距，你会因此而确定是统计信息不准确造成执行计划生成不正确。

但是，可能并不是。。。

存储过程、使用sp_executesql的参数化语句、预编译的SQL语句都会重用一个缓存的执行计划，它是由一个称为参数嗅探定义的，参数嗅探本身并没有问题，但是相同的存储过程或参数化语句去调用已经生成的执行计划时，就有可能引发一些问题。比如：如果一个参数化查询语句只返回一行数据，那么它可能会生成一个简单的轻量级执行计划，这个执行计划被缓存，它可能仅是一个非聚集索引查找+书签查找，但是，如果后面有一个查询返回大量的数据行，那么之前生成的执行计划可能就不适合了。由于执行计划缓存在内存中，有很多原因可能会从内存中消失，如果它刚消失，而这时过来一个使用频率极少的查询正好返回大量的数据行，那么这时生成的执行计划就会被缓存，这之后的查询哪怕只返回一行，也会用之前刚缓存的执行计划，这将会导致性能很差。

例如：

use tempdb
go
--drop table tb_1
create table tb_1 (id int primary key identity,name varchar(200),dt datetime default getdate(),xx int)
declare @i int =1
while @i<10000
begin
insert into tb_1 (name) select LTRIM(@i)
set @i+=1
end

create index ix_name on tb_1 (name)
go
insert into tb_1 (name) select '99999'
go 5000

exec sp_executesql N'select top 10 * from tb_1 where name=@name order by dt desc',N'@name varchar(20)',@name='1'

IO情况：表 'tb_1'。扫描计数 1，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
执行计划：

正常情况下，这个name='1'查询只会返回一行，而且当name变化成其它值时，绝大多数情况也只是返回少量的行，这时候如果name有索引，它就只是一个索引查找+Key查找，它会非常的快，从上面看到只有4个page.

 

假如有一个极少数name=99999会返回大量的行，它应该用聚集索引扫描会更好，但由于SQL Server的参数嗅探，所以它会使用之前缓存的执行计划，这就会使效率变得比较差了，但是由于很少去执行这个name=99999，这样看起来还可以接受，如下图：

exec sp_executesql N'select top 10 * from tb_1 where name=@name order by dt desc',N'@name varchar(20)',@name='99999'

IO情况：表 'tb_1'。扫描计数 1，逻辑读取 10014 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
执行计划：


如果不使用缓存的执行计划，会是什么样的呢？

select top 10 * from tb_1 where name='99999' order by dt desc

IO情况：表 'tb_1'。扫描计数 1，逻辑读取 64 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
执行计划:

 

然而，如果此时缓存中没有该语句的执行计划，或者它可能刚刚从内存中被清除出去，那么此时执行name=99999将会生成一个新的执行计划并缓存，也就是上面这个T-sql产生的Clustered Index Scan的执行计划，它是聚集索引扫描，在这种情况之后，其它的后续的只返回1行数据的大量查询，都会重用这个新的执行计划，性能都会很差了，如：

这就是开篇所述的情况，此时你查看上面的执行计划，你会发现预估行数和实际行数有很大的差距，那么它是由于统计信息不及时造成的吗？显然不是。

但是如果你不知道上面这个原因，一直认为是统计信息问题，你会怎么做呢？你会UPDATE STATISTICS tablename或者UPDATE STATISTICS tablename indexname
然后你再次执行有问题的存储过程或参数化语句，结果它的执行计划正确了，那么你更坚定的认为是统计信息问题了。

然而，更新统计信息是有副作用的，会使查询优化器重新编译相关的语句，并可能会产生其它新的问题。

那么应该如何做呢？
如果是存储过程，那么我们应该重新编译它： sp_recompile procedurename
如果是参数化语句，那么我们找出它的行计划，清除它：
SELECT  *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE Objtype in ('Prepared') and text like '%select top 10 * from tb_1 where name=@name order by dt desc%'
查到plan_handle为0x060002006E0F721540E14B8B000000000000000000000000
然后执行：DBCC FREEPROCCACHE (0x060002006E0F721540E14B8B000000000000000000000000); 

如果如此操作之后并未生成一个新的执行计划，那么再考虑更新统计信息。



更新统计信息一定会造成执行计划重新编译吗？no
1.在SQL Server 2005, 2008 and 2008R2版本中，如果auto update statistics开启（默认），那么会造成执行计划重新编译。
2.而在SQL Server 2012中不管auto update statistics是否开启，更新统计信息都不会影响执行计划。

Kimberly反馈给微软这个改变是否是SQL 2012的一个Bug,从微软回复来看，这个是改变是正确的，而在之前版本上的行为才是一个真正的Bug. -_- !!!

本文参考：
http://www.sqlskills.com/blogs/kimberly/post/what-caused-that-plan-to-go-horribly-wrong-should-you-update-statistics.aspx
https://connect.microsoft.com/SQLServer/feedback/details/769338/update-statistics-does-not-cause-plan-invalidation#