Poor cardinality estimate disqualifies INSERT from minimal logging?

Question

Why is the second INSERT statement ~5x slower than the first?

From the amount of log data generated, I think that the second is not qualifying for minimal logging. However, the documentation in the Data Loading Performance Guide indicates that both inserts should be able to be minimally logged. So if minimal logging is the key performance difference, why is it that the second query does not qualify for minimal logging? What can be done to improve the situation?

Query #1: Inserting 5MM rows using INSERT...WITH (TABLOCK)

Consider the following query, which inserts 5MM rows into a heap. This query executes in 1 second and generates 64MB of transaction log data as reported by 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

Query #2: Inserting the same data, but SQL underestimates the # of rows

Now consider this very similar query, which operates on exactly the same data but happens to draw from a table (or complex SELECT statement with many joins in my actual production case) where the cardinality estimate is too low. This query executes in 5.5 seconds and generates 461MB of transaction log data.

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

Full script

See this Pastebin for a full set of scripts to generate the test data and execute either of these scenarios. Note that you must use a database that is in the SIMPLE recovery model.

Business context

We are semi-frequently moving around millions of rows of data, and it's important to have these operations be as efficient as possible, both in terms of the execution time and the disk I/O load. We had initially been under the impression that creating a heap table and using INSERT...WITH (TABLOCK) was a good way to do this, but have now become less confident given that we observed the situation demonstrated above in an actual production scenario (albeit with more complex queries, not the simplified version here).

Answer 1

Why is it that the second query does not qualify for minimal logging?

Minimal logging is available for the second query, but the engine chooses not to use it at runtime.

There is a minimum threshold for INSERT...SELECT below which it chooses not to use the bulk load optimizations. There is a cost involved in setting up a bulk rowset operation, and bulk-inserting only a few rows would not result in efficient space utilization.

What can be done to improve the situation?

Use one of the many other methods (e.g. SELECT INTO) that does not have this threshold. Alternatively, you might be able to rewrite the source query in some way to boost the estimated number of rows/pages over the threshold for INSERT...SELECT.

See also Geoff's self-answer for more useful information.

---

Possibly interesting trivia: SET STATISTICS IO reports logical reads for the target table only when bulk loading optimizations are not used.

Answer 2

I was able to recreate the problem with my own test rig:

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;

This begs the question, why not "fix" the problem by updating statistics on the source tables prior to running the minimally-logged operation?

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;

Answer 3

Rewrite the source query in some way to boost the estimated number of rows

Expanding on Paul's idea, a workaround if you are truly desperate is to add a dummy table that guarantees that the estimated number of rows for the insert will be high enough to quality for bulk loading optimizations. I confirmed that this gets minimal logging and improves query performance.

-- 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)

Final takeaways

1. Use SELECT...INTO for one-time insert operations if minimally logging is required. As Paul points out, this will ensure minimal logging regardless of the row estimate
2. Wherever possible, write queries in a simple manner that the query optimizer can reason about effectively. It may be possible to break up a query into multiple pieces, for example, in order to allow statistics to be built on an intermediate table.
3. If you have access to SQL Server 2014, try it out on your query; in my actual production case, I just tried it out and the new Cardinality Estimator yielded a much higher (and better) estimate; the query then was minimally logged. But this may not be helpful if you need to support SQL 2012 and earlier.
4. If you're desperate, hacky solutions like this one may apply!

A related article

Paul White's May 2019 blog post Minimal Logging with INSERT…SELECT into Heap Tables covers some of this information in more detail.