澳门新浦京娱乐场网站-www.146.net-新浦京娱乐场官网
做最好的网站

参数嗅探,Server临界点游戏

在我的博客上,以前我经常谈到SQL Serverl里的书签查找,还有它们带来的很多问题。在今天的文章里,我想从性能角度进一步谈下书签查找,还有它们如何拉低你整个SQL Server性能。

当我们进行SQL Server问题处理的时候,有时候会发现一个很有意思的现象:SQL Server完全忽略现有定义好的非聚集索引,直接使用表扫描来获取数据。我们来看看下面的表和索引定义:

在今天的文章里,我想给你展示下,当你想对特定查询创建索引设计时,如何把你的工作和思考过程传达给查询优化器。下面就一起来探讨一下吧!

这个问题会在参数话的SQL语句(例如存储过程)与SQL Server里的计划缓存机制结合的时候会出现。这个文章分为2个部分,第1部分会介绍下参数嗅探(Parameter Sniffing)的概况,第2部分我们介绍下如何解决这个问题。

书签查找——反复循环

如果你的非聚集索引不是个覆盖非聚集索引,SQL Server的查询优化器会引入书签查找。对于从非聚集索引你返回的每一行,SQL Server需要在聚集索引里或堆表里进行额外的查找操作。

例如当你的的聚集索引包含3层,为了返回必要的信息,对于每一行,你需要3页额外的读取。因此,查询优化器再执行计划里选择书签查找操作,仅在有意义的时候发生——基于你查询的选择度。下图展示了有书签查找操作的执行计划。

图片 1

通常人们不会太关注书签查找,因为它们只执行几次。如果你的查询选择度太低,查询优化器会用聚集索引扫描或表扫描运算符直接扫描整个表。但只在SQL Server重用缓存的执行计划,这个计划是有多次不同运行值,包含书签查找的(基于最初提供的输入值),因此这个情况很容易发生,书签查找反复执行。

为了演示这个性能问题,接下来的查询我指定查询优化器使用特定的非聚集索引。查询本身返回80000行,因为对于每个查询执行,SQL Server需要进行书签查找80000次——反复执行。

CREATE PROCEDURE RetrieveData
AS
    SELECT * FROM Table1 WITH (INDEX(idxTable1_Column2))
    WHERE Column3 = 2
GO

下图展示了查询执行后的实际执行计划。

图片 2

执行计划看起来非常恐怖(查询优化器甚至启用了并行计划!),因为书签查找运算符这里执行了80000次,查询本身产生了超过165000个逻辑读!(逻辑读个数可以从STATISTIC IO里获取)。

图片 3

接下来向你展示下,当你有很多并行用户执行这个糟糕查询时,SQL Server会发生什么。我会使用ostress.exe(RML工具的一部分)来模拟100个并行用户的查询。

ostress.exe -Q”EXEC BookmarkLookupsPerformance.dbo.RetrieveData” -n100 -q

在我的测试系统上花费了近15秒来完成100个并行查询。在此期间,CPU占用很高,因为SQL Server需要嵌套循环运算符来进行书签查找操作。嵌套循环操作当然很占CPU资源。

现在让我们修改索引设计,为这个查询创建覆盖非聚集索引。有了非聚集索引,查询优化器不需要再执行计划里进行书签查找。一个非聚集索引查找就可以返回同样的结果:

CREATE NONCLUSTERED INDEX idxTable1_Column2 ON Table1(Column3)
INCLUDE (Column2)
WITH (DROP_EXISTING = ON)
GO

这次当我们再次用ostress.exe执行同个查询,我们看到每个查询在5秒内完成。和我们刚才看到的15秒有很大的区别。这就是覆盖非聚集索引的威力:在我们查询里气门请求的数据都可以在非聚集索引里直接找到,因此书签查找就可以避免。

 1 CREATE TABLE Customers
 2 (
 3    CustomerID INT NOT NULL,
 4    CustomerName CHAR(100) NOT NULL,
 5    CustomerAddress CHAR(100) NOT NULL,
 6    Comments CHAR(185) NOT NULL,
 7    Value INT NOT NULL
 8 )
 9 GO
10  
11 CREATE UNIQUE CLUSTERED INDEX idx_Customers ON Customers(CustomerID)
12 GO
13  
14 CREATE UNIQUE NONCLUSTERED INDEX idx_Test ON Customers(Value)
15 GO

有问题的查询 我们来看下列查询:

什么是参数嗅探(Parameter Sniffing)

在SQL Server里当你执行参数话的SQL查询时,查询优化器会基于第一个提供的参数值编译执行计划。然后生成的执行计划在计划缓存里缓存作为后期的重用。这就是说SQL Server后续会直接重用这个计划,而不管每次你提供的不同参数值。我们需要识别2类参数值:

  • 参数编译值(Compile time values)
  • 参数运行值(Runtime values)

参数编译值是用于查询优化器生成物理执行计划的值。参数运行值是提供给执行计划运行的值。对于第一次执行这些值是一致的,但接下来的执行,这些值就很可能不同了。这就会带来严重的性能问题,因为执行计划只为编译值而优化的,不是为你接下来提供的不同运行值而优化。

如果你在第一次查询执行的时候提供了一个特定值,然后查询优化器选择了非聚集索引查找和书签查找运算符从你表里来获取所有查询列。这样的执行计划只对特定值有意义,非特定值的话,你的逻辑读数就会很高,SQL Server会选择全表扫描,忽略定义的非聚集索引。SQL Server选择这2个计划的决定点就是所谓的参数嗅探,Server临界点游戏。临界点(Tipping Point)

如果书签查找的计划被缓存,SQL Server就不会理会输入值,盲目重用缓存的计划。这个情况下SQL Server的保护机制就失效了,只从计划缓存里执行缓存的计划。作为副作用,你的IO成本(逻辑都)就会爆表,查询的性能就会非常糟糕。我们来演示下这个情况,下面的脚本会创建一个简单的表,在表的第2列有不平均的数据分布(就第1条值是1,剩下的1499条值都是2)。

 1 -- Create a test table
 2 CREATE TABLE Table1
 3 (
 4     Column1 INT IDENTITY,
 5     Column2 INT
 6 )
 7 GO
 8 
 9 CREATE NONCLUSTERED INDEX idx_Test ON Table1(Column2)
10 
11 -- Insert 1500 records into Table1
12 INSERT INTO Table1 (Column2) VALUES (1)
13 
14 SELECT TOP 1499 IDENTITY(INT, 1, 1) AS n INTO #Nums
15 FROM
16 master.dbo.syscolumns sc1
17 
18 INSERT INTO Table1 (Column2)
19 SELECT 2 FROM #nums
20 DROP TABLE #nums
21 GO

基于这个不平均的数据分布和临界点,对于同个逻辑查询会有2个不同的执行计划,点击工具栏的图片 4显示包含实际的执行计划:

1 SELECT * FROM dbo.Table1 WHERE Column2=1
2 SELECT * FROM dbo.Table1 WHERE Column2=2

图片 5

现在当你创建一个存储过程时,查询优化器会根据第一次提供的参数值生成执行计划,然后在接下来的执行中就会盲目重用了。

1 -- Create a new stored procedure for data retrieval
2 CREATE PROCEDURE RetrieveData
3 (
4     @Col2Value INT
5 )
6 AS
7     SELECT * FROM Table1
8     WHERE Column2 = @Col2Value
9 GO

 

1 SET STATISTICS IO ON 
2 EXEC dbo.RetrieveData @Col2Value = 1 -- int
3 EXEC dbo.RetrieveData @Col2Value = 2 -- int

图片 6

图片 7

现在当你用1值运行存储过程时,只返回1条记录,查询优化器在执行计划里选择书签查找。查询只产生3个逻辑读。但是当你用2值运行存储过程时,缓存的计划被重用,书签查找反复执行1499次。每条记录上都执行!查询现在产生了1505个逻辑读。这和刚才的执行完全不同。当你看查看2值里执行计划里,SELECT运算符的属性时,在参数列表里你可以看到:
图片 8

如你所见它们是不一样的,参数编译值是1,参数运行值是2。这就是说在你面前的执行都是基于参数值1而优化的,但实际上你传给存储过程的参数值是2。这就是SQL Server里的参数嗅探(Parameter Sniffing)问题。

小结

在这个文章里我向你展示了不好的书签查找会伤及性能。因此,对于重要的查询快速完成查询非常重要——而使用并行的书签查找的执行计划并不是好的选择。这里覆盖非聚集索引可以帮到你。下次设计索引时可以考虑下这个方法。

感谢关注!

我们往表里插入80000条记录:

 DECLARE @i INT = 999
 SELECT
   SalesOrderID, 
   SalesOrderDetailID,
   CarrierTrackingNumber, 
   OrderQty, 
   LineTotal
 FROM Sales.SalesOrderDetail
 WHERE ProductID < @i
 ORDER BY CarrierTrackingNumber
 GO

小结

 如你所见,在SQL Server里很容易碰到这个问题。每次你使用参数话的SQL查询(像在存储过程里),当表数据分布不平均,提供的非聚集索引没有覆盖到查询列时,你就会碰到这个问题。这里我们只介绍了这个问题,下篇文章我会向你展示如何处理这个问题,即SQL Server向你提供了哪些方案来解决这个问题。

原文链接:

https://www.sqlpassion.at/archive/2017/03/13/the-performance-penalty-of-bookmark-lookups-in-sql-server/

 1 DECLARE @i INT = 1
 2 WHILE (@i <= 80000)
 3 BEGIN
 4 INSERT INTO Customers VALUES
 5 (
 6    @i,
 7    'CustomerName'   CAST(@i AS CHAR),
 8    'CustomerAddress'   CAST(@i AS CHAR),
 9    'Comments'   CAST(@i AS CHAR),
10    @i
11 )
12 
13 SET @i  = 1
14 END
15 GO

如你所见,这里用了一个本地变量与一个不等于谓语来从Sales.SalesOrderDetail表来获取一些记录。当你执行那个查询,看它的执行计划时,你会发现它有一些严重的问题:

参考文章:

https://www.sqlpassion.at/archive/2014/10/20/parameter-sniffing-part-1/

执行下列查询,就会发现SQL Server完全忽略非聚集索引,而使用表扫描来获取数据,点击工具栏的图片 9显示包含实际的执行计划:

图片 10

1 SELECT * FROM Customers
2 WHERE Value < 1267
3 GO
  • SQL Server需要扫描Sales.SalesOrderDetail表的整个非聚集索引,因为没有支持的非聚集索引。对这个扫描,查询需要1382个逻辑读,运行时间近800毫秒。
  • 查询优化器在查询计划里引入了筛选器(Filter)运算符,它进行逐行比较用来检查符合的行(ProductID < @i)
  • 因为ORDER BY CarrierTrackingNumber,在执行计划里一个排序(Sort)运算符被引入。
  • 排序运算符蔓延到了TempDb,因为不正确的基数计算(Cardinality Estimation)。用了带了本地变量与不等于谓语的组合,SQL Server从表的基数硬码估计30%的行。在我们的情况里估计行数是36395(121317 * 30%)。实际上查询返回120621行,这意味这排序(Sort)运算符必须蔓延到TempDb,因为请求的内存授予太小了。

 图片 11

现在我问你——你能改善这个查询么?你的建议是什么?休息下,想个几分钟。不修改查询本身,你如何改善这个查询?

而当我们把查询条件修改为1266时,我们惊奇的发现,SQL Server又重新使用非聚集索引来获取数据了:

我们来调试查询!
当然,我们要做索引相关的调整来改善。没有支持的非聚集索引,那只能是查询优化器唯一可以使用计划来运行我们的查询。但对这个指定查询,什么是好的非聚集索引呢?一般来说,我通过看搜索谓语来考虑可能的非聚集速印。在我们的例子里,搜索谓语如下:

1 SELECT * FROM Customers
2 WHERE Value < 1266
3 GO

WHERE ProductID < @i

图片 12

我们请求在ProductID列过滤的行。因此我们想在那个列创建支持的非聚集索引。我们建立索引:

很多人估计会很兴奋,因为他们认为它们找到了SQL Server里的一个BUG,用指定索引来查询就可以避免这个问题:

CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID)

 GO
1 SELECT * FROM Customers
2 WITH (INDEX(idx_Test))
3 WHERE Value < 1267
4 GO

在非聚集索引创建后,我们需要验证下改变,因此我们再次执行刚才的查询代码。结果如何捏?查询优化器并没有使用我们刚创建的非聚集索引!我们在搜索谓语上创建了支持的非聚集索引,查询优化器没有引用它?通常人们对此就无辙了。其实我们可以提示查询优化器来使用非聚集索引,来更好的理解“为什么”查询优化器没有自动选择索引:

 图片 13

 DECLARE @i INT = 999

 SELECT
  SalesOrderID, 
  SalesOrderDetailID,
  CarrierTrackingNumber, 
  OrderQty, 
  LineTotal
FROM Sales.SalesOrderDetail WITH (INDEX(idx_Test))
WHERE ProductID < @i
 ORDER BY CarrierTrackingNumber
 GO

从执行计划里我们可以看到,SQL Server需要进行书签查找,因为针对这个查询,我们并没有定义对应的覆盖非聚集索引。当你进行全表聚集索引扫描时,SQL Server这里帮了你一个大忙:用书签查找获取每条记录成本太高,因此SQL Server使用了全表扫描,这样就只需要较少的IO和CPU占用,因为书签查找都要通过内循环运算符完成。

当你现在看执行计划时,你会看到下列的野性——一个并行计划:

在SQL Server里,这个行为被称为临界点(Tipping Point) 。我们再详细解释下这个概念。简单来说,临界点定义了SQL Server是使用书签查找还是全表/索引扫描。这也意味着临界点只与非覆盖非聚集索引有关。一个对指定查询扮演覆盖非聚集索引的角色的话,不会有临界点,也就不会有刚才介绍的问题。

图片 14

在有书签查找的查询时,SQL Server使用书签查找还是全表扫描取决于获取的页数。是的,你没看错!获取的页数决定了书签查找是好的还是不好的!这与查询返回的记录条数完全无关,唯一有关就是页数。临界点出现在查询需要读取的24%-33%页数之间。

图片 15

在这范围之前,查询优化器会选择书签查找,在这范围之后,查询优化器会选择全表扫描(在全表扫描运算符里会有谓语定义)。

查询花费了370109个逻辑读!运行时间基本和刚才的一样。这里到底发生了什么?当你仔细看执行计划,你会发现查询优化器引入了书签查找,因为刚才创建的非聚集索引,对于查询来说,不是一个覆盖非聚集索引。查询越过了所谓的临界点(Tipping Point),因为我们用当前的搜索谓语来获得几乎所有行。因此用非聚集索引和书签查找来组合没有意义。

图片 16

不去想为什么查询优化器不选择刚才创建的非聚集索引,我们已经把自己的思路表达给了查询优化器本身,通过查询提示进行了询问了查询优化器,为什么非聚集索引没被自动选择。如我刚开始说的:我不想考虑太多。

这也意味着你记录的大小决定了临界点的位置。在查询越过临界点进行全表扫描时,小记录,你就只能从表获取小数量的记录,大记录,你就能够获得大量的记录。下图就是对临界点的一个图示。

使用非聚集索引解决这个问题,在非聚集索引的叶子层,我们必须对从SELECT列表的请求的额外列进行包含。你可以再次看下书签查找来看下在叶子层哪些列当前丢失:

图片 17

  • CarrierTrackingNumber
  • OrderQty
  • UnitPrice
  • UnitDiscountPrice

在我们刚才的例子里,每条记录是400 bytes长,因此8kb的页面里可以保存20条记录,当我们进行全表扫描时,SQL Server会产生4016个逻辑读。

我们重建那个非聚集索引:

1 SET STATISTICS IO ON
2 SELECT * FROM Customers
CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID)
INCLUDE (CarrierTrackingNumber, OrderQty, UnitPrice, UnitPriceDiscount)
 WITH
(
 DROP_EXISTING = ON
 )
GO

图片 18

我们已经做出了另1个改变,因此我们可以重新运行了查询来验证下。但是这次我们不加查询提示,因为现在查询优化器会自动选择非聚集索引。结果如何捏?当你看执行计划时,索引现在已被选择。

 刚才的例子里,我们的表在聚集索引的叶子层有4000个数据页,也就是说临界点在1000与1333页之间的某个地方。在优化器选择进行全表扫描前,你只能读取0.25%-0.67%(1000* 20/80000,1333*20/80000)的表数据。

图片 19

下面这个查询会用到书签查找:

图片 20

1 SET STATISTICS IO ON
2 SELECT * FROM Customers
3 WHERE Value < 1266
4 GO

SQL Server现在在非聚集索引上进行了查找操作,但在执行计划里我们还有排序(Sort)运算符。因为基数计算30%的硬编码,排序(Sort)还是要蔓延到TempDb。偶滴神!我们的逻辑读已经降到了757,但运行时间还是近800毫秒。你现在应该怎么做?

图片 21

现在我们可以尝试在非聚集索引的导航结构直接包含CarrierTrackingNumber列。这是SQL Server进行排序运算符的列。当我们在非聚集索引直接加了这列(作为主键),我们就物理排序了那列,因此排序(Sort)运算符应该会消失。作为积极的副作用,也不会蔓延到TempDb。在执行计划里,现在也没有运算符关心错误的基数计算。因此我们尝试那个假设,再次重建非聚集索引:

可以看到,这个查询需要3887个IO操作,而全表扫描只需要4016个IO,这里的书签查找成本(IO和CPU消耗)越来越昂贵了。超过了这个点,SQL Server就决定不使用书签查找,改用全表扫描了。

 CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(CarrierTrackingNumber, ProductID)
INCLUDE (OrderQty, UnitPrice, UnitPriceDiscount)
 WITH
(
   DROP_EXISTING = ON
 )
GO
1 SET STATISTICS IO ON
2 SELECT * FROM Customers
3 WHERE Value < 1267
4 GO

从索引定义可以看到,现在我们已经对CarrierTrackingNumber和ProductID列的数据物理预排序。当你再次重新执行查询,在你查看执行计划时,你会看到排序(Sort)运算符已经消失,SQL Server扫描了非聚集索引的整个叶子层(使用剩余谓语(residual predicate)作为搜索谓语)。

图片 22

图片 23

我们一起执行看下:

图片 24

1 SELECT * FROM Customers
2 WHERE Value < 1266
3 GO
4 SELECT * FROM Customers
5 WHERE Value < 1267
6 GO

这个执行计划并不坏!我们只需要763个逻辑读,现在的运行时间已经降至600毫秒。和刚才的相比已经有25%的改善!但是:查询优化器建议我们一个更好的非聚集索引,通过缺少索引建议(Missing Index Recommendations)!暂且相信下,我们创建建议的非聚集索引:

图片 25

CREATE NONCLUSTERED INDEX [SQL Server doesn't care about names, why I should care about names?]
ON [Sales].[SalesOrderDetail] ([ProductID])
INCLUDE ([SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber],[OrderQty],[LineTotal])
GO

2个近乎一样的查询,却有完全不同的执行计划,这在性能调优的时候是个大问题,因为你的执行计划失去了稳定性。

当你现在重新执行最初的查询,你会发现令人惊讶的事情:查询优化器使用“我们”刚才创建的非聚集索引,缺少索引建议已经消失!

针对输入参数的不同,却有完全不同的计划!这也是书签查找的重大缺陷!用了书签查找,你就不能获得稳定的执行计划。如果这个执行计划被缓存(或你的统计信息过期了),你用它获取大量数据的时候就会有性能上的问题,因为低效的书签查找被SQL Server盲目重用了!这会造成原先只要几秒的查询,要花好几分钟才能完成!

图片 26

我们说过,临界点取决于查询的读取页数。我们对刚才的表做下一点改动,每条记录40 bytes长,8k的页里能存储200条的记录,同样我们也插入80000条记录(记得关掉IO统计:SET STATISTICS IO OFF和执行计划显示,否则电脑蜗牛了-_-)。

你刚刚创建了SQL Server从不使用的索引——除了INSERT,UPDATE和DELETE语句,SQL Server都要去维护你的非聚集索引。对于你的数据库,你刚创建了“单纯”浪费空间的索引。当另一方面,你已经通过消除丢失索引建议,满足了查询优化器。但这不是目的:目的是创建会被再次使用的索引。

 1 CREATE TABLE Customers3
 2 (
 3    CustomerID INT NOT NULL,
 4    CustomerName CHAR(10) NOT NULL,
 5    CustomerAddress CHAR(10) NOT NULL,
 6    Comments CHAR(5) NOT NULL,
 7    Value INT NOT NULL
 8 )
 9 GO
10 
11 CREATE UNIQUE CLUSTERED INDEX idx_Customers ON Customers3(CustomerID)
12 GO
13  
14 CREATE UNIQUE NONCLUSTERED INDEX idx_Test ON Customers3(Value)
15 GO
16 
17 
18 DECLARE @i INT = 1
19 WHILE (@i <= 80000)
20 BEGIN
21 INSERT INTO Customers3 VALUES
22 (
23    @i,
24    'C2',
25    'C3',    
26    'C4',
27    @i
28 )
29 
30 SET @i  = 1
31 END
32 GO

结论:永不相信查询优化器!

这样的话,我们需要400页来存储这些数据。我们来看下临界点位置:临界点在100-133页读取的位置,也就是说通过非聚集索引,你只能读取0.125%-0.167%的数据,对于80000条数据的表来说,这几乎就是没数据你的非聚集索引毫无用处!

小结

我们来看下临界点的2个不同查询,这里我们可以打开执行计划显示。

今天的文章有点争议性,但我想你向你展示下,但你在创建索引时,查询优化器如何帮助你,还有查询优化器如何愚弄你。因此做出小的调整,就立即运行你的查询,验证改变非常重要。

 1 SET STATISTICS IO ON
 2 -- 书签查找会产生332个逻辑读。
 3 SELECT * FROM Customers3
 4 WHERE Value < 157
 5 GO
 6 
 7 -- 聚集索引扫描会产生419个逻辑读。
 8 -- The query produces 419 I/Os.
 9 SELECT * FROM Customers3
10 WHERE Value < 158
11 GO

以上就是本文的全部内容,希望对大家的学习有所帮助。

图片 27

您可能感兴趣的文章:

  • mysql 性能的检查和调优方法
  • Sql server2005 优化查询速度50个方法小结
  • 一次SQL调优数据库性能问题后的过程(300W)
  • SqlServer 执行计划及Sql查询优化初探
  • MySQL慢查询查找和调优测试
  • sqlserver性能调优经验总结
  • Mysql优化调优中两个重要参数table_cache和key_buffer
  • 10个MySQL性能调优的方法

图片 28

我们来看第2个查询,我们只选择80000条记录的157条,我们只选择了很少的数据,但是SQL Server在这里就非常聪明,完全忽略你的的非聚集索引,使用表扫描来获取数据。但对于整个查询来说,这个非聚集索引设计并不完美,因为不是覆盖的非聚集索引,如果有人用指定索引来查找数据,就会非常恐怖:

1 SELECT * FROM Customers3 WITH(INDEX(idx_Test))
2 WHERE Value < 80001
3 GO

图片 29

这个查询产生了165120个逻辑读,把聚集索引全表扫描需要的IO数直接秒杀!从这个例子我们可以看出,临界点是SQL Server里的性能保障,它阻止着使用书签查找,造成占用昂贵资源的查询发生。但这些和记录数完全无关。这2个例子里的表记录数都是80000。我们只修改了表记录的大小,因此我们就改变了表的大小,最后临界点也跟着改变,SQL Server就会忽略我们的非聚集索引。

寓意:非聚集索引,不是覆盖非聚集索引的话,在SQL Server里是非常,非常,非常,非常有选择性的用例!下次当你碰到这个情况的时候,想下你要怎么处理这个问题! 

参考文章:

https://www.sqlpassion.at/archive/2013/06/12/sql-server-tipping-games-why-non-clustered-indexes-are-just-ignored/

本文由澳门新浦京娱乐场网站发布于数据库,转载请注明出处:参数嗅探,Server临界点游戏