3

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.

asked Feb 20, 2019 at 4:20

1 Answer 1

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?

answered Feb 20, 2019 at 5:56
3
  • hopefully, if we get 10-20% improvement in ETL performance speed, that would benefit us, thanks Commented Feb 20, 2019 at 6:01
  • 1
    you should consider how this hint interacts with minimal logging Commented Feb 20, 2019 at 8:28
  • 1
    The reason for TABLOCK is mininal logging link: Commented Jan 24, 2021 at 15:19

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.