澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:积累进度参数字传送递的

2019-09-11 作者:数据库网络   |   浏览(95)

前言

  很多人认为数据库其实很简单,也没什么大深入的细节去研究,但是真正的一些细节问题决定着你的是否是专家。

  本文主要讲述一下存储过程参数传递的一些小细节,很多人知道参数嗅探,本例也可以理解成参数嗅探的威力加强版

--如果在存储过程中定义变量,并为变量SET赋值,该变量的值无法为执行计划提供参考(即执行计划不考虑该变量),将会出现预估行数和实际行数相差过大导致执行计划不优的情况

在讨论临时表和表变量的区别时,其中一个重点就是两者的预估行数,在默认设置下,表变量的预估行数总是为1,而临时表的预估行数会随表中数据量的变化而变化。正是因为这个区别,在处理大数据量时往往推荐使用临时表而非表变量(当然还有索引的问题)。

    前面我们了解了参数嗅探可能是好的也可能是坏的。当数列的分布不均匀的时候参数嗅探就是不好的事情。例如,考虑“Status”列在Orders表中有总共10M行。该列有7个不同的值,如下分布:

小例子

 1 ---创建测试表
 2 SELECT IDENTITY(INT,1,1) AS RID,
 3 * INTO TB1
 4 FROM sys.all_columns
 5 GO
 6 ---模拟大量数据
 7 INSERT INTO TB1
 8 SELECT *
 9 FROM sys.all_columns
10 GO 100
11  
12  
13  
14 --在 user_type_id列 创建一个索引
15 CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160625-164531] ON [dbo].[TB1]
16 (
17     [user_type_id] ASC
18 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
19 GO
20  
21 --开启IO统计
22 set statistics io on
23  
24 --测试查询执行计划
25 select * from tb1 where user_type_id = 10

澳门新萄京官方网站 1

 

澳门新萄京官方网站 2

澳门新萄京官方网站 3

澳门新萄京官方网站 4

 

注:本例中,语句的执行应该走索引seek key look up

 

--如果在存储过程中使用SET为存储过程参数重新赋值,执行计划仍采用执行时传入的值来生成执行计划。

--准备测试数据
DROP TABLE TB1
GO
SELECT IDENTITY(INT,1,1) AS RID,
*INTO TB1
FROM sys.all_columns
GO
INSERT INTO TB1
SELECT *
FROM sys.all_columns
GO 100
ALTER TABLE TB1
ADD PRIMARY KEY(RID)
 
 
--测试查询参数使用变量
--例如下列存储过程,由于在生成执行计划时不知道@ID的具体值,因此无法预估满足PID>@ID条件的
CREATE PROCEDURE dbo.USP_GetData
(
  @PIDINT
)
AS
BEGIN
DECLARE @ID INT
SET @ID= @PID
SELECT *
FROM TB1
WHERE RID>@ID
END
GO
EXEC dbo.USP_GetData @PID=606808
--由于预估行数有问题,导致生成不使用索引的查询计划
 澳门新萄京官方网站 5

--================================================= 

--测试修改传入参数的情况
--虽然传入参数在传入后被修改,但是生成执行计划时仍使用传入时的值
CREATE PROCEDURE dbo.USP_GetData2
(
  @PID INT
)
AS
BEGIN
SET @PID=@PID-606800
SELECT*
FROM TB1
WHERE RID>@PID
END
GO
EXEC dbo.USP_GetData2 @PID=606808

 澳门新萄京官方网站 6

--================================================= 
--测试在查询时对传入参数做运算
CREATE PROCEDURE dbo.USP_GetData3
(
  @PID INT
)
AS
BEGIN
SELECT COUNT(1)
FROM TB1
WHERE RID>@PID 600080
END
GO
EXEC dbo.USP_GetData3 @PID=20
 澳门新萄京官方网站 7

 --================================================= 
--测试在查询时对传入参数做运算(复杂运算)
----对应复杂运算,无法获得准确的值,因此不能准确地预估行数,也不能生成合理的执行计划
CREATE PROCEDURE dbo.USP_GetData4
(
  @PID INT
)
AS
BEGIN
SELECT COUNT(1)
FROM TB1
WHERE RID>@PID CAST(RAND()*6000800 AS INT)
END
GO
EXEC dbo.USP_GetData4 @PID=20
GO

 澳门新萄京官方网站 8

 

总结:
在存储过程中使用到的变量可以分为内部变量和外部变量
1>对于外部变量,存储过程编译时会使用该变量的真实值依据统计来生成执行计划,无论该外部变量是否在存储过程中发生修改
2>对于内部变量,存储过程编译时无法获取该变量的真实值,因此无法使用统计,从而只能生成"最通用"的执行计划(可能是比较差的执行计划)

补充:
可以使用OPTION(optimize for(@PID=75124))方式来解决因变量值导致的执行计划不优的问题

 

 

 科普下, 查询优化器会根据预估行数和操作运算符来预估资源消耗,根据资源消耗情况来选取相对“较优”的执行计划,如果预估行数与实际行数差距较大,则可能生成不高效的执行计划。

Status Number of Rows
Open 314
Pending Approval 561
Approved 28,990
Paid 17,610

Shipped

817,197

Closed

7,922,834

Cancelled

1,032,886

测试一

 1 --测试1:使用定义变量,把参数值传递给变量
 2 
 3 create PROCEDURE dbo.USP_GetData
 4 (
 5   @PID INT 
 6 )
 7 AS
 8 BEGIN
 9 DECLARE @ID INT
10 SET @ID= @PID
11 SELECT *
12 FROM TB1
13 WHERE user_type_id = @ID
14 END
15 GO
16 EXEC dbo.USP_GetData @PID=10

 

澳门新萄京官方网站 9

 

澳门新萄京官方网站 10

 结论:如果在存储过程中定义变量,并为变量SET赋值,该变量的值无法为执行计划提供参考(即执行计划不考虑该变量),将会出现预估行数和实际行数相差过大导致执行计划不优的情况

 

举个栗子,看着远处的小土包没多远,骑着马跑了半天发现还没到,这就是看山跑死马的典故,如果能相对“准确”地预估出距离,那么就不是骑马而是开飞机,这就是预估行数影响执行计划!

 

测试二

 1 ---测试2 : 对参数进行运算
 2 create PROCEDURE dbo.USP_GetData2
 3 (
 4   @PID INT
 5 )
 6 AS
 7 BEGIN
 8 SET @PID=@PID-1
 9 SELECT*
10 FROM TB1
11 WHERE user_type_id = @PID
12 END
13 GO
14 EXEC dbo.USP_GetData2 @PID=11

 

 

 澳门新萄京官方网站 11

 

澳门新萄京官方网站 12

结论:如果在存储过程中使用SET为存储过程参数重新赋值,执行计划仍采用执行时传入的值来生成执行计划。

 

今天就表变量的预估行数问题来学习下。

    如果查询status是“Open”的数据时使用参数嗅探,那么优化器很可能选择一个带有index seek 和 key lookup的执行计划。这个计划放在缓存中便于重用。当其他用户执行查询closed状态的时候,相同的执行计划被重用,这就很可能是一个灾难,因为现在将进行8M个键值查找操作。

测试三

 1 --测试3 :对参数行进拼接
 2 
 3 create PROCEDURE dbo.USP_GetData3
 4 (
 5 @PID INT
 6 )
 7 AS
 8 BEGIN
 9 DECLARE @ID INT
10 set @ID = 2 
11 SET @PID = @ID   @PID
12 SELECT *
13 FROM TB1
14 WHERE user_type_id = @PID
15 END
16 GO
17 EXEC dbo.USP_GetData3 @PID= 8

 

 

 澳门新萄京官方网站 13

 

澳门新萄京官方网站 14

 结论:如果在存储过程中使用新定义的变量与传入参数拼接重新赋值,执行计划仍采用执行时传入的值来生成执行计划。

 

测试1:首先看下默认设置下表变量的预估行数

    另外的使用参数嗅探的糟糕情况是用非相等的谓词使用参数。请看下面的查询:

测试四

 1 --测试4 : 对变量进行运算 
 2 create PROCEDURE dbo.USP_GetData4
 3 (
 4   @PID INT
 5 )
 6 AS
 7 BEGIN
 8 SELECT *
 9 FROM TB1
10 WHERE user_type_id = @PID  2
11 END
12 GO
13 EXEC dbo.USP_GetData4 @PID=8

 

 澳门新萄京官方网站 15

 

澳门新萄京官方网站 16

  结论:虽然传入参数在传入后被修改,但是生成执行计划时仍使用传入时的值

 

DECLARE @TB1 TABLE
(
    ID BIGINT IDENTITY(1,1) PRIMARY KEY,
    C1 BIGINT
)

INSERT INTO @TB1(C1)
SELECT object_id FROM sys.all_columns

SELECT COUNT(1) FROM @TB1 AS T1
INNER JOIN sys.objects AS T2
ON T1.C1 = T2.OBJECT_ID
SELECT
    Id ,
    CustomerId ,
    TransactionDateTime ,
    StatusId
FROM
    Billing.Transactions
WHERE
    TransactionDateTime BETWEEN @FromDateTime AND @ToDateTime
ORDER BY
    TransactionDateTime ASC;

测试五

 1 --测试5 :对变量进行复杂运算 
 2 create PROCEDURE dbo.USP_GetData5
 3 (
 4 @PID INT
 5 )
 6 AS
 7 BEGIN
 8 SELECT *
 9 FROM TB1
10 WHERE user_type_id = @PID  CAST(RAND()*600 AS INT)
11 END
12 GO
13 EXEC dbo.USP_GetData5 @PID=8
14 GO

 

 澳门新萄京官方网站 17

澳门新萄京官方网站 18

 结论:对参数做复杂运算,无法获得准确的值,因此不能准确地预估行数,也不能生成合理的执行计划

 

澳门新萄京官方网站 19

  

测试六

 1 --测试6 : 复杂运算使用变量拼接
 2 create PROCEDURE dbo.USP_GetData6
 3 (
 4 @PID INT
 5 )
 6 AS
 7 BEGIN
 8 DECLARE @ID INT
 9 set @ID = CAST(RAND()*600 AS INT)
10 SET @PID = @ID   @PID
11 SELECT *
12 FROM TB1
13 WHERE user_type_id = @PID
14 END
15 GO
16 EXEC dbo.USP_GetData6 @PID=8
17 GO

 

 

 澳门新萄京官方网站 20

 

 

 澳门新萄京官方网站 21

 

结论:针对测试五可以使用参数拼接的方式,以便准确地预估行数,使用正确的执行计划

 

 

通过上面的执行计划,很容易看到:临时表@TB1的实际行数为7490,而预估行数为1.

     如果查询使用参数嗅探编译,使用值“2014-07-01″ 和“2014-08-01″,那么优化器基于统计估计行数并且大概估计行数为20000。然后创建基于这个估计行数的计划并且放在缓存中。后来的执行可以使用完全不同的参数。例如,用户执行查询用时间参数“2012-01-01″ 和“2014-01-01″。结果集大概有61000行,但是基于之前的行数的计划被重用,并且很可能不是一个好的执行计划。

 总结

  技术支持做了比较长的时间了,遇到了很多很多坑,在这些坑中不断反思,慢慢成长!不要说什么数据库更优秀,不要说我们海量数据库需要什么什么高端的技术,其实解决问题的关键只是那么一点点的基础知识。

  注:本例中还有另外一种情况就是查询的数据量很大,那么本身走全表扫描是最优计划,而由于参数传递的问题错误的走了index seek key look up 道理是一样的。

 

--------------博客地址-----------------------------------------------------------------------------

原文地址: 

如有转载请保留原文地址! 

 

 ----------------------------------------------------------------------------------------------------

注:此文章为原创,欢迎转载,请在文章页面明显位置给出此文链接!
若您觉得这篇文章还不错请点击下右下角的推荐,非常感谢!

 

 

 

测试2:使用临时表

    那么,我们能做些什么来影响参数嗅探?  

CREATE TABLE #TB1
(
    ID BIGINT IDENTITY(1,1) PRIMARY KEY,
    C1 BIGINT
)

INSERT INTO #TB1(C1)
SELECT object_id FROM sys.all_columns

SELECT COUNT(1) FROM #TB1 AS T1
INNER JOIN sys.objects AS T2
ON T1.C1 = T2.OBJECT_ID

    我将展示一些基于我之前使用存储过程实例的技术:

澳门新萄京官方网站 22

 

从执行计划来看,临时表的预估行数和实际行数相同,均为7490.

CREATE PROCEDURE
    Marketing.usp_CustomersByCountry
(
    @Country AS NCHAR(2)
)
AS

SELECT
    Id ,
    Name ,
    LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = @Country;
GO

 

  

测试3:使用OPTION (RECOMPILE)查询提示

  

DECLARE @TB1 TABLE
(
    ID BIGINT IDENTITY(1,1) PRIMARY KEY,
    C1 BIGINT
)

INSERT INTO @TB1(C1)
SELECT object_id FROM sys.all_columns

SELECT COUNT(1) FROM @TB1 AS T1
INNER JOIN sys.objects AS T2
ON T1.C1 = T2.OBJECT_ID
OPTION (RECOMPILE)

这里是一个“Country”列的分布情况:

澳门新萄京官方网站 23

Country Number of Rows
BE 70

CL

55

CN

29,956

DK

74

EG

64

IL

72

MT

83

PT

75

TR

63

UK

28,888

US

40,101

VE

78

MSDN上对OPTION(RECOMPILE)的解释如下:

 

指示 SQL Server 数据库引擎在执行为查询生成的计划后将其丢弃,从而在下次执行同一查询时强制查询优化器重新编译查询计划。

     正如所见,一共12个不同的值,其中三个是较多的行数,然而其余的行数非常少。这是一个极端的分配不均匀情况没,生产环境中可能很难看到。这里恰好可以展示我的观点…

在编译查询计划时,RECOMPILE 查询提示将使用查询中任意本地变量的当前值,如果查询位于存储过程中,这些当前值将传递给任意参数。

     在讨论可行的解决方案之前,先看一下问题…

而在本测试中,使用OPTION(RECOMPILE)来使查询优化器对表变量有一个“准确”的预估行数,可以看到使用查询提示OPTION(RECOMPILE)下实际行数和预估行数均为7490。

     首先参数赋值为IL。当存储过程首次用“IL”参数执行时,生成计划包含了一个寻找“Country”的索引。对于这个指定的执行这是很有帮助的优化器估计行数是72,完全准确。

PS: 通过测试1和测试3可以发现,随着预估行数的变化,执行计划也发生了变化。

     下次存储过程执行时,使用参数为“US”。数据中有40,101行,并且这种情况下的最佳执行计划是使用聚集索引扫描,可以避免很多“key lookups”。但是计划已经在内存中,就会重用。不幸的是,这个计划包含了索引查找和“key lookup ”而不是聚集索引扫描,这就是一个非常差的执行计划。此时我们看到索引查找操作符的属性中估计行数是72,然后实际却是40000 。这就是执行计划错误引起的估计行数错误。如果我们查看SELECT 的“Parameter List” 属性,就能发现原因所在。由于编译1是“IL”,而运行时是“US”。

 

    那么现在我们发现了问题,接下来让我们看一下可能的解决方案…
Solution #1 – sys.sp_recompile

测试4:使用跟踪标志2453

    很简单就是使用系统存储过程sys.sp_recompile从缓存中移除指定的执行计划或者所有计划引用的指定表和视图。这就是说下次存储过程再次执行时需要重新编译,新的执行计划将被创建。

跟踪标志2453是SQL Server 2012 SP2和SQL Server CU3引入的,其作用于OPTION(RECOMPILE)类似,使得查询优化器在生成执行计划时对表变量有一个“准确”的预估行数,而不是简单粗暴地使用预估行数1。

    记住我们的主要问题是值的分布。因此基于一套新的参数重新编译存储过程将创建指定的执行计划,但是大多数时候这并不解决问题,因为新的计划仍然只针对本次的值是好的,当遇到其他不同分布的参数值时依然是不好的计划。我建议当查询中过滤的值绝大多数情况下是惟一值的时候可以考虑重新编译的方式来解决问题,比如当where后面的status 状态为1的占据99%的数据值时,一般情况就是好的计划。

DBCC TRACEON(2453)
DECLARE @TB1 TABLE
(
    ID BIGINT IDENTITY(1,1) PRIMARY KEY,
    C1 BIGINT
)

INSERT INTO @TB1(C1)
SELECT object_id FROM sys.all_columns

SELECT COUNT(1) FROM @TB1 AS T1
INNER JOIN sys.objects AS T2
ON T1.C1 = T2.OBJECT_ID

DBCC TRACEOFF(2453)

Solution #2 – WITH RECOMPILE

澳门新萄京官方网站 24

如果你不喜欢前面这个赌博式的方法,那么WITH RECOMPILE很适合你。与之前依赖传递给指定执行的参数值不同,这种方式使你可以告诉优化器编译在每一个存储过程中编译计划。

可以看到,在开启跟踪标志后,即使未使用查询提示OPTION(RECOMPILE),表变量的预估行数和实际行数均为7490。

ALTER PROCEDURE
    Marketing.usp_CustomersByCountry
(
    @Country AS NCHAR(2)
)
WITH
    RECOMPILE
AS

SELECT
    Id ,
    Name ,
    LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = @Country;
GO

 

  

测试5,跟踪标志2453的适用场景

 

一些文章介绍称跟踪标志2453仅使用与JOIN操作中,而对于一些“简单”查询,跟踪标志2453没有效果

    每一次参数嗅探被使用时,意味着执行将得到优化器提供的最佳执行计划。既然新的计划每次执行都被创建,那么SQLServer将不会把计划放到缓存中。

最简单的查询莫过于SELECT FROM,如:

这是一个不错的解决方案,因为每次执行存储过程都产生一个最佳的计划,消除了随机赌博式的副作用。但是缺点是每次编译都必须经过昂贵的优化过程。这是需要密集的CPU处理过程。如果系统已经处在PCU高负载并且存储过程频繁执行,那么这种方式是不合适的。另一方面,如果CPU使用率相对较低并且存储过程只是偶尔执行,那么这就是一个带给你最佳的解决方案。

DBCC TRACEON(2453)
DECLARE @TB1 TABLE
(
    ID BIGINT IDENTITY(1,1) PRIMARY KEY,
    C1 BIGINT
)

INSERT INTO @TB1(C1)
SELECT object_id FROM sys.all_columns


SELECT COUNT(1) FROM @TB1


DBCC TRACEOFF(2453)

Solution #3 – OPTION (RECOMPILE)

澳门新萄京官方网站 25

是一个与前者相似的解决方案,但是也有两个重要的不同点。首先,这个查询参数针对有问题的查询语句而不是整个存储过程。

诚然,对于上面的查询,开启跟踪2453仍不能解决表变量预估行数为1的问题。

ALTER PROCEDURE
    Marketing.usp_CustomersByCountry
(
    @Country AS NCHAR(2)
)
AS

SELECT
    Id ,
    Name ,
    LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = @Country
OPTION
    (RECOMPILE);
GO

哪对于稍微复杂但又没有JOIN的查询呢?如:

 

DBCC TRACEON(2453)
DECLARE @TB1 TABLE
(
    ID BIGINT IDENTITY(1,1) PRIMARY KEY,
    C1 BIGINT
)

INSERT INTO @TB1(C1)
SELECT object_id FROM sys.all_columns


SELECT C1, COUNT(1) FROM @TB1 AS T1
GROUP BY C1

SELECT * FROM @TB1 AS T1
ORDER BY NEWID() DESC


DBCC TRACEOFF(2453)
  只对一个语句的重编译节省了大量的资源。

  其次,“WITH RECOMPILE”发生在编译时,而“OPTION (RECOMPILE)” 发生在运行时。整个例子中运行时执行这个语句时,暂停执行,重新编译该查询,生成新的执行计划。而其他部分则使用计划缓存。运行时编译带来的好处就是使优化器能预先知道所有的运行时值,甚至不需要参数嗅探。优化器知道参数的值,局部变量和环境设置,然后使用这些数据编译查询。多数情况下,运行时编译生成的计划要比编译时生成的计划好很多。

澳门新萄京官方网站 26

因此,你应该考虑使用“OPTION (RECOMPILE)” 而不是“WITH RECOMPILE”,因为它使用了更少的资源长生了更好的计划。但是要注意这种方式依然是十分占用CPU的。

可以发现,即使没有JOIN操作,开启跟踪2453会影响表变量的预估行数。

Solution #4 – OPTIMIZE FOR

澳门新萄京官方网站:积累进度参数字传送递的熏陶,处理非均匀数据遍及。仔细分析下,对于简单的SELECT FROM操作,无表变量中数据量的数量多少,都只能进行聚集索引扫描,而对于后面两个查询,无论是GROUP BY还是ORDER BY,表变量中数据量不同,对数据进行排序所采用的算法会发生变化,因此推测:在开启跟踪2453条件下,如果表变量的数据量大小对最终生成的执行计划有影响,那么会对表变量返回一个“准确”的预估行数。

    另一查询选项“OPTIMIZE FOR”也可以解决参数嗅探问题。该选项指示优化器使用特定的一套参数而不是实际的参数来编译查询。实际上就是重写参数嗅探。注意,这个选项只有当查询必须被重编译的时候才能被使用。选项本身不会引起重编译。

 

ALTER PROCEDURE
    Marketing.usp_CustomersByCountry
(
    @Country AS NCHAR(2)
)
AS

SELECT
    Id ,
    Name ,
    LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = @Country
OPTION
    (OPTIMIZE FOR (@Country = N'US'));
GO

测试5,重编译问题

  

由于使用OPTION(RECOMPILE)会导致存储过程重编译,而频繁地重编译会大量消耗CPU资源,那么使用跟踪标志会导致重编译问题么?

     还记得“Sales. Orders”表的情形吗?99%的执行会使用“Pending Approval”作为参数。而不是使用sys.sp_recompile(重编译),综上所述,如果希望下一次执行已然使用这个参数,俺么使用OPTIMIZE FOR 将会是此种情况的更佳选择,并且指示优化器无论实际参数在下一次执行时是什么都使用该参数(如上例中的US)。

本人未对该问题进行测试,从参考文章中了解到,跟踪标志不会导致存储过程频繁地发生重编译的问题,但如果随存储过程的传入参数不同,其内部使用的表变量的数据量存在严重变化的情况下,会引发存储过程重编译。打个碧玉,同样是出门,10公里内打车,50公里内打地铁,10000公里得打飞机,不同的距离导致不同的出行方式,不同的参数导致不同的执行计划。

     通过使用“OPTIMIZE FOR UNKNOWN”可以禁止参数嗅探。这个选项指示优化器将参数设为位置,实际上就是禁用了参数嗅探。如果存储过程有多个参数,那么你能分别对每一个参数进行选项处理(禁用)。

虽然此跟踪标志没有引入频繁的重编译问题,同样也引入了新的问题,即不同参数需要使用不同执行计划,却因为没有重编译导致重用旧的执行计划引发性能问题。

ALTER PROCEDURE
    Marketing.usp_CustomersByCountry
(   
    @Country AS NCHAR(2)
)
AS

SELECT
    Id ,
    Name ,
    LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = @Country
OPTION
    (OPTIMIZE FOR (@Country UNKNOWN));
GO

查询提示OPTION(RECOMPILE)虽然导致每次都重编译,但却又能很好地防止“参数变化导致表变量的数据量发生变化最终生成不高效执行计划”的问题。

  

 

Solution #5 – 最佳方案

澳门新萄京官方网站:积累进度参数字传送递的熏陶,处理非均匀数据遍及。测试6,自定义表类型

    到目前为止你可能注意到了,有两个我们希望达到有互相冲突的目的。一个是为每个执行创建最优的计划,另一个是最小化编译避免资源的浪费。“WITH RECOMPILE”方式完成了第一个目的,但是它需要每个执行重新编译。另一方面,sys.sp_recompile方式只重新编译了一次存储过程,但是不会为每个执行产生最佳计划。

既然表变量可以受影响于此跟踪标志,那么用户自定义的表类型呢?答案是自定义表类型同样可以受该跟踪标志的影响。

    那么最佳的解决方案就是平衡这两种冲突的目标。这种平衡思想就是分离参数值到不同的组,每组有不同的优化计划,并且生成不同的优化计划。每个计划只被编译一次,然后从这点来说每个执行都会得到最佳计划,因为计划基于参数值产生,所以合理的分组导致生成对应组的计划。

 

    听起来像魔法吗?让我们看一下这个戏法如何实现…

##==================================================##

    首先我们需要把值分成不同的组。这是关键部分,并且有许多方式去分组。这里我将使用国家作为参数,将普通国家和非普通国家分成两组。如果该国家的行数占到了表行数的1%以上我将其定义为普通国家。假定SQLServer已经定义了普通国家,通过统计国家列字段。SQLServer 通常使用普通的参数值作为图形统计的条目。

总结:

    因此我们将普通国家插入到“CommonCountries”表的“Country”,然后删除非普通国家…

由于跟踪标志2453的存在,使得我们可以在不修改任何代码的情况下,使查询获得类似于增加查询提示OPTION(RECOMPILE)的效果,同时还避免OPTION(RECOMPILE)带来的重复编译问题,而且跟踪标志可以在会话级别和实例级别进行设置,通过设置SQL Server的启动参数,可以轻松解决表变量的预估行数为1的问题,看似很美好,但是事物都有两面性,实例级别的跟踪标志需要进行严格的测试,同时需要验证不同参数对表变量的数据量的影响。

CREATE TABLE
    Marketing.CommonCountries
(
    RANGE_HI_KEY        NCHAR(2)    NOT NULL ,
    RANGE_ROWS          INT         NOT NULL ,
    EQ_ROWS             INT         NOT NULL ,
    DISTINCT_RANGE_ROWS INT         NOT NULL ,
    AVG_RANGE_ROWS      FLOAT       NOT NULL ,

    CONSTRAINT
        pk_CommonCountries_c_RANGEHIKEY
    PRIMARY KEY CLUSTERED
        (RANGE_HI_KEY ASC)
);
GO


INSERT INTO
    Marketing.CommonCountries
(
    RANGE_HI_KEY ,
    RANGE_ROWS ,
    EQ_ROWS ,
    DISTINCT_RANGE_ROWS ,
    AVG_RANGE_ROWS
)
EXECUTE ('DBCC SHOW_STATISTICS (N''Marketing.Customers'' , ix_Customers_nc_nu_Country) WITH HISTOGRAM');
GO


DECLARE
    @RowCount AS INT;

SELECT
    @RowCount = COUNT (*)
FROM
    Marketing.Customers;

DELETE FROM
    Marketing.CommonCountries
WHERE
    EQ_ROWS < @RowCount * 0.01;
GO

就个人而言,了解各种跟踪标志有利于我们进一步了解SQL Server本质,但在大多数情况下,我们要尽可能地避免使用跟踪标志,能用常规办法解决问题问题最好还是使用常规办法!

  

谁说的“能动手的话尽量别动嘴”!!!

表的查询内容如下:

##==================================================##

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
CN 0 29956 0 1
UK 0 28888 0 1
US 0 40101 0 1

参考连接:

 

     这样清楚极了。这三个是普通国家的例子。当然这是比较简单的例子,实际环境可能要复杂的多,有时甚至需要提出一些算法来区分普通和不普通的值。可以使用我这种统计的结果。也可以使用某种监视机制来追踪使用结果和计划。又或者需要开发一套自己的统计机制。无论如何,多数时候是需要开发一个算法来区分值为不同的组。

    那么我们可以用这个国家的分组分别生成优化计划。这种方式需要创建不同存储过程,而存储过程除了名字外几乎都是一样的。

##==================================================##

    在实例中,我创建“Marketing.usp_CustomersByCountry_Common”和“Marketing.usp_CustomersByCountry_Uncommon”两个存储过程。如下:

澳门新萄京官方网站 27

CREATE PROCEDURE
    Marketing.usp_CustomersByCountry_Common
(
    @Country AS NCHAR(2)
)
AS

SELECT
    Id ,
    Name ,
    LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = @Country;
GO


CREATE PROCEDURE
    Marketing.usp_CustomersByCountry_Uncommon
(
    @Country AS NCHAR(2)
)
AS

SELECT
    Id ,
    Name ,
    LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = @Country;
GO

 

  

 

    接下来我们修改一个原始的存储过程,这个存储过程变成一个路由。它的工作就是价差参数值并根据值的分组确定执行哪一个对应的存储过程。

ALTER PROCEDURE
    Marketing.usp_CustomersByCountry
(
    @Country AS NCHAR(2)
)
AS

IF
    EXISTS
        (
            SELECT
                NULL
            FROM
                Marketing.CommonCountries
            WHERE
                RANGE_HI_KEY = @Country
        )
BEGIN

    EXECUTE Marketing.usp_CustomersByCountry_Common
        @Country = @Country;

END
ELSE
BEGIN

    EXECUTE Marketing.usp_CustomersByCountry_Uncommon
        @Country = @Country;

END;
GO

  

 

这是一个漂亮的解决方案:

    首次普通国家作为参数使用,路由存储过程调用普通存储过程。一旦第一次被执行以后,计划被生产在缓存中。多亏了参数嗅探,从此以后,只要普通国家的存储过程被执行都会使用这个计划。然后,同样不常用国家也是如此…

    因此,我们为每个参数值都提供了优秀的计划,并且每个计划只被编译一次。通常来书只有2到3组值,因此最多2到3个编译。这就是魔法的实质。

    缺点:

   当然这只是一个理想的方式,需要注意的是该方案的维护成本。一旦数据发生了改变,算法必须去维护修改来再次适应。如上面的例子,需要每一段时间去重新创建普通国家的表。

总结:

    参数嗅探能是好的也可以是坏的事情。既然在SQLServer中默认使用,只要它是好的,我们就应该使用。我们的目的是根据不同场景识别参数嗅探,然后应用文中提到的方式来解决不好的参数嗅探问题。

    今后我会选择一些具体生产问题来展示一下各种参数嗅探以及相应的衍生问题的处理方案。

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站:积累进度参数字传送递的

关键词: