Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


share|improve this question
Maybe copy your db in sql server 2008 and use that script to find them,if thats an option. – Mihai Jan 1 '14 at 8:12

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:

FROM [sys].[indexes] i
JOIN [sys].[objects] o
LEFT JOIN [sys].[dm_db_index_usage_stats] u
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
share|improve this answer
You should mention that you have to point your application or some equivalent workload generation at the new server, preferably representing a full business cycle. Anything less will contain more unused indexes than probably actually happen in production. – Aaron Bertrand Jan 1 '14 at 23:22
This is true. I shouldn't have assumed he would know that. Thanks. – Troy Jan 2 '14 at 21:46

Unused Index information is not exposed in sql 2000 as SQL 2000 and lower versions did not have DMV's.

The only possible way (in sql 2000) is to use a server side trace to see what tables and indexes are used by your queries for the entire business cycle or workload period.

You should plan for an upgrade to higher versions like 2008R2 or 2012 as SQL Server 2000 is now officially unsupported.

share|improve this answer
A trace won't really tell you which indexes are used - you'd have to analyze the execution plans. – Aaron Bertrand Jan 2 '14 at 0:01
Good catch and agreed.. Thanks @aaronbertrand ! I can't imagine any other way to find unused indexes on SQL2000. – Kin Jan 2 '14 at 13:13

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.