sp_IndexAnalysis-v11.sql

USE master
GO

IF OBJECT_ID('dbo.sp_IndexAnalysis') IS NOT NULL
    DROP PROCEDURE dbo.sp_IndexAnalysis
GO

/********************************************************************************************* 
Index Analysis Script v11.06
(C) 2013, Jason Strate

Feedback:
    mailto:jasonstrate@gmail.com 
    http://www.jasonstrate.com

License: 
   This query is free to download and use for personal, educational, and internal 
   corporate purposes, provided that this header is preserved. Redistribution or sale 
   of this query, in whole or in part, is prohibited without the author's express 
   written consent. 

Note: 
    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, the
    database was brought onling, or when the index metadata was updated (such as during reindex 
    operations, 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. 
   
Parameters
    @TableName(NVARCHAR(256)): Optional parameter to add
    @IncludeMissingIndexes(BIT): Identifies whether to include missing indexes in the output
    @IncludeMissingFKIndexes(BIT): Identifies whether to include missing foreign key indexes 
        in the output
    @Output(VARCHAR(20)): Determines the output results from the stored procedure.  The
        available values are
           * DETAILED: All results from the index analysis
           * DUPLICATE: Index results for deplicate indexes
           * OVERLAPPING: Index results for overlapping indexes

Columns:
    index_action: Analysis recommendation on action to take on the index 
        CREATE: Recommend adding the index to the table. 
        DROP-DUP: Recommend dropping the index since it is a duplicate 
		DROP-COUNT: Count of indexes on tables may indicate that the index may be a candidate
			for removal.
		DROP-USAGE: Usage of the index suggests it may be a candidate for removal 
        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. 
    index_pros: list of reasons that indicate the benefits provided by the index 
        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. 
    index_cons: list of reasons that indicate some negative aspects associate with the index 
        SCN: Flag indicating that the ratio of seeks to scans on the index less than 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 more cost  
            incurred by the index.
		DSB: Index is disabled.  These should be enabled or removed.
    filegroup: file group that the index is located.
    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. 
    missing_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_ids: Column IDs that are part of the index, missing index or foreign 
        key 
    included_column_ids: 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. 

Revision History
Date			Version		DESCRIPTION
----------------------------------------------------------------------------------------------
2012-12-10		11.01	    Rewrote information collection to reduce dynamic SQL
                            * Fixed issue with Object_ID
                            * Added unique check and filter definition to duplication and 
                              overlapping indexes
                            * Fixed errors with documentation section.
                            * Added parameters @IncludeMissingIndexes, @IncludeMissingFKIndexes,
                              and @Output
                            * Removed DDL create and drop columns for indexes, too much risk in
                              automatic index building
2012-12-28      11.02       * Fixed new issues WITH Object_ID
                            * change missing foreign key logic to include basic details but not
                            suggest missing indexes with @IncludeMissingFKIndexes flag 
                            * Fixed issues with missing foreign keys that was not displaying them
                            * Improved default sort for DETAILED output
                            * Updated case for case-sensitive collations
2013-01-08      11.03       * Fixed issue with overlapping indexes and duplicate indexes related
                            filtered indexes
                            * Improved error handling to solve issue with truncation MESSAGE
2013-03-07		11.04		* Fixed duplicate index logic to match that from 
							http://www.jasonstrate.com/2013/03/thats-actually-a-duplicate-index/
							* Added check for disabled indexes to checks
							* Expanded drop reasons with explanations and descriptions for 
							dropping because of duplication, usage, and count of indexes
							* Increased size of foreign key name to accommodate larger names
							* Fixed issue causing DROP recommmendation on missing indexes
2013-03-20		11.05		* Fixed data type on missing_index_impact (Credit: Dick Baker)
2013-05-22      11.06       * Fixed bug causing mismatch with foreign key analysis
                            * Adjusted overlapping index logic which was broke with new duplicate
                                index logic
*********************************************************************************************/ 
CREATE PROCEDURE dbo.sp_IndexAnalysis
(
@TableName NVARCHAR(256) = NULL 
,@IncludeMissingIndexes BIT = 1 
,@IncludeMissingFKIndexes BIT = 1
,@Output VARCHAR(20) = 'DETAILED'
)
WITH RECOMPILE
AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @ERROR_MESSAGE NVARCHAR(2048)
    ,@ERROR_SEVERITY INT
    ,@ERROR_STATE INT

	DECLARE @SQL NVARCHAR(max)
    ,@DB_ID INT
    ,@ObjectID INT
    ,@DatabaseName NVARCHAR(max)

BEGIN TRY
    IF @Output NOT IN ('DETAILED','DUPLICATE','OVERLAPPING')
        RAISERROR('The value "%s" provided for the @Output parameter is not valid',16,1,@Output)

    SELECT @DB_ID = DB_ID()
        ,@ObjectID = OBJECT_ID(QUOTENAME(DB_NAME(DB_ID()))+'.'+COALESCE(QUOTENAME(PARSENAME(@TableName,2)),'')+'.'+QUOTENAME(PARSENAME(@TableName,1)))
        ,@DatabaseName = QUOTENAME(DB_NAME(DB_ID()))

-- Obtain memory buffer information on database objects
    IF OBJECT_ID('tempdb..#MemoryBuffer') IS NOT NULL
        DROP TABLE #MemoryBuffer

    CREATE TABLE #MemoryBuffer (
        database_id INT
		,object_id INT
        ,index_id INT
        ,partition_number INT
        ,buffered_page_count INT
        ,buffered_mb DECIMAL(12, 2)
        )

    SET @SQL = 'WITH AllocationUnits
    AS (
        SELECT p.object_id
            ,p.index_id
            ,p.partition_number 
            ,au.allocation_unit_id
        FROM '+@DatabaseName+'.sys.allocation_units AS au
            INNER JOIN '+@DatabaseName+'.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 '+@DatabaseName+'.sys.allocation_units AS au
            INNER JOIN '+@DatabaseName+'.sys.partitions AS p ON au.container_id = p.partition_id AND au.type = 2
    )
    SELECT DB_ID()
		,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 '+@DatabaseName+'.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'

    BEGIN TRY
        INSERT INTO #MemoryBuffer
	    EXEC sys.sp_executesql @SQL
    END TRY
    BEGIN CATCH
        SELECT @ERROR_MESSAGE  = 'Populate #MemoryBuffer (Line '+CAST(ERROR_LINE() AS NVARCHAR(25))+'): ' + ERROR_MESSAGE()
            ,@ERROR_SEVERITY = ERROR_SEVERITY()
            ,@ERROR_STATE = ERROR_STATE()
    
        RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)
    END CATCH

-- Obtain index meta data information
    IF OBJECT_ID('tempdb..#IndexMeta') IS NOT NULL
        DROP TABLE #IndexMeta

	CREATE TABLE #IndexMeta (
		 database_id SMALLINT
		,filegroup_name NVARCHAR(128)
		,schema_id INT
		,schema_name NVARCHAR(128)
		,object_id INT
		,table_name NVARCHAR(128)
		,index_id INT
		,index_name NVARCHAR(128)
		,is_primary_key BIT
		,is_unique BIT
        ,is_disabled BIT
		,type_desc NVARCHAR(128)
		,partition_number INT
		,fill_factor TINYINT
		,is_padded BIT
		,reserved_page_count BIGINT
		,size_in_mb DECIMAL(12, 2)
		,row_count BIGINT
        ,filter_definition NVARCHAR(MAX)
		,indexed_columns NVARCHAR(MAX)
		,included_columns NVARCHAR(MAX)
        ,key_columns NVARCHAR(MAX)
		,data_columns NVARCHAR(MAX)
		,indexed_columns_ids NVARCHAR(1024)
		,included_column_ids NVARCHAR(1024)
		)

     SET @SQL = N'SELECT
	    database_id = DB_ID()
	    , filegroup = ds.name
	    , s.schema_id
	    , schema_name = s.name
	    , object_id = t.object_id
	    , table_name = t.name
	    , i.index_id
	    , index_name = COALESCE(i.name, ''N/A'')
	    , ps.partition_number
	    , i.is_primary_key
	    , i.is_unique
        , i.is_disabled
	    , type_desc = CASE WHEN i.is_unique = 1 THEN ''UNIQUE '' ELSE '''' END + i.type_desc
	    , i.fill_factor
	    , i.is_padded
	    , ps.reserved_page_count
	    , size_in_mb = CAST(reserved_page_count * CAST(8 as float) / 1024 as DECIMAL(12,2)) 
	    , row_count
        , i.filter_definition
	    , indexed_columns = STUFF((
			    SELECT '', '' + QUOTENAME(c.name)
			    FROM '+@DatabaseName+'.sys.index_columns ic
				    INNER JOIN '+@DatabaseName+'.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 '+@DatabaseName+'.sys.index_columns ic
				    INNER JOIN '+@DatabaseName+'.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, '''') 
	    , key_columns = STUFF((
			    SELECT '', '' + QUOTENAME(c.name)
			    FROM '+@DatabaseName+'.sys.index_columns ic
				    INNER JOIN '+@DatabaseName+'.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(''''))
                + COALESCE((SELECT '', '' + QUOTENAME(c.name)
			    FROM '+@DatabaseName+'.sys.index_columns ic
				    INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                    LEFT OUTER JOIN '+@DatabaseName+'.sys.index_columns ic_key ON c.object_id = ic_key.object_id 
                        AND c.column_id = ic_key.column_id 
                        AND i.index_id = ic_key.index_id
                        AND ic_key.is_included_column = 0
			    WHERE i.object_id = ic.object_id
			    AND ic.index_id = 1
			    AND ic.is_included_column = 0
                AND ic_key.index_id IS NULL
			    ORDER BY ic.key_ordinal ASC
			    FOR XML PATH('''')),''''), 1, 2, '''')
	    , data_columns = CASE WHEN i.index_id IN (0,1) THEN ''ALL-COLUMNS'' ELSE STUFF((
			    SELECT '', '' + QUOTENAME(c.name)
			    FROM '+@DatabaseName+'.sys.index_columns ic
				    INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                    LEFT OUTER JOIN '+@DatabaseName+'.sys.index_columns ic_key ON c.object_id = ic_key.object_id AND c.column_id = ic_key.column_id AND ic_key.index_id = 1
			    WHERE i.object_id = ic.object_id
			    AND i.index_id = ic.index_id
			    AND ic.is_included_column = 1
                AND ic_key.index_id IS NULL
			    ORDER BY ic.key_ordinal ASC
			    FOR XML PATH('''')), 1, 2, '''') END
	    , indexed_column_ids = (SELECT QUOTENAME(CAST(ic.column_id AS VARCHAR(10)) 
                    + CASE WHEN ic.is_descending_key = 0 THEN ''+'' ELSE ''-'' END, ''('')
			    FROM '+@DatabaseName+'.sys.index_columns ic
				    INNER JOIN '+@DatabaseName+'.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(''''))
                + ''|'' + COALESCE((SELECT QUOTENAME(CAST(ic.column_id AS VARCHAR(10)) 
                    + CASE WHEN ic.is_descending_key = 0 THEN ''+'' ELSE ''-'' END, ''('')
			    FROM '+@DatabaseName+'.sys.index_columns ic
				    INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                    LEFT OUTER JOIN '+@DatabaseName+'.sys.index_columns ic_key ON c.object_id = ic_key.object_id 
                        AND c.column_id = ic_key.column_id 
                        AND i.index_id = ic_key.index_id
                        AND ic_key.is_included_column = 0
			    WHERE i.object_id = ic.object_id
			    AND ic.index_id = 1
			    AND ic.is_included_column = 0
                AND ic_key.index_id IS NULL
			    ORDER BY ic.key_ordinal ASC
			    FOR XML PATH('''')),'''')
            + CASE WHEN i.is_unique = 1 THEN ''U'' ELSE '''' END
	    , included_column_ids = CASE WHEN i.index_id IN (0,1) THEN ''ALL-COLUMNS'' ELSE
                COALESCE((SELECT QUOTENAME(ic.column_id,''('')
			    FROM '+@DatabaseName+'.sys.index_columns ic
				    INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                    LEFT OUTER JOIN '+@DatabaseName+'.sys.index_columns ic_key ON c.object_id = ic_key.object_id AND c.column_id = ic_key.column_id AND ic_key.index_id = 1
			    WHERE i.object_id = ic.object_id
			    AND i.index_id = ic.index_id
			    AND ic.is_included_column = 1
                AND ic_key.index_id IS NULL
			    ORDER BY ic.key_ordinal ASC
			    FOR XML PATH('''')), SPACE(0)) END
    FROM '+@DatabaseName+'.sys.tables t
	    INNER JOIN '+@DatabaseName+'.sys.schemas s ON t.schema_id = s.schema_id
	    INNER JOIN '+@DatabaseName+'.sys.indexes i ON t.object_id = i.object_id
	    INNER JOIN '+@DatabaseName+'.sys.data_spaces ds ON i.data_space_id = ds.data_space_id
	    INNER JOIN '+@DatabaseName+'.sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id'

	IF @ObjectID IS NOT NULL
        SET @SQL = @SQL + CHAR(13) + 'WHERE t.object_id = @ObjectID '

    BEGIN TRY
	    INSERT INTO #IndexMeta (
		    database_id
		    ,filegroup_name
		    ,schema_id
		    ,schema_name
		    ,object_id
		    ,table_name
		    ,index_id
		    ,index_name
		    ,partition_number
		    ,is_primary_key
		    ,is_unique
            ,is_disabled
		    ,type_desc
		    ,fill_factor
		    ,is_padded
		    ,reserved_page_count
		    ,size_in_mb
		    ,row_count
            ,filter_definition
		    ,indexed_columns
		    ,included_columns
            ,key_columns
		    ,data_columns
		    ,indexed_columns_ids
		    ,included_column_ids)
	    EXEC sys.sp_executesql @SQL, N'@DB_ID INT, @ObjectID INT', @DB_ID = @DB_ID, @ObjectID = @ObjectID
    END TRY
    BEGIN CATCH
        SELECT @ERROR_MESSAGE  = 'Populate #IndexMeta (Line '+CAST(ERROR_LINE() AS NVARCHAR(25))+'): ' + ERROR_MESSAGE()
            ,@ERROR_SEVERITY = ERROR_SEVERITY()
            ,@ERROR_STATE = ERROR_STATE()
    
        RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)
    END CATCH 
      
    IF OBJECT_ID('tempdb..#IndexStatistics') IS NOT NULL
        DROP TABLE #IndexStatistics
        
	SELECT IDENTITY(INT,1,1) AS row_id
        ,CAST('' AS VARCHAR(10)) AS index_action
        ,CAST('' AS VARCHAR(25)) AS index_pros
        ,CAST('' AS VARCHAR(25)) AS index_cons
        ,im.database_id
        ,im.filegroup_name
        ,im.schema_id
        ,im.schema_name
        ,im.object_id
        ,im.table_name
        ,im.index_id
        ,im.index_name
        ,im.is_primary_key
        ,im.is_unique
        ,im.is_disabled
        ,CAST(0 AS BIT) AS has_unique
        ,im.type_desc
        ,im.partition_number
        ,im.fill_factor
        ,im.is_padded
        ,im.reserved_page_count
        ,im.size_in_mb
        ,mb.buffered_page_count
        ,mb.buffered_mb
        ,CAST(0 AS INT) AS table_buffered_mb
        ,CAST(100.*mb.buffered_page_count/NULLIF(im.reserved_page_count,0) AS DECIMAL(12,2)) AS buffered_percent
        ,im.row_count
        ,ROW_NUMBER() OVER (PARTITION BY im.object_id ORDER BY im.is_primary_key desc,ius.user_seeks + ius.user_scans + ius.user_lookups DESC) AS index_rank
        , ius.user_seeks + ius.user_scans + ius.user_lookups AS user_total
        , 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 im.object_id), 0) * 1.) as DECIMAL(6,2)),0) AS user_total_pct
        ,CAST(0 AS DECIMAL(6,2)) AS estimated_user_total_pct
        ,CAST(0 AS FLOAT) AS missing_index_impact -- Dick Baker 201303 (INT range not big enough and is f.p. anyway)
        ,ius.user_seeks
        ,ius.user_scans
        ,ius.user_lookups
        ,ius.user_updates
        ,(1.*(ius.user_seeks + ius.user_scans + ius.user_lookups))/NULLIF(ius.user_updates,0) AS read_to_update_ratio
        ,CASE WHEN ius.user_seeks + ius.user_scans + ius.user_lookups >= ius.user_updates
            THEN CEILING(1.*(ius.user_seeks + ius.user_scans + ius.user_lookups)/COALESCE(NULLIF(ius.user_seeks,0),1)) 
            ELSE 0 END AS read_to_update
        ,CASE WHEN ius.user_seeks + ius.user_scans + ius.user_lookups <= ius.user_updates
            THEN CEILING(1.*(ius.user_updates)/COALESCE(NULLIF(ius.user_seeks + ius.user_scans + ius.user_lookups,0),1))
            ELSE 0 END AS update_to_read
        ,ios.row_lock_count
        ,ios.row_lock_wait_count
        ,ios.row_lock_wait_in_ms
        ,CAST(100.0 * ios.row_lock_wait_count/NULLIF(ios.row_lock_count,0) AS DECIMAL(12,2)) AS row_block_pct 
        ,CAST(1. * ios.row_lock_wait_in_ms /NULLIF(ios.row_lock_wait_count,0) AS DECIMAL(12,2)) AS avg_row_lock_waits_ms
        ,ios.page_latch_wait_count
        ,CAST(1. * page_latch_wait_in_ms / NULLIF(ios.page_io_latch_wait_count,0) AS DECIMAL(12,2)) AS avg_page_latch_wait_ms
        ,ios.page_io_latch_wait_count
        ,CAST(1. * ios.page_io_latch_wait_in_ms / NULLIF(ios.page_io_latch_wait_count,0) AS DECIMAL(12,2)) AS avg_page_io_latch_wait_ms 
        ,ios.tree_page_latch_wait_count AS tree_page_latch_wait_count
        ,CAST(1. * tree_page_latch_wait_in_ms / NULLIF(ios.tree_page_io_latch_wait_count,0) AS DECIMAL(12,2)) AS avg_tree_page_latch_wait_ms 
        ,ios.tree_page_io_latch_wait_count
        ,CAST(1. * ios.tree_page_io_latch_wait_in_ms / NULLIF(ios.tree_page_io_latch_wait_count,0) AS DECIMAL(12,2)) AS avg_tree_page_io_latch_wait_ms
        ,range_scan_count + singleton_lookup_count AS read_operations
        ,ios.leaf_insert_count + ios.leaf_update_count + ios.leaf_delete_count + ios.leaf_ghost_count AS leaf_writes
        ,leaf_allocation_count As leaf_page_allocations
        ,ios.leaf_page_merge_count AS leaf_page_merges 
        ,ios.nonleaf_insert_count + ios.nonleaf_update_count + ios.nonleaf_delete_count AS nonleaf_writes
        ,ios.nonleaf_allocation_count AS nonleaf_page_allocations
        ,ios.nonleaf_page_merge_count AS nonleaf_page_merges
        ,im.indexed_columns
        ,im.included_columns
        ,im.filter_definition
        ,key_columns
		,data_columns
        ,im.indexed_columns_ids
        ,im.included_column_ids
        ,CAST('' AS VARCHAR(MAX)) AS duplicate_indexes
        ,CAST('' AS SMALLINT) AS first_dup_index_id
        ,CAST('' AS VARCHAR(MAX)) AS overlapping_indexes
        ,CAST('' AS VARCHAR(MAX)) AS related_foreign_keys
        ,CAST('' AS XML) AS related_foreign_keys_xml
    INTO #IndexStatistics
	FROM #IndexMeta im
		LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON im.object_id = ius.object_id AND im.index_id = ius.index_id AND im.database_id = ius.database_id
		LEFT OUTER JOIN sys.dm_db_index_operational_stats(@DB_ID, NULL, NULL, NULL) ios ON im.object_id = ios.object_id AND im.index_id = ios.index_id AND im.partition_number = ios.partition_number
		LEFT OUTER JOIN #MemoryBuffer mb ON im.object_id = mb.object_id AND im.index_id = mb.index_id AND im.partition_number = mb.partition_number

    IF @IncludeMissingIndexes = 1
    BEGIN
        INSERT INTO #IndexStatistics
            (filegroup_name, schema_id, schema_name, object_id, table_name, index_name, type_desc, missing_index_impact, index_rank, user_total, user_seeks, user_scans, user_lookups, indexed_columns, included_columns)
        SELECT 
            '' AS filegroup_name
            ,SCHEMA_ID(OBJECT_SCHEMA_NAME(mid.object_id)) AS schema_id
            ,OBJECT_SCHEMA_NAME(mid.object_id) AS schema_name
            ,mid.object_id
            ,OBJECT_NAME(mid.object_id) AS table_name
            ,'--MISSING INDEX--' AS index_name
            ,'NONCLUSTERED' AS type_desc
            ,(migs.user_seeks + migs.user_scans) * migs.avg_user_impact as impact
            ,0 AS index_rank
            ,migs.user_seeks + migs.user_scans as user_total
            ,migs.user_seeks 
            ,migs.user_scans
            ,0 as user_lookups
            ,COALESCE(equality_columns + CASE WHEN inequality_columns IS NOT NULL THEN ', ' ELSE SPACE(0) END, SPACE(0)) + COALESCE(inequality_columns, SPACE(0)) as indexed_columns
            ,included_columns
        FROM sys.dm_db_missing_index_details mid
            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
    END

    -- Collect foreign key information.
    IF OBJECT_ID('tempdb..#ForeignKeys') IS NOT NULL
        DROP TABLE #ForeignKeys

    CREATE TABLE #ForeignKeys
        (
        foreign_key_name NVARCHAR(256)
        ,object_id INT
        ,fk_columns NVARCHAR(max)
        ,fk_columns_ids NVARCHAR(1024)
        )
		
     SET @SQL = N'SELECT fk.name + ''|PARENT'' AS foreign_key_name
        ,fkc.parent_object_id AS object_id
        ,STUFF((SELECT '', '' + QUOTENAME(c.name)
            FROM '+@DatabaseName+'.sys.foreign_key_columns ifkc
                INNER JOIN '+@DatabaseName+'.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(CAST(ifkc.parent_column_id AS VARCHAR(10))+''+'',''('')
            FROM '+@DatabaseName+'.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 '+@DatabaseName+'.sys.foreign_keys fk
        INNER JOIN '+@DatabaseName+'.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 '+@DatabaseName+'.sys.foreign_key_columns ifkc
                INNER JOIN '+@DatabaseName+'.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(CAST(ifkc.referenced_column_id AS VARCHAR(10))+''+'',''('')
            FROM '+@DatabaseName+'.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 '+@DatabaseName+'.sys.foreign_keys fk
        INNER JOIN '+@DatabaseName+'.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)'

    BEGIN TRY
        INSERT INTO #ForeignKeys
            (foreign_key_name, object_id, fk_columns, fk_columns_ids)
        EXEC sp_executesql @SQL, N'@DB_ID INT, @ObjectID INT', @DB_ID = @DB_ID, @ObjectID = @ObjectID
    END TRY
    BEGIN CATCH
        SELECT @ERROR_MESSAGE  = 'Populate #ForeignKeys (Line '+CAST(ERROR_LINE() AS NVARCHAR(25))+'): ' + ERROR_MESSAGE()
            ,@ERROR_SEVERITY = ERROR_SEVERITY()
            ,@ERROR_STATE = ERROR_STATE()
    
        RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)
    END CATCH 

    -- Determine duplicate, overlapping, and foreign key index information
    UPDATE i
    SET duplicate_indexes = STUFF((SELECT ', ' + index_name AS [data()]
            FROM #IndexStatistics iibl
            WHERE i.object_id = iibl.object_id
            AND i.is_primary_key = iibl.is_primary_key
            AND i.is_unique = iibl.is_unique
            AND ISNULL(i.filter_definition,'') = ISNULL(iibl.filter_definition,'')
            AND i.index_id <> iibl.index_id
            AND REPLACE(i.indexed_columns_ids,'|','')  = REPLACE(iibl.indexed_columns_ids,'|','') 
            AND i.included_column_ids = iibl.included_column_ids
            FOR XML PATH('')), 1, 2, '')
        ,first_dup_index_id = (SELECT MIN(index_id)
            FROM #IndexStatistics iibl
            WHERE i.object_id = iibl.object_id
            AND i.is_primary_key = iibl.is_primary_key
            AND i.is_unique = iibl.is_unique
            AND ISNULL(i.filter_definition,'') = ISNULL(iibl.filter_definition,'')
            AND i.index_id > iibl.index_id
            AND REPLACE(i.indexed_columns_ids,'|','') = REPLACE(iibl.indexed_columns_ids,'|','')
            AND i.included_column_ids = iibl.included_column_ids)
        ,overlapping_indexes = STUFF((SELECT ', ' + index_name AS [data()]
            FROM #IndexStatistics iibl
            WHERE i.object_id = iibl.object_id
            AND i.is_primary_key = iibl.is_primary_key
            AND i.is_unique = iibl.is_unique
            AND ISNULL(i.filter_definition,'') = ISNULL(iibl.filter_definition,'')
            AND i.index_id <> iibl.index_id
            AND LEFT(i.indexed_columns_ids, CHARINDEX('|',iibl.indexed_columns_ids,1)-1) 
                LIKE LEFT(iibl.indexed_columns_ids, CHARINDEX('|',i.indexed_columns_ids,1)-1) + '%'
            AND Replace(i.indexed_columns_ids,'|','') <> Replace(iibl.indexed_columns_ids,'|','')
            FOR XML PATH('')), 1, 2, '')
        ,related_foreign_keys = STUFF((SELECT ', ' + foreign_key_name AS [data()]
            FROM #ForeignKeys ifk
            WHERE ifk.object_id = i.object_id
            AND i.indexed_columns_ids LIKE ifk.fk_columns_ids + '%'
            FOR XML PATH('')), 1, 2, '')
        ,related_foreign_keys_xml = CAST((SELECT foreign_key_name
            FROM #ForeignKeys fk
            WHERE fk.object_id = i.object_id
            AND i.indexed_columns_ids LIKE fk.fk_columns_ids + '%'
            FOR XML AUTO) as xml)  
    FROM #IndexStatistics i
    
    IF @IncludeMissingFKIndexes = 1
    BEGIN
        INSERT INTO #IndexStatistics
            (filegroup_name, schema_id, schema_name, object_id, table_name, index_name, type_desc, index_rank, indexed_columns, related_foreign_keys)
        SELECT '' AS filegroup_name
            ,SCHEMA_ID(OBJECT_SCHEMA_NAME(fk.object_id)) AS schema_id
            ,OBJECT_SCHEMA_NAME(fk.object_id) AS schema_name
            ,fk.object_id
            ,OBJECT_NAME(fk.object_id) AS table_name
            ,'--MISSING FOREIGN KEY--' AS index_name
            ,'NONCLUSTERED' AS type_desc
            ,9999
            ,fk.fk_columns
            ,fk.foreign_key_name
        FROM #ForeignKeys fk 
            LEFT OUTER JOIN #IndexStatistics i ON fk.object_id = i.object_id AND i.indexed_columns_ids LIKE fk.fk_columns_ids + '%'
        WHERE i.index_name IS NULL
    END
	
	-- Determine whether tables have unique indexes
    UPDATE i
    SET has_unique = 1
    FROM #IndexStatistics i
    WHERE EXISTS (SELECT * FROM #IndexStatistics ii WHERE i.object_id = ii.object_id AND ii.is_unique = 1)

    -- Calculate estimated user total for each index.
    ;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(buffered_mb) OVER(PARTITION BY schema_name, table_name) as table_buffered_mb
        FROM #IndexStatistics 
    )
    UPDATE ibl
    SET estimated_user_total_pct = COALESCE(a.estimated_user_total_pct, 0)
        ,table_buffered_mb = a.table_buffered_mb
    FROM #IndexStatistics ibl
        INNER JOIN Aggregation a ON ibl.row_id = a.row_id

    -- Update Index Action information
    ;WITH IndexAction
    AS (
        SELECT row_id
            ,CASE WHEN user_lookups > user_seeks AND type_desc IN ('CLUSTERED', 'HEAP', 'UNIQUE CLUSTERED') THEN 'REALIGN'
                WHEN is_disabled = 1 THEN 'ENABLE'
                WHEN duplicate_indexes IS NOT NULL AND first_dup_index_id IS NOT NULL AND index_id IS NOT NULL THEN 'DROP-DUP' 
                WHEN type_desc = '--MISSING FOREIGN KEY--' THEN 'CREATE'
                WHEN type_desc = 'XML' THEN '---'
                WHEN is_unique = 1 THEN '---'
                WHEN related_foreign_keys IS NOT NULL 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, index_rank) > 10 AND index_id IS NOT NULL THEN 'DROP-COUNT'
                WHEN index_id NOT IN (0,1) AND duplicate_indexes IS NULL AND user_total = 0 AND index_id IS NOT NULL  
                    THEN 'DROP-USAGE' 
                ELSE '---' END AS index_action
        FROM #IndexStatistics
    )
    UPDATE ibl
    SET index_action = ia.index_action
    FROM #IndexStatistics ibl INNER JOIN IndexAction ia
    ON ibl.row_id = ia.row_id

    -- Update Pro/Con statuses
    UPDATE #IndexStatistics
    SET index_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,''),'')
        ,index_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, '')
            + CASE WHEN is_disabled = 1 THEN ', DSB' ELSE '' END
            ,1,2,''),'')

IF @Output = 'DETAILED'
BEGIN
    SELECT
        index_action
        , index_pros
        , index_cons
        , QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) as object_name
        , index_name
        , type_desc
        , indexed_columns
        , included_columns
        , filter_definition
        , is_primary_key
        , is_unique
        , is_disabled
        , has_unique
        , partition_number
        , fill_factor
        , is_padded
        , size_in_mb
        , buffered_mb
        , table_buffered_mb
        , buffered_percent
        , row_count
        , user_total_pct
        , estimated_user_total_pct
        , missing_index_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
        , key_columns
		, data_columns
    FROM #IndexStatistics
    WHERE (estimated_user_total_pct > 0.01 AND index_id IS NULL)
    OR related_foreign_keys IS NOT NULL
    OR index_id IS NOT NULL
    ORDER BY table_buffered_mb DESC, object_id, COALESCE(user_total,-1) DESC, COALESCE(user_updates,-1) DESC, COALESCE(index_id,999)
END
ELSE IF @Output = 'DUPLICATE'
BEGIN
    SELECT
		DENSE_RANK() OVER (ORDER BY key_columns, data_columns) AS duplicate_group
        , index_action
        , index_pros
        , index_cons
        , QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) as object_name
        , index_name
        , type_desc
        , indexed_columns
        , included_columns
        , is_primary_key
        , is_unique
        , duplicate_indexes
        , size_in_mb
        , buffered_mb
        , table_buffered_mb
        , buffered_percent
        , row_count
        , user_total_pct
        , user_total
        , user_seeks
        , user_scans
        , user_lookups
        , user_updates
        , read_operations
    FROM #IndexStatistics
    WHERE duplicate_indexes IS NOT NULL
    ORDER BY table_buffered_mb DESC, object_id, RANK() OVER (ORDER BY key_columns, data_columns)
END
ELSE IF @Output = 'OVERLAPPING'
BEGIN
    SELECT 
        index_action
        , index_pros
        , index_cons
        , QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) as object_name
        , overlapping_indexes      
        , index_name
        , type_desc
        , indexed_columns
        , included_columns
        , is_primary_key
        , is_unique
        , size_in_mb
        , buffered_mb
        , table_buffered_mb
        , buffered_percent
        , row_count
        , user_total_pct
        , user_total
        , user_seeks
        , user_scans
        , user_lookups
        , user_updates
        , read_operations
    FROM #IndexStatistics
    WHERE overlapping_indexes IS NOT NULL
    ORDER BY table_buffered_mb DESC, object_id, user_total DESC
END

END TRY
BEGIN CATCH
    SELECT @ERROR_MESSAGE  = 'Procedure Error (Line '+CAST(ERROR_LINE() AS NVARCHAR(25))+'): ' + ERROR_MESSAGE()
        ,@ERROR_SEVERITY = ERROR_SEVERITY()
        ,@ERROR_STATE = ERROR_STATE()
    
    RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)
END CATCH
GO

USE AdventureWorks2012
GO

EXEC dbo.sp_IndexAnalysis @Output = 'DETAILED'
,@TableName = '[Production].[ProductDescription]'
,@IncludeMissingIndexes = 1
,@IncludeMissingFKIndexes = 1

EXEC dbo.sp_IndexAnalysis @Output = 'OVERLAPPING'
,@TableName = '[Production].[ProductDescription]'
,@IncludeMissingIndexes = 1
,@IncludeMissingFKIndexes = 1