Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2002-01-23 : 05:04:36
|
| Hi all,I need to determine when SQL server does lock escalation. I currently have a stored procedure that determines who is locking which resources by joining data from sysprocesses and syslockinfo, with a bit of data from the sysobjects table. My problem is that, afaik, I report that the duration of a lock being held isn't accurate, in the sense that I report the duration from when a process acquired a lock on an object until the time of reporting, but I can't determine at which points during that period the lock escalates from a RID to a PAGE etc. So I currently report, for example, that a table has been locked for the duration, if a process has escalated to table locks, for the entire duration. While this is still helpful for us in terms of identifying who has what, and when they last did anything, I want to see if I can track the actual lock escalation events, so we can determine how quickly certain applications are getting table locks.BOL doesn't seem to be very helpful on the subject - other than to say the SQL server will decide to escalate the locks. I need to understand the criteria it uses, or else be able to track the actual escalation event - outside of profiler.Any Thoughts ? |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-02-20 : 04:08:35
|
quote:
I don't believe you can capture the event of lock escalation. To explain in a bit more detail:"lock escalation" is the term used to describe the process oftrading in locks for one or more locks of a higher granularity to minimize the number of locks acquired for a transaction.
Do you mean that the current entry in syslockinfo is updated, or a new entry is created?quote:
In SQL Server 7.0, lock escalation converts many individual row or page locks to a table lock; escalation never converts row locks into page locks. This action is initiated in general by twoconditions:
I had the impression from BOL that lock escalation went through the varying levels - i.e. ROW to EXTENT to PAGE to TABLE - do I understand you to be saying that x ROW locks are converted to a table lock ?quote:
removed by graz - explanation coming...
This does help. Where did you find these numbers ? - also - is it 765 or 768 (768 being a multiple of 256) ?quote:
removed by graz - explanation coming...
Still trying to digest this. If the total number of lock's, regardless of resource, is greater than 2500 ? For arguments sake, 2500 rows locked on 2500 different tables ? ( I know it's a silly example), but none have more than a sinhlge lock per table - then no escalation. But if 1732 row locks on 1732 different tables, and 768 row locks on another table ( total locks = 2500, and number of locks for a resources >= 765 ), then locks escalation would occur ? Only on the table that is being locked, I assume?Thanx for yours response - hopefully you can help explain the pieces I'm missing. PS where did you find this info - I got pretty much nowhere on any searches on lock escalation , threshold etc.ciao & TIA |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-20 : 13:17:48
|
| Sorry for the confusion, I realized after reading this reply, I had posted some MSONLY references, which have been removed.Suffice it to say, I don't know of any way to capture the escalation event, outside of monitoring a profiler trace of sysprocesses, and capturing a change of the original record. Sorry again for the mix up.-Chad |
|
|
|
MuadDBA
628 Posts |
Posted - 2002-02-21 : 16:06:34
|
| Curious....MSONLY references? Is this stuff about the software that Microsoft doesn't want people to know? Are they afraid someone is going to create a better product and integrate it into their operating system or something? |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-21 : 16:13:48
|
| These are references just like any other company in the world has. Proprietary information that is not availble to public. If you think this is unique to MS, you are extremely naive.-Chad |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-02-26 : 10:52:37
|
| CHAD - thank you for the responses - I have made what notes I could. I can appreciate that MS would need to keep certain information proprietary - though in this case it does make monitoring more ... difficult.I do think that MS does keep some cards so close to it's chest as to force us to simply trust SQL to know what is doing - and having come from a mainframe environment, I'm not always that keen on that - but I guess that we never live in an ideal world ...Thanx again for the response !! |
|
|
|
|
|
|
|
|
Lock escalation