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.
1 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?
-
hopefully, if we get 10-20% improvement in ETL performance speed, that would benefit us, thanksuser172734– user1727342019年02月20日 06:01:12 +00:00Commented Feb 20, 2019 at 6:01
-
1you should consider how this hint interacts with minimal loggingMartin Smith– Martin Smith2019年02月20日 08:28:30 +00:00Commented Feb 20, 2019 at 8:28
-
1