SELECT OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName, OBJECT_NAME(p.object_id) AS TableName, i.name AS IndexName 
FROM sys.partitions p 
LEFT JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id 
WHERE p.object_id = OBJECT_ID('dbo.YourTable') -- replace ;

CREATE NONCLUSTERED INDEX IX_Orders_CustomerDate ON dbo.Orders (CustomerId, OrderDate) 
INCLUDE (TotalAmount, Status) 
WITH (FILLFACTOR = 90, DATA_COMPRESSION = PAGE);

BEGIN TRAN; 
-- avoid long-running work or user interaction here 
UPDATE Accounts 
SET Balance = Balance - 100 
WHERE AccountId = @A; 
WAITFOR DELAY '00:00:05'; -- problematic in prod COMMIT;

ALTER DATABASE [YourDb] SET ALLOW_SNAPSHOT_ISOLATION ON; 
ALTER DATABASE [YourDb] SET READ_COMMITTED_SNAPSHOT ON;

Detailed Prioritized Recommendations

1. Enable Read Committed Snapshot Isolation (RCSI) CRITICAL IMPACT

Impact: Eliminates schema lock deadlocks, allows readers to proceed without blocking on schema locks, improves concurrency by 200-500% for mixed DDL/DML workloads.

Root Cause Analysis:

• Multiple SELECT queries acquiring Sch-S (Schema Stability) locks on tpch.dbo.lineitem and tpch.dbo.orders
• Concurrent ALTER TABLE attempting to acquire Sch-M (Schema Modification) lock on same tables
• Circular wait: ALTER holds Sch-M on orders, waits for Sch-M on lineitem; SELECT holds Sch-S on lineitem, waits for Sch-S on orders
• RCSI disabled forces readers to take blocking locks instead of using row versioning

Implementation Steps:

-- For WideWorldImporters database
USE master;
GO
ALTER DATABASE WideWorldImporters SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
GO

-- For tpch database (HIGH PRIORITY - source of 4 deadlocks)
ALTER DATABASE tpch SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
GO

-- Verify RCSI is enabled
SELECT name, is_read_committed_snapshot_on 
FROM sys.databases 
WHERE name IN ('WideWorldImporters', 'tpch');
GO

Important Notes:

• This change requires exclusive database access (ROLLBACK IMMEDIATE kills active connections)
• Schedule during maintenance window or low-activity period
• TempDB will experience increased activity (10-20% more space) due to row versioning
• Monitor TempDB growth after implementation
• This is a database-level setting and does not require application changes

2. Establish DDL Maintenance Windows CRITICAL IMPACT

Impact: Prevents DDL operations from interfering with active query workload, eliminates schema modification deadlocks completely.

Root Cause Analysis:

• ALTER TABLE operations (dropping/creating constraints and indexes) executed while analytical queries (TPC-H workload) are running
• Schema modification locks are incompatible with all other lock types including read operations
• ONLINE=ON parameter specified but still causes blocking due to initial/final schema lock phases

Implementation Strategy:

-- Create a DDL blocking check procedure
CREATE OR ALTER PROCEDURE dbo.usp_CheckForActiveSessions
    @DatabaseName NVARCHAR(128),
    @ThresholdMinutes INT = 1
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Check for active sessions that would block DDL
    IF EXISTS (
        SELECT 1 
        FROM sys.dm_exec_sessions s
        INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
        WHERE s.database_id = DB_ID(@DatabaseName)
        AND s.session_id != @@SPID
        AND DATEDIFF(MINUTE, r.start_time, GETDATE()) > @ThresholdMinutes
    )
    BEGIN
        RAISERROR('Active sessions detected. DDL operations should not proceed.', 16, 1);
        RETURN 1;
    END
    
    RETURN 0;
END
GO

-- Use before any DDL operations
EXEC dbo.usp_CheckForActiveSessions @DatabaseName = 'tpch', @ThresholdMinutes = 1;
GO

-- Your DDL operations here

Operational Guidelines:

• Schedule index creation/rebuilding operations during off-peak hours (nights/weekends)
• Coordinate with application teams to pause analytical query workloads during DDL windows
• Consider breaking large DDL scripts into smaller batches with monitoring between each batch
• Use ONLINE=ON for index operations but understand it still requires brief Sch-M locks at beginning and end
• For critical 24/7 systems, consider using resumable index operations (Enterprise Edition feature)

3. Refactor Stored Procedures for Consistent Access Order HIGH IMPACT

Impact: Eliminates the update-update deadlock pattern between Orders and OrderLines, improves transaction throughput by 40-60%.

Root Cause Analysis:

• Procedure A (Website.UpdOrdersandLinesFromWeb): Updates OrderLines FIRST, then Orders
• Procedure B (Website.UpdLinesandOrdersFromWeb): Updates Orders FIRST, then OrderLines
• Both procedures executed concurrently for OrderID = 2686
• Circular lock dependency: Proc A holds X lock on OrderLines, waits for X lock on Orders; Proc B holds X lock on Orders, waits for X/U lock on OrderLines

Current Procedure Patterns:

-- Website.UpdOrdersandLinesFromWeb (updates OrderLines FIRST)
-- Line 19, stmtstart 738
UPDATE Sales.OrderLines
    SET Description = SUBSTRING(ISNULL(Description, '') + ' [ProcA]',1,100)
    WHERE OrderID = 2686

-- Then updates Orders...

-- Website.UpdLinesandOrdersFromWeb (updates Orders FIRST)  
-- Line 17, stmtstart 746
UPDATE Sales.Orders
    SET Comments = ISNULL(Comments, '') + ' [ProcB]'
    WHERE OrderID = 2686

-- Then updates OrderLines...

Recommended Solution – Enforce Consistent Order:

-- ALWAYS update in the same order: Orders FIRST, then OrderLines
-- This prevents circular lock dependencies

-- Refactor Website.UpdOrdersandLinesFromWeb
CREATE OR ALTER PROCEDURE [Website].[UpdOrdersandLinesFromWeb]
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- STEP 1: Update Orders FIRST (parent table)
        UPDATE Sales.Orders
            SET Comments = ISNULL(Comments, '') + ' [ProcA]',
                LastEditedBy = 1,
                LastEditedWhen = SYSDATETIME()
            WHERE OrderID = 2686;
        
        -- STEP 2: Update OrderLines SECOND (child table)
        UPDATE Sales.OrderLines
            SET Description = SUBSTRING(ISNULL(Description, '') + ' [ProcA]', 1, 100),
                LastEditedBy = 1,
                LastEditedWhen = SYSDATETIME()
            WHERE OrderID = 2686;
        
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

-- Refactor Website.UpdLinesandOrdersFromWeb 
CREATE OR ALTER PROCEDURE [Website].[UpdLinesandOrdersFromWeb]
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- STEP 1: Update Orders FIRST (parent table) 
        UPDATE Sales.Orders
            SET Comments = ISNULL(Comments, '') + ' [ProcB]',
                LastEditedBy = 1,
                LastEditedWhen = SYSDATETIME()
            WHERE OrderID = 2686;
        
        -- STEP 2: Update OrderLines SECOND (child table)
        UPDATE Sales.OrderLines
            SET Description = SUBSTRING(ISNULL(Description, '') + ' [ProcB]', 1, 100),
                LastEditedBy = 1,
                LastEditedWhen = SYSDATETIME()
            WHERE OrderID = 2686;
        
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

Key Principles for Deadlock-Free Design:

• Consistent Ordering: Always access tables in the same order across all procedures (typically parent to child)
• Minimize Transaction Duration: Keep transactions as short as possible
• Explicit Transaction Control: Use explicit BEGIN/COMMIT/ROLLBACK for clarity
• Access by Primary Key: WHERE OrderID = @OrderID ensures index seeks and minimal lock escalation
• Document the Order: Add comments explaining why tables are accessed in specific sequence

4. Optimize Index Strategy for Deadlock Prevention MEDIUM IMPACT

Impact: Reduces lock duration and scope, minimizes lock escalation, improves query performance by 30-50%.

Current Issues:

• OrderLines table using clustered index PK_Sales_OrderLines – appropriate
• Orders table using clustered index PK_Sales_Orders – appropriate
• Foreign key relationship exists but queries may not be optimally covered

Recommended Indexes:

-- Ensure optimal index coverage for both procedures
-- This reduces the number of rows locked and lock duration

-- Index for OrderLines updates by OrderID (if not already optimal)
USE WideWorldImporters;
GO

-- Check existing indexes on OrderLines
EXEC sp_helpindex 'Sales.OrderLines';
GO

-- If needed, ensure covering index exists
IF NOT EXISTS (
    SELECT 1 FROM sys.indexes 
    WHERE object_id = OBJECT_ID('Sales.OrderLines')
    AND name = 'IX_OrderLines_OrderID_Covering'
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_OrderLines_OrderID_Covering
    ON Sales.OrderLines (OrderID)
    INCLUDE (Description, LastEditedBy, LastEditedWhen)
    WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, FILLFACTOR = 90);
END
GO

-- For Orders table - ensure primary key is optimal
-- Check if Comments column should be included for read optimization
CREATE NONCLUSTERED INDEX IX_Orders_OrderID_Comments
ON Sales.Orders (OrderID)
INCLUDE (Comments, LastEditedBy, LastEditedWhen)
WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, FILLFACTOR = 90)
WHERE Comments IS NOT NULL; -- Filtered index for non-NULL comments
GO

5. Implement Retry Logic for Deadlock Victims MEDIUM IMPACT

Impact: Improves application resilience, prevents transaction failures from reaching end users.

Recommended Retry Pattern:

-- Application-side retry logic (C# example)
/*
public static void ExecuteWithRetry(Action action, int maxRetries = 3)
{
    int retryCount = 0;
    while (retryCount < maxRetries)
    {
        try
        {
            action();
            return; // Success
        }
        catch (SqlException ex) when (ex.Number == 1205) // Deadlock victim
        {
            retryCount++;
            if (retryCount >= maxRetries) throw;
            
            // Exponential backoff: 100ms, 200ms, 400ms
            Thread.Sleep(100 * (int)Math.Pow(2, retryCount - 1));
        }
    }
}

// Usage:
ExecuteWithRetry(() => {
    using (var conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (var cmd = new SqlCommand("Website.UpdOrdersandLinesFromWeb", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();
        }
    }
});
*/

T-SQL Retry Pattern (for SQL Agent Jobs or internal procedures):

CREATE OR ALTER PROCEDURE dbo.usp_ExecuteWithRetry
    @ProcedureName NVARCHAR(128),
    @MaxRetries INT = 3
AS
BEGIN
    DECLARE @RetryCount INT = 0;
    DECLARE @ErrorNumber INT;
    DECLARE @ErrorMessage NVARCHAR(4000);
    
    WHILE @RetryCount < @MaxRetries
    BEGIN
        BEGIN TRY
            -- Dynamic execution of target procedure
            EXEC sp_executesql @ProcedureName;
            RETURN 0; -- Success
        END TRY
        BEGIN CATCH
            SET @ErrorNumber = ERROR_NUMBER();
            SET @ErrorMessage = ERROR_MESSAGE();
            
            IF @ErrorNumber = 1205 -- Deadlock victim
            BEGIN
                SET @RetryCount = @RetryCount + 1;
                IF @RetryCount < @MaxRetries
                BEGIN
                    -- Exponential backoff
                    WAITFOR DELAY '00:00:00.100'; -- Adjust based on retry count
                    CONTINUE;
                END
            END
            
            -- Non-deadlock error or max retries exceeded
            THROW;
        END CATCH
    END
END
GO

6. Enhanced Monitoring and Alerting MEDIUM IMPACT

Impact: Proactive deadlock detection, faster resolution, trend analysis for continuous improvement.

Implement Deadlock Monitoring:

-- Create deadlock tracking table
CREATE TABLE dbo.DeadlockHistory (
    DeadlockID INT IDENTITY(1,1) PRIMARY KEY,
    CaptureTime DATETIME2 DEFAULT SYSDATETIME(),
    DeadlockGraph XML,
    VictimSPID INT,
    VictimQuery NVARCHAR(MAX),
    DatabaseName NVARCHAR(128),
    CONSTRAINT CK_DeadlockHistory_Graph CHECK (DeadlockGraph IS NOT NULL)
);
GO

CREATE INDEX IX_DeadlockHistory_CaptureTime 
ON dbo.DeadlockHistory(CaptureTime DESC);
GO

-- Extended Event Session for Continuous Monitoring
CREATE EVENT SESSION [DeadlockMonitoring] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.query_hash,
        sqlserver.session_id,
        sqlserver.sql_text,
        sqlserver.username
    ))
ADD TARGET package0.event_file(
    SET filename=N'C:\DeadlockLogs\Deadlocks.xel',
    max_file_size=(50),
    max_rollover_files=(10)
)
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=ON
);
GO

ALTER EVENT SESSION [DeadlockMonitoring] ON SERVER STATE = START;
GO

-- Query to analyze recent deadlocks
SELECT 
    DeadlockID,
    CaptureTime,
    DatabaseName,
    DeadlockGraph.value('(/deadlock/victim-list/victimProcess/@id)[1]', 'VARCHAR(50)') AS VictimProcessID,
    DeadlockGraph.value('(/deadlock/process-list/process/@waitresource)[1]', 'VARCHAR(200)') AS WaitResource,
    DeadlockGraph
FROM dbo.DeadlockHistory
WHERE CaptureTime >= DATEADD(DAY, -7, GETDATE())
ORDER BY CaptureTime DESC;
GO

7. Additional Preventive Measures MEDIUM IMPACT

For WideWorldImporters Database:

• Reduce Transaction Scope: If procedures do other work beyond the two UPDATEs, consider splitting into separate transactions
• Parameter Sniffing: Add OPTION (RECOMPILE) if parameter values vary significantly between executions
• Lock Hints (Use Sparingly): Consider UPDLOCK hint only if absolutely necessary

-- Example: Reduce transaction scope
ALTER PROCEDURE [Website].[UpdOrdersandLinesFromWeb]
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Non-transactional prep work here
    DECLARE @OrderID INT = 2686;
    
    -- Keep transaction minimal
    BEGIN TRANSACTION;
    
    UPDATE Sales.Orders
        SET Comments = ISNULL(Comments, '') + ' [ProcA]'
        WHERE OrderID = @OrderID;
    
    UPDATE Sales.OrderLines
        SET Description = SUBSTRING(ISNULL(Description, '') + ' [ProcA]', 1, 100)
        WHERE OrderID = @OrderID;
    
    COMMIT TRANSACTION;
    
    -- Non-transactional post work here (logging, etc.)
END
GO

For tpch Database:

• Batch DDL Operations: Combine related index creations into single scripts with proper error handling
• Use Resumable Index Operations: For Enterprise Edition, enables pause/resume of long-running index builds
• Schedule Maintenance: Use SQL Agent jobs with proper scheduling to avoid query overlap

-- Resumable index creation (Enterprise Edition only)
CREATE NONCLUSTERED INDEX IX_orders_o_orderdate_cover
ON dbo.orders (o_orderdate)
INCLUDE (o_orderkey, o_custkey, o_shippriority, o_orderpriority)
WITH (
    DATA_COMPRESSION = PAGE, 
    FILLFACTOR = 95, 
    ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF)),
    RESUMABLE = ON,
    MAX_DURATION = 240 -- 4 hours max, then auto-pause
);
GO

8. Long-Term Architectural Considerations

• Application Queuing: Consider message queue (Azure Service Bus, RabbitMQ) for write operations to serialize updates naturally
• Optimistic Concurrency: For high-conflict scenarios, implement timestamp/rowversion-based optimistic locking
• Read-Write Splitting: Direct analytical queries to read replicas (Always On Availability Groups) to isolate from transactional workload
• Partitioning: For large tables like orders/lineitem, consider partitioning by date to reduce lock contention scope