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 = ' INSERT INTO PostsDestination SELECT TOP 500000 * FROM Posts ORDER BY Id ', @Schema = 'dbo', @Table = 'PostsDestination', @ClearData = 1 */ CREATE PROCEDURE [dbo].[sp_GetLogUseStats] ( @Sql NVARCHAR(400), @Schema NVARCHAR(20), @Table NVARCHAR(200), @ClearData BIT = 0 ) AS IF @ClearData = 1 BEGIN TRUNCATE TABLE PostsDestination END /*Checkpoint to clear log (Assuming Simple/Bulk Recovery Model*/ CHECKPOINT /*Snapshot of logsize before query*/ CREATE TABLE #BeforeLogUsed( [Db] NVARCHAR(100), LogSize NVARCHAR(30), Used NVARCHAR(50), Status INT ) INSERT INTO #BeforeLogUsed EXEC('DBCC SQLPERF(logspace)') /*Run Query*/ EXECUTE sp_executesql @SQL /*Snapshot of logsize after query*/ CREATE TABLE #AfterLLogUsed( [Db] NVARCHAR(100), LogSize NVARCHAR(30), Used NVARCHAR(50), Status INT ) INSERT INTO #AfterLLogUsed EXEC('DBCC SQLPERF(logspace)') /*Return before and after log size*/ SELECT CAST(#AfterLLogUsed.Used AS DECIMAL(12,4)) - CAST(#BeforeLogUsed.Used AS DECIMAL(12,4)) AS LogSpaceUsersByInsert FROM #BeforeLogUsed LEFT JOIN #AfterLLogUsed ON #AfterLLogUsed.Db = #BeforeLogUsed.Db WHERE #BeforeLogUsed.Db = DB_NAME() /*Get list of affected indexes from insert query*/ SELECT @Schema + '.' + so.name + '.' + si.name AS IndexName INTO #IndexNames FROM sys.indexes si JOIN sys.objects so ON si.[object_id] = so.[object_id] WHERE si.name IS NOT NULL AND so.name = @Table /*Insert Record For Heap*/ INSERT INTO #IndexNames VALUES(@Schema + '.' + @Table) /*Get log recrod sizes for heap and/or any indexes*/ SELECT AllocUnitName, [operation], AVG([log record length]) AvgLogLength, SUM([log record length]) TotalLogLength, COUNT(*) Count INTO #LogBreakdown FROM fn_dblog(null, null) fn INNER JOIN #IndexNames ON #IndexNames.IndexName = allocunitname GROUP BY [Operation], AllocUnitName ORDER BY AllocUnitName, operation SELECT * FROM #LogBreakdown SELECT AllocUnitName, SUM(TotalLogLength) TotalLogRecordLength FROM #LogBreakdown GROUP BY AllocUnitName
Inserting into a heap with no indexes and TABLOCK using following code...
EXEC sp_GetLogUseStats @Sql = ' INSERT INTO PostsDestination SELECT * FROM Posts ORDER BY Id ', @Schema = 'dbo', @Table = 'PostsDestination', @ClearData = 1
I get these results
At 0.0024mb log file growth, very small log record sizes and very few of them I'm happy that this is using minimal logging.
If I then create a non clustered index on id...
CREATE INDEX ndx_PostsDestination_Id ON PostsDestination(Id)
Then run my same insert again...
Not only am I not getting minimal logging on the non clustered index but I've also lost it on the heap. After doing some more tests it seems if I make ID clustered it does minimally log but from what I've read 2016+ should minimally log to a heap with non clustered index when tablock is used.
FINAL EDIT :
I've reported the behaviour to Microsoft on the SQL Server UserVoice and will update if I get a response. I've also written up the full details of the minimal log scenarios that I couldn't get to work at https://gavindraper.com/2018/05/29/SQL-Server-Minimal-Logging-Inserts/