Benefits of using WITH TABLOCK on an INSERT

Question

Under some circumstances, doing an INSERT INTO <tablename> (WITH TABLOCK) will be faster due to minimal logging. Those circumstances include having the database in the BULK_LOGGED recovery model.

Is there any other potential performance benefit to using WITH TABLOCK on an INSERT on an empty table when the database (tempdb) is using the SIMPLE recovery model?

I'm working with SQL Server 2012 Standard Edition.

My use case is for creating and then immediately populating a temp table within a stored procedure using an INSERT...SELECT, which could contain as many as a few million rows. I try to avoid that kind of tempdb abuse, but it is sometimes needed.

I'm trying to build a case to require TABLOCK. It doesn't seem like it would hurt anything, and might have a benefit. I'm trying to figure out if there is enough potential benefit to add it wherever throughout our code base, where I'm sure there is no other process that wants to write to the table.

I'm usually inserting into a newly created local temp table with a clustered PK, but do sometimes use a heap.

Answer 1

I know of a few benefits but they're mostly situational.

1. Using TABLOCK will reduce concurrency but will immediately take a table lock on the target table. As long as you can guarantee that just one session will insert into the table this will avoid unnecessary row or page locks and will prevent lock escalation. After all, if you are inserting so much data that you'll get lock escalation anyway why not do it upfront?
2. If you are inserting into an empty page-compressed heap without TABLOCK all of the pages will have row compression instead of page compression:

The newly inserted row is page-compressed:

• if new row goes to an existing page with page compression

• if the new row is inserted through BULK INSERT with TABLOCK

• if the new row is inserted through INSERT INTO ... (TABLOCK) SELECT FROM

Otherwise, the row is row-compressed.

3. In SQL Server 2016 the TABLOCK hint is required to get parallel insert into heaps, CCIs (Clustered Columnstore Indices), and local temp tables. There are lots of restrictions, some of which aren't documented. There can't be an IDENTITY column, the insert can't be done through an OUTPUT, etc.

See also The Data Loading Performance Guide