HOW TO identify whether or not an index file has ever been used? 1

[sobeit]

We're using SQL Server 7.0.

I like to remove any index that is not being used by any applications, but I don't want to do a profile on the database and run every possible functions in all applications.

Is there a better way?

Thanks!

 

[tlbroadbent]

I don't know how you can identify which indexes are used (or not used) unless you do some research or you simply remove a candidate index and watch what happens. If you know your data and the applications well enough, you can often determine which indexes should be most/least useful.

One technique you might try is to create an aggregate query to count the occurrences of values in an indexed column. If a value is not well distributed or there are few values for the number of rows in the table, that index may not be used and could be removed.

Suppose a tale contains a status code column and there are 12 possible statuses. The aggregate query might show that 90% of the rows in the table contain one particular status while the other 11 codes are spread over the remaining 10% of the data. Due to poor distribution of the data, status may not be a good candidate for an index.

Suppose another table contains demographic info including gender. Gender would likely be a poor index candidate because there are only two values. In a table containing 1000s of rows, SQL Server may not choose to use an index on the gender column.

You are still going to have to do some analysis if you want to determine which indexes can be removed and additional indexes that may increase performance. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.