开发者

Do unused indexes cause performance issues with sql server? And should I remove them?

I've spent some time researching this and simply can't find a definitive answer. Here is the query I am using to look for indexes not used:

SELECT object_name(i.object_id) as tableName, i.name as indexName
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id AND s.database_id = db_id()
WHERE objectproperty(i.object_id,'IsUserTable') = 1 and i.index_id> 0
AND s.object_id IS NULL
AND i.is_Primary_Key = 0
AND i.is_unique_constraint = 0
AND i.is_unique = 0

My understanding is that indexes returned by this query have not been 开发者_如何转开发utilized by the query optimizer since sql server was last restarted. I believe I should remove them....I just don't fully understand if there are performance implications or harm in leaving them.


The harm of leaving them in is the space used and the increased work for INSERT, UPDATE and DELETE statements as they have to keep the indexes updated as well as the base table.


There are performance implications in leaving them there; indexes are maintained for every insert, update or delete to the table.

On the other hand, how do you know the index hasn't been used? The DMV is great, but it only covers the time since the last restart of SQL Server. So if the DMV does not have data for a full business cycle, you may be killing the CEO's annual report the next time he runs it.

In my humble opinion: Never, ever, ever drop an index based solely on the information in the usage DMV.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜