Home / server / Questions / 333316
Accepted
David W
Asked: 2011-11-22 10:02:05 +0800 CST

SQL 2005 Deadlocks: Running Traces & Examining Logs

  • 772

We have a 3rd party commercial application which we believe is causing deadlocks to occur on our SQL Server 2005 (64bit) machine. Coming away from a 1 & 1/2 day training with the software vendor last week to help us better administer the software, I'm now doing some research on how to best use SQL Server Profiler and Trace Templates to the best of my advantage.

My personal motto is "if you're a software vendor building an application that requires you to be able to remotely connect to the client's server, then you're doing it wrong." Unforunately, we don't much of a choice right now.

The more I get to know these guys (software vendor), the less I'm impressed with them - and the more "behind the scenes" work I want to do myself. For example, we've been having problems for months with the server slowing down to a crawl - but as of now, I see absolutely no trace file or trace template file on the system.

On to my questions...

  1. Does running a trace file noticeably affect server performance? My guess is the answer will be "it depends". If that's the case, then here's the "events" I've selected on the new trace template I've just created:

    • Deadlock graph
    • Lock: Deadlock
    • Lock: Deadlock Chain
    • RPC:Completed
    • SP:StmtCompleted
    • SQL:BatchCompleted
    • SQL:BatchStarting

  2. Would a trace have to be run before the deadlock actually occurs, or would I be able to run the trace at the time we notice substantial decrease in performance?

  3. I'm reading up on tips and techniques for reviewing the SQL logs now, as that hasn't been something we've paid a whole lot of attention to. When I go into SQL Server Management Studio, go into Management and SQL Server Logs, I can't find anything in there that says "deadlock" / "deadlocked" etc... So perhaps nothing is being deadlocked. Could someone confirm for me whether or not Deadlocks will show up in the SQL logs, and if so, what I can use in my search criteria to find the entries?

sql-server-2005 deadlock

2 Answers

  1. Best Answer

    Running a trace on a SQL Server will impact the SQL Server. The basic rule of thumb is that anything that you do on the server takes resources. Can you cause performance problems running a trace or SQL Profiler against the SQL Server? Yep, you sure can if you don't have any filtering in place.

    If you are having deadlocking problems turn on Trace Flags 1204 and 1222 which will output the information about the deadlock to the errorlog. Don't leave these on all the time as they will impact performance. The information that is output to the errorlog will tell you all about the statements which were part of the deadlock.

    • 2

  2. As far as running diagnostic logs like Trace, it does use less resources than Profiler but as always the answer does depend on your server specs as well as how much is going on at once during normal production. Since you're on only SQL 2005, I assume the hardware is a bit long in the tooth which means you should be careful of running it on a production box. Which isn't really recommended anyway when trying to troubleshoot a problem semi-blindly or even on a brand new box.

    For #2, if you're trying to capture something, IMO you should have the diagnostics running, and then perform what causes the deadlock (assuming you have it narrowed down to a particular reason or type of event with the application, or just the app in general)

    Unfortunately, can't help much with #3.

    • 1