主页 / dba / 问题 / 116368
Accepted
Geoff Patterson
Asked: 2015-09-29 10:44:21 +0800 CST

基数估计不佳会取消 INSERT 从最小日志记录的资格吗？

  • 772

为什么第二个INSERT语句比第一个慢 ~5 倍？

从生成的日志数据量来看，我认为第二个不符合最小日志记录的条件。但是，数据加载性能指南中的文档指出这两个插入应该能够被最少地记录。因此，如果最小日志记录是关键的性能差异，为什么第二个查询不符合最小日志记录的条件？可以做些什么来改善这种情况？


查询 #1：使用 INSERT...WITH (TABLOCK) 插入 5MM 行

考虑以下查询，它将 5MM 行插入到堆中。此查询在 中执行1 second并生成64MB所报告的事务日志数据sys.dm_tran_database_transactions。

CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
GO
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that correctly estimates that it will generate 5MM rows
FROM dbo.fiveMillionNumbers
-- Provides greater consistency on my laptop, where other processes are running
OPTION (MAXDOP 1)
GO


查询 #2：插入相同的数据，但 SQL 低估了行数

现在考虑这个非常相似的查询，它对完全相同的数据进行操作，但恰好是从SELECT基数估计值太低的表（或在我的实际生产案例中具有许多连接的复杂语句）中提取的。此查询在事务日志数据中执行5.5 seconds并生成461MB。

CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
GO
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that produces 5MM rows but SQL estimates just 1000 rows
FROM dbo.fiveMillionNumbersBadEstimate
-- Provides greater consistency on my laptop, where other processes are running
OPTION (MAXDOP 1)
GO


完整脚本

请参阅此 Pastebin以获取用于生成测试数据并执行其中任一场景的全套脚本。请注意，您必须使用SIMPLE 恢复模型中的数据库。


商业背景

我们半频繁地移动数百万行数据，让这些操作尽可能高效非常重要，无论是在执行时间还是磁盘 I/O 负载方面。我们最初的印象是创建堆表并使用INSERT...WITH (TABLOCK)它是一个很好的方法，但现在我们变得不那么自信了，因为我们在实际生产场景中观察到了上面展示的情况（尽管有更复杂的查询，而不是此处为简化版）。

sql-server performance

3 个回答

  1. Best Answer

    为什么第二个查询不符合最少日志记录的条件？

    最小日志记录可用于第二个查询，但引擎选择在运行时不使用它。

    有一个最小阈值，INSERT...SELECT低于该阈值它选择不使用批量加载优化。设置批量行集操作会产生成本，并且仅批量插入几行不会导致有效的空间利用。

    可以做些什么来改善这种情况？

    SELECT INTO使用没有此阈值的许多其他方法之一（例如）。或者，您可能能够以某种方式重写源查询，以提高估计的行/页数超过INSERT...SELECT.

    另请参阅Geoff 的自我回答以获取更多有用信息。

    可能有趣的琐事： 仅在不使用批量加载优化时SET STATISTICS IO报告目标表的逻辑读取。

    • 7

  2. 我能够用自己的测试装置重现问题：

    USE test;

CREATE TABLE dbo.SourceGood
(
    SourceGoodID INT NOT NULL
        CONSTRAINT PK_SourceGood
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData VARCHAR(384) NOT NULL
);

CREATE TABLE dbo.SourceBad
(
    SourceBadID INT NOT NULL
        CONSTRAINT PK_SourceBad
        PRIMARY KEY CLUSTERED
        IDENTITY(-2147483647,1)
    , SomeData VARCHAR(384) NOT NULL
);

CREATE TABLE dbo.InsertTest
(
    SourceBadID INT NOT NULL
        CONSTRAINT PK_InsertTest
        PRIMARY KEY CLUSTERED
    , SomeData VARCHAR(384) NOT NULL
);
GO

INSERT INTO dbo.SourceGood WITH (TABLOCK) (SomeData) 
SELECT TOP(5000000) o.name + o1.name + o2.name
FROM syscolumns o
    , syscolumns o1
    , syscolumns o2;
GO

ALTER DATABASE test SET AUTO_UPDATE_STATISTICS OFF;
GO

INSERT INTO dbo.SourceBad WITH (TABLOCK) (SomeData)
SELECT TOP(5000000) o.name + o1.name + o2.name
FROM syscolumns o
    , syscolumns o1
    , syscolumns o2;
GO

ALTER DATABASE test SET AUTO_UPDATE_STATISTICS ON;
GO

BEGIN TRANSACTION;

INSERT INTO dbo.InsertTest WITH (TABLOCK)
SELECT *
FROM dbo.SourceGood;

SELECT * FROM sys.dm_tran_database_transactions;

/*
database_transaction_log_record_count
472 
database_transaction_log_bytes_used
692136
*/

COMMIT TRANSACTION;


BEGIN TRANSACTION;

INSERT INTO dbo.InsertTest WITH (TABLOCK)
SELECT *
FROM dbo.SourceBad;

SELECT * FROM sys.dm_tran_database_transactions;

/*
database_transaction_log_record_count   
5000003 
database_transaction_log_bytes_used
642699256
*/

COMMIT TRANSACTION;

    这就引出了一个问题，为什么不在运行最少日志记录操作之前通过更新源表上的统计信息来“解决”问题？

    TRUNCATE TABLE dbo.InsertTest;
UPDATE STATISTICS dbo.SourceBad;

BEGIN TRANSACTION;

INSERT INTO dbo.InsertTest WITH (TABLOCK)
SELECT *
FROM dbo.SourceBad;

SELECT * FROM sys.dm_tran_database_transactions;

/*
database_transaction_log_record_count
472
database_transaction_log_bytes_used
692136
*/

COMMIT TRANSACTION;
    • 5

  3. 以某种方式重写源查询以增加估计的行数

    扩展 Paul 的想法，如果您真的绝望，一种解决方法是添加一个虚拟表，以确保插入的估计行数足够高以达到批量加载优化的质量。我确认这会获得最少的日志记录并提高查询性能。

    -- Create a dummy table that SQL Server thinks has a million rows
CREATE TABLE dbo.emptyTableWithMillionRowEstimate (
    n INT PRIMARY KEY
)
GO
UPDATE STATISTICS dbo.emptyTableWithMillionRowEstimate
WITH ROWCOUNT = 1000000
GO

-- Concatenate this table into the final rowset:
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that correctly estimates that it will generate 5MM rows
FROM dbo.fiveMillionNumbersBadEstimate
-- Add in dummy rowset to ensure row estimate is high enough for bulk load optimization
UNION ALL
SELECT NULL FROM dbo.emptyTableWithMillionRowEstimate
OPTION (MAXDOP 1)

    最后的收获

    1. 如果需要最少的日志记录，则用于SELECT...INTO一次性插入操作。正如保罗指出的那样，无论行估计如何，这都将确保最少的日志记录
    2. 尽可能以查询优化器可以有效推理的简单方式编写查询。可以将一个查询分解成多个部分，例如，为了允许在中间表上构建统计信息。
    3. 如果您有权访问 SQL Server 2014，请在您的查询中试用它；在我的实际生产案例中，我只是尝试了一下，新的 Cardinality Estimator 产生了更高（更好）的估计；然后查询被最少记录。但如果您需要支持 SQL 2012 及更早版本，这可能没有帮助。
    4. 如果你绝望了，像这样的 hacky 解决方案可能适用！

    相关文章

    Paul White 的 2019 年 5 月博客文章使用 INSERT…SELECT 进入堆表进行最小日志记录更详细地介绍了其中的一些信息。

    • 4

相关问题