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.

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
Michael J Swart
  • 185
  • 1
  • 7
  • 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! http://blogs.msdn.com/b/ntdebugging/archive/2010/04/22/etw-storport.aspx – tony roth Jul 22 '10 at 15:22

2 Answers2

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:

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
  • 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
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...

Michael J Swart
  • 185
  • 1
  • 7