As with anything you do to a database you run your business on, backups should be made and care taken to ensure that you have a recovery plan if you unintentionally remove something you shouldn't have.  For example, you should never remove any unique index as the ERP vendor may be using this to enforce database integrity.  Thanks for visiting the site and I hope this helps!

Unnecessary Indexes

You can run additional queries to identify indexes that you could easily do without, even if occasionally used.  The following query will return indexes where the first column is identical.  Typically, you would want to keep the index that has the left most column and additional columns as the one with less columns is a subset of the other and the information is redundant.  This query can be run in SQL Server Management Studio or if your ERP program provides one, a query window. 

Having the proper indexes in place in a relational database is important to ensure that you get the performance you are expecting.  Having too many can ultimately have the reverse effect.  SQL Server starting with SQL 2005 keeps track of which ones don't get used in it's Dynamic Management Views.  The information kept in some of these views is reset when you alter the schema of a table and it is all reset when you restart SQL Server, so using these to identify the unused indexes is best done after the server has been up for an extended period of time.  The following query can be run in the SQL Server Management studio, or if your ERP program provides one, a query window.  It lists all the indexes not used that have been updated since the DMV for that index was reset.

Begin
select
    s.Name + N'.' + t.name as [Table]
    ,i1.index_id as [Index1 ID], i1.name as [Index1 Name]
    ,dupIdx.index_id as [Index2 ID], dupIdx.name as [Index2 Name] 
    ,c.name as [Column]
from 
    sys.tables t join sys.indexes i1 on
        t.object_id = i1.object_id
    join sys.index_columns ic1 on
        ic1.object_id = i1.object_id and
        ic1.index_id = i1.index_id and 
        ic1.index_column_id = 1  
    join sys.columns c on
        c.object_id = ic1.object_id and
        c.column_id = ic1.column_id      
    join sys.schemas s on 
        t.schema_id = s.schema_id
    cross apply
    (
        select i2.index_id, i2.name
        from
            sys.indexes i2 join sys.index_columns ic2 on       
                ic2.object_id = i2.object_id and
                ic2.index_id = i2.index_id and 
                ic2.index_column_id = 1  
        where    
            i2.object_id = i1.object_id and 
            i2.index_id > i1.index_id and 
            ic2.column_id = ic1.column_id
    ) dupIdx     
order by
    s.name, t.name, i1.index_id
    End

Begin
SELECT TOP 1
        DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,user_updates    
        ,system_updates    
INTO #TempUnusedIndexes
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND    user_seeks = 0
    AND user_scans = 0 
    AND user_lookups = 0
    AND s.[object_id] = -999;

EXEC sp_MSForEachDB    'USE [?]; 
INSERT INTO #TempUnusedIndexes 
SELECT     
        DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,user_updates    
        ,system_updates    
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
    AND    user_seeks = 0
    AND user_scans = 0 
    AND user_lookups = 0
    AND i.name IS NOT NULL and i.is_Unique = 0
    ORDER BY user_updates DESC;'

SELECT * FROM #TempUnusedIndexes ORDER BY [user_updates]  DESC
DROP TABLE #TempUnusedIndexes
End