Hi Friends,
This is my 17th blog on SQL Server Trace Flag 1224 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.
We all know that by default, SQL server require locks to perform any kind of operation. That lock can be Shared, Update, Exclusive, Intent Shared etc. Now Lock escalation can be defined as the process of converting many low levels of locks (like row or page) to few high levels of locks (like table).
Let me take an example to explain, we have a table with 20000 rows. Obviously these rows are stored on 8K pages. Now I want to update 12000 of rows by TSQL statement with where clause. Then without lock escalation during that update operation, SQL Server will take many locks like (12000 X row locks + IX Page locks + IX Object lock). Keep in mind that locks also consume resources like memory. Now think about the same situation with lock escalation, where all row and page level locks will escalate to object level lock. That means number of locks will be reduced to very few. So the resource consumption will be very low here as compared to without lock escalation.
Lock escalation take places based on some conditions or you can say on thresholds. If you want to know that then you can click here.
Till now we discussed about the benefit of lock escalation but there is also one drawback of lock escalation. If we will use high level of locks rather than low level of locks then concurrency will be reduced. You may see some kind of blocking in the system due to lock escalation process. If this is really a problem in your scenario then you can use trace flag 1224. This trace flag will disabled the lock escalation that happens due to the number of locks.
PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.
HAPPY LEARNING!
Regards:
Prince Kumar Rastogi
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook
