DaveWentzel.com            All Things Data

Lock Escalation

Understanding lock escalation can help you design more performant ETL data loads (or really design any SQL data access in a more performant manner).  I mentioned briefly here some information on lock escalation.  Here I want to cover some additional details.  

You can google "SQL Server Lock Escalation" yourself if you want more details, but the quick definition is that many finer-grained locks will convert to fewer coarser-grained locks when a given threshold is reached.  For instance, if your process takes about 5000 row-level locks the locking engine will simply convert those locks to a coarser-grained lock (maybe a table lock) which will require holding less lock memory.  The downside is that the coarser-grained lock may cause blocking and concurrency issues where a row-level lock would not.  

Understand BU Locks and Lock Escalation With ETL
You really want to take BU locks, not X locks, when doing ETL loads.  This is the only way you can multi-thread your table load, which is a Best Practice in my opinion.  
Most people don't even know what a BU lock is (it is a Bulk Update lock).  
The more X locks at the row level you have, the more chance you get a lock escalation which will single-thread your loads.  This happens, again, generally at 5000 locks, unless you set BATCHSIZE to 5000.  Using trace flag 1211 disables all lock escalation.  In 2008 you can disable lock escalation for your table (ALTER TABLE ... SET LOCK_ESCALATION=DISABLE).



Add new comment