У нас есть стороннее коммерческое приложение, которое, как мы считаем, вызывает зависания на нашей машине SQL Server 2005 (64 бит). Пройдя полуторадневный тренинг с поставщиком программного обеспечения на прошлой неделе, чтобы помочь нам лучше администрировать программное обеспечение, я сейчас провожу исследование того, как лучше всего использовать SQL Server Profiler и шаблоны трассировки, чтобы получить максимальную выгоду.
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?
решение1
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
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.
Что касается пункта 2, если вы пытаетесь что-то захватить, на мой взгляд, вам следует запустить диагностику, а затем выполнить действия, вызывающие взаимоблокировку (предполагая, что вы сузили круг до конкретной причины или типа события с приложением или только с приложением в целом).
К сожалению, с пунктом 3 ничем помочь не могу.