1

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.

5
  • 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. Jul 22, 2010 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, 2010 at 15:03
  • SQL Server 2005 Jul 22, 2010 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. Jul 22, 2010 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, 2010 at 15:22

2 Answers 2

0

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:

1
  • 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... Jul 23, 2010 at 14:56
0

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...

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .