Search
Resolved
as Won't Fix Help for as Won't Fix

8
Sign in to vote
0
Sign in to vote
Sign in
 to vote
Type: Suggestion
ID: 490453
Opened: 9/17/2009 9:45:00 AM
Access Restriction: Public
0
Workaround(s)
When customer comes to PSS with a deadlock issue, most of the times we end up with no data - as they dont enable 1204 or 1222. PSS have to request the customer to enable these trace flags and wait for the issue to occur to get the deadlock information. Also, the customers are not ready to keep the trace flag on, as it will load a lots of info to SQL Error logs.

We end up losing time and the customer has to wait for the issue to occur.
Details (expand)
Product Language
English

Category

SQL Engine
Proposed Solution
As soon as the deadlock monitor finds the deadlock thread - it identifies the victim based on the cost and kills the victim. Instead of enabling the trace flag manually - we can automate the process of data collection. We can have a deadlockmonitor counter, it gets incremented whenever the deadlock occurs. Whenever the deadlock monitors finds the deadlock counter is set to 1 and the trace flag is enable to collect the trace. If the counter reaches 4-5 the trace flag should be disabled - so SQL Errorlog is not flooded with deadlock information.
Benefits
Improved Reliability
Other (please provides details below)
Other Benefits
Faster Case resolution
File Attachments
0 attachments
Sign in to post a comment.
Posted by Microsoft on 9/24/2009 at 7:08 AM
Dear Venkat,
Thank you for the feedback. Let us take a look at your request (enabling deadlock graph on default trace upon first occurence of a deadlock) and see what we have currently and what we can do.
thanks,
Madhan
Posted by Microsoft on 9/24/2009 at 2:14 PM
Dear customer,
Looking into this, we do think this is a good idea and will consider this for our next major release.

thanks,
Madhan
Posted by Microsoft on 10/7/2009 at 11:33 AM
Venkat,
looks like the following event would satisfy your requirement, given that the event is part of the default health session which should have the same information as TF1222.


create event session system_health on server
…
add event sqlserver.xml_deadlock_report,
…

Would this work? if not please let us know.
thanks,
Madhan

Posted by venkn on 10/9/2009 at 5:46 AM
Hi Madhan,

Thanks for the response.

This suggestion will help, however it requires us to enable or start the XEvent session when the issue occurs for the first time. My suggestion is to automate this process and the data is readily available for investigation when the customer comes to us.

That is the primary reason for raising this as a “Feature Request”.

Regards,
Venkat.
Posted by Microsoft on 10/9/2009 at 12:30 PM
system_health event session is running by default out of the box (it is created with startup_state = on). No need to start it. It should be on all the time.

Thanks,
-Ivan
© 2009 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement