SQL Server Performance

Index usage statistics

Discussion in 'Performance Tuning for DBAs' started by anil.nair@gtnexus.com, Nov 14, 2002.

  1. Is there any way I can tell which indexes in my databases are being used and how often? I want to see this to determine which indexes in my database are really being used vs. what the developers assumed would be needed.

    I am not looking for the answer that says: Use Query Analyzer and look at the execution plan to see which index is used for a particular query.

    I am looking for something that says: over the last month index 'a' was used 984326 times but index 'b' was used only 820 times.

    Does SQL server keep track of some counter which is incremented when a query uses a index? Maybe this is not done by default. Is there anyway I can turn this on and ask SQL server to keep track of this for a period of time after which I could turn this off.

  2. bradmcgehee New Member

    I know of no way to do what you want automatically through, although I understand why you would want to capture this information. The closest I can think of is to perform a Profiler trace over time, although it would be difficult to capture that much data with Profiler over a period of months. Generally, Profiler is used to capture small time frames of data, such as an hour or two. Trying to capture data for multiple days, or longer would eat up a lot disk space and resources.

    Brad M. McGehee
  3. satya Moderator

    The Index Tuning Wizard in can help you avoid these problems. It recommends the right mix of indexes and indexed views for the given workload of queries and updates.

    To make its decision, it employs usage statistics, it consults the query processor in evaluating the usefulness of indexes and indexed views, and it searches the space of possible indexes and indexed views.

    In addition, the wizard requires a workload (specified as a file or a table) as its input

    So for the queries as Brad referred run PROFILER to capture the trace.


    Satya SKJ
  4. josephobrien New Member

    There is a great perl script script called IndexRpt.pl written by a
    person name Linchi Shea that does just what you want.
    The script and article describing its use was published in Pinnicle Publishing's SQL Professional Magazine/Newsletter.

    The idea is that you use profiler/trace to collect explain plans, save this information to a database table - and then to a
    text file.

    The script summarizes the index and access paths and also queries the database for all of the existing indeces and makes note of any indexes that are not used.

    A great, great , great script.

    Joe E O

Share This Page