Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

What's the best way to monitor SQL Server Conccurency issues? Specifically my goal is to reduce or prevent deadlocks and blocking and to catch potential concerns before they become a problem.

For example if I were to monitor windows performance counters for memory usage, I would be able to predict when memory issues would become a problem. I'm looking for a similar approach that monitors concurrency problems.

share|improve this question
    
Some approaches I've considered: watching trace events (profiler): blocked process report and deadlock graph events. I'm open to looking at third party tools. –  Michael J Swart Jul 22 '10 at 15:00
    
what version of sql? with 2005+ the dmv stats will be your friend. Google/bing DMV ALL STARS, should link you to a MS blog. –  tony roth Jul 22 '10 at 15:03
    
SQL Server 2005 –  Michael J Swart Jul 22 '10 at 15:04
    
SQL Server 2005. Took a look at the DMV All Stars queries. They look promising. I'll 1) look at wait stats, or realtime blockers 2) determining trends, tying back to the app if possible 3) take action to prevent problems. Step 1 and 2 are tricky here. –  Michael J Swart Jul 22 '10 at 15:12
    
also if your OS is w2k8+ there is a new storport for getting more useful storage statistics! blogs.msdn.com/b/ntdebugging/archive/2010/04/22/… –  tony roth Jul 22 '10 at 15:22
add comment

2 Answers 2

As long as you keep in mind that server side tracing always has some impact on performance & you've weighed up the benefit vs. the overhead then you can setup a server side trace to look for:

share|improve this answer
    
Thanks for your answer Nick! I was hoping for a metric that could catch potential trouble before they become a problem. If I'm catching deadlock graphs, it's too late to prevent them. The blocked process might be promising with a low enough threshold, but in terms of a measure or a performance indicator, the best I could do is count them. I know there are wait statistics, but I wonder how useful these are... –  Michael J Swart Jul 23 '10 at 14:56
add comment
up vote 0 down vote accepted

Using perfmon, the MSSQL:Latches and MSSQL:Locks categories have counters that seem to be as close as we can get.

It's clear now that a db doesn't have a "capacity for concurrency" that can be measured. (Not the same way that a server has storage capacity that can be measured).

Applications that were running fine yesterday can start blocking suddenly. Due to lock escalation, new query plans etc...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.