CODEWITHSUNDEEP
sqlsql-serverperformancedeadlocksql-execution-plan
Ankush
Ankush

Reputation: 2554

How to capture actual execution plan in a deadlock graph?

Is there any option or any setting by which SQL Server can dump the actual execution plan (at that instance) for stored procs involved in a deadlock?

This is in context of SQL Server 2008.

Upvotes: 3

Views: 1568

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300728

Capture the SQL and plan handle and then you might get the query execution plan from the plan cache.

Here's how to capture a deadlock graph before it happens.

  1. Start SQL Server Profiler. On the File menu, click New Trace, and then connect to an instance of SQL Server. Give the trace a name and pick one of the trace templates.

enter image description here

  1. Do one of the following:

    • Select the Save to file check box to capture the trace to a file. Specify a value for Set maximum file size. Optionally, select Enable file rollover and Server processes trace data.

    • Select the Save to table check box to capture the trace to a database table. Optionally, click Set maximum rows, and specify a value.

  2. Optionally, select the Enable trace stop time check box, and specify a stop date and time.

  3. Select the Events Selection tab. Check Show All Events. In the Events data column, expand the Locks event category, and then select the Deadlock graph check box.

enter image description here

  1. The Events Extraction Settings tab is added to the Trace Properties dialog box.

  2. On the Events Extraction Settings tab, click Save Deadlock XML Events Separately. In the Save As dialog box, enter the name of the file in which to store the deadlock graph events.

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

enter image description here

After you have saved the deadlock file, you can open the file in SQL Server Management Studio.

Upvotes: 2

Related Questions