SQL Server Performance

Determining Index Usage

Discussion in 'Performance Tuning for DBAs' started by tomhasquestions, Jan 6, 2003.

  1. tomhasquestions New Member

    I've noticed that my indexes are rather large, approximatly 50% of my total database size. I'd like to eliminate a few, but I am not sure which are used lest often (or not at all). Is there a way to determine what indexes are being used, and how often?

    There are a large number of different queries/operations that occur on this database, so it would be very difficult to analize each with query analyzer or index tuning wizard. I'd rather have a report showing each index and the number of times run during a time interval or something similar.

    -- Tom
    Motivation: If pretty poster and a cute saying are all that it takes to motivate you, you probably have a very easy job. The kind robots will be doing soon.
  2. bradmcgehee New Member

    I am curious as to why you don't want to use the Index Tuning Wizard. One of its best features is to identify indexes that are not used. If you use Profiler to capture a representative trace, and then run it through Index Wizard (be sure you select the option to identify unused indexes), this is a relatively easy process, no matter how many tables or indexes you have.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. tomhasquestions New Member

    Brad,
    My resident 'sql expert' suggested i not rely on the tuning wizard due to problems it has (he says) with indexes with several rows. I also lack faith in my ablity to correctly select all needed options in the SQL profiler when creating the trace table.

    - Will the tuning wizard work well with large tables and indexes that have multiple rows?
    - Is the 'index tuning' profiler template really 'one-size-fits-all', or do i need to customize it?
    -Many reports/processes are initiated through storce procedures that are stored within a seperate DB on the same server. Will this impact the ability of the ttuning wizard to make accurate recommendations?

    -- Tom
    Motivation: If pretty poster and a cute saying are all that it takes to motivate you, you probably have a very easy job. The kind robots will be doing soon.
  4. satya Moderator

    Index tuning wizard is a powerful tool which analyzes the indexes and its usage, to fine tune them you need to run PROFILER to capture the trace which helps in defining the recommendations.

    Satya SKJ
  5. josephobrien New Member

    There is a brilliant Perl script that a person name Linchi Shea wrote that does a great job in summerizing index usage. It is listed in the Pinnicle Publishing's (www.pinnaclepublishing.com) Sql Server Professional News. One would use Trace to grab the explain plan - save the trace to a dtb table, save the explain plan output to a text file and then use the per script to summerize the index usage. The script also queries the database for all the names of all the indexes so it produces a listing of unused indexes...Just a brilliant, brilliant script...
  6. bradmcgehee New Member

    The Index Wizard is not perfect, but it is a good place to start. Once I run the Wizard, I then evaluate its suggestions, and make or don't make the suggestions based on my experience. After that, then I starting looking for long running queries and view Graphical Execution Plans of them to try and indentify better ways to optimize queries.

    The Index Wizard does not look at every row in a trace, only some of them. Because of this, different runs of the Wizard against the same table may produce somewhat different results. Sometimes, I run the Wizard two or three times against the same trace to get good results. I don't know of any issues with the Wizard and large tables with multiple rows. I use the Wizard all the time with such tables.

    See my tips on the Index Wizard to find out what you need to capture in your Profiler Trace. Essentially, it is not very much.

    Having the SP stored in another database will not affect the Index Wizard, but it may affect your appliation's performance. For best performance, be sure you refers to these SPs using their fully qualified name.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  7. sqljunkie New Member

    I agree with josephobrien in his previous post.

    You can also collect a Profiler trace with just the execution plans and write your own query to compare the indexes used in the execution plans to the indexes that are listed in the sysindexes table.
  8. Chappy New Member

    I have a question vaguely related to this thread...
    If I have an execution plan in textual format (as supplied by SET SHOWPLAN_TEXT), is there any way I can persuade Query Analyser to render it into a nice diagram?
    Im thinking not, and that it will only accept input directly from SQL server, which is a shame, especially when comparing execution plans.
  9. bradmcgehee New Member

    Chappy, I have never seen this feature from QA, although there are some third-party programs that more or less do this.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page