何谓SQLSERVER参数嗅探 大家听到 嗅探 这个词应该会觉得跟黑客肯定有关系吧，使用工具嗅探一下参数，然后截获，脱裤o(_)o 。 事实上，我觉得大家太敏感了，其实这篇文章跟数据库安全没有什么关系，实际上跟数据库性能调优有关 相信大家有泡SQLSERVER论坛的话
何谓SQLSERVER参数嗅探
大家听到“嗅探”这个词应该会觉得跟黑客肯定有关系吧，使用工具嗅探一下参数，然后截获，脱裤o(∩_∩)o 。
事实上，我觉得大家太敏感了，其实这篇文章跟数据库安全没有什么关系，实际上跟数据库性能调优有关
相信大家有泡SQLSERVER论坛的话不多不少应该都会见过“参数嗅探”这几个字
这里有三篇帖子都是讲述参数嗅探的
http://msdn.microsoft.com/zh-cn/magazine/ee236412.aspx
下面我给出一个测试数据库的备份文件，里面有一些表和一些测试数据 ，大家可以去下载，因为我下面用的测试表都是这个数据库里的
只需要还原数据库就可以了，这个数据库是本文来源gaodai#ma#com搞*代#码9网#SQL2005版本的，数据库名：AdventureWorks
下面只需要用到三张表，表里面有索引：
[Production].[Product]
[SalesOrderHeader_test]
[SalesOrderDetail_test]
数据库下载链接：AdventureWorks_Full_backup_2013-3-4.bak
其实简单来讲，参数嗅探我的很通俗的解释就是：SQLSERVER用鼻子嗅不到具体参数是多少
所以他不能选择最合适的执行计划去执行你的查询，所以参数嗅探是一个不好的现象。
想真正了解参数嗅探，大家可以先创建下面两个存储过程
存储过程一：
<span> 1</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span><span> 2</span> <span>GO</span><span> 3</span> <span>DROP</span> <span>PROC</span><span> Sniff</span><span> 4</span> <span>GO</span><span> 5</span> <span>CREATE</span> <span>PROC</span> Sniff(<span>@i</span> <span>INT</span><span>)</span><span> 6</span> <span>AS</span><span> 7</span> <span>SELECT</span> <span>COUNT</span>(b.<span>[</span><span>SalesOrderID</span><span>]</span>),<span>SUM</span>(p.<span>[</span><span>Weight</span><span>]</span><span>)</span><span> 8</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>SalesOrderHeader_test</span><span>]</span><span> a</span><span> 9</span> <span>INNER</span> <span>JOIN</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>SalesOrderDetail_test</span><span>]</span><span> b</span><span>10</span> <span>ON</span> a.<span>[</span><span>SalesOrderID</span><span>]</span><span>=</span>b.<span>[</span><span>SalesOrderID</span><span>]</span><span>11</span> <span>INNER</span> <span>JOIN</span> <span>[</span><span>Production</span><span>]</span>.<span>[</span><span>Product</span><span>]</span><span> p</span><span>12</span> <span>ON</span> b.<span>[</span><span>ProductID</span><span>]</span><span>=</span>p.<span>[</span><span>ProductID</span><span>]</span><span>13</span> <span>WHERE</span> a.<span>[</span><span>SalesOrderID</span><span>]</span><span>=</span><span>@i</span><span>14</span> <span>GO</span>
存储过程二：
<span> 1</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span><span> 2</span> <span>GO</span><span> 3</span> <span>DROP</span> <span>PROC</span><span> Sniff2</span><span> 4</span> <span>GO</span><span> 5</span> <span>CREATE</span> <span>PROC</span> Sniff2(<span>@i</span> <span>INT</span><span>)</span><span> 6</span> <span>AS</span><span> 7</span> <span>DECLARE</span> <span>@j</span> <span>INT</span><span> 8</span> <span>SET</span> <span>@j</span><span>=</span><span>@i</span><span> 9</span> <span>SELECT</span> <span>COUNT</span>(b.<span>[</span><span>SalesOrderID</span><span>]</span>),<span>SUM</span>(p.<span>[</span><span>Weight</span><span>]</span><span>)</span><span>10</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>SalesOrderHeader_test</span><span>]</span><span> a</span><span>11</span> <span>INNER</span> <span>JOIN</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>SalesOrderDetail_test</span><span>]</span><span> b</span><span>12</span> <span>ON</span> a.<span>[</span><span>SalesOrderID</span><span>]</span><span>=</span>b.<span>[</span><span>SalesOrderID</span><span>]</span><span>13</span> <span>INNER</span> <span>JOIN</span> <span>[</span><span>Production</span><span>]</span>.<span>[</span><span>Product</span><span>]</span><span> p</span><span>14</span> <span>ON</span> b.<span>[</span><span>ProductID</span><span>]</span><span>=</span>p.<span>[</span><span>ProductID</span><span>]</span><span>15</span> <span>WHERE</span> a.<span>[</span><span>SalesOrderID</span><span>]</span><span>=</span><span>@j</span><span>16</span> <span>GO</span>
然后请做下面这两个测试
测试一：
<span> 1</span> <span>--</span><span>测试一：</span><span> 2</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span><span> 3</span> <span>GO</span><span> 4</span> <span>DBCC</span><span> freeproccache</span><span> 5</span> <span>GO</span><span> 6</span> <span>EXEC</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Sniff</span><span>]</span> <span>@i</span> <span>=</span> <span>500000</span> <span>--</span><span> int</span><span> 7</span> <span>--</span><span>发生编译，插入一个使用nested loops联接的执行计划</span><span> 8</span> <span>GO</span><span> 9</span> <span>10</span> <span>EXEC</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Sniff</span><span>]</span> <span>@i</span> <span>=</span> <span>75124</span> <span>--</span><span> int</span><span>11</span> <span>--</span><span>发生执行计划重用，重用上面的nested loops的执行计划</span><span>12</span> <span>GO</span>
测试二：
<span> 1</span> <span>--</span><span>测试二：</span><span> 2</span> <span> 3</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span><span> 4</span> <span>GO</span><span> 5</span> <span>DBCC</span><span> freeproccache</span><span> 6</span> <span>GO</span><span> 7</span> <span>SET</span> <span>STATISTICS</span> PROFILE <span>ON</span><span> 8</span> <span>EXEC</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Sniff</span><span>]</span> <span>@i</span> <span>=</span> <span>75124</span> <span>--</span><span> int</span><span> 9</span> <span>--</span><span>发生编译，插入一个使用hash match联接的执行计划</span><span>10</span> <span>GO</span><span>11</span> <span>12</span> <span>EXEC</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Sniff</span><span>]</span> <span>@i</span> <span>=</span> <span>50000</span> <span>--</span><span> int</span><span>13</span> <span>--</span><span>发生执行计划重用，重用上面的hash match的执行计划</span><span>14</span> <span>GO</span>
从上面两个测试可以清楚地看到执行计划重用的副作用。
由于数据分布差别很大参数50000和75124只对自己生成的执行计划有好的性能，
如果使用对方生成的执行计划，性能就会下降。参数50000返回的结果集比较小，
所以性能下降不太严重。参数75124返回的结果集大，就有了明显的性能下降，两个执行计划的差别有近10倍
对于这种因为重用他人生成的执行计划而导致的水土不服现象，SQSERVERL有一个专有名词，叫“参数嗅探 parameter sniffing”
因为语句的执行计划对变量的值很敏感，而导致重用执行计划会遇到性能问题，就是我上面说的
“
SQLSERVER用鼻子嗅不到具体参数是多少，所以他不能选择最合适的执行计划去执行你的查询
”
本地变量的影响
那对于有parameter sniffing问题的存储过程，如果使用本地变量，会怎样呢？
下面请看测试3。这次用不同的变量值时，都清空执行计划缓存，迫使其重编译
<span>1</span> <span>--</span><span>第一次</span><span>2</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span><span>3</span> <span>GO</span><span>4</span> <span>DBCC</span><span> freeproccache</span><span>5</span> <span>GO</span><span>6</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span><span>7</span> <span>SET</span> <span>STATISTICS</span> PROFILE <span>ON</span><span>8</span> <span>EXEC</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Sniff</span><span>]</span> <span>@i</span> <span>=</span> <span>50000</span> <span>--</span><span> int</span><span>9</span> <span>GO</span>
<span>1</span> <span>--</span><span>第二次</span><span>2</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span><span>3</span> <span>GO</span><span>4</span> <span>DBCC</span><span> freeproccache</span><span>5</span> <span>GO</span><span>6</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span><span>7</span> <span>SET</span> <span>STATISTICS</span> PROFILE <span>ON</span><span>8</span> <span>EXEC</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Sniff</span><span>]</span> <span>@i</span> <span>=</span> <span>75124</span> <span>--</span><span> int</span><span>9</span> <span>GO</span>
<span>1</span> <span>--</span><span>第三次</span><span>2</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span><span>3</span> <span>GO</span><span>4</span> <span>DBCC</span><span> freeproccache</span><span>5</span> <span>GO</span><span>6</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span><span>7</span> <span>SET</span> <span>STATISTICS</span> PROFILE <span>ON</span><span>8</span> <span>EXEC</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Sniff2</span><span>]</span> <span>@i</span> <span>=</span> <span>50000</span> <span>--</span><span> int</span><span>9</span> <span>GO</span>
<span>1</span> <span>--</span><span>第四次</span><span>2</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span><span>3</span> <span>GO</span><span>4</span> <span>DBCC</span><span> freeproccache</span><span>5</span> <span>GO</span><span>6</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span><span>7</span> <span>SET</span> <span>STATISTICS</span> PROFILE <span>ON</span><span>8</span> <span>EXEC</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Sniff2</span><span>]</span> <span>@i</span> <span>=</span> <span>75124</span> <span>--</span><span> int</span><span>9</span> <span>GO</span>
看他们的执行计划：
对于第一句和第二句，因为SQL在编译的时候知道变量的值，所以在做EstimateRows的时候，做得非常准确，选择了最适合他们的执行计划
但是对于第三句和第四句，SQLSERVER不知道@j的值是多少，所以在做EstimateRows的时候，不管代入的@i值是多少，
一律给@j一样的预测结果。所以两个执行计划是完全一样的（都是Hash Match）。
参数嗅探的解决办法
参数嗅探的问题发生的频率并不高，他只会发生在一些表格里的数据分布很不均匀，或者用户带入的参数值很不均匀的情况下。
由于篇幅原因我就不具体说了,只是做一些归纳
（1）用exec()的方式运行动态SQL
如果在存储过程里不是直接运行语句，而是把语句带上变量，生成一个字符串，再让exec()这样的命令做动态语句运行，
那SQL就会在运行到这句话的时候，对动态语句进行编译。
这时SQL已经知道了变量的值，会根据生成优化的执行计划，从而绕过参数嗅探问题
<span> 1</span> <span>--</span><span>例如前面的存储过程Sniff，就可以改成这样</span><span> 2</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span><span> 3</span> <span>GO</span><span> 4</span> <span>DROP</span> <span>PROC</span><span> NOSniff</span><span> 5</span> <span>GO</span><span> 6</span> <span>CREATE</span> <span>PROC</span> NOSniff(<span>@i</span> <span>INT</span><span>)</span><span> 7</span> <span>AS</span><span> 8</span> <span>DECLARE</span> <span>@cmd</span> <span>VARCHAR</span>(<span>1000</span><span>)</span><span> 9</span> <span>SET</span> <span>@cmd</span><span>=</span><span>'</span><span>SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])</span><span>10</span> <span>FROM [dbo].[SalesOrderHeader_test] a</span><span>11</span> <span>INNER JOIN [dbo].[SalesOrderDetail_test] b</span><span>12</span> <span>ON a.[SalesOrderID]=b.[SalesOrderID]</span><span>13</span> <span>INNER JOIN [Production].[Product] p</span><span>14</span> <span>ON b.[ProductID]=p.[ProductID]</span><span>15</span> <span>WHERE a.[SalesOrderID]=</span><span>'</span><span>16</span> <span>EXEC</span>(<span>@cmd</span><span>+</span><span>@i</span><span>)</span><span>17</span> <span>GO</span>
（2）使用本地变量local variable
（3）在语句里使用query hint，指定执行计划
在select,insert,update,delete语句的最后，可以加一个”option()”的子句
对SQLSERVER将要生成的执行计划进行指导。当DBA知道问题所在以后，可以通过加hint的方式，引导
SQL生成一个比较安全的，对所有可能的变量值都不差的执行计划
<span> 1</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span><span> 2</span> <span>GO</span><span> 3</span> <span>DROP</span> <span>PROC</span><span> NoSniff_QueryHint_Recompile</span><span> 4</span> <span>GO</span><span> 5</span> <span>CREATE</span> <span>PROC</span> NoSniff_QueryHint_Recompile(<span>@i</span> <span>INT</span><span>) </span><span> 6</span> <span>AS</span><span> 7</span> <span>SELECT</span> <span>COUNT</span>(b.<span>[</span><span>SalesOrderID</span><span>]</span>),<span>SUM</span>(p.<span>[</span><span>Weight</span><span>]</span><span>)</span><span> 8</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>SalesOrderHeader_test</span><span>]</span><span> a</span><span> 9</span> <span>INNER</span> <span>JOIN</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>SalesOrderDetail_test</span><span>]</span><span> b</span><span>10</span> <span>ON</span> a.<span>[</span><span>SalesOrderID</span><span>]</span><span>=</span>b.<span>[</span><span>SalesOrderID</span><span>]</span><span>11</span> <span>INNER</span> <span>JOIN</span> <span>[</span><span>Production</span><span>]</span>.<span>[</span><span>Product</span><span>]</span><span> p</span><span>12</span> <span>ON</span> b.<span>[</span><span>ProductID</span><span>]</span><span>=</span>p.<span>[</span><span>ProductID</span><span>]</span><span>13</span> <span>WHERE</span> a.<span>[</span><span>SalesOrderID</span><span>]</span><span>=</span><span>@i</span><span>14</span> <span>OPTION</span><span>(RECOMPILE)</span><span>15</span> <span>GO</span>
（4）Plan Guide
可以用下面的方法，在原来那个有参数嗅探问题的存储过程“Sniff”上，解决sniffing问题
<span> 1</span> <span>USE</span> <span>[</span><span>AdventureWorks</span><span>]</span><span> 2</span> <span>GO</span><span> 3</span> <span>EXEC</span> <span>[</span><span>sys</span><span>]</span>.<span>[</span><span>sp_create_plan_guide</span><span>]</span><span> 4</span> <span>@name</span><span>=</span>N<span>'</span><span>Guide1</span><span>'</span><span>,</span><span> 5</span> <span>@stmt</span><span>=</span>N<span>'</span><span>SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])</span><span> 6</span> <span>FROM [dbo].[SalesOrderHeader_test] a</span><span> 7</span> <span>INNER JOIN [dbo].[SalesOrderDetail_test] b</span><span> 8</span> <span>ON a.[SalesOrderID]=b.[SalesOrderID]</span><span> 9</span> <span>INNER JOIN [Production].[Product] p</span><span>10</span> <span>ON b.[ProductID]=p.[ProductID]</span><span>11</span> <span>WHERE a.[SalesOrderID]=@i</span><span>'</span><span>,</span><span>12</span> <span>@type</span><span>=</span>N<span>'</span><span>OBJECT</span><span>'</span><span>,</span><span>13</span> <span>@module_or_batch</span><span>=</span>N<span>'</span><span>Sniff</span><span>'</span><span>,</span><span>14</span> <span>@params</span><span>=</span><span>NULL</span><span>,</span><span>15</span> <span>@hints</span><span>=</span>N<span>'</span><span>option(optimize for(@i=75124))</span><span>'</span><span>;</span><span>16</span> <span>GO</span>
对于Plan Guide，他还可以使用在一般的语句调优里
终于搞定了，因为要搞测试数据的原因所以搞了很久啊~~
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：何谓SQLSERVER参数嗅探
转载请注明原文链接：何谓SQLSERVER参数嗅探
