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