2

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.

0

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?

|improve this answer|||||
  • hopefully, if we get 10-20% improvement in ETL performance speed, that would benefit us, thanks – user172734 Feb 20 '19 at 6:01
  • you should consider how this hint interacts with minimal logging – Martin Smith Feb 20 '19 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy