Why am I not getting minimal logging when inserting to indexed tables

I'm testing minimal logging inserts in different scenarios and from what I've read INSERT INTO SELECT into a heap with a non clustered index using TABLOCK and SQL Server 2016+ should minimally log, however in my case when doing this I'm getting full logging. My database is in the simple recovery model and I successfully get minimally logged inserts on a heap with no indexes and TABLOCK.

I'm using an old backup of the Stack Overflow database to test on and have created a replicate of the Posts table with the following schema...

CREATE TABLE [dbo].[PostsDestination](
    [Id] [int] NOT NULL,
    [AcceptedAnswerId] [int] NULL,
    [AnswerCount] [int] NULL,
    [Body] [nvarchar](max) NOT NULL,
    [ClosedDate] [datetime] NULL,
    [CommentCount] [int] NULL,
    [CommunityOwnedDate] [datetime] NULL,
    [CreationDate] [datetime] NOT NULL,
    [FavoriteCount] [int] NULL,
    [LastActivityDate] [datetime] NOT NULL,
    [LastEditDate] [datetime] NULL,
    [LastEditorDisplayName] [nvarchar](40) NULL,
    [LastEditorUserId] [int] NULL,
    [OwnerUserId] [int] NULL,
    [ParentId] [int] NULL,
    [PostTypeId] [int] NOT NULL,
    [Score] [int] NOT NULL,
    [Tags] [nvarchar](150) NULL,
    [Title] [nvarchar](250) NULL,
    [ViewCount] [int] NOT NULL
)
CREATE NONCLUSTERED INDEX ndx_PostsDestination_Id ON PostsDestination(Id)

I then try to copy the posts table into this table...

INSERT INTO PostsDestination WITH(TABLOCK)
SELECT * FROM Posts ORDER BY Id

From looking at fn_dblog and the log file usage I can see I'm not getting minimal logging from this. I've read that versions before 2016 require trace flag 610 to minimally log to indexed tables, I've also tried setting this but still no joy.

I'm guessing I'm missing something here?

EDIT - More Info

To add more info I'm using the following procedure that I've written to try to detect minimal logging, maybe I've got something here wrong...

```
/*
Example Usage...

EXEC sp_GetLogUseStats
@Sql = '

I can reproduce your results on SQL Server 2017 using the Stack Overflow 2010 database, but not (all of) your conclusions.

Minimal logging to the heap is unavailable when using INSERT...SELECT with TABLOCK to a heap with a nonclustered index, which is unexpected. My guess is INSERT...SELECT cannot support bulk loads using RowsetBulk (heap) at the same time as FastLoadContext (b-tree). Only Microsoft would be able to confirm if this is a bug or by design.

The nonclustered index on the heap is minimally logged (assuming TF610 is on, or SQL Server 2016+ is used, enabling FastLoadContext) with the following caveats:

• Only rows inserted to newly allocated pages are minimally logged.

• Rows added to the first index page are not minimally logged, if the index was empty at the start of the operation.

The 497 LOP_INSERT_ROWS entries shown for the nonclustered index correspond to the first page of the index. Since the index was empty beforehand, these rows are fully logged. The remaining rows are all minimally logged. If documented trace flag 692 is enabled (2016+) to disable FastLoadContext, all nonclustered index rows are minimally logged.

I found that minimal logging is applied to both the heap and nonclustered index when bulk loading the same table (with index) using BULK INSERT from a file:

BULK INSERT dbo.PostsDestination
FROM 'D:\SQL Server\Posts.bcp'
WITH (TABLOCK, DATAFILETYPE = 'native');

I note this for completeness. Bulk loading using INSERT...SELECT uses different code paths, so the fact the behaviours differ is not entirely unexpected.

For full details about minimal logging using RowsetBulk and FastLoadContext with INSERT...SELECT see my three part series on SQLPerformance.com:

• Minimal Logging with INSERT…SELECT into Heap Tables

• Minimal Logging with INSERT…SELECT into Empty Clustered Tables

• Minimal Logging with INSERT…SELECT and Fast Load Context

Other scenarios from your blog post

Comments are closed so I will address these briefly here.
Empty Clustered Index With Trace 610 Or 2016+

This is minimally logged using FastLoadContext without TABLOCK. The only rows fully logged are those inserted to the first page because the clustered index was empty at the start of the transaction.
Clustered Index With Data and Trace 610 OR 2016+

This is also minimally logged using FastLoadContext. Rows added to the existing page are fully logged, the remainder are minimally logged.
Clustered Index With NonClustered Indexes and TABLOCK Or Trace 610/SQL 2016+

This can also be minimally logged using FastLoadContext as long as the nonclustered index is maintained by a separate operator, DMLRequestSort is set to true, and the other conditions laid out in my posts are met.

StackExchange Database Administrators Q#207814, answer score: 13