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.