Sql-server – Minimal Logging Conditions in SQL

bulk-insertsql serversql-server-2008

I have written a script to test out the claims made on this page http://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx in the table titled Summarizing Minimal Logging Conditions about when minimal logging does or does not occur.

Using this script I find that the sum of the Log Record Lengths for each of the different types of inserts is the following:

  • Heap empty no tablock 60000
  • Heap empty with tablock 56000
  • Heap non empty no tablock 60000
  • Heap non empty with tablock 56000
  • Heap plus index empty no tablock 126188
  • Heap plus index empty with tablock 114188
  • Heap plus index non empty no tablock 138696
  • Heap plus index non empty with tablock 112000
  • Cluster empty ordered no tablock 64168
  • Cluster empty ordered with tablock 56168
  • Cluster empty unordered no tablock 73388
  • Cluster empty unordered with tablock 65388
  • Cluster non empty no tablock 63912
  • Cluster non empty with tablock 55944
  • Cluster plus index empty no tablock 124336
  • Cluster plus index empty with tablock 108336
  • Cluster plus index non empty no tablock 123876
  • Cluster plus index non empty with tablock 107924

A few of these numbers do not seem to match the table on the technet page. In particular:

  • There seems to be no difference in logging between inserting into empty vs non empty tables, but the page claims there should be full logging when inserting into a non empty cluster without tablock
  • Inserting with tablock into a heap or cluster with and index does seem to reduce logging, but the page claims there should be full logging.
  • When using the SELECT INTO method of insert, there are no rows in fn_dblog whose operation is insert, but the page lists this method as a bulk load operation that should have the behavior described in the table

For reference, this was run on a SQL express database, and when I run DBCC TRACESTATUS (610) everything is 0.

Can anyone help to explain why I might be seeing these discrepencies?

For reference the code is below: 

SET NOCOUNT ON

CREATE TABLE numbers (num INT)
CREATE TABLE numbersUnordered (num INT)

Declare @cnt int
Select @cnt=0
while (@cnt<500)
BEGIN
INSERT INTO NUMBERS(num) SELECT @cnt
SELECT @cnt=@cnt+1
END

Select @cnt=0
while (@cnt<250)
BEGIN
INSERT INTO numbersUnordered(num) SELECT @cnt*2
SELECT @cnt=@cnt+1
END

Select @cnt=0
while (@cnt<250)
BEGIN
INSERT INTO numbersUnordered(num) SELECT @cnt*2+1
SELECT @cnt=@cnt+1
END


---- heap empty without tablock
CREATE TABLE noKey1 (val INT)

INSERT INTO noKey1 (val)
SELECT * FROM numbers

DECLARE @heapEmptyNoTablock INT

SELECT @heapEmptyNoTablock = SUM([Log Record Length])
FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%noKey1%'
AND operation like '%insert%'

---- heap empty with tablock
CREATE TABLE noKey2 (val INT)

INSERT INTO noKey2 WITH(TABLOCK) (val)
SELECT * FROM numbers

DECLARE @heapEmptyTablock INT

SELECT @heapEmptyTablock = SUM([Log Record Length])
FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%noKey2%'
AND operation like '%insert%'

---- heap non empty without tablock
CREATE TABLE noKey3 (val INT)

INSERT INTO noKey3 WITH(TABLOCK) (val)
SELECT * FROM numbers

INSERT INTO noKey3 (val)
SELECT num+5 FROM numbers

DECLARE @heapNonEmptyNoTablock INT

SELECT @heapNonEmptyNoTablock = SUM(a.[Log Record Length])
FROM 
(SELECT TOP 500 * FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%noKey3%'
AND operation like '%insert%'
ORDER BY [Current LSN] desc) a

---- heap non empty with tablock
CREATE TABLE noKey4 (val INT)

INSERT INTO noKey4 WITH(TABLOCK) (val)
SELECT * FROM numbers

INSERT INTO noKey4 WITH (TABLOCK) (val)
SELECT num+5 FROM numbers

DECLARE @heapNonEmptyTablock INT

SELECT @heapNonEmptyTablock = SUM(a.[Log Record Length])
FROM 
(SELECT TOP 500 * FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%noKey4%'
AND operation like '%insert%'
ORDER BY [Current LSN] desc) a

--- heap plus index empty without tablock
CREATE TABLE noKey5 (val INT)
CREATE INDEX MSindex1
ON noKey5 (val)

INSERT INTO noKey5 (val)
SELECT * FROM numbers

DECLARE @heapIndexEmptyNoTablock INT

SELECT @heapIndexEmptyNoTablock = SUM([Log Record Length])
FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%noKey5%'
AND operation like '%insert%'


--- heap plus index empty with tablock
CREATE TABLE noKey6 (val INT)
CREATE INDEX MSindex2
ON noKey6 (val)

INSERT INTO noKey6 WITH(TABLOCK) (val)
SELECT * FROM numbers

DECLARE @heapIndexEmptyTablock INT

SELECT @heapIndexEmptyTablock = SUM([Log Record Length])
FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%noKey6%'
AND operation like '%insert%'

--- heap plus index non empty without tablock
CREATE TABLE noKey7 (val INT)
CREATE INDEX MSindex3
ON noKey7 (val)

INSERT INTO noKey7 WITH(TABLOCK) (val)
SELECT * FROM numbers

INSERT INTO noKey7 (val)
SELECT num+5 FROM numbers

DECLARE @heapIndexNonEmptyNoTablock INT

SELECT @heapIndexNonEmptyNoTablock = SUM(a.[Log Record Length])
FROM 
(SELECT TOP 1000 * FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%noKey7%'
AND operation like '%insert%'
ORDER BY [Current LSN] desc) a

--- heap plus index non empty with tablock
CREATE TABLE noKey8 (val INT)
CREATE INDEX MSindex4
ON noKey7 (val)

INSERT INTO noKey8 WITH(TABLOCK) (val)
SELECT * FROM numbers

INSERT INTO noKey8 WITH(TABLOCK) (val)
SELECT num+5 FROM numbers

DECLARE @heapIndexNonEmptyTablock INT

SELECT @heapIndexNonEmptyTablock = SUM(a.[Log Record Length])
FROM 
(SELECT TOP 1000 * FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%noKey8%'
AND operation like '%insert%'
ORDER BY [Current LSN] desc) a

--- cluster empty ordered without tablock
CREATE TABLE withKey1 (val INT PRIMARY KEY)

INSERT INTO withKey1 (val)
SELECT * FROM numbers

DECLARE @clusterEmptyNoTablock INT

SELECT @clusterEmptyNoTablock = SUM([Log Record Length])
FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%withKey1%'
AND operation like '%insert%'

--- cluster empty ordered with tablock
CREATE TABLE withKey2 (val INT PRIMARY KEY)

INSERT INTO withKey2 WITH(TABLOCK) (val)
SELECT * FROM numbers

DECLARE @clusterEmptyTablock INT

SELECT @clusterEmptyTablock = SUM([Log Record Length])
FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%withKey2%'
AND operation like '%insert%'

--- cluster empty unordered without tablock
CREATE TABLE withKey5 (val INT PRIMARY KEY)

INSERT INTO withKey5 (val)
SELECT * FROM numbersUnordered

DECLARE @clusterEmptyNoTablockUnordered INT

SELECT @clusterEmptyNoTablockUnordered = SUM([Log Record Length])
FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%withKey5%'
AND operation like '%insert%'

--- cluster empty undordered with tablock
CREATE TABLE withKey6 (val INT PRIMARY KEY)

INSERT INTO withKey6 WITH(TABLOCK) (val)
SELECT * FROM numbersUnordered

DECLARE @clusterEmptyTablockUnordered INT

SELECT @clusterEmptyTablockUnordered = SUM([Log Record Length])
FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%withKey6%'
AND operation like '%insert%'

--- cluster non empty no tablock
CREATE TABLE withKey7 (val INT PRIMARY KEY)

INSERT INTO withKey7 (val)
SELECT num FROM numbers

INSERT INTO withKey7 (val)
SELECT num+500 FROM numbers

DECLARE @clusterNonEmptyNoTablock INT

SELECT @clusterNonEmptyNoTablock = SUM(a.[Log Record Length])
FROM 
(SELECT TOP 500 * FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%withKey7%'
AND operation like '%insert%'
ORDER BY [Current LSN] desc) a

--- cluster non empty with tablock
CREATE TABLE withKey8 (val INT PRIMARY KEY)

INSERT INTO withKey8 WITH(TABLOCK) (val)
SELECT num FROM numbers

INSERT INTO withKey8 WITH(TABLOCK) (val)
SELECT num+500 FROM numbers

DECLARE @clusterNonEmptyTablock INT

SELECT @clusterNonEmptyTablock = SUM(a.[Log Record Length])
FROM 
(SELECT TOP 500 * FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%withKey8%'
AND operation like '%insert%'
ORDER BY [Current LSN] desc) a

--- cluster plus index empty no tablock
CREATE TABLE withKey9 (val INT PRIMARY KEY)
CREATE INDEX MSindex5
ON withKey9 (val)

INSERT INTO withKey9 (val)
SELECT * FROM numbers

DECLARE @clusterIndexEmptyNoTablock INT

SELECT @clusterIndexEmptyNoTablock = SUM([Log Record Length])
FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%withKey9%'
AND operation like '%insert%'

--- cluster plus index empty with tablock
CREATE TABLE withKey10 (val INT PRIMARY KEY)
CREATE INDEX MSindex6
ON withKey10 (val)

INSERT INTO withKey10 WITH(TABLOCK) (val)
SELECT * FROM numbers

DECLARE @clusterIndexEmptyTablock INT

SELECT @clusterIndexEmptyTablock = SUM([Log Record Length])
FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%withKey10%'
AND operation like '%insert%'

--- cluster plus index nonempty no tablock
CREATE TABLE withKey11 (val INT PRIMARY KEY)
CREATE INDEX MSindex7
ON withKey11 (val)

INSERT INTO withKey11 (val)
SELECT num FROM numbers

INSERT INTO withKey11 (val)
SELECT num+500 FROM numbers

DECLARE @clusterIndexNonEmptyNoTablock INT

SELECT @clusterIndexNonEmptyNoTablock = SUM(a.[Log Record Length])
FROM 
(SELECT TOP 1000 * FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%withKey11%'
AND operation like '%insert%'
ORDER BY [Current LSN] desc) a

--- cluster plus index nonempty with tablock
CREATE TABLE withKey12 (val INT PRIMARY KEY)
CREATE INDEX MSindex8
ON withKey12 (val)

INSERT INTO withKey12 WITH(TABLOCK) (val)
SELECT num FROM numbers

INSERT INTO withKey12 WITH(TABLOCK) (val)
SELECT num+500 FROM numbers

DECLARE @clusterIndexNonEmptyTablock INT

SELECT @clusterIndexNonEmptyTablock = SUM(a.[Log Record Length])
FROM 
(SELECT TOP 1000 * FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%withKey12%'
AND operation like '%insert%'
ORDER BY [Current LSN] desc) a

--- select into
/*SELECT * 
INTO selectIntoTable
FROM numbers

SELECT * FROM sys.fn_dblog(null, null)
WHERE allocunitname like '%selectIntoTable%'
AND operation like '%insert%'

DROP TABLE selectIntoTable
*/


PRINT 'Heap empty no tablock ' + CAST(@heapEmptyNoTablock AS VARCHAR)
PRINT 'Heap empty with tablock ' + CAST(@heapEmptyTablock AS VARCHAR)
PRINT 'Heap non empty no tablock ' + CAST(@heapNonEmptyNoTablock AS VARCHAR)
PRINT 'Heap non empty with tablock ' + CAST(@heapNonEmptyTablock AS VARCHAR)
PRINT 'Heap plus index empty no tablock ' + CAST(@heapIndexEmptyNoTablock AS VARCHAR)
PRINT 'Heap plus index empty with tablock ' + CAST(@heapIndexEmptyTablock AS VARCHAR)
PRINT 'Heap plus index non empty no tablock ' + CAST(@heapIndexNonEmptyNoTablock AS VARCHAR)
PRINT 'Heap plus index non empty with tablock ' + CAST(@heapIndexNonEmptyTablock AS VARCHAR)
PRINT 'Cluster empty ordered no tablock ' + CAST(@clusterEmptyNoTablock AS VARCHAR)
PRINT 'Cluster empty ordered with tablock ' + CAST(@clusterEmptyTablock AS VARCHAR)
PRINT 'Cluster empty unordered no tablock ' + CAST(@clusterEmptyNoTablockUnordered AS VARCHAR)
PRINT 'Cluster empty unordered with tablock ' + CAST(@clusterEmptyTablockUnordered AS VARCHAR)
PRINT 'Cluster non empty no tablock ' + CAST(@clusterNonEmptyNoTablock AS VARCHAR)
PRINT 'Cluster non empty with tablock ' + CAST(@clusterNonEmptyTablock AS VARCHAR)
PRINT 'Cluster plus index empty no tablock ' + CAST(@clusterIndexEmptyNoTablock AS VARCHAR)
PRINT 'Cluster plus index empty with tablock ' + CAST(@clusterIndexEmptyTablock AS VARCHAR)
PRINT 'Cluster plus index non empty no tablock ' + CAST(@clusterIndexNonEmptyNoTablock AS VARCHAR)
PRINT 'Cluster plus index non empty with tablock ' + CAST(@clusterIndexNonEmptyTablock AS VARCHAR)


DROP TABLE numbers
DROP TABLE numbersUnordered
DROP TABLE noKey1
DROP TABLE noKey2
DROP TABLE noKey3
DROP TABLE noKey4
DROP TABLE noKey5
DROP TABLE noKey6
DROP TABLE noKey7
DROP TABLE noKey8
DROP TABLE withKey1
DROP TABLE withKey2
DROP TABLE withKey5
DROP TABLE withKey6
DROP TABLE withKey7
DROP TABLE withKey8
DROP TABLE withKey9
DROP TABLE withKey10
DROP TABLE withKey11
DROP TABLE withKey12

Best Answer

A few of these numbers do not seem to match the table on the technet page.

There are small differences in the sizes of the log records generated in your tests, but these are due to other internal logging behaviours, not whether minimal logging is occurring or not.

A good definition of minimal logging is provided by Sunil Agarwal of the Storage Engine team:

Individual rows are not logged and only the changes to page allocation structures are logged

Any test where you see individual row changes logged (e.g. LOP_INSERT_ROWS) is not using minimal logging for the associated allocation unit. Some operations can be minimally logged with respect to one allocation unit (e.g. an index) and not minimally logged against another. Also, in some circumstances, inserts to existing pages may not be minimally-logged but changes to newly allocated page may be.

Most of the details can be found in a series of Storage Engine team blog posts:

One detail not explored there is that to be minimally-logged (in SQL Server 2008 or later) INSERT...SELECT changes to b-tree structures must have the DMLRequestSort query plan operator property set to true. This applies to the circumstances where the Data Loading Performance Guide shows 'Depends': the query plan must use wide (per-index) maintenance with DMLRequestSort=true.

I wrote more about this in Minimal Logging with INSERT…SELECT and Fast Load Context.

Related Question