sql-server - 如何避免 SQL Server 对缺失值使用默认行估计？

问题描述

我有一个包含几百万行的表，其中一Status列是非聚集索引中的第二列。状态为char(10)并包含“新建”、“处理中”、“已处理”和“失败”

轮询函数检查新行

SELECT TOP 1 ... FROM Table WHERE firstColumnInIdex = 1 AND Status = 'New' ORDER BY Id

（实际上是对状态“处理中”和其他一些差异的更新，但在这里无关紧要）

该查询使用非聚集索引，但行估计约为行的 30%，因此内存授予在 GB 范围内。

我的测试表明问题出在统计数据上。由于表中通常没有状态为“New”的行，因此统计信息中不存在“New”（表示数百万“已处理”和数千“失败”）。如果在统计信息中找不到该值，SQL Server 似乎会采用默认估计值，在这种情况下约为 30% 的行。

我在表格中添加了一行，状态为“New”，并创建了新的统计信息FULLSCAN NORECOMPUTE。（所以它变成数百万“已处理”，数千“失败”和 1“新”）

现在行估计是 1 行，查询成本从 82 下降到 6，内存授予很小。

（删除统计数据再次导致 30%）

虽然这个技巧解决了问题，但感觉就像是某天可能会停止工作的黑客（例如，某些未来的 dba 会发现这个过时的统计数据并删除/更新它）。

有没有更好的方法来解决这个问题？例如

改用整数状态？
使用外键或约束使 SQL Server 知道“新建”状态？

版本是 2016SP1

标签： sql-serversql-execution-plan

解决方案

我发现有用的一件事是过滤索引。

假设这是一个队列并且事情以状态“新”开始，你

选择一个或所有“新”行（获取 PK ID）
对这些 ID 采取行动
根据 ID 更新状态

在这些情况下，您可以创建一个过滤索引，它基本上只是状态为“新”的所有行的最新列表。

CREATE NONCLUSTERED INDEX ix_myindex ON [myTable] 
([ID])
WHERE (Status = 'New')

注意 - 索引将非常“热”，例如，有很多变化（一旦它们不再是“新的”，它们就会从索引中删除）。

然而，这个想法是保持如此之小以至于它并不重要。

确保索引具有识别相关行（例如，您的 PK）所需的所有字段，以使其尽可能简单/小，并查看它是否有效。

更新以下评论

这些问题可能与“升序关键问题”有关 - 请随意研究和审查。

我可能在上面犯了一个小错误 - 如果您实际上包含要过滤的字段，通常过滤索引会更好地工作。因此，以下可能会更好。

CREATE NONCLUSTERED INDEX ix_myindex ON [myTable] 
([ID], [Status])
WHERE (Status = 'New')

关于解决方案中的方法 - 我们将完全忽略统计数据。相反，我们实际上创建了一个具有相关行数的临时表，而这些行将限制基数估计。

对于测试，我有一个名为“test”的表，它有大约 150 万行，带有一个 ID PK 和 4 个带有 UUID 的列（基本上是随机数据）。

我用它来创建一个带有状态列的新表“test2”。其中大约 80% 的状态为“已处理”，10% 的状态为“处理中”，10% 的状态为“失败”。

然后我插入一个状态为“新”的新行。请注意，统计信息不会更新。

但是，然后我使用过滤索引来识别相关行，方法是将它们放入临时表中 - 并使用该表进行进一步处理。

设置

IF OBJECT_ID (N'test2', N'U') IS NOT NULL DROP TABLE dbo.Test2;
GO

CREATE TABLE [dbo].[test2](
    [ID] [int] NOT NULL,
    [Status] [varchar](12) NULL,
    [col2] [varchar](100) NULL,
    [col3] [varchar](100) NULL,
    [col4] [varchar](100) NULL,
    [col5] [varchar](100) NULL,
    
 CONSTRAINT [PK_test2] PRIMARY KEY CLUSTERED ([ID] ASC)
 );
GO

CREATE NONCLUSTERED INDEX [IX_test2_StatusNew] ON [dbo].[test2] ([ID] ASC, [Status] ASC)
    WHERE ([Status]='New');
GO

INSERT INTO dbo.Test2 (ID, Status, Col2, Col3, Col4, Col5)
    SELECT ID, CASE WHEN ID % 12 < 10 THEN 'Processed' WHEN ID % 12 = 10 THEN 'Processing' ELSE 'Failed' END,
          Col2, Col3, Col4, Col5
    FROM dbo.Test;
GO

CREATE STATISTICS [S_Status] ON [dbo].[test2]([Status]);
GO

DBCC SHOW_STATISTICS ('dbo.Test2', 'S_Status');
/*
RANGE_HI_KEY  RANGE_ROWS EQ_ROWS   DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
Failed        0          141420    0                    1
Processed     0          1417080   0                    1
Processing    0          141420    0                    1
*/

这是我的存储过程 - 它首先标记适当的行（将它们的状态更改为“处理中”）并记录它们的 ID。

然后使用 ID 处理表中的行，然后再次将状态更新为“已处理”。

为简洁起见，我没有包括任何事务或错误检查。

CREATE PROCEDURE UpdateTest2News
AS
BEGIN
SET NOCOUNT ON;

    CREATE TABLE #IDs_to_process (ID int PRIMARY KEY);

    UPDATE      test2
        SET     Status = 'Processing'
        OUTPUT  deleted.ID
        INTO    #IDs_to_process
        WHERE   Status = 'New';

    UPDATE      test2
        SET     Col2 = NEWID(),
                Col3 = NEWID(),
                Col4 = NEWID(),
                Col5 = NEWID()
        FROM    test2
                INNER JOIN #IDs_to_Process IDs ON test2.ID = IDs.ID;

    UPDATE      test2
        SET     Status = 'Processed'
        FROM    test2
                INNER JOIN #IDs_to_Process IDs ON test2.ID = IDs.ID;

END;

然后我在 Test2 中添加一个新行（状态为“New”）。检查统计信息时，它们没有改变（没有发生足够的变化来强制更新）。

SELECT TOP 1 ID FROM dbo.test2 ORDER BY ID DESC; -- Getting the latest value for next step
/* Max ID = 1699920 */

INSERT INTO dbo.Test2 (ID, Status, Col2, Col3, Col4, Col5)
SELECT 1699921, 'New', NULL, NULL, NULL, NULL;

DBCC SHOW_STATISTICS ('dbo.Test2', 'S_Status');
/*  Same as above  */
DBCC SHOW_STATISTICS ('dbo.Test2', 'IX_test2_StatusNew');
/*  No records represented in stats  */
GO

现在，最后的步骤

运行SET STATISTICS TIME, IO ON;以查看处理统计信息
还设置“包括实际执行计划”以查看估计值与实际值等

EXEC UpdateTest2News

这是一个经过清理的版本统计数据 - 非常好。

Stats summary

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

Table '#IDs_to_process___...________________0000000000BC'. Scan count 0, logical reads 2
Table 'test2'. Scan count 1, logical reads 7
Table 'Worktable'. Scan count 1, logical reads 5

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 14 ms.


SQL Server parse and compile time: 
   CPU time = 25 ms, elapsed time = 25 ms.

Table 'test2'. Scan count 0, logical reads 11
Table '#IDs_to_process________...__________0000000000BC'. Scan count 1, logical reads 2

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 593 ms.


SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

Table 'test2'. Scan count 0, logical reads 3
Table '#IDs_to_process_____...______0000000000BC'. Scan count 1, logical reads 2

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 45 ms.


 SQL Server Execution Times:
   CPU time = 61 ms,  elapsed time = 683 ms.

这是执行计划/等，估计值与实际值也很好。

注意 - 它确实记住/缓存了执行计划，当您拥有大量不同数量的“新”行时，这可能会变成一个问题。

如果需要，您可以OPTION (RECOMPILE)在存储过程中添加语句 2 或 3，以便它采用新的行数估计值。

如果需要，该命令UPDATE STATISTICS test2 (IX_test2_StatusNew) WITH fullscan也很容易运行（因为该索引中几乎没有行） - 这可能对您的情况有所帮助。

sql-server - 如何避免 SQL Server 对缺失值使用默认行估计？

问题描述

解决方案

更新以下评论

推荐阅读