Capture Trend in Dead Lock Occurances

Deadlocks are usually detected and resolved automatically by SQL Server by killing one of the SPIDs involved in it.  You may have an alert setup to notify you and/or users may approach you with the issue.

But this post is not about finding the root cause and fix the issue for good. 

Ideally, you don't want to see dead locks occur at all and especially not on a regular basis.  But situations could arise where a database starts experiencing deadlocks. In this post I would like to share a code I have used to capture the daily count of deadlocks for a trending report.

Note: I am using the DMV sys.dm_os_performance_counters in this code so it will not work if the SQL Server performance counters are disabled for some reason.

You can use this query to check if the performance counters are enabled or disabled.

SELECT COUNT(*) FROM sys.dm_os_performance_counters;  

If the return value is 0 rows, more likely than not, it indicates that 

the performance counters have been disabled.

Step 1: Create empty table to store the deadlock counts

---

USE [AdminDBA]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[deadlock_counter](

       [ID] [int] IDENTITY(1,1) NOT NULL,

       [record_created_date] [datetime] NOT NULL,

       [SQLStartedOn] [datetime] NOT NULL,

       [object_name] [nchar](128) NULL,

       [counter_name] [nchar](128) NULL,

       [instance_name] [nchar](128) NULL,

       [cntr_value] [bigint] NULL,

       [cntr_type] [int] NULL,

       [AveragePerDay] [bigint] NULL,

PRIMARY KEY CLUSTERED

(

       [ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

 Step 2: Create a SQL Server Agent Job

---

/*

Please update email operator value for @notify_email_operator_name parameter

Please feel free to update any of the settings including the schedule you would like to use.

*/

USE [msdb]

GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA - Deadlock Counter',

             @enabled=1,

             @notify_level_eventlog=0,

             @notify_level_email=2,

             @notify_level_netsend=0,

             @notify_level_page=0,

             @delete_level=0,

             @description=N'Populate deadlock performance counter values',

             @category_name=N'[Uncategorized (Local)]',

             @owner_login_name=N'sa',

             @notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'deadlock performance counter values',

             @step_id=1,

             @cmdexec_success_code=0,

             @on_success_action=1,

             @on_success_step_id=0,

             @on_fail_action=2,

             @on_fail_step_id=0,

             @retry_attempts=0,

             @retry_interval=0,

             @os_run_priority=0, @subsystem=N'TSQL',

             @command=N'set nocount on

set transaction isolation level read uncommitted

go

use AdminDBA

go

-- the counter values are cumulative since SQL Server service was started, not per second

insert into deadlock_counter

SELECT getdate() record_created_date,

       d.create_date SQLStartedOn, p.*, AveragePerDay = CONVERT(BIGINT, (( 1.0 * p.cntr_value / NULLIF(Datediff(dd, d.create_date,CURRENT_TIMESTAMP), 0 ))))

-- INTO deadlock_counter

FROM   sys.dm_os_performance_counters p

       INNER JOIN sys.databases d ON d.NAME = ''tempdb''

WHERE  Rtrim(p.counter_name) = ''Number of Deadlocks/sec''   

--       AND cntr_value > 0

      AND Rtrim(p.instance_name) = ''_Total''

ORDER  BY cntr_value DESC

-- select * from deadlock_counter',

             @database_name=N'master',

             @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily',

             @enabled=1,

             @freq_type=4,

             @freq_interval=1,

             @freq_subday_type=1,

             @freq_subday_interval=0,

             @freq_relative_interval=0,

             @freq_recurrence_factor=0,

             @active_start_date=20190228,

             @active_end_date=99991231,

             @active_start_time=80000,

             @active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Step 3: Query to extract deadlock counts for each day/interval

---

-- Note: the counter values are cumulative since SQL Server service was started, not per second

set nocount on

set transaction isolation level read uncommitted

select * ,cntr_value - coalesce(lag(cntr_value) over (order by id), cntr_value) new_deadlocks

from AdminDBA..deadlock_counter

order by ID desc

Do you have out of date index statistics?

As you may all know, having up to date index statistics is the key to make sure the query optimizer can choose the most optimal execution plan for any query.

Here is DMV query that you can use against a database to see if there are statistics that have not been updated and have had good deal of data modifications in the underlying tables.

SELECT TOP 100 OBJ.NAME, STAT.NAME, SCHEMA_NAME(OBJ.SCHEMA_ID) SCHEMA_NAME, OBJ.CREATE_DATE, OBJ.MODIFY_DATE, C.NAME AS COLUMN_NAME, SP.* FROM SYS.OBJECTS AS OBJ INNER JOIN SYS.STATS AS STAT ON STAT.OBJECT_ID = OBJ.OBJECT_ID INNER JOIN SYS.STATS_COLUMNS AS SC ON STAT.OBJECT_ID = SC.OBJECT_ID AND STAT.STATS_ID = SC.STATS_ID INNER JOIN SYS.COLUMNS AS C ON SC.OBJECT_ID = C.OBJECT_ID AND C.COLUMN_ID = SC.COLUMN_ID

CROSS APPLY SYS.DM_DB_STATS_PROPERTIES(STAT.OBJECT_ID, STAT.STATS_ID) AS SP

WHERE MODIFICATION_COUNTER > 10000 AND OBJECTPROPERTY(OBJ.OBJECT_ID, 'ISMSSHIPPED') != 1 AND LAST_UPDATED > GETDATE() - 30 ORDER BY MODIFICATION_COUNTER DESC;

Note: Now just because a statistics is out of date does not mean it’s affecting the query performance.  You may have statistics that are there but are no longer being used by the optimizer, maybe because they were auto created in the past or its part of an unused or duplicate index.  Those stats can be candidates for cleanup.

Script to Recreate All Indexes in SQL Server

For me, this particular code to generate a script for recreating indexes has its most practical use in a replication environment. For example, by default SQL Server replication often excludes non‑clustered indexes from subscribers, and later you may realize that some or all of those indexes are actually needed to maintain query performance on the subscriber databases. This script lets you quickly generate consistent index definitions across the publisher and subscribers without having to manually reverse‑engineer each index.

Also, generally speaking, index requirements for publisher and subscriber databases can be quite different. As a result, you may end up with different sets of indexes on the publisher and its subscribers.

Beyond replication, the script is also useful in several other common DBA scenarios. For instance, you can use it to recreate indexes after a schema‑only restore or when building dev/test environments from production schemas, ensuring that performance‑critical indexes are not accidentally omitted. It is also handy when migrating databases to new instances or clusters, or when documenting and auditing index layouts across multiple environments, giving you a repeatable, script‑based approach rather than relying on ad‑hoc hand‑written DDL.

/*
 ============================================================================
 INDEX SCRIPT GENERATOR (SQL Server 2005+)
 ============================================================================
 Purpose:
   Generates CREATE INDEX statements for all user‑defined indexes in the
   current database, including:
   - Clustered / nonclustered rowstore
   - Clustered / nonclustered columnstore
   - Filtered indexes
   - XML and spatial indexes

 Result:
   Outputs a column "CreateIndexStatement" containing full T‑SQL scripts.
   These can be pasted into a new window, run as‑is, or loaded into a temp
   table (e.g., #dba_index) for later use.

 Author:  
 Updated: 2026‑03‑26

 USAGE NOTES AND CAVEATS
 ----------------------------------------------------------------------------
 1. Database context:
    - This script runs in the context of the current database.
    - It only lists indexes in that database.

 2. Index types handled:
    - CLUSTERED / NONCLUSTERED (rowstore)
    - CLUSTERED_COLUMNSTORE / NONCLUSTERED_COLUMNSTORE
    - XML / SPATIAL
    - Filtered indexes (via WHERE clause)

 3. Indexes skipped:
    - Primary key / unique constraint indexes (is_primary_key = 1,
      is_unique_constraint = 1).
    - Hypothetical indexes (is_hypothetical = 1).
    - System objects (is_ms_shipped = 1).

 4. Collation‑handling:
    - Uses COLLATE DATABASE_DEFAULT on s.name, t.name, i.name to avoid
      collation conflicts when concatenating strings.
    - This assumes the database’ default collation is acceptable for all
      generated scripts.

 5. Generated statement behavior:
    - Wraps each CREATE INDEX in:
        IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE ...)
      to avoid duplicate index creation.
    - Uses OBJECT_ID with schema‑qualified names (e.g., [schema].[table]).
    - Includes key columns, ASC/DESC, included columns, and index options
      (PAD_INDEX, FILLFACTOR, ONLINE effects, etc.).

 6. What it does NOT do:
    - It does not script:
        - PRIMARY KEY / UNIQUE constraints (only pure indexes).
        - Statistics objects not backed by an index.
        - Columnstore ORDER(), MAXDOP=, ONLINE=, or other advanced options
          beyond the common WITH options listed.
    - It does not:
        - Drop indexes.
        - Handle partitioning syntax beyond the ON <filegroup | partition scheme>.
    - It does not preserve:
        - Existing index names on constraint‑created indexes (they are skipped).
        - Any index‑specific hints or undocumented options not exposed in
          sys.indexes.

 7. Safety and testing:
    - Always test output in a non‑production environment first.
    - In active OLTP systems, CREATE INDEX can block or slow down workloads;
      consider:
        - Using ONLINE = ON (if Enterprise Edition).
        - Creating indexes during maintenance windows.
        - Monitoring blocking and wait stats.
    - If you have collation‑specific comparisons elsewhere in your app,
      verify that the generated script’s collation semantics match.

 8. Compatibility:
    - Uses views introduced in SQL Server 2005 (sys.indexes, sys.index_columns,
      sys.stats, sys.data_spaces), so it works on 2005+.
    - ORDER BY CASE i.type_desc LIKE 'CLUSTERED%'... is SQL Server 2005+.
    - Some WITH options (e.g., ONLINE, DATA_COMPRESSION) may require SQL Server
      2008+ or 2012+ depending on index type.
    - Columnstore indexes require SQL Server 2012+ (Enterprise Edition for
      some features).

 9. Performance:
    - For very large databases with many tables and indexes, this query can
      be moderately heavy on catalog metadata; run it during low‑activity windows
      if that is a concern.

 ============================================================================ */

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

;WITH IncludedCols AS
(
    SELECT
        ic.object_id,
        ic.index_id,
        IncludedColumns =
            STUFF(
                (
                    SELECT ', ' + QUOTENAME(c.name)
                    FROM sys.index_columns ic1
                    JOIN sys.columns c
                        ON c.object_id = ic1.object_id
                       AND c.column_id = ic1.column_id
                    WHERE ic1.object_id = ic.object_id
                      AND ic1.index_id  = ic.index_id
                      AND ic1.is_included_column = 1
                    ORDER BY c.column_id
                    FOR XML PATH(''), TYPE
                ).value('.', 'nvarchar(max)')
            ,1,2,'')
    FROM sys.index_columns ic
    GROUP BY ic.object_id, ic.index_id
),
KeyCols AS
(
    SELECT
        ic.object_id,
        ic.index_id,
        KeyColumns =
            STUFF(
                (
                    SELECT ', ' + QUOTENAME(c.name) +
                           CASE WHEN ic1.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
                    FROM sys.index_columns ic1
                    JOIN sys.columns c
                        ON c.object_id = ic1.object_id
                       AND c.column_id = ic1.column_id
                    WHERE ic1.object_id = ic.object_id
                      AND ic1.index_id  = ic.index_id
                      AND ic1.is_included_column = 0
                    ORDER BY ic1.key_ordinal
                    FOR XML PATH(''), TYPE
                ).value('.', 'nvarchar(max)')
            ,1,2,'')
    FROM sys.index_columns ic
    GROUP BY ic.object_id, ic.index_id
)
SELECT
    'IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N''' +
        QUOTENAME(s.name COLLATE DATABASE_DEFAULT) +
        '.' +
        QUOTENAME(t.name COLLATE DATABASE_DEFAULT) +
        ''') AND name = N''' +
        i.name COLLATE DATABASE_DEFAULT +
        ''')
CREATE ' +
        CASE WHEN i.is_unique = 1
             THEN 'UNIQUE '
             ELSE ''
        END +
        CASE
            WHEN i.type_desc IN ('CLUSTERED', 'NONCLUSTERED')
                THEN i.type_desc + ' '
            WHEN i.type_desc LIKE '%COLUMNSTORE%'
                THEN i.type_desc + ' '
            WHEN i.type_desc IN ('XML', 'SPATIAL')
                THEN i.type_desc + ' '
            ELSE ''
        END + 'INDEX ' + QUOTENAME(i.name COLLATE DATABASE_DEFAULT) + ' ON ' +
        QUOTENAME(s.name COLLATE DATABASE_DEFAULT) + '.' +
        QUOTENAME(t.name COLLATE DATABASE_DEFAULT) +
        CASE
            WHEN i.type_desc LIKE '%COLUMNSTORE%' THEN
                ISNULL(' (' + kc.KeyColumns + ')','')
            ELSE
                ' (' + kc.KeyColumns + ')'
        END +
        ISNULL(' INCLUDE (' + ic.IncludedColumns + ')','') +
        ISNULL(' WHERE ' + i.filter_definition,'') +
        ' WITH (' +
            'PAD_INDEX = ' + CASE WHEN i.is_padded = 1 THEN 'ON' ELSE 'OFF' END +
            ', FILLFACTOR = ' + CONVERT(varchar(5), CASE WHEN i.fill_factor = 0 THEN 100 ELSE i.fill_factor END) +
            ', IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END +
            ', STATISTICS_NORECOMPUTE = ' + CASE WHEN st.no_recompute = 1 THEN 'ON' ELSE 'OFF' END +
            ', ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END +
            ', ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END +
        ')' +
        ' ON [' + ds.name + '];'
AS CreateIndexStatement
FROM sys.indexes i
JOIN sys.tables t ON t.object_id = i.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.stats st ON st.object_id = i.object_id
   AND st.stats_id  = i.index_id
JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
LEFT JOIN IncludedCols ic ON ic.object_id = i.object_id
   AND ic.index_id  = i.index_id
LEFT JOIN KeyCols kc ON kc.object_id = i.object_id
   AND kc.index_id  = i.index_id
WHERE
    i.is_hypothetical      = 0        -- Exclude hypothetical indexes.
    AND i.index_id         > 0        -- Exclude heap row locators.
    AND i.is_primary_key   = 0        -- Skip primary key indexes.
    AND i.is_unique_constraint = 0    -- Skip unique constraint indexes.
    AND t.is_ms_shipped    = 0        -- Skip system tables.
ORDER BY
    s.name,
    t.name,
    CASE WHEN i.type_desc LIKE 'CLUSTERED%' THEN 0 ELSE 1 END,  -- Clustered first...
    i.name;                                                      -- then index name.

The script is thoroughly documented, so please review it carefully before running it, even in a test environment.

I welcome your comments, feedback, and suggestions for improvement.

Generate SQL Script To Recreate Indexes