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

   

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

2 Comments on “Bulk Insert into Heap with TABLOCK”

Leave a Reply

Your email address will not be published.