Hi Friends,
This is my 19th blog on SQL Server Trace Flag 1211 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.
We all know about locks and lock escalation process in SQL Server. 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.
One drawback of lock escalation is, 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.
As per the above link, threshold for lock escalation is based on number of locks or memory pressure. In one of my previous blog post on sql server trace flag 1224, I describe that you can use trace flag 1224 to prevent lock escalation, which are occurring due to number of lock threshold.
Now if you want to disable the lock escalation in both thresholds scenarios: number of locks as well as memory pressure. Then you can use trace flag 1211 to disable the lock escalation process in both cases. If you are going to use this trace flag then think about it one more time because it may generate huge number of locks even if you are running under memory pressure. You may also face Out of lock situation.
At last we can say that trace flag 1224 is a subset of trace flag 1211. If you will enable both the trace flags then TF 1211 will take precedence over TF 1224.
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
