SQL Server 2000 - How to find out what indexes are in use?

Question

In SQL Server 2000, how can I find out what indexes are being used?

I can find a list of all the indexes easily enough, but how do I know which ones can be removed because they are no longer used?

I don't want to slog through all the sql used on our server running explain plans to find this out and also risk missing something.

Answer 1

The information is not kept in SQL Server itself until version 2005 (see this link for how to do it there: http://sqlserverpedia.com/wiki/Find_Indexes_Not_In_Use). Until then, you'll have to test each index manually.

Answer 2

Hi

As Joel says SS2005 has means (http://samsudeenb.blogspot.com/2008/07/index-maintenance-using-dmvs-in-sql.html) to find what you need. As far as SS2000 is concerned, you might try this crude way -

Try to capture workload of your server using a trace. Check this link for more on workloads and traces http://msdn.microsoft.com/en-us/library/ms190957.aspx?ppud=4

Once you have the workload, you need to invoke Index Tuning Wizard i.e. ITW (http://technet.microsoft.com/en-us/library/cc966541.aspx). You can then pick the Index Usage Report generated by ITW. This report lists the indexes used by your workload.

Then you will have to compare that list with information from sysindexes table to get all indexes which are no longer used.

I hope it is of some help to you.

cheers