Monday, November 19, 2012

SQL Server: Indexes List With Key and Involved Columns Name Alongwith Usage Statistics

A year back I have shared a script from query bank, which can be helpful to get indexes list of a database, with key and involved columns. Being a DBA, I never remember a day, without using this script. 
Getting detail of indexes on a database most of the time I also need indexes usage statistics, through which I can figure out which indexes are being used and which indexes can be discarded. 
To avoid to use two separate scripts, let me share following script which brings both, usage and structural information for all indexes of a database or a given table.


Script output

User Seek +  User Scans will decide which index are useful and which are just burden for database. Indexes with less seek + scan should be removed for better DML operations performance.

4 comments:

  1. where is the script?

    ReplyDelete
    Replies
    1. you can directly get it from https://gist.github.com/lionofdezert/3722342#file-alldbindexeslistwithincludecolumns-sql

      Delete
  2. Hi, Thanks for posting index usages script, I have doubt if User Seek column have high number - 95123 and User scan = 0, so i can deceide this NC index havey usage.. Pls. confirm.

    ReplyDelete
    Replies
    1. High number of seeks and less or zero scan means, index is well defined and is being used for query execution.

      Delete

All suggestions are welcome