June 9, 2009 at 3:41 pm
Hi, i have many complains of having lock escalations in my DB, i read in an article that the best way to capture this is to run profiler and choose the lock escalation event. Now when running the profiler i do see some cases where the lock escalation is in the event class column and in the mode i see 3-S. That means i do have lock escalation right? is there a way to see what storedprocedure, query, and tables are being affected?
Also, do you guys know any other way (maybe by a DMV) where i can find this information ?
June 10, 2009 at 7:16 am
sys.dm_tran_locks is going to have the locking information, although I'm not sure if it will show lock escalation as it occurs. Once you're in there, you can get the sessionID for the escalation and use sys.dm_exec_connections or sys.dm_exec_sessions for general information. But to really drill down, you go to sys.dm_exec_requests. Here you can get the sql_handle which you can use to get the execution plan and the sql text.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 11, 2009 at 7:54 am
okay, but the only way to see that is happening at the moment it is , is to run the profiler with the lock escalation event correct?
when i was running the profiler with this event, my performance was terrible so i had to stop it. :crazy:
June 11, 2009 at 8:23 am
Well, you can also see if a lock has an outstanding convert request inside sys.dm_tran_locks. That's an indication of lock escalation. Once the lock is escelated though, you won't see it.
Did you set up profiler to collect information through the gui or are you running a scripted server-side trace. If the former, I'd change it to the latter.
You should be able to capture lock escalation using a server-side trace without it killing performance. Did you also collect a lot of other information?
Also, if you know you're see excessive lock escelation, it's time to drill down on your queries to understand what's going on. Have you read this white paper from Microsoft yet?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 23, 2009 at 4:43 pm
thank you!
June 23, 2009 at 5:58 pm
I tend to look at sys.dm_db_index_operation_stats, to see how many lock promotions were attempted as well as how many succeeded:
SELECTTOP (10)
OBJECT_NAME(object_id, database_id) object_nm,
index_id,
partition_number,
index_lock_promotion_attempt_count,
index_lock_promotion_count
FROMsys.dm_db_index_operational_stats
(DB_ID(), NULL, NULL, NULL)
ORDER BY
index_lock_promotion_count DESC;
see the useful SQL Server Premier Field Engineers' Blog more more information.
Paul
June 24, 2009 at 3:33 am
You say you are getting many complaints of lock escalation.
How do the people who complain know that the problem is lock escalation? If they do not have access to SQL Profiler or are not getting alerts from some monitoring software, then they have no idea what is causing the problem.
My advice is to ask the users who complain what symptoms they are getting, and start your troubleshooting from there. The problem could be lock escalation, but is much more likely to be something else. If you spend your time looking at lock escalation and the problem is something else, you will not fix the user's problem.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 24, 2009 at 3:47 am
Hey Ed,
Maybe we read the original post differently, but:
DBA (6/9/2009)
Hi, i have many complains of having lock escalations in my DB
Notice the word 'complains' not 'complaints' - I took this to be a language thing, so I think 'DBA' is saying he has many problems with lock escalation. I may be wrong!
Paul
June 24, 2009 at 5:40 am
Paul White (6/24/2009)
Hey Ed,Maybe we read the original post differently, but:
DBA (6/9/2009)
Hi, i have many complains of having lock escalations in my DBNotice the word 'complains' not 'complaints' - I took this to be a language thing, so I think 'DBA' is saying he has many problems with lock escalation. I may be wrong!
Paul
I read it the same way for what it's worth.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 24, 2009 at 5:57 am
Grant Fritchey (6/24/2009)
I read it the same way for what it's worth.
I think that makes $0.04 in total 😉
P.S. Enjoyed your article today Grant
June 24, 2009 at 6:20 am
To some extent it does not matter how the OP was interpreted.
To me the key thing is that if the person who said they had lock escalation problems did not have access to anything that definitely showed lock escalation, then they were guessing what the cause was.
In this situation I would start with the symptoms and not take for granted the problem was caused by lock escalation.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 24, 2009 at 6:51 am
EdVassie (6/24/2009)
To some extent it does not matter how the OP was interpreted.
That's an interesting point of view 🙂
EdVassie (6/24/2009)
To me the key thing is that if the person who said they had lock escalation problems did not have access to anything that definitely showed lock escalation, then they were guessing what the cause was.
Would you suggest using Profiler to look for instances of lock escalation? Because that's what the OP did (see post #1).
Paul
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply