Normal locking threshold?

Question

Normal locking threshold?

jevitts

Mr or Mrs. 500

Points: 543
More actions
December 10, 2015 at 11:52 am

#307000

Hello! I recently started at a new company, and they are having a few issues with locking and blocking on their Production database. At my previous company I knew the database like the back of my hand, so I knew what normal locking processes looked like for that database.
I tried researching this, but couldn't come up with an answer (and I know it's very subjective!) - approximately how long is "normal" locking and blocking? A few seconds? A minute?
The SQL Monitor alert thresholds for Blocked Processes are currently set at:
Low: 20 seconds
Medium: 40 seconds
High: 1 minute
The high alert is triggered at least a dozen, sometimes two dozen times a day. We recently had an entire module in the application become unresponsive because of continuously blocked processes that would not resolve. Required a service restart.
I've identified one major table involved in most of the blocked processes. I suspect readjusting the indexes may help alleviate the problem a bit.
Any insight/experience you can provide is appreciated!
Thanks,
Jessica
What would you attempt to do if you knew you could not fail? -Robert H. Schuller

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

In a properly tuned OLTP database, it should be milliseconds. An analytics system would probably take longer-lasting locks because it's doing larger queries. It's not about normal, it's about whether the blocking's causing a problem in your database and application.

We recently had an entire module in the application become unresponsive because of continuously blocked processes that would not resolve.

Which we have an answer to. 🙂

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jevitts Mr or Mrs. 500 Points: 543 More actions · Answer 2

I figured that was the case. Thanks for confirming!

I've definitely got my work cut out for me... the table in question has 55 indexes. :crazy:

Thanks,
Jessica
What would you attempt to do if you knew you could not fail? -Robert H. Schuller

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

jevitts (12/10/2015)
I figured that was the case. Thanks for confirming!
I've definitely got my work cut out for me... the table in question has 55 indexes. :crazy:

Ow.

The place to start with a new system are the wait statistics, sys.dm_os_wait_stats. You want to know what's causing the server to run slow. Yeah, the blocking and resource contention are issues, but where are those issues. That's why you want to go after waits right at the start. Then you can drill into query metrics, indexing, statistics and all the rest of the joy and entertainment of performance tuning.

"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

jevitts Mr or Mrs. 500 Points: 543 More actions · Answer 4

Thanks for the tip! I'll definitely start there. Yesterday I actually printed out the Wait Statistics section from the Accidental DBA e-book - glad to know I was on the right track! 🙂 I've never had the fortune of having to dive deep into performance tuning, so this should be fun! Excited to start researching this very important aspect of databases and further broadening my skill set.

If there are any other resources that you think would be a good source of information, feel free to share.

Thanks again!

Thanks,
Jessica
What would you attempt to do if you knew you could not fail? -Robert H. Schuller

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

I'd have recommended Jonathan's book, but doing so seems to be redundant. Chapter 1 and Chapter 6 mainly (I think 6 is blocking)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

There are a couple of links in my signature below this that I think are useful for performance tuning.

"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

MadAdmin SSChampion Points: 11261 More actions · Answer 7

55 indexes?

Excuse the french but, gudddddamn!##^#^@^@^@2

Gets my blood boiling.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]