April 23, 2013 at 12:54 pm
Hi everyone. I used Brent Ozar's excellent sp_blitzindex script to discover the below issue. There is a link in the script to explain the output but I'm still a bit confused. Is it telling me that the index had to wait 11,40 times to get a page lock totaling 86 minutes since the last time SQL Server restarted? It has been almost a month since restart so does that means that 86 minutes of processing time has been lost? With an avg duration of 0, should I be concerned?
Thanks for reading.
Howard
Aggressive Indexes: Total lock wait time > 5 minutes (row + page)
dbo.OAD.JJ_032913_DTA1 (12): Page lock waits: 11,480; total duration: 86 minutes; avg duration: 0 seconds; Lock escalation attempts: 10,601; Actual Escalations: 7.
April 24, 2013 at 6:51 am
I would definitely look into the index because of the number lock escalation attempts. This means that SQL Server is having to take so many row or page locks that it is trying to get a table lock to keep lock resources under control. All of those failed attempts means you waited for a bit. To me this seems to indicate a lot of scans of the index and not seeks (not necessarily a bad thing) so I'd look at the index definition and the queries that use it, either there are a lot of queries that return a lot of rows or the index isn't defined well enough for the queries that use it (column order incorrect, i.e. first name, last name instead of last name, first name).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply