Hopefully you can help me with the following - I haven't yet found a resource - online or hardcopy, that can help me with this.
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.
I'm still researching this one. This is going to take time and we may not be able to provide an answer. You are delving into some extremely technical and very specific information about the exact internal operation of the SQL Server engine. Can the exact lock escalation and the criteria used to determine it be identified? Absolutely otherwise it would not work. However, I know this is an area where the specificity of the data you are after hits head on with proprietary algorithms, and it is highly probable that not only can they not be explained due to the proprietary information, but there is most likely not even an API that you could use to get at that data. If I can get that information, and it is publicly disclosable, I will post it. I would be very interested in exactly what you are tracking and the business need behind it, since I have never encountered a situation where this level of detail is required.
Database design expert Pat Phelan had this to add:
The lock escalation question can't be answered in versions prior to 7.0. The only way to trace lock escalation was to enable diagnostic messages within the server (using a trace flag), then wade through mountains of log entries looking for the few pieces of information that you need. Many times the act of writing that much log slowed the server down to the point that you couldn't reproduce the problem!
In MS SQL 7.0 and later, the SQL Profiler makes it easy to follow locking activity. You can monitor the creation and escalation of locks graphically using the Profiler, or via the sp_trace% system stored procedures. If you elect to use the stored procedures, sp_trace_setevent is very important for watching locks and escalations.
The biggest problem with trying to predict locking behavior is that it requires extensive knowledge of the server and the load that the server is carrying. There are LOTS of factors that contribute to the engine making lock promotion decisions, many of which are hard or impossible to determine from outside the engine.
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Microsoft SQL Server Installation
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.