FixDeadlocks Tuning Goal

Overview

The FixDeadlocks tuning goal extracts recent deadlock graphs (when supported by the platform) and asks the AI to identify root causes and provide actionable prevention steps.

In addition to general guidance, FixDeadlocks uses the deadlock graph to help you identify the database objects and code paths involved (when the deadlock XML contains enough detail). You can then use that list to perform deeper follow-up analysis (for example, by running CodeReview on the stored procedures involved, or IndexTuning on the affected database).

How the AI is used

FixDeadlocks follows a simple workflow:

1. Collect the deadlock graph(s)

   • Reads one or more recent deadlock XML graphs from the platform’s supported source (for example the system_health session on SQL Server).

2. Ask the AI to review the deadlock XML

   • The AI summarizes what happened in each deadlock: victim selection, each process/session, and the resources involved (keys/pages/objects) as described by the XML.

3. Extract likely objects and code involved

   • The AI attempts to identify the objects involved (tables, indexes, constraints) and—when possible—links them to likely code paths.
   • If the deadlock includes an inputbuf (statement text) for a session, the AI uses it to call out the relevant statements/procedures for follow-up.
   • If the XML does not include statement text or object names, the AI will still provide best-effort guidance based on lock/resource types, but object identification may be limited.

4. Provide a prioritized next-step plan

   • Concrete recommendations such as adding/adjusting indexes to avoid scans, enforcing consistent access order, narrowing transaction scope, or changing isolation approaches.
   • A shortlist of objects to analyze next, so you can quickly focus on the most likely root cause.

Important Platform Support Notes

Deadlock graph extraction depends on platform capabilities:

• On-premises SQL Server: Supported (reads deadlocks from system_health extended events).
• Azure SQL Managed Instance: Supported (reads from system_health ring buffer).
• Azure SQL Database: Not supported (no access to system_health deadlock graphs in the same way).
• Microsoft Fabric SQL Database: Not supported (treated like Azure SQL Database for deadlock extraction).

If you run FixDeadlocks on an unsupported platform, AI SQL Tuner will return general guidance and recommended alternatives to help troubleshoot deadlocks on that platform.

What It Analyzes

1. Recent deadlock graphs

• XML deadlock graphs
• Basic timestamp context

2. Root cause identification

Common patterns include:

• Competing index/key order on the same tables
• Different access paths / inconsistent locking order
• Range locks (serializable / key-range)
• Foreign key cascades
• Large scans causing lock contention

3. Recommendations

The AI typically provides:

• Index and query changes to enforce consistent access order
• Transaction scope reductions
• Isolation level guidance
• “Retry on deadlock” guidance when appropriate

Usage

Command Line Examples

On-premises SQL Server with Windows Authentication:

# Database optional for FixDeadlocks (defaults to master)
aisqltuner -S localhost -goal FixDeadlocks -E -n 5 -o deadlocks.html

Azure SQL Managed Instance (Azure AD Interactive):

aisqltuner -S myinstance.database.windows.net -goal FixDeadlocks -aad -n 5 -o deadlocks.html

Output language (optional):

aisqltuner -S localhost -goal FixDeadlocks -E -n 5 -o deadlocks_es.html -l Spanish

Bring your own Azure OpenAI deployment/model (optional):

# Required
$env:AZURE_OPENAI_ENDPOINT = "https://<your-resource-name>.cognitiveservices.azure.com/"
$env:AZURE_OPENAI_KEY = "<your-azure-openai-key>"

# Optional (deployment name as created in Azure OpenAI)
$env:AISQLTUNER_DEPLOYMENT = "<your-deployment-name>"
$env:AISQLTUNER_SECONDARY_DEPLOYMENT = "<your-secondary-deployment-name>"
aisqltuner -S localhost -goal FixDeadlocks -E -n 5 -o deadlocks.html

Parameters

• -n, -count, -deadlocks – number of recent deadlocks to analyze (default 5, max 20)

What You Get

The AI will produce:

1. Prioritized recommendations
2. Specific objects involved (tables, indexes when identifiable)
3. Suggested scripts where appropriate
4. Prevention strategies (consistent order, targeted indexes, shorter transactions)

Output Format

• Console: Plain text (when -o not specified)
• HTML: Full HTML report (when -o specified). Footer includes the model and reasoning effort used.

Troubleshooting

“FixDeadlocks goal is not supported”

• Expected on Azure SQL Database and Microsoft Fabric SQL Database.
• Use these alternatives:
  • IndexTuning (identify missing indexes / request patterns)
  • CodeReview (find locking and transaction anti-patterns)

“Access denied” / system_health not accessible

• On-premises: requires appropriate permissions to read extended events.
• Azure SQL MI: requires permissions to read the ring buffer DMVs.

Related Resources

• System Health Extended Events Session
• Analyze and Prevent Deadlocks on Azure SQL Database
• Deadlock analysis example