<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Researching Deadlocks
Researching Deadlocks
Rate Topic
Display Mode
Topic Options
Author
Message
Del Lee
Del Lee
Posted Wednesday, August 29, 2012 7:07 AM
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 235,
Visits: 332
Hi all:
I've seen that you can use trace flags 1204 and 1222 to return information to the error log about deadlocks:
http://msdn.microsoft.com/en-us/library/ms178104%28v=sql.90%29.aspx
I've also seen a comment on these forums that setting any trace flags could potentially introduce performance issues, but then I saw another comment where someone uses 1204 on their (busy) instance 24/7 without issue.
How can I determine if setting these trace flags will significantly affect performance before I turn them on?
Thanks...
Del Lee
Post #1351590
Grant Fritchey
Grant Fritchey
Posted Wednesday, August 29, 2012 9:09 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 13,383,
Visits: 25,187
First, I'd stick to using 1222. It has better information than 1204. Second, yes, these are very lightweight processes. They don't put excessive load on the system. They capture the deadlock graph output from the deadlock into the error log. If they're going to put any kind of load on the system, it's during the writes to the error log. But I've run them for years on systems without ever hitting issues, so I have no trouble recommending their use.
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #1351687
Del Lee
Del Lee
Posted Thursday, August 30, 2012 6:59 AM
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 235,
Visits: 332
Thanks for the recommendation. We have it turned on with no issues today.
Del Lee
Post #1352174
jason.spangler
jason.spangler
Posted Friday, August 31, 2012 6:53 AM
Valued Member
Group: General Forum Members
Last Login: Monday, May 20, 2013 11:01 AM
Points: 62,
Visits: 308
You can also use SQL Profiler to setup a trace via the GUI, or as a server side trace to capture the deadlock information. With this method, you don't need to set trace flags. Although, if the deadlocks don't occur very often, you may be better off with the trace flags.
This is just another option.
-
Jason
@emtJason
Post #1352772
Del Lee
Del Lee
Posted Friday, August 31, 2012 6:56 AM
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 235,
Visits: 332
Yes, I was aware of the profiler trace option, but I had already ruled out doing that on a production server.
Del Lee
Post #1352776
jason.spangler
jason.spangler
Posted Friday, August 31, 2012 7:08 AM
Valued Member
Group: General Forum Members
Last Login: Monday, May 20, 2013 11:01 AM
Points: 62,
Visits: 308
Why rule it out because it's a production server? I would rule out running the Profiler GUI on the production server, but I wouldn't rule out running a server side trace.
-
Jason
@emtJason
Post #1352785
Grant Fritchey
Grant Fritchey
Posted Friday, August 31, 2012 7:16 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 13,383,
Visits: 25,187
jason.spangler (8/31/2012)
Why rule it out because it's a production server? I would rule out running the Profiler GUI on the production server, but I wouldn't rule out running a server side trace.
-
Jason
@emtJason
Ditto. It's a great way to capture information. If you were on 2008 or better you could use extended events, but a server-side trace is almost as good.
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #1352790
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
