SQL Server for .NET Developers: Indexes, Query Plans and Transactions

-- SQL Server: recent deadlock graphs from the system_health session
SELECT
    xed.value('@timestamp', 'datetime2(3)')   AS event_time_utc,
    xed.query('(data/value/deadlock)[1]')     AS deadlock_graph
FROM (
    SELECT CAST(event_data AS xml) AS event_xml
    FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
    WHERE object_name = N'xml_deadlock_report'
) AS e
CROSS APPLY e.event_xml.nodes('/event') AS x(xed)
ORDER BY event_time_utc DESC;

using System.Data;
using Microsoft.Data.SqlClient;
using Polly;
using Polly.Retry;

public sealed class TransferService(string connectionString)
{
    private static readonly ResiliencePipeline Retry = new ResiliencePipelineBuilder()
        .AddRetry(new RetryStrategyOptions
        {
            // 1205: deadlock victim; 3960: update conflict under SNAPSHOT isolation
            ShouldHandle = new PredicateBuilder()
                .Handle<SqlException>(ex => ex.Number is 1205 or 3960),
            MaxRetryAttempts = 3,
            Delay = TimeSpan.FromMilliseconds(100),
            BackoffType = DelayBackoffType.Exponential,
            UseJitter = true
        })
        .Build();

    public Task TransferAsync(int fromId, int toId, decimal amount, CancellationToken ct) =>
        Retry.ExecuteAsync(async token =>
        {
            await using var connection = new SqlConnection(connectionString);
            await connection.OpenAsync(token);
            await using var tx = connection.BeginTransaction(IsolationLevel.ReadCommitted);

            // Touch accounts in ascending ID order so opposite transfers cannot deadlock
            var steps = fromId < toId
                ? new[] { (fromId, -amount), (toId, amount) }
                : new[] { (toId, amount), (fromId, -amount) };

            foreach (var (accountId, delta) in steps)
            {
                await UpdateBalanceAsync(connection, tx, accountId, delta, token);
            }

            await tx.CommitAsync(token);
        }, ct).AsTask();

    private static async Task UpdateBalanceAsync(
        SqlConnection connection, SqlTransaction tx, int accountId, decimal delta,
        CancellationToken ct)
    {
        await using var command = new SqlCommand(
            "UPDATE dbo.Accounts SET Balance += @delta WHERE AccountId = @id;",
            connection, tx);
        command.Parameters.Add(new SqlParameter("@delta", SqlDbType.Decimal)
        {
            Precision = 19,
            Scale = 4,
            Value = delta
        });
        command.Parameters.Add("@id", SqlDbType.Int).Value = accountId;
        await command.ExecuteNonQueryAsync(ct);
    }
}