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.