sql server trace flag 1211

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

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published. Required fields are marked *