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

DMV - Active queries sorted by CPU usage

select

           s.session_id,

           db_name(s.database_id) DB,

           s.login_time,

           s.host_name,

           s.program_name,

           s.login_name,

           s.cpu_time,

           s.memory_usage,

           s.total_scheduled_time,

           s.total_elapsed_time,

           s.endpoint_id,

           s.last_request_start_time,

           s.last_request_end_time,

           s.reads,

           s.writes,

           s.logical_reads,

           s.is_user_process,

           s.row_count,

           s.open_transaction_count,

           t.text as [command]

from sys.dm_exec_sessions s

inner join sys.dm_exec_connections c on c.session_id = s.session_id

cross apply sys.dm_exec_sql_text(most_recent_sql_handle) t

where s.session_id != @@spid

      and status = 'running'

order by cpu_time desc

---

And for older versions of SQL Server, the the following (Not Tested)

select

           db_name(a.dbid) as [db_name],

           hostname as [host_name],

           NT_USERNAME,

           spid,

           physical_io,

           cpu cpu2,

           db_name(a.dbid) as [database_name],

           program_name,

           status,

           text as [command]

From master..sysprocesses a

cross apply ::fn_get_sql(sql_handle) as command

where status = 'runnable'

--where db_name(a.dbid) like 'temp%'

order by cpu desc