SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Index Breakdown

By Sean Smith,

I've always been wary about dealing with indexes and indexing strategies. As I learn more and more about the subject I realize how little I actually know. On the surface indexing can appear quite simple and straightforward. But the reality is that indexing requires a great deal of knowledge, can differ from environment to environment, and is basically an art form.

A while back I was tasked with investigating performance issues within an application driven by a SQL Server database solution. It ended up coming down to improper indexing, but in the course of the investigation I started to notice other, unrelated, indexing issues. As I dug further I needed more and more data to properly assess each and every index in the database (I won't go into further details but the design was a disaster to say the least). By the time I had finished my investigation and applied my fixes I was left with a fairly robust script that lists out many details regarding each and every index contained within a database.

Below is a listing of all the fields in the output. It's somewhat overwhelming at first, but depending on what one column's output is will determine what additional columns you'll likely want to look at. I will try to explain how I evaluate the data though I won't go through all of the columns nor will I go into any extensive details as you should already have a decent understanding of indexes if you're going to be using the script for any type of troubleshooting and / or architectural restructuring.

NOTE: I do not claim to be an indexing expert in any way, shape, or form. I simply want to make available to others what I've learned as well as to share some code which might come in handy for individuals who find themselves in a situation similar to the one I experienced. Also, be very careful when playing around with indexes. What seems like a good idea / change could have an extremely negative impact!

Output Columns:

  • object_type: type of object (will either be "U" for User-Defined Table or "V" for View)
  • database_name: name of the database
  • schema_name: name of the schema
  • object_name: name of the object
  • create_date: date the object was created (time portion truncated, meant to give a general idea of how long ago the object was created)
  • modify_date: date the object was last modified (time portion truncated, will contain ".................." if it is the same date as the create_date column)
  • rows: total rows of data contained within the object
  • total_mb: total size of the object in megabytes
  • used_mb: total space used in megabytes
  • unused_mb: space reserved but not yet allocated in megabytes
  • data_mb: space used by data in megabytes
  • index_mb: space used by indexes in megabytes
  • pct_data: percentage of space used which is allocated to data
  • pct_index: percentage of space used which is allocated to indexes
  • index_type: type of index (Clustered, Nonclustered, Heap)
  • index_name: name of the index
  • system_named: indicates if the index name was generated by SQL Server or explicitly named (applicable to PKs and Unique Constraints / Indexes only)
  • is_pk: indicates if the index is a primary key
  • unique: indicates if the index is unique and whether its uniqueness if enforced by the index definition (displayed as "I") or a constraint (displayed as "C")
  • disabled: indicates if the index is disabled
  • hypothetical: indicates if the index is hypothetical
  • total_columns: number of columns which comprise the entire object
  • index_columns: number of columns which comprise the index key
  • include_columns: number of columns which comprise the include key
  • index_pct_of_columns: percentage of columns that make up the index as compared to the entire object
  • include_pct_of_columns: percentage of columns that make up the include as compared to the entire object
  • total_pct_of_columns: percentage of columns that make up the index + include as compared to the entire object
  • key_mb: space used by this particular index in megabytes
  • key_mb_pct: percentage of space used as compared to total space allocated to all indexes on the object
  • max_key_size_bytes: maximum possible byte size of the index based on the data size(s) of the column(s) involved
  • over_key_size_limit: indicates if the index key size exceeds the 900 byte limit
  • index_key: list of columns which comprise the index (ordinal sort) showing column name, data type, size, precision, scale, and sort order (ascending / descending)
  • include_key: list of columns which comprise the include portion of the index (ordinal sort) showing column name, data type, size, precision, scale, and sort order (ascending / descending)
  • filter_definition: filtered index definition / criteria
  • dupe_id: indicates if the index is a duplicate (assigns a random "dupe group" ID and shows a count of the total times the index is duplicated)
  • unused: indicates if the index is unused and therefore can potentially be dropped (does not look at PKs, Indexed FKs, Clustered Indexes, Unique Indexes / Constraints)
  • statistics_date: date when statistics were last updated on the index (blank for Heaps)
  • row_locks: indicates if the index allows row locks
  • page_locks: indicates if the index allows page locks
  • ignore_dupes: determines the error response when an insert operation attempts to insert duplicate key values into a unique index
  • auto_stats: indicates if statistics are automatically recomputed
  • padded: indicates if index padding is used
  • fill_factor: percentage of space to use when the index is created / rebuilt
  • user_seeks: total seeks by user queries
  • user_scans: total scans by user queries
  • user_lookups: total bookmark lookups by user queries
  • user_updates: total updates by user queries
  • last_user_seek: date of last user seek (time portion truncated)
  • last_user_scan: date of last user scan (time portion truncated)
  • last_user_lookup: date of last user lookup (time portion truncated)
  • last_user_update: date of last user update (time portion truncated)
  • system_seeks: total seeks by system queries
  • system_scans: total scans by system queries
  • system_lookups: total bookmark lookups by system queries
  • system_updates: total updates by system queries
  • last_system_seek: date of last system seek (time portion truncated)
  • last_system_scan: date of last system scan (time portion truncated)
  • last_system_lookup: date of last system lookup (time portion truncated)
  • last_system_update: date of last system update (time portion truncated)

Some Things To Keep In Mind / Look For Right Off The Bat:

  • Check the dupe_id column first, this is the easiest way to find low-hanging fruit
  • Check the unused column, but keep in mind how often the data is accessed and how long the instance has been online before deciding to drop any indexes
  • Make sure your statistics are up to date as this plays a large roll on how and when indexes are (or not) used
  • Compare how much of the object's space is used by indexes, and if it seems overly large consider dropping indexes which are rarely used and / or do little other than take up space
  • Check to make sure your index does not exceed the maximum index key size (see the over_key_size_limit column for this)
  • Even if an index is used, consider looking at the number of user_scans, user_seeks, etc. columns (if it is low then the index is likely not utilized that often and you are really just incurring a penalty to maintain it)
  • If an index is used, check to make sure it's not simply being used by the system for things such as updating statistics (check the system_scans, system_seeks, etc. columns)

Some Final Notes On The Script:

  • Certain rows of the output will have duplicate information suppressed for easier readability (if you prefer a more traditional grid style then simply change the @Report_Style variable contained within the script from 1 to 0)
  • The script does not include Spatial Indexes, XML Indexes, etc. (only Heaps, Clustered Indexes, and Non-Clustered Indexes), and some corresponding data values (such as size details) are calculated on these attributes only
  • Percentage breakdowns may not roll up to exactly 100% due to rounding issues
  • Certain stats (such as seeks / scans) on indexed views may not be what you'd expect (this will depend on how you query the object, for example whether you use WITH (NOEXPAND) or not on indexed views)

Any friendly feedback is always welcome. Enjoy!

Total article views: 6715 | Views in the last 30 days: 5
Related Articles

Column and Row Total

Column and Row Total


Index on BIT column

Index on BIT column





Indexes optimize for the system databases

Maintenance of the system databases in SQL Server regarding the indexes is important. The system tab...


SSAS Total column

Change the formula / other aggregated formula on the SSAS Total Column