![]() The longer it takes lock monitor to output the results the less time it has for detecting and resolving deadlocks leading to the reason the deadlocks were being resolved slower than I expected. The test was using all 5 background tasks and the lock monitor to produce the output and the I/O path to the error log was saturated. In my case the test was generating large numbers of deadlocks and flooding the log with –T1222 output. When the 5 background tasks are actively writing the lock monitor has to participate with those tasks for I/O bandwidth to the error log. Each line of the output is protected so multiple tasks can’t write at the exact same time. The contention point appears at the error log. unknown where the tasks are allowed to resolve object names.) When a background task complete, lock monitor can leverage is again for the next output request. Once 5 tasks are active the 6th, … output from lock monitor happens inline (lock monitor outputs the results to the error log, without performing object name resolutions, I.E. SQL Server limits the background tasks to a total of five (5) tasks to avoid flooding the scheduler with deadlock output tasks. The key is the background tasks used to output the XML deadlock information. The design does off-load the output activity to background workers so the lock monitor does not have to participate in outputting the results and can remain focused on detection and resolution activities. This adds overhead for logging which has the impact of slowing down lock monitor and in turn causing longer blocking scenarios. When you enable –T1222 the output of the XML deadlock information (usually seen in trace facilities) is written to the error log. I was looking to see if the problem was an undetected deadlock that lock monitor was struggling with or something else, and to my surprise I found it was the trace flag –T1222. The test in question is designed to cause large amounts of deadlocks in order to stress the deadlock detection and resolution code paths. This week I was tracking down a blocking situation, which I expected the lock monitor to resolve as a deadlock. Click All Deadlock XML batches in a single file to save all deadlock graph events in a single XML file, or click Each Deadlock XML batch in a distinct file to create a new XML file for each deadlock graph.The trace flag 1222 can be very powerful and helpful in tracking down the cause of a deadlock when used correctly.In the Save As dialog box, enter the name of the file in which to store the deadlock graph events.On the Events Extraction Setting stab, click Save Deadlock XML Events Separately. ![]() The Events Extraction Settings tab is added to the Trace Properties dialog box. If the Locks event category is not available, check Show all events to display it. In the Events data column, expand the Locks event category, and then select the Deadlock graph check box.Optionally, select the Enable trace stop time check box, and specify a stop date and time.Optionally, click Set maximum rows, and specify a value. Select the Save to table check box to capture the trace to a database table.Optionally, select Enable file rollover and Server processes trace data. Specify a value for Set maximum file size. Select the Save to file check box to capture the trace to a file.In the Use the template list, select a trace template on which to base the trace, or select blank if you do not want to use a template.In the Trace Properties dialog box, type a name for the trace in the Trace name box.On the File menu, click New Trace, and then connect to an instance of SQL Server.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |