Bulk Insert into Heap with TABLOCK

Hi Friends,

Today, I want to show you about How to achieve Bulk Insert into Heap with minimal logging. There may be the situation where you want to create a table (Heap) and load a huge amount of data into that table with few select statements. I faced this situation where Database was in simple recovery model and I don’t want to change it. When we ran the insert into .. select statement to load the huge amount of data (approx. 100 million rows), transaction log started growing very fast because of the full logging of this operation. So I want to minimize the impact of this data load on transaction log. Let me show you this situation with AdventureWorksDW2012 database.

Right now the log usage for the database is:

Database Name Log Size (MB)      Log Space            Used (%)              Status

AdventureWorksDW2012              0.484375             86.79436             0

First I want to create a new heap structure with some data load (60398 rows only):

USE [AdventureWorksDW2012]
GO
SELECT [ProductKey]
      ,[OrderDateKey]
      ,[DueDateKey]
      ,[ShipDateKey]
      ,[CustomerKey]
      ,[PromotionKey]
      ,[CurrencyKey]
      ,[SalesTerritoryKey]
      ,[SalesOrderNumber]
      ,[SalesOrderLineNumber]
      ,[RevisionNumber]
      ,[OrderQuantity]
      ,[UnitPrice]
      ,[ExtendedAmount]
      ,[UnitPriceDiscountPct]
      ,[DiscountAmount]
      ,[ProductStandardCost]
      ,[TotalProductCost]
      ,[SalesAmount]
      ,[TaxAmt]
      ,[Freight]
      ,[CarrierTrackingNumber]
      ,[CustomerPONumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
  INTO [FactInternetSalesCopy]
  FROM [AdventureWorksDW2012].[dbo].[FactInternetSales]

After running above query my log usages is:

Database Name Log Size (MB)      Log Space Used (%)          Status

AdventureWorksDW2012              1.492188             48.16754             0

Now I am going to load the data again with Insert Into Statement:

USE [AdventureWorksDW2012]
GO
INSERT INTO [FactInternetSalesCopy]
SELECT [ProductKey]
      ,[OrderDateKey]
      ,[DueDateKey]
      ,[ShipDateKey]
      ,[CustomerKey]
      ,[PromotionKey]
      ,[CurrencyKey]
      ,[SalesTerritoryKey]
      ,[SalesOrderNumber]
      ,[SalesOrderLineNumber]
      ,[RevisionNumber]
      ,[OrderQuantity]
      ,[UnitPrice]
      ,[ExtendedAmount]
      ,[UnitPriceDiscountPct]
      ,[DiscountAmount]
      ,[ProductStandardCost]
      ,[TotalProductCost]
      ,[SalesAmount]
      ,[TaxAmt]
      ,[Freight]
      ,[CarrierTrackingNumber]
      ,[CustomerPONumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
  FROM [AdventureWorksDW2012].[dbo].[FactInternetSales]

Now Log usages for my database is:

Database Name Log Size (MB)      Log Space Used (%)          Status

AdventureWorksDW2012              31.67969             57.23181             0

Log usage now has been increased due to the logging of that transaction. You can change this fully logged behavior to minimally by using WITH (TABLOCK) option for the heap.

USE [AdventureWorksDW2012]
GO
INSERT INTO [FactInternetSalesCopy] WITH (TABLOCK)
SELECT [ProductKey]
      ,[OrderDateKey]
      ,[DueDateKey]
      ,[ShipDateKey]
      ,[CustomerKey]
      ,[PromotionKey]
      ,[CurrencyKey]
      ,[SalesTerritoryKey]
      ,[SalesOrderNumber]
      ,[SalesOrderLineNumber]
      ,[RevisionNumber]
      ,[OrderQuantity]
      ,[UnitPrice]
      ,[ExtendedAmount]
      ,[UnitPriceDiscountPct]
      ,[DiscountAmount]
      ,[ProductStandardCost]
      ,[TotalProductCost]
      ,[SalesAmount]
      ,[TaxAmt]
      ,[Freight]
      ,[CarrierTrackingNumber]
      ,[CustomerPONumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
  FROM [AdventureWorksDW2012].[dbo].[FactInternetSales]

Now if you will check the log usages then you will find out the impact here.

Database Name Log Size (MB)      Log Space Used (%)          Status

AdventureWorksDW2012              31.67969             9.516029             0

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook