Maintaining Index on SQL SERVER 2005 - Part 1

Indexing can perform your database faster, but sometimes it's make your database slower. It is caused using index make your database faster within searching data and slower within insert or update data. So you need to be analyze the index if you want to implement or not. if you've had index that reasonable so you have to maintain it whether it is uses or not.

Use this query to find your useless or uses index :

Declare @tablename varchar(200)
    ,@indexName varchar(500)
    ,@indexColumn Varchar(500)

CREATE TABLE #INDEX_TEMP_STATISTICS
(
    alldensity numeric(29,10),
    averagelength numeric(29,6),
    columns varchar(500)
)

CREATE TABLE #INDEX_STATISTICS
(
    tablename varchar(200),
    indexname varchar(500),
    alldensity numeric(29,10),
    averagelength numeric(29,6)
)

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE C_CURSOR CURSOR FAST_FORWARD FOR
    SELECT
        o.name,
        i.name,
        RTRIM(LTRIM(ISNULL(MIN (CASE ik.keyno WHEN 1 THEN c.name END),'') + (CASE WHEN ISNULL(MIN (CASE ik.keyno WHEN 2         THEN c.name END),'T') = 'T' THEN '' ELSE ',' END) +
        ISNULL(MIN (CASE ik.keyno WHEN 2 THEN c.name END),'') + (CASE WHEN ISNULL(MIN (CASE ik.keyno WHEN 3 THEN c.name         END),'T') = 'T' THEN '' ELSE ',' END) +
        ISNULL(MIN (CASE ik.keyno WHEN 3 THEN c.name END),'') + (CASE WHEN ISNULL(MIN (CASE ik.keyno WHEN 4 THEN c.name         END),'T') = 'T' THEN '' ELSE ',' END) +
        ISNULL(MIN (CASE ik.keyno WHEN 4 THEN c.name END),'') + (CASE WHEN ISNULL(MIN (CASE ik.keyno WHEN 5 THEN c.name         END),'T') = 'T' THEN '' ELSE ',' END) +
        ISNULL(MIN (CASE ik.keyno WHEN 5 THEN c.name END),'') + (CASE WHEN ISNULL(MIN (CASE ik.keyno WHEN 6 THEN c.name         END),'T') = 'T' THEN '' ELSE ',' END) +
        ISNULL(MIN (CASE ik.keyno WHEN 6 THEN c.name END),'') + (CASE WHEN ISNULL(MIN (CASE ik.keyno WHEN 7 THEN c.name         END),'T') = 'T' THEN '' ELSE ',' END) +
        ISNULL(MIN (CASE ik.keyno WHEN 7 THEN c.name END),'') + (CASE WHEN ISNULL(MIN (CASE ik.keyno WHEN 8 THEN c.name         END),'T') = 'T' THEN '' ELSE ',' END) +
        ISNULL(MIN (CASE ik.keyno WHEN 8 THEN c.name END),'') + (CASE WHEN ISNULL(MIN (CASE ik.keyno WHEN 9 THEN c.name         END),'T') = 'T' THEN '' ELSE ',' END) +
        ISNULL(MIN (CASE ik.keyno WHEN 9 THEN c.name END),'') +
        ISNULL(MIN (CASE ik.keyno WHEN 10 THEN c.name END),'')))
FROM sysobjects o
    JOIN sysindexes i ON i.id = o.id
    JOIN sysindexkeys ik ON ik.id = i.id
        AND ik.indid = i.indid
    JOIN syscolumns c ON c.id = ik.id
        AND c.colid = ik.colid
    JOIN INFORMATION_SCHEMA.TABLES IFT ON IFT.table_name = o.name
        AND IFT.table_type = 'BASE TABLE'
WHERE i.indid BETWEEN 1 AND 254
    AND indexproperty(o.id, i.name, 'IsStatistics') = 0
    AND indexproperty(o.id, i.name, 'IsHypothetical') = 0
--And o.name = 'Addresses'
GROUP BY o.name, i.name
ORDER BY o.name, i.name

OPEN C_CURSOR

FETCH NEXT FROM C_CURSOR INTO @tablename,@indexName,@indexColumn

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #INDEX_TEMP_STATISTICS
    EXEC ('DBCC SHOW_STATISTICS (''' + @tablename + ''',' +     @indexName + ') WITH density_vector,NO_INFOMSGS')

    INSERT INTO #INDEX_STATISTICS
    SELECT @TABLENAME,@INDEXNAME,
        ALLDENSITY * 100,
        averagelength
    FROM #INDEX_TEMP_STATISTICS
    WHERE [columns] = @indexColumn

    print @indexColumn

    DELETE FROM #INDEX_TEMP_STATISTICS

    FETCH NEXT FROM C_CURSOR INTO     @tablename,@indexName,@indexColumn
END

CLOSE C_CURSOR
DEALLOCATE C_CURSOR

SELECT * FROM #INDEX_STATISTICS
Order by tablename

DROP TABLE #INDEX_STATISTICS
DROP TABLE #INDEX_TEMP_STATISTICS

See the result ...

If the alldensity field > 10, i suggest that index must be dropped. I think it is useless, Cause Sql Server has a logic if the density of index higher than 10%, it uses table scans (I have read other references that density index higher than 15%). So the index is never uses. If you keep this index, it doesn't make your database performance better instead it getting worse and worse, like i said at the beginning, your INSERT or UPDATE performance will fall.