http://www.sqlservercentral.com/blogs/stratesql/2010/10/06/return-of-index-analysis-part-2/

Return of Index Analysis Part 2

By StrateSQL, 2010/10/06

Let’s Anaylze An Index!

Time for part two of my continued Index Analysis query.  The previous posts in this series are:

1. Analyze Your Indexes Part 1 – Combine existing index statistics and missing indexes into a single output
2. Analyze Your Indexes Part 2 – Find existing duplicate and overlapping indexes
3. Analyze Your Indexes Part 3 – Find the relationship between indexes and foreign keys
4. Analyze Your Indexes Part 4 – Add size and current memory utilization
5. Analyze Your Indexes Part 5 – Add in index analysis guidelines
6. Analyze Your Indexes Part 6 – Add in blocking statistics
7. Analyzing Your Indexes with a Custom Report
8. Return of Index Analysis Part 1 – Add in data from sys.dm_db_index_operational stats.

Alright, introduction… check.  Let’s get to this post.

Pro / Con Columns

One of the things that I’ve noticed as I’ve added data to this view is that the amount of information can be overwhelming.  There’s a lot to take in and looking at the information at a glance doesn’t uncover much.  The index action column provides some information but it isn’t too informative because it requires knowing more about the index to determine if you want to use it.

As a result, I’ve added a couple columns to provide some fast facts about the index without digging into the details.  When I was originally envisioning the column I was thinking about the dashboard of dollar signs and codes you often see with hotel and restaurant reviews.  With a quick glance you get a lot of information.

For the index pros, I’ve added the following codes:

• FK: The index schema maps to a foreign key.
• UQ: Index is a unique constraint
• $, $$, $$$, $$$+: Indicates the ratio of read to write uses in execution plans.  The higher the ratio the more dollar signs; this should correlate to greater benefit provided by the index.

On the opposite side, the following index cons were added:

• SCN: Flag indicating that the ratio of seeks to scans on the index exceeds 1,000.
• DP: Index schema is a duplicate of another index.
• OV: Index schema overlaps another index.
• $, $$, $$$, $$$+: Indicates the ratio of write to read uses in execution plans.  The higher the ratio the more dollar signs; this should correlate to greater benefit provided by the index.

This list isn’t all inclusive, if you have some you think are worth adding please leave a comment below.  Also, if you disagree comment as well.  This query is aimed at being a community resource and, as such, community input is welcomed.

Index Analysis Query

Adding the pro and con columns to the previous query get us up to the current version:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @ObjectID int
    ,@DB_ID int

SELECT @ObjectID = OBJECT_ID('')
    ,@DB_ID = db_id()

IF OBJECT_ID('tempdb..#IndexBaseLine') IS NOT NULL
    DROP TABLE #IndexBaseLine

CREATE TABLE #IndexBaseLine
    (
    row_id int IDENTITY(1,1)
    ,index_action varchar(10)
    ,pros varchar(25)
    ,cons varchar(25)
    ,schema_id int
    ,schema_name sysname
    ,object_id int
    ,table_name sysname
    ,index_id int
    ,index_name nvarchar(128)
    ,is_unique bit DEFAULT(0)
    ,has_unique bit DEFAULT(0)
    ,type_desc nvarchar(67)
    ,partition_number int
    ,fill_factor tinyint
    ,is_padded bit
    ,reserved_page_count bigint
    ,size_in_mb decimal(12, 2)
    ,buffered_page_count int
    ,buffer_mb decimal(12, 2)
    ,pct_in_buffer decimal(12, 2)
    ,table_buffer_mb decimal(12, 2)
    ,row_count bigint
    ,impact int
    ,existing_ranking bigint
    ,user_total bigint
    ,user_total_pct decimal(6, 2)
    ,estimated_user_total_pct decimal(6, 2)
    ,user_seeks bigint
    ,user_scans bigint
    ,user_lookups bigint
    ,user_updates bigint
    ,read_to_update_ratio nvarchar(30)
    ,read_to_update int
    ,update_to_read int
    ,row_lock_count bigint
    ,row_lock_wait_count bigint
    ,row_lock_wait_in_ms bigint
    ,row_block_pct decimal(6, 2)
    ,avg_row_lock_waits_ms bigint
    ,page_latch_wait_count bigint
    ,avg_page_latch_wait_ms bigint
    ,page_io_latch_wait_count bigint
    ,avg_page_io_latch_wait_ms bigint
    ,tree_page_latch_wait_count bigint
    ,avg_tree_page_latch_wait_ms bigint
    ,tree_page_io_latch_wait_count bigint
    ,avg_tree_page_io_latch_wait_ms bigint
    ,read_operations bigint
    ,leaf_writes bigint
    ,leaf_page_allocations bigint
    ,leaf_page_merges bigint
    ,nonleaf_writes bigint
    ,nonleaf_page_allocations bigint
    ,nonleaf_page_merges bigint
    ,indexed_columns nvarchar(max)
    ,included_columns nvarchar(max)
    ,indexed_columns_compare nvarchar(max)
    ,included_columns_compare nvarchar(max)
    ,duplicate_indexes nvarchar(max)
    ,overlapping_indexes nvarchar(max)
    ,related_foreign_keys nvarchar(max)
    ,related_foreign_keys_xml xml
    )

IF OBJECT_ID('tempdb..#ForeignKeys') IS NOT NULL
    DROP TABLE #ForeignKeys

CREATE TABLE #ForeignKeys
    (
    foreign_key_name sysname
    ,object_id int
    ,fk_columns nvarchar(max)
    ,fk_columns_compare nvarchar(max)
    )

;WITH AllocationUnits
AS (
    SELECT p.object_id
        ,p.index_id
        ,p.partition_number
        ,au.allocation_unit_id
    FROM sys.allocation_units AS au
        INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3)
    UNION ALL
    SELECT p.object_id
        ,p.index_id
        ,p.partition_number
        ,au.allocation_unit_id
    FROM sys.allocation_units AS au
        INNER JOIN sys.partitions AS p ON au.container_id = p.partition_id AND au.type = 2
),MemoryBuffer
AS (
    SELECT au.object_id
        ,au.index_id
        ,au.partition_number
        ,COUNT(*)AS buffered_page_count
        ,CONVERT(decimal(12,2), CAST(COUNT(*) as bigint)*CAST(8 as float)/1024) as buffer_mb
    FROM sys.dm_os_buffer_descriptors AS bd
        INNER JOIN AllocationUnits au ON bd.allocation_unit_id = au.allocation_unit_id
    WHERE bd.database_id = db_id()
    GROUP BY au.object_id, au.index_id, au.partition_number
)
INSERT INTO #IndexBaseLine
    (
    schema_id, schema_name, object_id, table_name, index_id, index_name, is_unique, type_desc, partition_number, fill_factor, is_padded
    , reserved_page_count, size_in_mb, buffered_page_count, buffer_mb, pct_in_buffer, row_count, existing_ranking, user_total
    , user_total_pct, user_seeks, user_scans, user_lookups, user_updates, read_to_update_ratio, read_to_update, update_to_read, row_lock_count
    , row_lock_wait_count, row_lock_wait_in_ms, row_block_pct, avg_row_lock_waits_ms, page_latch_wait_count, avg_page_latch_wait_ms
    , page_io_latch_wait_count, avg_page_io_latch_wait_ms, tree_page_latch_wait_count, avg_tree_page_latch_wait_ms, tree_page_io_latch_wait_count
    , avg_tree_page_io_latch_wait_ms, read_operations, leaf_writes, leaf_page_allocations, leaf_page_merges, nonleaf_writes
    , nonleaf_page_allocations, nonleaf_page_merges, indexed_columns, included_columns, indexed_columns_compare, included_columns_compare
    )
SELECT
    schema_id =  s.schema_id
    , schema_name = s.name
    , object_id = t.object_id
    , table_name = t.name
    , index_id = i.index_id
    , index_name = COALESCE(i.name, 'N/A')
    , is_unique = i.is_unique
    , type_desc = CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + i.type_desc
    , partition_number = ps.partition_number
    , fill_factor = i.fill_factor
    , is_padded = i.is_padded
    , reserved_page_count = ps.reserved_page_count
    , size_in_mb = CAST(reserved_page_count * CAST(8 as float) / 1024 as decimal(12,2))
    , buffered_page_count = mb.buffered_page_count
    , buffer_mb = mb.buffer_mb
    , pct_in_buffer = CAST(100*buffer_mb/NULLIF(CAST(reserved_page_count * CAST(8 as float) / 1024 as decimal(12,2)),0) AS decimal(12,2))
    , row_count = row_count
    , existing_ranking = ROW_NUMBER()
        OVER (PARTITION BY i.object_id ORDER BY i.is_primary_key desc, ius.user_seeks + ius.user_scans + ius.user_lookups desc)
    , user_total = ius.user_seeks + ius.user_scans + ius.user_lookups
    , user_total_pct = COALESCE(CAST(100 * (ius.user_seeks + ius.user_scans + ius.user_lookups)
        /(NULLIF(SUM(ius.user_seeks + ius.user_scans + ius.user_lookups)
        OVER(PARTITION BY i.object_id), 0) * 1.) as decimal(6,2)),0)
    , user_seeks = ius.user_seeks
    , user_scans = ius.user_scans
    , user_lookups = ius.user_lookups
    , user_updates = ius.user_updates
    , read_to_update_ratio = (1.*(ius.user_seeks + ius.user_scans + ius.user_lookups))/NULLIF(ius.user_updates,0)
    , read_to_update = CASE WHEN ius.user_seeks + ius.user_scans + ius.user_lookups >= ius.user_seeks
        THEN CEILING(1.*(ius.user_seeks + ius.user_scans + ius.user_lookups)/COALESCE(NULLIF(ius.user_seeks,0),1))
        ELSE 0 END
    , update_to_read = CASE WHEN ius.user_seeks + ius.user_scans + ius.user_lookups <= ius.user_seeks
        THEN CEILING(1.*(ius.user_seeks)/COALESCE(NULLIF(ius.user_seeks + ius.user_scans + ius.user_lookups,0),1))
        ELSE 0 END
    , row_lock_count = ios.row_lock_count
    , row_lock_wait_count = ios.row_lock_wait_count
    , row_lock_wait_in_ms = ios.row_lock_wait_in_ms
    , row_block_pct = CAST(100.0 * ios.row_lock_wait_count/NULLIF(ios.row_lock_count, 0) AS decimal(12,2))
    , avg_row_lock_waits_ms = CAST(1. * ios.row_lock_wait_in_ms /NULLIF(ios.row_lock_wait_count, 0) AS decimal(12,2))
    , page_latch_wait_count = ios.page_latch_wait_count
    , avg_page_latch_wait_ms = CAST(1. * page_latch_wait_in_ms / NULLIF(ios.page_io_latch_wait_count,0) AS decimal(12,2))
    , page_io_latch_wait_count = ios.page_io_latch_wait_count
    , avg_page_io_latch_wait_ms = CAST(1. * ios.page_io_latch_wait_in_ms / NULLIF(ios.page_io_latch_wait_count,0) AS decimal(12,2))
    , tree_page_latch_wait_count = ios.tree_page_latch_wait_count
    , avg_tree_page_latch_wait_ms = CAST(1. * tree_page_latch_wait_in_ms / NULLIF(ios.tree_page_io_latch_wait_count,0) AS decimal(12,2))
    , tree_page_io_latch_wait_count = ios.tree_page_io_latch_wait_count
    , avg_tree_page_io_latch_wait_ms = CAST(1. * ios.tree_page_io_latch_wait_in_ms / NULLIF(ios.tree_page_io_latch_wait_count,0) AS decimal(12,2))
    , read_operations = range_scan_count + singleton_lookup_count
    , leaf_writes = ios.leaf_insert_count + ios.leaf_update_count + ios.leaf_delete_count + ios.leaf_ghost_count
    , leaf_page_allocations = leaf_allocation_count
    , leaf_page_merges = ios.leaf_page_merge_count
    , nonleaf_writes = ios.nonleaf_insert_count + ios.nonleaf_update_count + ios.nonleaf_delete_count
    , nonleaf_page_allocations = ios.nonleaf_allocation_count
    , nonleaf_page_merges = ios.nonleaf_page_merge_count
    , indexed_columns = STUFF((
            SELECT ', ' + QUOTENAME(c.name)
            FROM sys.index_columns ic
                INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE i.object_id = ic.object_id
            AND i.index_id = ic.index_id
            AND is_included_column = 0
            ORDER BY key_ordinal ASC
            FOR XML PATH('')), 1, 2, '')
    , included_columns = STUFF((
            SELECT ', ' + QUOTENAME(c.name)
            FROM sys.index_columns ic
                INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE i.object_id = ic.object_id
            AND i.index_id = ic.index_id
            AND is_included_column = 1
            ORDER BY key_ordinal ASC
            FOR XML PATH('')), 1, 2, '')
    , indexed_columns_compare = (SELECT QUOTENAME(ic.column_id,'(')
            FROM sys.index_columns ic
            WHERE i.object_id = ic.object_id
            AND i.index_id = ic.index_id
            AND is_included_column = 0
            ORDER BY key_ordinal ASC
            FOR XML PATH(''))
    , included_columns_compare = COALESCE((
            SELECT QUOTENAME(ic.column_id, '(')
            FROM sys.index_columns ic
            WHERE i.object_id = ic.object_id
            AND i.index_id = ic.index_id
            AND is_included_column = 1
            ORDER BY key_ordinal ASC
            FOR XML PATH('')), SPACE(0))
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.indexes i ON t.object_id = i.object_id
    INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
    LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = db_id()
    LEFT OUTER JOIN sys.dm_db_index_operational_stats(@DB_ID, NULL, NULL, NULL) ios ON ps.object_id = ios.object_id AND ps.index_id = ios.index_id AND ps.partition_number = ios.partition_number
    LEFT OUTER JOIN MemoryBuffer mb ON ps.object_id = mb.object_id AND ps.index_id = mb.index_id AND ps.partition_number = mb.partition_number
WHERE t.object_id = @ObjectID OR @ObjectID IS NULL

INSERT INTO #IndexBaseLine
    (schema_id, schema_name, object_id, table_name, index_name, type_desc, impact, existing_ranking, user_total, user_seeks, user_scans, user_lookups, indexed_columns, included_columns)
SELECT s.schema_id
    ,s.name AS schema_name
    ,t.object_id
    ,t.name AS table_name
    ,'--MISSING--' AS index_name
    ,'--NONCLUSTERED--' AS type_desc
    ,(migs.user_seeks + migs.user_scans) * migs.avg_user_impact as impact
    ,0 AS existing_ranking
    ,migs.user_seeks + migs.user_scans as user_total
    ,migs.user_seeks
    ,migs.user_scans
    ,0 as user_lookups
    ,COALESCE(equality_columns + ', ', SPACE(0)) + COALESCE(inequality_columns, SPACE(0)) as indexed_columns
    ,included_columns
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.dm_db_missing_index_details mid ON t.object_id = mid.object_id
    INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
    INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = db_id()
AND (mid.object_id = @ObjectID OR @ObjectID IS NULL)

INSERT INTO #ForeignKeys
    (foreign_key_name, object_id, fk_columns, fk_columns_compare)
SELECT fk.name + '|PARENT' AS foreign_key_name
    ,fkc.parent_object_id AS object_id
    ,STUFF((SELECT ', ' + QUOTENAME(c.name)
        FROM sys.foreign_key_columns ifkc
            INNER JOIN sys.columns c ON ifkc.parent_object_id = c.object_id AND ifkc.parent_column_id = c.column_id
        WHERE fk.object_id = ifkc.constraint_object_id
        ORDER BY ifkc.constraint_column_id
        FOR XML PATH('')), 1, 2, '') AS fk_columns
    ,(SELECT QUOTENAME(ifkc.parent_column_id,'(')
        FROM sys.foreign_key_columns ifkc
        WHERE fk.object_id = ifkc.constraint_object_id
        ORDER BY ifkc.constraint_column_id
        FOR XML PATH('')) AS fk_columns_compare
FROM sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fkc.constraint_column_id = 1
AND (fkc.parent_object_id = @ObjectID OR @ObjectID IS NULL)
UNION ALL
SELECT fk.name + '|REFERENCED' as foreign_key_name
    ,fkc.referenced_object_id AS object_id
    ,STUFF((SELECT ', ' + QUOTENAME(c.name)
        FROM sys.foreign_key_columns ifkc
            INNER JOIN sys.columns c ON ifkc.referenced_object_id = c.object_id AND ifkc.referenced_column_id = c.column_id
        WHERE fk.object_id = ifkc.constraint_object_id
        ORDER BY ifkc.constraint_column_id
        FOR XML PATH('')), 1, 2, '') AS fk_columns
    ,(SELECT QUOTENAME(ifkc.referenced_column_id,'(')
        FROM sys.foreign_key_columns ifkc
        WHERE fk.object_id = ifkc.constraint_object_id
        ORDER BY ifkc.constraint_column_id
        FOR XML PATH('')) AS fk_columns_compare
FROM sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fkc.constraint_column_id = 1
AND (fkc.referenced_object_id = @ObjectID OR @ObjectID IS NULL)

UPDATE ibl
SET duplicate_indexes = STUFF((SELECT ', ' + index_name AS [data()]
        FROM #IndexBaseLine iibl
        WHERE ibl.object_id = iibl.object_id
        AND ibl.index_id <> iibl.index_id
        AND ibl.indexed_columns_compare = iibl.indexed_columns_compare
        AND ibl.included_columns_compare = iibl.included_columns_compare
        FOR XML PATH('')), 1, 2, '')
    ,overlapping_indexes = STUFF((SELECT ', ' + index_name AS [data()]
        FROM #IndexBaseLine iibl
        WHERE ibl.object_id = iibl.object_id
        AND ibl.index_id <> iibl.index_id
        AND (ibl.indexed_columns_compare LIKE iibl.indexed_columns_compare + '%'
            OR iibl.indexed_columns_compare LIKE ibl.indexed_columns_compare + '%')
        AND ibl.indexed_columns_compare <> iibl.indexed_columns_compare
        FOR XML PATH('')), 1, 2, '')
    ,related_foreign_keys = STUFF((SELECT ', ' + foreign_key_name AS [data()]
        FROM #ForeignKeys ifk
        WHERE ifk.object_id = ibl.object_id
        AND ibl.indexed_columns_compare LIKE ifk.fk_columns_compare + '%'
        FOR XML PATH('')), 1, 2, '')
    ,related_foreign_keys_xml = CAST((SELECT foreign_key_name
        FROM #ForeignKeys ForeignKeys
        WHERE ForeignKeys.object_id = ibl.object_id
        AND ibl.indexed_columns_compare LIKE ForeignKeys.fk_columns_compare + '%'
        FOR XML AUTO) as xml)
FROM #IndexBaseLine ibl

INSERT INTO #IndexBaseLine
    (schema_id, schema_name, object_id, table_name, index_name, type_desc, existing_ranking, indexed_columns)
SELECT s.schema_id
    ,s.name AS schema_name
    ,t.object_id
    ,t.name AS table_name
    ,fk.foreign_key_name AS index_name
    ,'--MISSING FOREIGN KEY--' as type_desc
    ,9999
    ,fk.fk_columns
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN #ForeignKeys fk ON t.object_id = fk.object_id
    LEFT OUTER JOIN #IndexBaseLine ia ON fk.object_id = ia.object_id AND ia.indexed_columns_compare LIKE fk.fk_columns_compare + '%'
WHERE ia.index_name IS NULL

;WITH Aggregation
AS (
    SELECT row_id
        ,CAST(100. * (user_seeks + user_scans + user_lookups)
            /(NULLIF(SUM(user_seeks + user_scans + user_lookups)
            OVER(PARTITION BY schema_name, table_name), 0) * 1.) as decimal(12,2)) AS estimated_user_total_pct
        ,SUM(buffer_mb) OVER(PARTITION BY schema_name, table_name) as table_buffer_mb
    FROM #IndexBaseLine
)
UPDATE ibl
SET estimated_user_total_pct = COALESCE(a.estimated_user_total_pct, 0)
    ,table_buffer_mb = a.table_buffer_mb
FROM #IndexBaseLine ibl
    INNER JOIN Aggregation a ON ibl.row_id = a.row_id

;WITH IndexAction
AS (
    SELECT row_id
        ,CASE WHEN user_lookups > user_seeks AND type_desc IN ('CLUSTERED', 'HEAP', 'UNIQUE CLUSTERED') THEN 'REALIGN'
            WHEN type_desc = '--MISSING FOREIGN KEY--' THEN 'CREATE'
            WHEN type_desc = 'XML' THEN '---'
            WHEN is_unique = 1 THEN '---'
            WHEN type_desc = '--NONCLUSTERED--' AND ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY user_total desc) <= 10 AND estimated_user_total_pct > 1 THEN 'CREATE'
            WHEN type_desc = '--NONCLUSTERED--' THEN 'BLEND'
            WHEN ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY user_total desc, existing_ranking) > 10 THEN 'DROP'
            WHEN user_total = 0 THEN 'DROP'
            ELSE '---' END AS index_action
    FROM #IndexBaseLine
)
UPDATE ibl
SET index_action = ia.index_action
FROM #IndexBaseLine ibl INNER JOIN IndexAction ia
ON ibl.row_id = ia.row_id

UPDATE ibl
SET has_unique = 1
FROM #IndexBaseLine ibl
    INNER JOIN (SELECT DISTINCT object_id FROM sys.indexes i WHERE i.is_unique = 1) x ON ibl.object_id = x.object_id

UPDATE #IndexBaseLine
SET Pros = COALESCE(STUFF(CASE WHEN related_foreign_keys IS NOT NULL THEN ', FK' ELSE '' END
        + CASE WHEN is_unique = 1 THEN ', UQ' ELSE '' END
        + COALESCE(', ' + CASE WHEN read_to_update BETWEEN 1 AND 9 THEN '$'
            WHEN read_to_update BETWEEN 10 AND 99 THEN '$$'
            WHEN read_to_update BETWEEN 100 AND 999 THEN '$$$'
            WHEN read_to_update > 999 THEN '$$$+' END, '')
        ,1,2,''),'')
    ,Cons = COALESCE(STUFF(CASE WHEN user_seeks / NULLIF(user_scans,0) < 1000 THEN ', SCN' ELSE '' END
        + CASE WHEN duplicate_indexes IS NOT NULL THEN ', DP' ELSE '' END
        + CASE WHEN overlapping_indexes IS NOT NULL THEN ', OV' ELSE '' END
        + COALESCE(', ' + CASE WHEN update_to_read BETWEEN 1 AND 9 THEN '$'
            WHEN update_to_read BETWEEN 10 AND 99 THEN '$$'
            WHEN update_to_read BETWEEN 100 AND 999 THEN '$$$'
            WHEN update_to_read > 999 THEN '$$$+' END, '')
        ,1,2,''),'')

SELECT
    index_action
    ,pros
    ,cons
    ,schema_name + '.' + table_name as object_name
    , index_name
    , type_desc
    , indexed_columns
    , included_columns
    , is_unique
    , has_unique
    , partition_number
    , fill_factor
    , is_padded
    , size_in_mb
    , buffer_mb
    , table_buffer_mb
    , pct_in_buffer
    , row_count
    , user_total_pct
    , estimated_user_total_pct
    , impact
    , user_total
    , user_seeks
    , user_scans
    , user_lookups
    , user_updates
    , read_to_update_ratio
    , read_to_update
    , update_to_read
    , row_lock_count
    , row_lock_wait_count
    , row_lock_wait_in_ms
    , row_block_pct
    , avg_row_lock_waits_ms
    , page_latch_wait_count
    , avg_page_latch_wait_ms
    , page_io_latch_wait_count
    , avg_page_io_latch_wait_ms
    , tree_page_latch_wait_count
    , avg_tree_page_latch_wait_ms
    , tree_page_io_latch_wait_count
    , avg_tree_page_io_latch_wait_ms
    , read_operations
    , leaf_writes
    , leaf_page_allocations
    , leaf_page_merges
    , nonleaf_writes
    , nonleaf_page_allocations
    , nonleaf_page_merges
    , duplicate_indexes
    , overlapping_indexes
    , related_foreign_keys
    , related_foreign_keys_xml
FROM #IndexBaseLine
ORDER BY table_buffer_mb DESC, object_id, user_total DESC

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
  1. CREATE: Recommend adding the index to the table.
  2. DROP: Recommend dropping the index from the table
  3. BLEND: Review the missing index details to see if the missing index details can be added to an existing index.
  4. 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.
• pros: list of reasons that indicate the benefits provided by the index
  1. FK: The index schema maps to a foreign key
  2. UQ: Index is a unique constraint
  3. $, $$, $$$, $$$+: Indicates the ratio of read to write uses in execution plans.  The higher the ratio the more dollar signs; this should correlate to greater benefit provided by the index.
• cons: list of reasons that indicate some negative aspects associate with the index
  1. SCN: Flag indicating that the ratio of seeks to scans on the index exceeds 1,000.
  2. DP: Index schema is a duplicate of another index.
  3. OV: Index schema overlaps another index.
  4. $, $$, $$$, $$$+: Indicates the ratio of write to read uses in execution plans.  The higher the ratio the more dollar signs; this should correlate to greater benefit provided by the index.
• 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.
• is_padded: Boolean value indicating whether fill factor is applied to nonleaf levels
• reserved_page_count: Total number of pages reserved for the index.
• size_in_mb: The amount of space in MB the index utilizes on disk.
• 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.
• read_operations: Cumulative count of range_scan_count and singleton_lookup_count operations
• leaf_writes: Cumulative count of leaf_insert_count, leaf_update_count, leaf_delete_count and leaf_ghost_count operations
• leaf_page_allocations: Cumulative count of leaf-level page allocations in the index or heap.  For an index, a page allocation corresponds to a page split.
• leaf_page_merges: Cumulative count of page merges at the leaf level.
• nonleaf_writes: Cumulative count of leaf_insert_count, leaf_update_count and leaf_delete_count operations
• nonleaf_page_allocations: Cumulative count of page allocations caused by page splits above the leaf level.
• 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:

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.

Related posts:

1. Return of Index Analysis Part 1
2. Analyze This – Analyze Your Indexes – Part 6
3. Analyze This – Your Indexes Analysis – Part 1