Simple but accurate following script will give you list of all the indexes in the database which are unused. If indexes are not used they should be dropped as Indexes reduces the performance for INSERT/UPDATE statement. Indexes are only useful when used with SELECT statement.
Script to find unused Indexes.
USE
AdventureWorks
GO
DECLARE
@dbid
INT
SELECT
@dbid
=
DB_ID
(
DB_NAME
())
SELECT
OBJECTNAME
=
OBJECT_NAME
(
I.
OBJECT_ID
),
INDEXNAME
=
I.NAME
,
I.INDEX_ID
FROM
SYS.INDEXES?I
JOIN
SYS.OBJECTS?O
ON
I.
OBJECT_ID
=
O.
OBJECT_ID
WHERE
OBJECTPROPERTY
(
O.
OBJECT_ID
,
'IsUserTable'
)
=
1
AND
I.INDEX_ID
NOT
IN
(
SELECT
S.INDEX_ID
FROM
SYS.DM_DB_INDEX_USAGE_STATS?S
WHERE
S.
OBJECT_ID
=
I.
OBJECT_ID
AND
I.INDEX_ID
=
S.INDEX_ID
AND
DATABASE_ID
=
@dbid
)
ORDER?BY
OBJECTNAME
,
I.INDEX_ID
,
INDEXNAME
ASC
GO
Note: This is only for SQL Server 2005, it will not run for previous version of SQL Server.
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
