Estimativa de cardinalidade ruim desqualifica INSERT do log mínimo?

Por que a segunda consulta não se qualifica para registro mínimo?

O log mínimo está disponível para a segunda consulta, mas o mecanismo opta por não usá-lo no tempo de execução.

Há um limite mínimo abaixo do INSERT...SELECTqual ele escolhe não usar as otimizações de carregamento em massa. Há um custo envolvido na configuração de uma operação de conjunto de linhas em massa e a inserção em massa de apenas algumas linhas não resultaria na utilização eficiente do espaço.

O que pode ser feito para melhorar a situação?

Use um dos muitos outros métodos (por exemplo SELECT INTO) que não possui esse limite. Como alternativa, você pode reescrever a consulta de origem de alguma forma para aumentar o número estimado de linhas/páginas acima do limite para INSERT...SELECT.

Veja também a auto-resposta de Geoff para informações mais úteis.

Curiosidades possivelmente interessantes: SET STATISTICS IO relata leituras lógicas para a tabela de destino somente quando as otimizações de carregamento em massa não são usadas .

Consegui recriar o problema com meu próprio equipamento de teste:

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;

Isso levanta a questão: por que não "corrigir" o problema atualizando as estatísticas nas tabelas de origem antes de executar a operação minimamente registrada?

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;

Reescreva a consulta de origem de alguma forma para aumentar o número estimado de linhas

Expandindo a ideia de Paul, uma solução alternativa se você estiver realmente desesperado é adicionar uma tabela fictícia que garanta que o número estimado de linhas para a inserção seja alto o suficiente para qualidade para otimizações de carregamento em massa. Confirmei que isso obtém um registro mínimo e melhora o desempenho da consulta.

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

Considerações finais

1. Use SELECT...INTOpara operações de inserção únicas se for necessário registrar minimamente. Como Paul aponta, isso garantirá o registro mínimo, independentemente da estimativa de linha
2. Sempre que possível, escreva consultas de maneira simples que o otimizador de consulta possa raciocinar com eficiência. Pode ser possível dividir uma consulta em várias partes, por exemplo, para permitir que as estatísticas sejam construídas em uma tabela intermediária.
3. Se você tiver acesso ao SQL Server 2014, experimente em sua consulta; no meu caso de produção real, acabei de experimentá-lo e o novo Estimador de Cardinalidade produziu uma estimativa muito maior (e melhor); a consulta então foi minimamente registrada. Mas isso pode não ser útil se você precisar oferecer suporte ao SQL 2012 e anteriores.
4. Se você estiver desesperado, soluções hacky como esta podem ser aplicadas!

Um artigo relacionado

A postagem no blog de maio de 2019 de Paul White Registro mínimo com INSERT…SELECT em tabelas heap aborda algumas dessas informações com mais detalhes.