Sql-server – SQL Server 2000 – How to find unused indexes

sql serversql-server-2000

In SQL Server 2000 – how can we find unused indexes, so that we can drop them.]

I have scripts for 2008 but don't know how to do it in SQL Server 2000.

Please help.

Regards

Best Answer

The only thing I can recommend is to put your 2000 database on a server with SQL Server 2005 or greater (a development or test server) installed, find the unused indexes with the help of the new DMVs and once you have a list of changes apply them back to the production SQL Server 2000 instance.

Please be aware that SQL Server 2000 is no longer supported and you should move your production processes, if possible, to a later version of SQL Server.

An example query in SQL Server 2012 using the sys.dm_db_index_usage_stats DMV:

SELECT u.*
FROM [sys].[indexes] i
JOIN [sys].[objects] o
ON (i.OBJECT_ID = o.OBJECT_ID)
LEFT JOIN [sys].[dm_db_index_usage_stats] u
ON (i.OBJECT_ID = u.OBJECT_ID)
AND i.[index_id] = u.[index_id]
AND u.[database_id] = DB_ID() --returning the database ID of the current database
WHERE o.[type] <> 'S' --shouldn't be a system base table
AND i.[type_desc] <> 'HEAP'
AND i.[name] NOT LIKE 'PK_%'
AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0
AND u.[last_system_scan] IS NOT NULL
ORDER BY 1 ASC
Related Question