NOTE:Populating the table name in the function for the @ObjectID variable is optional. If the value of @ObjectID is NULL then information on all tables is returned.

Index Analysis Columns

The query populates the following columns:

row_id: Row identifier used for populating the table

index_action:Analysis recommendation on action to take on the index

CREATE: Recommend adding the index to the table.

DROP: Recommend dropping the index from the table

BLEND: Review the missing index details to see if the missing index details can be added to an existing index.

REALIGN: Bookmark lookups on the index exceed the number of seeks on the table. Recommend investigating whether to move the clustered index to another index or add included columns to the indexes that are part of the bookmark lookups.

schema_id: Schema ID

schema_name: Name of the schema.

object_id: Object ID

table_name: Name of the table name

index_id: Index ID

index_name: Name of the index.

is_unique: Flag indicating whether an index has a unique index.

has_unique: Flag indicating whether the table has a unique index.

type_desc: Type of index; either clustered or non-clustered.

partition_number: Partition number.

fill_factor: Percentage of free space left on pages the index was created or rebuilt.

buffered_page_count: Total number of pages in the buffer for the index.

buffer_mb: The amount of space in MB in the buffer for the index.

pct_in_buffer: The percentage of an index that is current in the SQL Server buffer.

table_buffer_mb: The amount of space in MB in the SQL Server buffer that is being utilized by the table.

row_count: Number of rows in the index.

impact: Calculation of impact of a potential index. This is based on the seeks and scans that the index could have utilized multiplied by average improvement the index would have provided. This is included only for missing indexes.

existing_ranking: Ranking of the existing indexes ordered by user_total descending across the indexes for the table.

user_total: Total number of seek, scan, and lookup operations for the index.

user_total_pct: Percentage of total number of seek, scan, and lookup operations for this index compared to all seek, scan, and lookup operations for existing indexes for the table.

estimated_user_total_pct: Percentage of total number of seek, scan, and lookup operations for this index compared to all seek, scan, and lookup operations for existing and potential indexes for the table. This number is naturally skewed because a seek for potential Index A resulted in another operation on an existing index and both of these operations would be counted.

user_seeks: Number of seek operations on the index.

user_scans: Number of scan operations on the index.

user_lookups: Number of lookup operations on the index.

user_updates: Number of update operations on the index.

read_to_update_ratio: Ratio of user_seeks, user_scans, and user_lookups to user_updates.

read_to_update: Division of user_seeks, user_scans, and user_lookups by user_updates.

update_to_read: Division of user_updates to user_seeks, user_scans by user_lookups.

row_lock_count: Cumulative number of row locks requested.

row_lock_wait_count: Cumulative number of times the Database Engine waited on a row lock.

row_lock_wait_in_ms: Total number of milliseconds the Database Engine waited on a row lock.

row_block_pct: Percentage of row locks that encounter waits on a row lock.

avg_row_lock_waits_ms: Average number of milliseconds the Database Engine waited on a row lock.

page_latch_wait_count: Cumulative number of times the page latch waits occurred

avg_page_latch_wait_ms: Average number of milliseconds the Database Engine waited on a page latch wait.

page_io_latch_wait_count: Cumulative number of times the page IO latch waits occurred

avg_page_io_latch_wait_ms: Average number of milliseconds the Database Engine waited on a page IO latch wait.

tree_page_latch_wait_count: Cumulative number of times the tree page latch waits occurred

avg_tree_page_latch_wait_ms: Average number of milliseconds the Database Engine waited on a tree page latch wait.

tree_page_io_latch_wait_count: Cumulative number of times the tree page IO latch waits occurred

avg_tree_page_io_latch_wait_ms: Average number of milliseconds the Database Engine waited on a tree page IO latch wait.

nonleaf_page_merges: Cumulative count of page merges above the leaf level.indexed_columns: Columns that are part of the index, missing index or foreign key.

included_columns: Columns that are included in the index or missing index.

indexed_columns_compare: Column IDs that are part of the index, missing index or foreign key

included_columns_compare: Column IDs that are included in the index or missing index.

duplicate_indexes: List of Indexes that exist on the table that are identical to the index on this row.

overlapping_indexes: List of Indexes that exist on the table that overlap the index on this row.

related_foreign_keys: List of foreign keys that are related to the index either as an exact match or covering index.

related_foreign_keys_xml: XML document listing foreign keys that are related to the index either as an exact match or covering index.

Disclaimers

There are a few things to take note of when looking at this information:

The recommendations in the Index Action column are not black and white recommendations. They are more light grey ideas of what may be appropriate. Always use your experience with the database in place of a blanket recommendation.

The information in the DMVs is gathered from when the SQL Server service last started or when the database was last created, which ever event is more recent.

The index statistics accumulated in sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats are reset when the index is rebuilt.

The index statistics for a table that are accumulated in the DMVs sys.dm_db_missing_index_* are reset whenever an index is created on the table.

The index name provided in the name column for indexes that do not exist is not a recommended name for the index. It’s just an informative placeholder.

Usage Scenarios

I haven’t included any usage scenarios with this post. If you have a table profile you’d like to see covered, feel free to comment below or reach out to me via e-mail. Maybe I can make a post out of it and provide some ideas on improving the indexing for your table while explaining how I use the query results in the post.