December 3, 2013 at 6:49 am
I know that if I use the syntax
INSERT INTO <DestinationTable> (<Columns>) WITH (TABLOCK)
SELECT <Columns> FROM <SomeStatement>
I will have a minimally logged insert if the destination is a heap. Does this also apply if the destination has an IDENTITY(1,1) field?
I will be testing, but wanted to know if anyone else has any experience they could share.
Reference: http://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx
December 3, 2013 at 8:42 am
After testing, I am more confused.
I created a table with 8 columns of various data types, and populated it with random data. I then ran three INSERT INTO statements. The first is into a heap and used TABLOCK, the second one was into a table with one non-clustered index, and the third was into a heap where there was an additional IDENTITY column.
INSERT INTO table 1: took 5 seconds, and transaction log went from 136 pages to 2808 pages.
INSERT INTO table 2: took 59 seconds, and transaction log went from 136 pages to 442,816 pages
INSERT INTO table 3: took 10 seconds, and transaction log went from 136 pages to 30,680 pages
So it appears to be somewhere in between. Thoughts?
CREATE DATABASE InsertTest
GO
USE InsertTest
GO
CREATE TABLE SourceOfData
(
Col1 BIGINT ,
Col2 VARCHAR(255) ,
Col3 DECIMAL(5, 2) ,
Col4 DATETIME2(0) ,
Col5 BINARY(24) ,
Col6 CHAR(11) ,
Col7 BIT ,
Col8 INT
)
--I have inserted 5 MM rows of random data here.
--CHECK DB LOG Size, size is # of pages
SELECT D.name AS DatabaseName ,
MF.name AS [Logical Name] ,
MF.size
FROM sys.master_files AS MF
JOIN sys.databases AS D ON MF.database_id = D.database_id
WHERE D.database_id = DB_ID('InsertTest')
AND type_desc = 'LOG'
DBCC SHRINKFILE (InsertTest_log, 1);
GO
--create a table to verify that the INSERT..into with (TABLOCK) is minmally logged
CREATE TABLE DestWithNoIDENT
(
Col1 BIGINT ,
Col2 VARCHAR(255) ,
Col3 DECIMAL(5, 2) ,
Col4 DATETIME2(0) ,
Col5 BINARY(24) ,
Col6 CHAR(11) ,
Col7 BIT ,
Col8 INT
)
INSERT INTO DestWithNoIDENT WITH ( TABLOCK )
( Col1 , Col2 , Col3 , Col4 , Col5 , Col6 , Col7 , Col8 )
SELECT Col1 , Col2 , Col3 , Col4 , Col5 , Col6 , Col7 , Col8
FROM SourceOfData
--Took 5 seconds to run
--CHECK DB LOG Size, size is # of pages
SELECT D.name AS DatabaseName ,
MF.name AS [Logical Name] ,
MF.size
FROM sys.master_files AS MF
JOIN sys.databases AS D ON MF.database_id = D.database_id
WHERE D.database_id = DB_ID('InsertTest')
AND type_desc = 'LOG'
--Shows 2808 data pages.
DBCC SHRINKFILE (InsertTest_log, 1);
GO
--and back to 136...
--create a table to verify that the INSERT..into with a clustered index and a is minmally logged
CREATE TABLE DestWithNoIDENTandIndex
(
Col1 BIGINT ,
Col2 VARCHAR(255) ,
Col3 DECIMAL(5, 2) ,
Col4 DATETIME2(0) ,
Col5 BINARY(24) ,
Col6 CHAR(11) ,
Col7 BIT ,
Col8 INT
)
CREATE NONCLUSTERED INDEX [testIndex] ON [dbo].[DestWithNoIDENTandIndex]
(
[Col1] ASC
)
GO
INSERT INTO DestWithNoIDENTandIndex
( Col1 , Col2 , Col3 , Col4 , Col5 , Col6 , Col7 , Col8)
SELECT Col1 , Col2 , Col3 , Col4 , Col5 , Col6 , Col7 , Col8
FROM SourceOfData
--took 59 seconds to run
--CHECK DB LOG Size, size is # of pages
SELECT D.name AS DatabaseName ,
MF.name AS [Logical Name] ,
MF.size
FROM sys.master_files AS MF
JOIN sys.databases AS D ON MF.database_id = D.database_id
WHERE D.database_id = DB_ID('InsertTest')
AND type_desc = 'LOG'
--Shows 442816 data pages.
DBCC SHRINKFILE (InsertTest_log, 1);
GO
--and back to 136...
CREATE TABLE DestWithIDENT
(Col0 INT IDENTITY(1,1),
Col1 BIGINT ,
Col2 VARCHAR(255) ,
Col3 DECIMAL(5, 2) ,
Col4 DATETIME2(0) ,
Col5 BINARY(24) ,
Col6 CHAR(11) ,
Col7 BIT ,
Col8 INT
)
INSERT INTO DestWithIDENT WITH ( TABLOCK )
( Col1 , Col2 , Col3 , Col4 , Col5 , Col6 , Col7 , Col8 )
SELECT Col1 , Col2 , Col3 , Col4 , Col5 , Col6 , Col7 , Col8
FROM SourceOfData
--took 10 seconds
SELECT D.name AS DatabaseName ,
MF.name AS [Logical Name] ,
MF.size
FROM sys.master_files AS MF
JOIN sys.databases AS D ON MF.database_id = D.database_id
WHERE D.database_id = DB_ID('InsertTest')
AND type_desc = 'LOG'
--We have 30680 datapages. So, somewhere in between...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply