The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

Archive for May, 2016

Seek & Destroy Duplicate Indexes in your SQL Database

Posted by Sudarshan Narasimhan on May 20, 2016

This post is Part 1 of my series of upcomingposts on Index Maintenance for a DBA. So here goes.

There are all sorts of indexing tuning & optimizations that can be done on a SQL database. One of the no-brainer things a DBA must do is “seek & destroy” (pun intended) duplicate indexes. Duplicate indexes are indexes exactly similar in structure, including key columns and their base tables. These indexes serve no useful purpose and in fact contribute to slowness for WRITE database operations as the WRITER (INSERT/UPDATE/DELETE) must do more work to update the index as well as this directly contributes to the slowness of a write operation. If AlwaysON or other HA, then it has to do this on all the replicas as well, adding additional unnecessary network overhead.

You’d be surprised at the results. Given this script a run on any of your databases especially Vendor application databases. If you do see any duplicates, time to talk to your vendor or DB developer.

Here is query that I use to identify duplicate indexes. This query considers 2 or more indexes to be Exact Duplicates when all the key columns & included columns (if any) match another index on the same table.

USE [tempdb]  /* Change to your DB Name */
DECLARE @i int=1, @count int=0, @tblname nvarchar(250), @indxname nvarchar(250), @cmd nvarchar(4000)
DECLARE @DuplicateIndexes TABLE(id int IDENTITY(1,1), table_name nvarchar(250), index_name nvarchar(250), dup_index_name nvarchar(250))
DECLARE @IndexInfo TABLE(id int IDENTITY(1,1), table_name nvarchar(250), index_name nvarchar(250), index_description nvarchar(250),index_keys nvarchar(250), included_columns nvarchar(250) null)

-- exact duplicates
;WITH indexcols AS
    select object_id as id, index_id as indid, name,
    (select case keyno when 0 then NULL else colid end as [data()]
    from sys.sysindexkeys as k
    where = i.object_id
    and k.indid = i.index_id
    order by keyno, colid
    for xml path('')) as cols,
    (select case keyno when 0 then colid else NULL end as [data()]
    from sys.sysindexkeys as k
    where = i.object_id
    and k.indid = i.index_id
    order by colid
    for xml path('')) as inc
    from sys.indexes as i
DuplicateIndexes AS
    object_schema_name( + '.' + object_name( as [TableName], as [IndexName], as [DuplicateIndexName]
    FROM indexcols as c1
    join indexcols as c2
    ON =
    and c1.indid < c2.indid
    and c1.cols = c2.cols
    and =
INSERT INTO @DuplicateIndexes
SELECT * FROM DuplicateIndexes

SELECT @count=count(*) FROM @DuplicateIndexes
WHILE (@i <= @count)
    SELECT @tblname = [table_name], @indxname = [index_name] FROM @DuplicateIndexes WHERE [id] = @i
    DECLARE @tmptbl TABLE (index_name nvarchar(250), index_description nvarchar(250),index_keys nvarchar(250))
    DELETE FROM @tmptbl
    SET @cmd = 'EXEC sp_helpindex ''' +@tblname+ ''''

    INSERT INTO @tmptbl (index_name, index_description, index_keys)
        EXEC (@cmd)

    INSERT INTO @IndexInfo (table_name, index_name, index_description, index_keys)
    SELECT @tblname, index_name, index_description, index_keys FROM @tmptbl

    DECLARE @incld_cols sysname
    SET @cmd = '
    SELECT @tmpstr=b.IncludedColumns
    FROM (
    SELECT LEFT(a.indx_cols_included, LEN(a.indx_cols_included)-1) as [IncludedColumns]
    FROM (
    SELECT STUFF (( SELECT C.Name + '','' AS [text()] 
    FROM sys.indexes I 
     INNER JOIN sys.index_columns IC 
      ON  I.object_id = IC.object_id AND I.index_id = IC.index_id 
     INNER JOIN sys.columns C 
      ON IC.object_id = C.object_id and IC.column_id = C.column_id 
    WHERE IC.object_id=object_id('''+@tblname+''') and = '''+@indxname+''' and IC.is_included_column=1
    FOR XML PATH('''')
    ), 1, 0, '''') AS indx_cols_included) as a) as b'
    EXEC sp_executesql @cmd, N'@tmpstr sysname OUTPUT', @incld_cols OUTPUT
    UPDATE @IndexInfo SET included_columns = @incld_cols WHERE [id]=@i

SET @i = @i + 1

SELECT DB_NAME() AS [DB Name], d.table_name as [TableName], d.index_name as [IndexName], d.dup_index_name as [DuplicateIndexName], i.index_keys as [IndexKeyColumns], i.included_columns as [IncludedColumns]
FROM @DuplicateIndexes d
JOIN @IndexInfo i
    ON d.table_name = i.table_name
    AND d.index_name = i.index_name


Script Result
Here is a sample output from my test system where I’ve created 2 indexes having the same structure




Note: The original source script was from HERE. I’ve modified & added to this to include index key column information as well as included columns to present a more clear output on the duplicate indexes.


Stay tuned for more index related scripts in this series.

-Sudarshan (TheSQLDude)


Posted in DB Maintenance, Indexing, Query Optimization, T-SQL | 4 Comments »

SQL Server 2016 – General Availability Date Announced

Posted by Sudarshan Narasimhan on May 3, 2016

Extra Extra – Read all about it.

Exciting news as Microsoft just announced the general availability date for SQL Server 2016 – June 1st, 2016

You can read the announcement and more on the Data Platform Insider Blog


SQL 2016 comes in Enterprise, Standard, Express and Developer Editions. You can compare the editions using the datasheet published here


Stay tuned for more as the release date approaches. For those looking to move away from Oracle Databases, if you have/get Software Assurance then the SQL 2016 Editions costs are free. You can read more about this offer here,


Posted in SQL News | Leave a Comment »