主页 / dba / 问题 / 64101
Accepted
crokusek
Asked: 2014-04-29 10:28:40 +0800 CST

如何改进新插入数据的连接中 1 行的行估计

  • 772

表的 CacheId 列存在自定义统计信息。经过一夜的统计数据更新后：

Statistics for INDEX 'ST_TableName_CacheId'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ST_TableName_CacheId Apr 26 2014  2:04AM             121482                          121482                          6                               0                               4                               NO                                                              121482                          

All Density                     Average Length                  Columns                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0.1666667                       4                               CacheId                         

Histogram Steps                 
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
39968                           0                               20247                           0                               1                               
40058                           0                               20247                           0                               1                               
40062                           0                               20247                           0                               1                               
40066                           0                               20247                           0                               1                               
40069                           0                               20247                           0                               1                               
41033                           0                               20247                           0                               1

1) 针对此表中现有数据集的连接性能，其中 CacheId = 41033 表现良好，估计值良好（23622 与 20247 的实际值）。

2) 然后使用 CacheId = 41273 of 20247 行执行插入。

3) 然后，针对这个新插入的数据集的连接显示对 1 行的估计不佳，导致计划错误。

4) 手动更新统计信息（最初使用全扫描）显示一个新的直方图：

Statistics for INDEX 'ST_TableName_CacheId'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ST_TableName_CacheId Apr 28 2014 10:41AM             141729                          141729                          7                               0                               4                               NO                                                              141729                          

All Density                     Average Length                  Columns                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0.1428571                       4                               CacheId                         

Histogram Steps                 
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
39968                           0                               20247                           0                               1                               
40058                           0                               20247                           0                               1                               
40062                           0                               20247                           0                               1                               
40066                           0                               20247                           0                               1                               
40069                           0                               20247                           0                               1                               
41033                           0                               20247                           0                               1                               
41274                           0                               20247                           0                               1

5) 为 CacheId = 41274 再次运行相同的连接查询显示完美的估计 (20247) 和良好的性能。

Q1）为什么在数学上原始估计如此糟糕？我的意思是 CacheId 是稀疏的，但不是 20000:1 的比率。

Q2）随着 cacheId 数量的增加，您是否期望新插入数据的估计值自然提高？

Q3）是否有任何方法（gulp，技巧或其他方式）来改进估计（或使其不太确定 1 行）而不必在每次插入新数据集时更新统计信息（例如在更大的 CacheId = 999999）。

以下是表中所有 CacheId 的真实行数：

CacheId Rows
39968   20247
40058   20247
40062   20247
40066   20247
40069   20247
41033   20247
41274   20247

[我认为不需要 QP 来回答这个问题，并且需要一些工作来清理它们。如果需要，我可以回答具体问题！]

sql-server statistics

2 个回答

  1. Best Answer

    Q1）为什么在数学上原始估计如此糟糕？我的意思是 CacheId 是稀疏的，但不是 20000:1 的比率。

    这是触发自动更新SQL Server 中的统计信息维护功能 (autostats)的规则：

    上面的算法可以用表格的形式来概括：

    表类型 | 空置条件 | 空时的阈值|非空时的阈值

    永久 | < 500 行 | 更改次数 >= 500 | # of Changes >= 500 + (20% of Cardinality)

    即使 KB 指向 2000 年，到 2012 年仍然如此。

    运行这个场景，自己看看。

    步骤1

    SET STATISTICS IO OFF;
GO
SET NOCOUNT ON;
GO
-- make sure the Include Actual Execution Plan is off!!!
IF OBJECT_ID('IDs') IS NOT NULL
DROP TABLE dbo.IDs;

CREATE TABLE IDs
(
ID tinyint NOT NULL
)

INSERT INTO IDs
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7;

IF OBJECT_ID('TestStats') IS NOT NULL
DROP TABLE dbo.TestStats;

CREATE TABLE dbo.TestStats
(
 ID tinyint NOT NULL,
 Col1 int NOT NULL,
 CONSTRAINT PK_TestStats PRIMARY KEY CLUSTERED (ID, col1)
);

DECLARE @id int = 1
DECLARE @i int = 1

WHILE @id <= 6
BEGIN
 SET @i = 1

WHILE @i <= 20247
BEGIN
    INSERT INTO dbo.TestStats VALUES(@id,@i);

    SET @i = @i + 1
END

SET @id = @id + 1
END

-- so far so good!
SELECT ID, COUNT(*) AS RowCnt FROM dbo.TestStats GROUP BY ID;

DBCC SHOW_STATISTICS('TestStats',PK_TestStats) WITH HISTOGRAM;

    现在我们有一个 ID 为 1 到 6 的表，每个 ID 有 20247 行。到目前为止的统计数据看起来不错！

    第2步

    -- now insert another ID = 7 with 20247 rows
DECLARE @i int = 1;

WHILE @i <= 20247
BEGIN
  INSERT INTO dbo.TestStats VALUES(7,@i);

  SET @i = @i + 1
END

-- see the problem with the histogram?
SELECT ID, COUNT(*) FROM dbo.TestStats GROUP BY ID;

DBCC SHOW_STATISTICS('TestStats',PK_TestStats) WITH HISTOGRAM;

    看表格和直方图！实际表的 ID = 7 有 20247 行，但直方图不知道您刚刚插入了新数据，因为没有触发自动更新。根据您需要插入 (20247 * 6) * 0.2 + 500 = 24,796.4 行的公式来触发此表上统计信息的自动更新。

    因此，如果您查看这些查询的计划，您会看到错误的估计：

    -- CTRL + M to include the Actual Execution plan
-- now, IF we run these queries, the Optimizer has no info about ID = 7
-- and the Estimates 1 because it cannot say 0.
SELECT ts.*
FROM dbo.TestStats ts
INNER JOIN dbo.IDs ON IDs.ID = ts.ID
WHERE IDs.ID = 1;

SELECT ts.*
FROM dbo.TestStats ts
INNER JOIN dbo.IDs ON IDs.ID = ts.ID
WHERE IDs.ID = 7;

    查询 #1：

    查询 #1：

    查询 #2：

    查询 #2

    Optimize 不能说 0 行，所以它只显示 1。

    步骤#3

    -- now we manually update the stats
UPDATE STATISTICS dbo.TestStats WITH FULLSCAN;

-- check the histogram
DBCC SHOW_STATISTICS('TestStats',PK_TestStats) WITH HISTOGRAM;

-- rerun the queries
SELECT ts.*
FROM dbo.TestStats ts
INNER JOIN dbo.IDs ON IDs.ID = ts.ID
WHERE IDs.ID = 1;

SELECT ts.*
FROM dbo.TestStats ts
INNER JOIN dbo.IDs ON IDs.ID = ts.ID
WHERE IDs.ID = 7;

    现在直方图显示了缺失的 ID 7，执行计划也显示了正确的估计。

    查询 #1：

    查询 #1

    查询 #2：

    查询 #2

    Q2）随着 cacheId 数量的增加，您是否期望新插入数据的估计值自然提高？

    是的，只要您从总行数中超过 20% + 500 的阈值。将触发自动更新。您可以通过重新运行 STEP#1 来运行此方案，然后通过运行以下查询来修改 STEP#2：

    -- now insert another ID = 7 with 20247 rows
DECLARE @i int = 1;

WHILE @i <= 20247
BEGIN
   INSERT INTO dbo.TestStats VALUES(7,@i);

   SET @i = @i + 1
END

-- see the problem with the histogram?
SELECT ID, COUNT(*) FROM dbo.TestStats GROUP BY ID;

DBCC SHOW_STATISTICS('TestStats',PK_TestStats) WITH HISTOGRAM;
GO
-- try to insert ID = 8 to trigger the auto update for the stats
DECLARE @i int = 1;

WHILE @i <= 4548
BEGIN
  INSERT INTO dbo.TestStats VALUES(8,@i);

  SET @i = @i + 1
END

-- no update yet
SELECT ID, COUNT(*) FROM dbo.TestStats GROUP BY ID;

DBCC SHOW_STATISTICS('TestStats',PK_TestStats) WITH HISTOGRAM;

    还没有更新，因为阈值是 24,796.4 - 20247 = 4549.4 但我们只为 ID 8 插入了 4548 行。现在插入这一行并仔细检查直方图：

    -- this will trigger the update
INSERT INTO dbo.TestStats VALUES(8,4549);

-- double check
SELECT ID, COUNT(*) FROM dbo.TestStats GROUP BY ID;

DBCC SHOW_STATISTICS('TestStats',PK_TestStats) WITH HISTOGRAM;

    Q3）是否有任何方法（gulp，技巧或其他方式）来改进估计（或使其不太确定 1 行）而不必在每次插入新数据集时更新统计信息（例如在更大的 CacheId = 999999）。

    控制 SQL Server 中的 Autostat (AUTO_UPDATE_STATISTICS) 行为

    但是，当表变得非常大时，旧阈值（固定比率 - 20% 的行更改）可能太高，并且 Autostat 过程可能不会足够频繁地触发。这可能会导致潜在的性能问题。SQL Server 2008 R2 Service Pack 1 和更高版本引入了跟踪标志 2371，您可以启用它来更改此默认行为。表中的行数越高，触发统计信息更新的阈值就越低。例如，如果启用了跟踪标志，则在发生 100 万次更改时，将在具有 10 亿行的表上触发更新统计信息。如果未激活跟踪标志，则在触发更新统计信息之前，具有 10 亿条记录的同一个表将需要 2 亿次更改。

    希望这有助于您理解！很好的问题！

    • 6

  2. Q3的一个答案）

    Q3）是否有任何方法（gulp，技巧或其他方式）来改进估计（或使其不太确定 1 行），而不必在每次插入新数据集时更新统计信息（例如在更大的 CacheId = 999999）。

    在连接中，使用 IsNull() 添加一些混淆，最后添加“优化”。

     select ... from ... join ...
   where CacheId = IsNull(@cacheId, 0)    
  option (recompile, optimize for (@cacheId = 41274))

    两者似乎都需要。Id 0 并不真正存在。“优化”中使用的 ID 值似乎无关紧要，显然甚至不需要存在。

    旁注：我还尝试删除自定义统计信息，在 CacheId 上添加新索引，但就更新行计数阈值而言，其隐式统计信息最终仍与显式自定义统计信息表现相同。

    编辑 2014-04-29：

    SQL Server 2014 改进的基数估计器中的“升序键”估计值可能已经得到改进

    Mark Storey-Smith评论自 2005 SP1 以来还有一个用于升序键的 traceon() 解决方案。

    编辑 2015-05-07：

    有些案例仍在估计 1 行（有时）。使用 unknown 似乎有帮助，然后 IsNull() 也可以删除：

      select ... from ... join ...
   where CacheId = @cacheId
  option (recompile, optimize for (@cacheId = unknown))
    • 1

相关问题