Sql-server – Benefits of using WITH TABLOCK on an INSERT

insertperformancesql serversql-server-2012

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.

Best Answer

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

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?
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.

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.

Related Solutions

Sql-server – Why “Select * into targettable from sourcetable “ is faster than “insert into targettable select * from sourcetable

A couple of ideas/theories:

SELECT INTO... lets the RDBMS determine sort order based on order of your original table. If you insert into an existing table, there may be a sort needed to match a clustered or nonclustered index(es).

No Indexes - when you SELECT INTO... the RDBMS knows for certain there are no pre-existing indexes to update.

No Contention - since the table you are inserting into does not exist, SQL Server doesn't need to worry about row-level locking or contention handling. Nothing else can reference the table you create since it doesn't exist.

All that being said, there are other ways to insert into a table very quickly.

Make sure your clustered index keys match when possible. This means there is no on-the-fly sorting
Disable all non-clustered indexes. Self-explanatory.
Set recovery mode to simple and trace flag 610 to ON. Use the TABLOCK hint on your target table and NOLOCK hint on your source table.

For example, assume tablea and tableb have the same clustered index:

INSERT INTO TableB WITH (TABLOCK)
SELECT <Columns>
FROM TableA WITH (NOLOCK)

In my experience this is faster than using SELECT INTO... and then creating the clustered index afterwards. Please note this can also work on a table that already has data in it which is a much more useful scenario.

EDIT:

Here's a fantastically detailed whitepaper from MS for data load performance in Sql Server 2008.

Sql-server – Minimal Logging Conditions in SQL

A few of these numbers do not seem to match the table on the technet page.

There are small differences in the sizes of the log records generated in your tests, but these are due to other internal logging behaviours, not whether minimal logging is occurring or not.

A good definition of minimal logging is provided by Sunil Agarwal of the Storage Engine team:

Individual rows are not logged and only the changes to page allocation structures are logged

Any test where you see individual row changes logged (e.g. LOP_INSERT_ROWS) is not using minimal logging for the associated allocation unit. Some operations can be minimally logged with respect to one allocation unit (e.g. an index) and not minimally logged against another. Also, in some circumstances, inserts to existing pages may not be minimally-logged but changes to newly allocated page may be.

Most of the details can be found in a series of Storage Engine team blog posts:

One detail not explored there is that to be minimally-logged (in SQL Server 2008 or later) INSERT...SELECT changes to b-tree structures must have the DMLRequestSort query plan operator property set to true. This applies to the circumstances where the Data Loading Performance Guide shows 'Depends': the query plan must use wide (per-index) maintenance with DMLRequestSort=true.

I wrote more about this in Minimal Logging with INSERT…SELECT and Fast Load Context.

Best Answer

Related Solutions

Sql-server – Why “Select * into targettable from sourcetable “ is faster than “insert into targettable select * from sourcetable

Sql-server – Minimal Logging Conditions in SQL

Related Question