Empty Table: Insert into Table WITH (TABLOCK)

Question

We have a clustered index table which is empty. Table has around eg: 20 columns, 50 million rows, around (40GB).

Is it best practice to apply Insert With (TABLOCK) for empty tables such as this? The table is a datawarehouse table, and conducted during ETL hours. There are no concurrent users, and we are in simple recovery mode. Just curious, if so we can update our 100+ stored procedures.

Answer 1

Like many things database-related this option solves some problems but can cause others.

By using TABLOCK you will avoid lock escalation. By using TABLOCKX you may even avoid lock conversion. The downside is that other activity against that table must wait for this lock to be released. You say there is no concurrent activity, so that shouldn't be an actual problem in real life. Taking locks is quite cheap and fast, so I don't really think that would be a problem either.

So my answer is - what problem are you trying to solve? Will the work of changing 100+ stored procedures be repaid by the improvement you see?

• user172734: hopefully, if we get 10-20% improvement in ETL performance speed, that would benefit us, thanks
• Martin Smith: you should consider how this hint interacts with minimal logging
• xhr489: The reason for TABLOCK is mininal logging link: