SQL Bulk Inserts with TABLOCK Performance Considerations

Problem

Does bulk insert performance improve when you use the TABLOCK hint? In some cases, YES! Let’s take a look at this in action to see how this hint could improve insert performance when using SQL bulk insert with TABLOCK.

Solution

This tip will demonstrate that using the TABLOCK hint can reduce concurrency but should be avoided when multiple sessions are performing bulk-insert operations concurrently.

Overview of locking

Below is a query to access a user’s information (ID 1) via the table Users, which has a clustered primary on the ID column:

SELECT * FROM Users WHERE ID = 1;

During query execution:

• SQL Server applies a shared lock on the row with ID 1.
• Only one row is locked.
• Other users can update data.

Let’s add a TABLOCK hint:

SELECT * FROM Users WITH (TABLOCK) WHERE ID = 1;

During query execution:

• SQL Server applies a shared lock to the entire table.
• The whole table is locked.
• Other users cannot update data because they will be blocked.

Using TABLOCK Hint Example

To test the scenario, use the following scripts:

Script 1 in one query window.

BEGIN TRANSACTION;
SELECT * FROM Users WITH (TABLOCK, HOLDLOCK) WHERE ID = 1;

Script 2 in a different query window:

DELETE FROM Users WHERE ID = 1000000;

How Does Using TABLOCK Improve Bulk Insert Performance?

When you insert a batch of rows (a million rows) into an empty heap table, SQL Server writes a log record to the transaction log file for each row inserted. Consider the following script:

INSERT INTO B (cl1, cl2, ... cln)
SELECT cl1, cl2, ... cln FROM A;

This is a bulk-insert operation. Writing a log record to the transaction log file for each row inserted into table B during the bulk insert operation will cause the transaction log file to fill up quickly. This approach increases disk I/O and reduces performance.

If you add the TABLOCK hint to your query SQL Server writes only the information that is essential for the operation, such as extent allocations and metadata changes into the transaction log file.

INSERT INTO B (cl1, cl2, ... cln) WITH (TABLOCK)
SELECT cl1, cl2, ... cln FROM A;

This is called minimal logging, which reduces the number of log records and improves bulk insert performance.

To demonstrate the TABLOCK hint abilities, this tip will provide two examples:

1. Improving the bulk-insert operation performance by allowing fewer log records.
2. Decreasing performance with its blocking element.

Set Up Test Environment

For this demonstration, I will use the open-source Stack Overflow database. Be sure to download the database and set the database recovery model to simple to achieve the minimum logging.

Use StackOverflow
GO
 
Alter Database Current Set Recovery Simple
GO

This database includes a table named Users, which includes user details. Let’s create a heap table called Users_Destination with identical columns as the Users table:

Drop Table If Exists [dbo].[Users_Destination]
GO
 
CREATE TABLE [dbo].[Users_Destination]
(
 [Id] [int] NOT NULL IDENTITY(1, 1),
 [AboutMe] [nvarchar](max) NULL,
 [Age] [int] NULL,
 [CreationDate] [datetime] NOT NULL,
 [DisplayName] [nvarchar](40) NOT NULL,
 [DownVotes] [int] NOT NULL,
 [EmailHash] [nvarchar](40) NULL,
 [LastAccessDate] [datetime] NOT NULL,
 [Location] [nvarchar](100) NULL,
 [Reputation] [int] NOT NULL,
 [UpVotes] [int] NOT NULL,
 [Views] [int] NOT NULL,
 [WebsiteUrl] [nvarchar](200) NULL,
 [AccountId] [int] NULL
)
GO

Next, let’s perform a checkpoint command on the heap table. Remember, we set the database recovery model as simple. This means a checkpoint will identify and mark inactive VLFs.

CHECKPOINT
GO

To see the records in the active log file, use the sys.fn_dblog function:

Select * From sys.fn_dblog(Null, Null)
GO

The query results show three log records.

Let’s load the Users_Destination heap table, which has approximately 1 million rows and no indexes. Please note that I’m not using the TABLOCK hint. Also, just like the earlier example, the database recovery model is again set to simple.

Insert Into [dbo].[Users_Destination] 
 (
  AboutMe,
  Age,
  CreationDate,
  DisplayName,
  DownVotes,
  EmailHash,
  LastAccessDate,
  Location,
  Reputation,
  UpVotes,
  Views,
  WebsiteUrl,
  AccountId
 )
Select 
  AboutMe,
  Age,
  CreationDate,
  DisplayName,
  DownVotes,
  EmailHash,
  LastAccessDate,
  Location,
  Reputation,
  UpVotes,
  Views,
  WebsiteUrl,
  AccountId
From Users Where Id < 1600000
GO

It completed in 27 seconds. Now, let’s see how many log records were produced by this operation:

SELECT AllocUnitName, Count(*) As LogRecordCount
FROM sys.fn_dblog(NULL,NULL)
WHERE AllocUnitName LIKE 'dbo.Users_Destination%'
GROUP BY AllocUnitName 
GO

As you can see from the image below, the query created more than 1 million log records.

What an interesting outcome: over 1 million log records with a query that took almost 30 seconds.

Let’s see what happens when we use the TABLOCK hint.

Drop Table If Exists [dbo].[Users_Destination]
GO
 
CREATE TABLE [dbo].[Users_Destination]
(
 [Id] [int] NOT NULL IDENTITY(1, 1),
 [AboutMe] [nvarchar](max) NULL,
 [Age] [int] NULL,
 [CreationDate] [datetime] NOT NULL,
 [DisplayName] [nvarchar](40) NOT NULL,
 [DownVotes] [int] NOT NULL,
 [EmailHash] [nvarchar](40) NULL,
 [LastAccessDate] [datetime] NOT NULL,
 [Location] [nvarchar](100) NULL,
 [Reputation] [int] NOT NULL,
 [UpVotes] [int] NOT NULL,
 [Views] [int] NOT NULL,
 [WebsiteUrl] [nvarchar](200) NULL,
 [AccountId] [int] NULL
)
GO
 
CHECKPOINT
GO
 
Insert Into [dbo].[Users_Destination] With (Tablock)
 (
  AboutMe,
  Age,
  CreationDate,
  DisplayName,
  DownVotes,
  EmailHash,
  LastAccessDate,
  Location,
  Reputation,
  UpVotes,
  Views,
  WebsiteUrl,
  AccountId
 )
Select 
  AboutMe,
  Age,
  CreationDate,
  DisplayName,
  DownVotes,
  EmailHash,
  LastAccessDate,
  Location,
  Reputation,
  UpVotes,
  Views,
  WebsiteUrl,
  AccountId
From Users Where Id < 1600000
GO
 
SELECT AllocUnitName, Count(*) As LogRecordCount
FROM sys.fn_dblog(NULL,NULL)
WHERE AllocUnitName LIKE 'dbo.Users_Destination%'
GROUP BY AllocUnitName 
GO

The query execution time was reduced to 7 seconds, and the following image shows that the number of log records created was only 8577.

This example shows we can achieve minimal logging when the TABLOCK hint is used, improving the bulk-insert operation performance. To learn more about this issue, check out the following article: Prerequisites for minimal logging in bulk import.

Using TABLOCK and Concurrency

Using the TABLOCK hint was beneficial for the previous example. However, many know that this hint tends to lower concurrency. My second example will demonstrate this issue.

In a production environment, there is a clustered table where various bulk-insert operations are performed. Let’s use the Users-Destination heap table (1 million+ rows) from the previous example and add a clustered primary key.

Alter Table [dbo].[Users_Destination] ADD Constraint PK_Users_Destination Primary Key Clustered (Id) 
GO

Will adding the TABLOCK hint reduce concurrency? Let’s see. Use the following script to add about 2000 rows within a transaction into the Users_Destination table. Note: The transaction will not be committed or rolled back.

Begin Transaction
Insert Into [dbo].[Users_Destination] 
Select 
  AboutMe,
  Age,
  CreationDate,
  DisplayName,
  DownVotes,
  EmailHash,
  LastAccessDate,
  Location,
  Reputation,
  UpVotes,
  Views,
  WebsiteUrl,
  AccountId
From Users Where Id > 1600000 And Id < 1602000
GO

Now, while the transaction in the first session is working, let’s add another 2000 rows from a different query window.

Insert Into [dbo].[Users_Destination] 
Select 
  AboutMe,
  Age,
  CreationDate,
  DisplayName,
  DownVotes,
  EmailHash,
  LastAccessDate,
  Location,
  Reputation,
  UpVotes,
  Views,
  WebsiteUrl,
  AccountId
From Users Where Id > 1602000 And Id < 1604000
GO

The image below shows that the row insert was successful, i.e., the active transaction did not block the bulk-insert operation.

Let’s run the test again, but this time using the TABLOCK hint. Begin by committing the active transaction:

Commit Transaction
GO

The next script starts a transaction that adds about 2000 rows to the Users_Destination table without committing or rolling back the transaction. However, this time, the TABLOCK hint was included in the INSERT…SELECT statement.

Begin Transaction
Insert Into [dbo].[Users_Destination] With (Tablock)
Select 
  AboutMe,
  Age,
  CreationDate,
  DisplayName,
  DownVotes,
  EmailHash,
  LastAccessDate,
  Location,
  Reputation,
  UpVotes,
  Views,
  WebsiteUrl,
  AccountId
From Users Where Id > 2000000 And Id < 2002000
GO

What will happen if I try to perform a bulk-insert operation in another session? Let’s do it.

Insert Into [dbo].[Users_Destination]
Select 
  AboutMe,
  Age,
  CreationDate,
  DisplayName,
  DownVotes,
  EmailHash,
  LastAccessDate,
  Location,
  Reputation,
  UpVotes,
  Views,
  WebsiteUrl,
  AccountId
From Users Where Id > 2002000 And Id < 2004000
GO

Unfortunately, you can see in the image below that a blocking issue arose because of the TABLOCK hint in the INSERT…SELECT statement during the bulk-insert operation in the first session.

To detect blocking, you can use the following code:

SELECT session_id, command, blocking_session_id 
FROM sys.dm_exec_requests 
WHERE session_id > 50 And database_id = db_id ()
GO

Summary

If you want to use the TABLOCK hint, test your situation before choosing. Remember that using this hint can significantly improve query performance, but it can also block users and reduce concurrency in some cases.

Next Steps

• Minimally Logging Bulk Load Inserts into SQL Server
• What are the recovery models and how does the log use them?