SQL server row locking, blocking, and escalations

Dear Sir,

any script to find out the locks escalations for MS SQL server? I want to measure it from time to time to see if the lock escalation is increasing or not !

does SQL profiler has this kind of checking items?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:
Hi,

There is one good link telling what Lock escalation is how to detect and how to resolve.

Hope you like it.

http://aboutsqlserver.com/2012/01/11/locking-in-microsoft-sql-server-part-12-lock-escalation/
marrowyungSenior Technical architecture (Data)Author Commented:
you are very proactive ! let me take a look
marrowyungSenior Technical architecture (Data)Author Commented:
one thing, if lock escalation is increasing, what should we do ? it just implies more RAM is needed, right?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Vitor MontalvãoMSSQL Senior EngineerCommented:
if lock escalation is increasing
What you mean with that? The number of lock escalations? If so, isn't a RAM issue but a query performance issue.
marrowyungSenior Technical architecture (Data)Author Commented:
"What you mean with that? The number of lock escalations? "

yes, the number of lock escalations increase.

but from that article, it seems that if we set it to auto, the SQL server once escalate the lock from row lock to table lock, it use less RAM.

"If so, isn't a RAM issue but a query performance issue. "

turning using index for example, right? let the query finish asap.
marrowyungSenior Technical architecture (Data)Author Commented:
from my point of view, what should I propose?

1) increase RAM as ROW lock use RAM.
2) turn query
3) add or increase tempdb file size ?
4) add CPU
5) nothing has to be done ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Before going to any increase of resources like RAM or CPU you always need to check if your databases are tuned otherwise you'll expend money with no reason.
Lock escalation only occurs when a query is running too long (basically more than 20 seconds).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"Lock escalation only occurs when a query is running too long (basically more than 20 seconds). "

if this is a trigger point for us to start tuning for our query, what should be the threshold value for that?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, I've confused with deadlock. A deadlock may occurs after 20 seconds. Lock escalation uses the following thresholds:

•A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.

•A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.

•The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.



You can confirm this and much more in this MSDN article.
marrowyungSenior Technical architecture (Data)Author Commented:
that link only apply for 2k8 R2 but not for SQL 2014/16, right? I can't see the option .

"•The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.
"

what is that sorry ? or you copy and paste portion of it for me ?

any query to check those figure out ? the query in my link already enough?

http://aboutsqlserver.com/2012/01/11/locking-in-microsoft-sql-server-part-12-lock-escalation/
Vitor MontalvãoMSSQL Senior EngineerCommented:
that link only apply for 2k8 R2 but not for SQL 2014/16, right? I
Should be the same. I don't think they changed it.


or you copy and paste portion of it for me ?
Yes, it was a copy from the article.


any query to check those figure out ? the query in my link already enough?
Problem isn't with query but the number of the locks. That link reproduces a lock escalation based in the incrementation of the number of the locks, so should  be good for testing.
marrowyungSenior Technical architecture (Data)Author Commented:
"Problem isn't with query but the number of the locks. "

I mean query to check out number of locks right now, increasing or not.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.