1

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?

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
1
  • Thanks for this. Your suggestion to turn the Trace Flags on confirms for me that I've started going down the right track. After further research yesterday, I ran the following query after hours to turn 1204 on late last night: DBCC TRACEON(1204, 3605, -1). I've been closely monitoring the server all day today with Perfmon and occasionally running exec sp_who2, and am finding some interesting results (most of the times when I find SUSPENDED statuses, I find that the same action is causing them, which happens to be built by an in-house dev that interfaces w/ the commercial app/DB)
    – David W
    Nov 22, 2011 at 19:28
1

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.

Improve this answer
1
  • This is helpful, thank you. The system itself is older, but we recently put in new drives & new RAM, and moved the application / database from an even OLDER (and more unstable) machine to this one (which was running SQL 2000 in 32bit mode, where as this is 2005 running in 64bit mode. Thanks again.
    – David W
    Nov 22, 2011 at 19:32

You must log in to answer this question.

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