TABLOCK Hint can Negatively Impact SQL Server Concurrency

By: Mehdi Ghapanvari   |   Updated: 2023-11-13   |   Comments (2)   |   Related: More > Performance Tuning

Problem

Using the TABLOCK hint could improve bulk insert performance in some scenarios. However, it might negatively affect it in cases of concurrency. This article will provide examples of both scenarios to give insight into the use or non-use of the TABLOCK hint.

Solution

SQL Server implements minimally logged operations to support high-volume data loading scenarios. Minimally logged operations can be utilized only when the database is set to either bulk-logged or simple recovery mode. Minimally logged operations keep track of extent allocations and metadata changes only, unlike fully logged operations, which use the transaction log to keep track of every row change. The reduced logging overhead can significantly improve the performance of the operation. Minimal logging requires that the target table meets some conditions. One of them is using the TABLOCK hint.

However, it's important to consider the potential blocking and concurrency implications when using this hint. In this article, I'll illustrate two scenarios to show how using the TABLOCK hint affects performance and concurrency in INSERT...SELECT statements. One demonstrates how using the TABLOCK hint improves performance, while the other shows its potential to reduce concurrency.

Set Up Test Environment

For this demonstration, I will utilize the StackOverflow database and SQL Server 2022.

Use the code below to set the recovery model for the database to simple:

Use StackOverflow
GO
Alter Database Current Set Recovery Simple
GO

Create a table with identical columns to 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

The created table is a heap table with no indexes. Run a Checkpoint command.

CHECKPOINT
GO

During checkpoints in the SIMPLE recovery model, inactive VLFs in the log are identified and marked as such. The transaction log contains valuable information regarding the activities in your database, which can be accessed using the fn_dblog function.

After executing the checkpoint command, the log records count is three, as depicted in the image below:

To access the transaction log, use the following code:

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

It is important to note that the table is empty, and we did not use the TABLOCK hint. Additionally, there are no indexes implemented on the table. The following T-SQL statement loads approximately one million records into the table:

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

The query ran for 27 seconds on my laptop. To obtain the quantity of log records, use the following code:

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

The illustration below displays more than one million generated log records.

Rerun the test, but this time, add the TABLOCK hint to the INSERT...SELECT statement.

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 image below shows that the number of log records has decreased to approximately 8500.

Additionally, the query execution time was reduced to seven seconds. We conducted tests on an empty table without any indexes. It is worth noting that performing a bulk insert on an empty clustered table, for example, inserting several thousand records using the INSERT...SELECT command and the TABLOCK hint results in minimal logging and improved performance. According to Microsoft documentation, when you use the INSERT...SELECT method, you do not need to specify the ORDER hint, but the rows must be in the same order as the clustered index. If using BULK INSERT, the order hint must be used. There are many cases, like empty clustered tables with one or more non-clustered indexes, and you need to test them. Applying the TABLOCK hint to a pre-existing populated clustered table does not reduce the volume of log records generated during an INSERT...SELECT operation. Indeed, the number of log records produced in both situations (using or not using the hint) is nearly the same. The decision on whether to use the hint depends on the specific requirements and circumstances of the scenario. Using the TABLOCK hint can reduce concurrency. In the subsequent section, I will demonstrate this aspect.

Testing with a Clustered Index

Let's assume we have a clustered table that has data. Since we already have a heap table with one million records, let's add a clustered primary key to it:

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

The following code initiates a transaction and inserts approximately 2000 records into the destination table without either committing or rolling back the transaction:

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

In a separate session, let's insert approximately 2000 records while keeping the transaction open:

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

You can see the result code executing in the image below:

The insertion of records was successful without any blocks to the operation.

Testing with the TABLOCK Hint

Now, let's rerun the test, this time utilizing the TABLOCK hint. First, commit the open transaction:

Commit Transaction
GO

Once more, initiate a transaction and insert roughly 2000 records into the destination table without committing or rolling back the transaction. Furthermore, include a TABLOCK hint 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

In a separate session, insert approximately 2000 records while keeping the transaction open:

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

Use the simple code below to detect blocking:

Select session_id, command, blocking_session_id From sys.dm_exec_requests
Where session_id > 50 And database_id = db_id ()
GO

As depicted in the image below, it is evident that the second insertion encountered a blocking situation.

Summary

In some cases, using the TABLOCK hint is critical to achieving minimal logging. However, it's important to recognize that there are scenarios where using the TABLOCK hint may not be beneficial and could potentially reduce concurrency. In my opinion, the most effective approach is to perform thorough testing, as the outcome will depend on various conditions and factors specific to your environment. If testing is not feasible, consulting Microsoft documentation to weigh all aspects is advised.

Next Steps

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

About the author

Mehdi Ghapanvari is an SQL Server database administrator with 6+ years of experience. His main area of expertise is improving database performance. He is skilled at managing high-performance servers that can handle several terabytes of data and hundreds of queries per second, ensuring their availability and reliability.

View all my tips