SQL Server SQL性能优化之--数据库在“简单”参数化模式下，自动参数化SQL带来的问题

2004

　　数据库参数化的模式
　　数据库的参数化有两种方式，简单（simple）和强制（forced），默认的参数化默认是“简单”，
简单模式下，如果每次发过来的SQL，除非完全一样，否则就重编译它（特殊情况会自动参数化，正是本文想说的重点）
强制模式就是将adhoc SQL强制参数化，避免每次运行的时候因为参数值的不同而重编译，这里不详细说明。
　　这首先要感谢“潇湘隐者”大神的提示，
当时也是遇到一个实际问题，
发现执行计划对数据行的预估，怎么都不对，有观察到无论怎么改变参数，SQL语句执行前都没有重编译，疑惑了好一会，
这个问题正是简单参数化模式下，对某些SQL自动参数化造成执行计划重用引起的，也是本文想表达的重点。
这个问题之前就写过，当时也只是看书上理论上这么说的，没有想到其带来的影响
　　该参数是一个数据级别的选项，设置情况可以参考下图

　　什么情况下会自动参数化
　　简单参数化模式下，对于有且只有一种执行方式的Adhoc SQL语句，SQL Server会自动参数化它，从而达到重用执行计划的目的。
　　究竟哪些类型的SQL会被自动参数化，后面会举例说明。
　　自动参数化会存在哪些问题
　　在简单模式下，SQL对于某些SQL会自动参数化他，避免每次都重编译。
　　SQL Server 自动参数化SQL语句的行为，能够避免一些重编译，原本也是出于“好意”，但是这种“好意”往往不一定总是给我们带来好处。

　　举例说明什么情况下会自动参数化
　　先造一个简单的测试环境



create table TestAuotParameter
(
id int not null,
col2 varchar(50)
)
GO
declare @i int=0
while @i<100000
begin
insert into TestAuotParameter values (@i, NEWID())
set @i=@i+1
end
GO
create unique index idx_id on TestAuotParameter(id)
GO

　　之所以自动参数化了SQL语句，就是因为select * from TestAuotParameter where id=33333 （66666,99999）这句SQL语句，
在当前的数据量下和唯一索引的特点，决定了有且只有一种高效的执行方式（也就是索引查找）
这里说有且只有一种方式是表中数据量相对较多，又因为idx_id这个索引是unique的。如果不是unique的，那么情况就不同了
　　
下面来解释什么是有且只有一种高效的执行计划
　　
　　如下截图：同样的测试，我删除id上的唯一索引，创建为非唯一索引，再做同样的测试，就会发现执行同样的SQL并没有被自动参数化
　　这里解释一下原因，索引类型怎么跟执行计划缓存扯上了？
　　对于非唯一索引，有可能作做引查找是高效的，有可能做全表扫描是高效的（比如某个ID的数据分布的特别多）
　　此时执行计划有可能是多样的，不仅仅只有一种方式，所以就不会自动参数化SQL

　　自动参数化存在的问题
　　自动参数化好处并不用多说，因为可以重用缓存的执行计划，避免了每次参数值不一样就重编译的问题
　　说到执行计划重用，不得不说的一个话题就是parameter sniff，嘴皮子都磨破的问题了
　　没错，自动参数化因为不同参数会重用第一次编译生成的执行计划，很有可能造成parameter sniff问题，以及parameter sniff衍生出来的其他问题
　　
　　同样用一个例子来做演示，该问题是最近在观察执行计划统计信息（statistics）预估问题时遇到的一个问题，让我困惑了好一会，
这里再次感谢潇湘隐者。
该问题同样也是因为自动参数化了SQL语句，造成执行计划重用，从而造成一个极其简单的SQL执行效率在某些情况下较低的情况，
为什么自动化参数的原因跟上述类似，都是有且只有一种执行方式（索引查找）的情况下，不同参数执行计划重用造成对数据行的错误预估。
　　测试之前清空一下缓存执行计划，观察不同查询条件下的实际执行计划对数据行的预估
　　
　　如下查询条件：
　　1，初始查询条件为：CreateDate>'2016-6-1' and CreateDate<'2016-6-2'，观察执行计划，实际行数是37903，预估行数为37117,预估还算准确　　

　　2，将查询条件更新为：CreateDate>'2016-6-1' and CreateDate<'2016-6-5'，观察执行计划，实际行数为150706，预估行数不变，还是37117

　　3，将查询条件更新为：CreateDate>'2016-6-1' and CreateDate<'2016-6-9'，观察执行计划，实际行数为302114，预估行数不变，还是37117
　　,
　　发现没有，因为查询时间段有变化，实际行数也有变化，但是不管实际行数多少，预估行数总是为第一次执行预估的行数。
　　这肯定不对吧？随便带入什么条件，预估行数都是37117，当时一下子蒙了，怎么每次执行SQL对数据行的预估都是一样的？
其实这个问题跟一开始举例的一样，都是SQL语句被自动参数化了，因此造成了执行计划重用，
执行计划重用，导致错误地预估实际查询的数据行数。
　　
如何解决自动参数化造成错误地重用执行计划的问题
　　很多问题找到了真正的原因，解决起来往往并不难，这个问题的原因是执行计划重用造成的，那么我们只需要解决执行计划重用的问题即可
　　也就是不让他重用执行计划，只需要在SQL语句中加一个提示即可，
　　也即：select COUNT(1) from Test20160810 where CreateDate>'2016-6-1' and CreateDate<'2016-6-9' OPTION(RECOMPILE)
　　原因就在于加上OPTION(RECOMPILE)这个查询提示之后，不缓存SQL的执行计划缓存，没有了执行计划缓存，也就没得重用了
　　比如这个查询，在查询语句中加入OPTION(RECOMPILE)查询提示，让其执行之前重编译SQL语句，他就可以正确地预估数据行了。

　　总结
　　本文通过一个实际案例说明了什么是简单参数模式下的自动化参数，自动化参数会带来哪些问题，以及如何解决，
问题本身非常简单，如果不注意还是会偶尔还是会出现困惑的。
　　题外话
　　有一点感受非常深，就是说，越来越多的实际问题，都要有理论知识作支撑，
但往往理论上说的情况并没有频繁出现或者即使出现了也没有引起注意，有时间就忽略了一些理论上的知识。
对于遇到的问题，如果真的要想弄清楚，还是要有一些理论知识做铺垫的。很多时候，往往是遇到问题之后，回忆起来曾经好似乎看过这一方面的理论知识。
这也是我们需要坚持看书，了解一些理论知识的原因。