个人中心
文章发布
退出
作者热门文章
- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
26
4
CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章SQL Server中参数化SQL写法遇到parameter sniff ，导致不合理执行计划重用的快速解决方法由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.
parameter sniff问题是重用其他参数生成的执行计划，导致当前参数采用该执行计划非最优化的现象。想必熟悉数据的同学都应该知道，产生parameter sniff最典型的问题就是使用了参数化的SQL（或者存储过程中使用了参数化）写法，如果存在数据分布不均匀的情况下，正常情况下生成的执行计划，在传入在分布数据较多的参数的情况下，重用了正常参数生成的执行计划，而这种缓存的执行计划并非适合当前参数的一种情况.
这种情况，在实际业务中，出现的频率还是比较高的，因为存储过程一般都是采用参数化的写法，这时，遇到分布不均匀的数据参数时，parameter sniff现象就出现了，这种问题还是比较让人头疼的.
具体parameter sniff产生的原因，我就不做过多的解释了，解释这个就显得太low了 。
我举个简单的例子，模拟一下这个现象，说明参数化的存存储过程是怎么写的，存在哪些问题，又如何解决parameter sniff问题， 。
先创建一个测试环境:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
create
table
ParameterSniffProblem
(
id
int
identity(1,1),
CustomerId
int
,
OrderId
int
,
OrederStatus
int
,
CreateDate Datetime,
Remark
varchar
(200)
)
declare
@i
int
= 0
while @i<500000
begin
INSERT
INTO
ParameterSniffProblem
values
(@i%10000,@i,RAND()*10,GETDATE()-RAND()*100,NEWID())
set
@i=@i+1
end
--假如某一个客户有非常多的订单，模拟数据分布不均匀的情况
INSERT
INTO
ParameterSniffProblem
values
(6666,RAND()*100000,1,GETDATE()-RAND()*100,NEWID())
GO 100000
--创建正常的索引
CREATE
CLUSTERED
INDEX
IDX_CreateDate
on
ParameterSniffProblem(CreateDate
)<br>
CREATE
INDEX
IDX_CustomerId
ON
ParameterSniffProblem(CustomerId)
|
参数化存储过程的写法:
在编写存储过程的时候，我们一般建议采用参数化的写法，目的是为了减少存储过程的编译和加强执行计划缓存的重用 。
大概是这样子的 。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
CREATE
PROCEDURE
[dbo].ParameterSniffTest
(
@p_CustomerId
int
,
@p_Status
int
,
@p_FromDate datetime,
@p_ToDate datetime
)
AS
BEGIN
SET
NOCOUNT
ON
DECLARE
@Parm NVARCHAR(
MAX
),
@sqlcommand NVARCHAR(
MAX
) = N
''
SET
@sqlcommand =
'SELECT * FROM ParameterSniffProblem WHERE 1=1'
IF(@p_CustomerId
IS
NOT
NULL
)
SET
@sqlcommand = CONCAT(@sqlcommand,
'AND CustomerId=@p_CustomerId '
)
IF(@p_Status
IS
NOT
NULL
)
SET
@sqlcommand = CONCAT(@sqlcommand,
'AND OrederStatus=@p_Status '
)
IF(@p_FromDate
IS
NOT
NULL
)
SET
@sqlcommand = CONCAT(@sqlcommand,
'AND CreateDate>=@p_FromDate '
)
IF(@p_ToDate
IS
NOT
NULL
)
SET
@sqlcommand = CONCAT(@sqlcommand,
'AND CreateDate<=@p_ToDate '
)
SET
@Parm=
'@p_CustomerId int,
@p_Status int,
@p_FromDate datetime,
@p_ToDate datetime '
EXEC
sp_executesql @sqlcommand,@Parm,
@p_CustomerId = @p_CustomerId,
@p_Status = @p_Status,
@p_FromDate = @p_FromDate,
@p_ToDate = @p_ToDate
END
GO
|
Parameter Sniff问题:
这就潜在一个parameter sniff问题， 。
比如我查询用户ID=100的订单信息，一个正常的分布的数据，存储过程第一次编译，这个执行计划完全没有问题， 。
如果我接着改变参数执行查询用户6666的信息，一个分布及其不均匀的数据，但是因为重用上面缓存的执行计划，就出现parameter sniff问题了，这个执行计划显然是不合理的 。
IO就不看了，刻意造的例子 。
如果我清空执行计划缓存，重新执行上述查询，因为有了重编译，执行计划就是不这个样子，对于CustomerID=6666这个参数来说，显然走全表扫描代价要更小一点 。
想必这是一个开发中常见的问题给，我们参数化SQL就是为了让不同参数的查询重用执行计划，但是很不幸，数据分布不均匀的时候，重用执行计划恰恰又给数据库造成了伤害，例中，如果是正常参数重用了分布较多数据的执行计划，比如命名可以用到索引，结果是表扫描，后果会更严重.
那么，既想要尽可能的重用执行计划，又要避免因为执行计划重用产生parameter sniff问题，怎么办?
我们知道问题在于@p_CustomerId身上，那么可不可以对有可能产生parameter sniff问题的@p_CustomerId不做参数化，直接拼凑在SQL中，如果@p_CustomerId变化了就重编译SQL，也就是对传入进来的@p_CustomerId重编译 。
如果是@p_CustomerId不变，其他参数有变化，比如这里时间字段的变化，还可以享受参数化带来的执行计划重用的好处 也就是这样处理 @p_CustomerId这个参数，直接把@p_CustomerId以字符串的方式平凑在SQL语句中，这样的话，就相当于即席查询了，不通过参数化的方式给CustomerId这个查询条件字段赋值 。
|
1
2
|
IF(@p_CustomerId
IS
NOT
NULL
)
SET
@sqlcommand = CONCAT(@sqlcommand,
'AND CustomerId= '
,@p_CustomerId)
|
这样再去执行存储过程的时候， 。
带入@p_CustomerId=1的时候，执行IDX_CustomerId的index seek 。
带入@p_CustomerId=6666的时候，重编译，执行计划是全表扫描，避免重用上面生成的执行计划，造成不合理的执行方式对效率以及数据库服务器资源的消耗 。
这样会尽可能的减少parameter sniff问题带来的影响，当缓存了@p_CustomerId=1的执行计划的时候，再次传入@p_CustomerId=1，其他条件有较小的变化，比如时间字段上有改动，依然可以重用缓存的执行计划，避免重编译带来的影响 。
结论:
这种方式于处理parameter sniff问题，当然不是完美的，肯定也有问题，我当然知道一旦@p_CustomerId不同就要重编译 。
肯定会因为@p_CustomerId参数值不同，这样的话，不可避免地增加了重编译的机会， 。
但是却不会因为不合理的执行计划重用，带来的parameter sniff问题 。
要知道一旦产生parameter sniff问题，大量的查询用到不合理的执行计划，会对整个服务器产生非常严重的影响，比如可能会产生大量的IO等 。
同时存在一个好处，比如第一次传入@p_CustomerId=1， 。
再次传入@p_CustomerId=1，其他条件有较小的变化，比如时间字段上有改动，依然可以重用缓存的执行计划，避免重编译带来的影响当然我这里只是一个简单的例子，实际应用中远远比这个复杂 。
比如分布的特别的多的数据有两个特点，第一分布的标示不仅仅只有一个，第二分布不均的数据是动态的，有可能第一季度是A这部分数据占据大多数，有可能是第二季度B数据占绝大多数 。
所以很难采用Plan Guide的方式解决parameter sniff问题 。
这种方式可以在一定程度上也能够重用缓存的执行计划，可以减少（但不可避免）重编译的次数 。
同时，这种方式与拼凑一个SQL字符串执行的即席查询方式相比，同时还可以利用参数化带来的其他好处，比如SQL注入等等 。
总结:
parameter sniff问题的解决方式有很多，不一一啰嗦了 。
最典型的就是强制重编译， 。
或者使用EXEC执行一个拼凑出来的字符串，这种方式属于Adhoc查询 。
或者查询提示， 。
或者是使用本地变量， 。
或者使用Plan Guide等等等等， 。
每种方式都有他的局限性，至少到目前为止，还没有一种十全十美的方式来解决parameter sniff问题 。
遇到问题，解决方法有很多种，以最小的代价解决问题才是王道.
最后此篇关于SQL Server中参数化SQL写法遇到parameter sniff ，导致不合理执行计划重用的快速解决方法的文章就讲到这里了,如果你想了解更多关于SQL Server中参数化SQL写法遇到parameter sniff ，导致不合理执行计划重用的快速解决方法的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。
26
4
0
目前我正在尝试创建一个 Web 部署包。所以我在我的项目的根目录中添加了一个 parameters.xml 并指定了一些自定义参数。 我发现我的很多参数都部分相同。所以我想做某种参数引用。寻找这个，我
如何设置我的 Symfony 2 项目以使用 parameters.yml 而不是 parameters.ini？ 在 Controller 中，我可以像这样从 parameters.ini 中获取变
有什么建议说明为什么此 AWS CloudFormation 不断回滚吗？ { "Description" : "Single Instance", "Resources" : {
PARAMETERS: p_1 TYPE i, p_2 TYPE i. 因此在初始屏幕中，我看到了 2 个文本框，每个参数一个。 如果我填写其中一个，但不按回车键，然后我在第二个上调用 F4 帮助，我
我需要存储 Parameter由 Build() 返回作为 Parameter (因为我将参数存储在一个数组中，另一种方法就是为每个参数数量复制粘贴相同的类太多，因为 c# 没有可变参数泛型)。 问题
我正在为我的 CS 类(class)做作业，它使用了 const，我对何时使用它们感到有点困惑。 这3个函数有什么区别？ int const function(parameters) int fu
在 xgboost 的文档中，我读到: base_score [default=0.5] : the initial prediction score of all instances, global
我正在创建一个新的 REST 服务。 向 REST 服务传递参数的标准是什么。在 Java 的不同 REST 实现中，您可以将参数配置为路径的一部分或请求参数。例如， 路径参数 http://www.
在我的程序中，我需要验证传递给程序的参数是一个整数，所以我创建了这个小函数来处理用户键入“1st”而不是“1”的情况。 问题是它根本不起作用。我尝试调试，我只能告诉你参数是 12，long 是 2。(
谁能告诉我如何使用存储在 &rest 指定值中的参数。 我已经阅读了很多，似乎作者只知道如何列出所有参数。 (defun test (a &rest b) b) 这很高兴看到，但并不是很有用。 到目前
我使用 git 有一段时间了，但大多数时候我更喜欢与 Intelij IDEA 的集成。现在，为了扩展我对系统的知识和理解，我决定更多地使用命令行。我观察到的是有两种类型的参数: --paramete
我正在用 RAML 编写一些 REST 文档，但我被卡住了。 我的问题: - 我有一个用于搜索的 GET 请求，它可以采用参数“id”或( 独占或 )“引用”。拥有 只有其中之一 是必须的。 我知道怎
我定义了一个这样的 Action : /secure/listaAnnunci.action /login.jsp 我可以从 Action 内部访问参数吗？谢谢 最佳答案 您需要实现 S
我有一个 TeamCity 8.0.3 项目，其中包含多个配置，其中有一个通用参数(定义为项目参数):targetServerIP .这些配置之一是“一键部署”，它通过使用快照依赖项启动其他配置。我已
try{ Class.forName("com.mysql.jdbc.Driver"); mycon = DriverManager.getConnec
我在实际的 javascript 项目中遇到了一个非常奇怪的情况。我创建了一个自定义事件并将数组放入该事件 $.publish("merilon/TablesLoaded", getTables())
在使用参数数组进行插入/更新期间，可以忽略一个/一些特定行的一个/一些参数。 我提供了一个简单的例子。想象一下，我们有一个包含 3 列的表:X、Y 和 Z。我们想在 block 中执行更新(如果缺少某
如何编写接受未定义参数的函数？我想它可以像那样工作: void foo(void undefined_param) { if(typeof(undefined_param) == int) {
CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界. 这篇CFSDN的博客文章PDO版本问题 Invalid parameter number: no
Jenkins 管道作业如下所示: 部分 Jenkinsfile(我们使用脚本化管道)是: properties([parameters([string(defaultValue: "", descr
我是一名优秀的程序员,十分优秀！