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

Printed 2014/11/25 05:39PM

Return of Index Analysis Part 2

By StrateSQL, 2010/10/06



Looking for Minifig Monday?
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:

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

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:

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


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.