Prioritize Missing Index Recommendations 2 (2005)

,

This is an enhanced version of my previous script: Prioritize Missing Index Recommendations (2005).

To aid in evaluation of whether the recommended index is reasonable, I have added :

1. counts for key columns and the total columns of the recommended index 

2. the length/bytes for both key and all columns  Note this is 'per row', not what the space used would be once actually created

I have also removed the threshold/min row construct, so all recommended indexes will appear.

-- ---------------------------------------------------------------------------------------------------
--  Author:  Michael Smith, Minneapolis, MN
--  Date:    2007-08-17
--  
--  Purpose: To report indexes proposed by the database engine that have highest probable user impact.
--  
--  Gives counts of columns, plus the bytes of columns (key columns and total) to assist in determining
--  if recommended index is a reasonable index to implement.
-- ---------------------------------------------------------------------------------------------------

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
GO

WITH	missing_index_impact AS (
		        SELECT	
				        dm_db_missing_index_groups.index_handle,
				        SUM(
				            (dm_db_missing_index_group_stats.avg_total_user_cost * dm_db_missing_index_group_stats.avg_user_impact *
				                (dm_db_missing_index_group_stats.user_seeks + dm_db_missing_index_group_stats.user_scans))
				            ) AS "total_impact",
				        (100.00 *
				            SUM(dm_db_missing_index_group_stats.avg_total_user_cost * dm_db_missing_index_group_stats.avg_user_impact *
				                (dm_db_missing_index_group_stats.user_seeks + dm_db_missing_index_group_stats.user_scans)) /
                                 SUM(SUM(dm_db_missing_index_group_stats.avg_total_user_cost *
                                    dm_db_missing_index_group_stats.avg_user_impact *
                                    (dm_db_missing_index_group_stats.user_seeks + dm_db_missing_index_group_stats.user_scans))) OVER()
                         ) AS "percent_impact",
				        ROW_NUMBER() OVER(ORDER BY SUM(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)) DESC ) AS rn
		        FROM	sys.dm_db_missing_index_groups AS dm_db_missing_index_groups
		        JOIN	sys.dm_db_missing_index_group_stats AS dm_db_missing_index_group_stats
		          ON	dm_db_missing_index_groups.index_group_handle = dm_db_missing_index_group_stats.group_handle
		        GROUP
		           BY	dm_db_missing_index_groups.index_handle),
		 agg_missing_index_impact AS (
 		        SELECT	missing_index_impact_1.index_handle,
		                missing_index_impact_1.total_impact,
		                SUM(missing_index_impact_2.total_impact) AS running_total_impact,
		                missing_index_impact_1.percent_impact,
		                SUM(missing_index_impact_2.percent_impact) AS running_total_percent,
		                missing_index_impact_1.rn
		        FROM	missing_index_impact AS missing_index_impact_1
		        JOIN	missing_index_impact AS missing_index_impact_2
		          ON	missing_index_impact_1.rn <= missing_index_impact_2.rn
		        GROUP
		           BY	missing_index_impact_1.index_handle, missing_index_impact_1.total_impact,
		                missing_index_impact_1.percent_impact, missing_index_impact_1.rn
		 ),
		 missing_index_details AS (
	 		    SELECT	dm_db_missing_index_details."object_id",
	 		            dm_db_missing_index_details.index_handle,
			            dm_db_missing_index_details."statement",
			            dm_db_missing_index_details.equality_columns,
			            dm_db_missing_index_details.inequality_columns,
			            dm_db_missing_index_details.included_columns
	            FROM	sys.dm_db_missing_index_details AS dm_db_missing_index_details
		 )
		 
SELECT	agg_missing_index_impact.rn,
		missing_index_details."statement",
		agg_missing_index_impact.running_total_impact,
		agg_missing_index_impact.total_impact,
		agg_missing_index_impact.running_total_percent,
		agg_missing_index_impact.percent_impact,
		missing_index_details.equality_columns,
		missing_index_details.inequality_columns,
		missing_index_details.included_columns,
		"key".index_key_column_count,
		"key".index_key_column_bytes,
		"all".index_all_column_count,
		"all".index_all_column_bytes
FROM	agg_missing_index_impact AS agg_missing_index_impact
JOIN	missing_index_details AS missing_index_details
  ON	agg_missing_index_impact.index_handle = missing_index_details.index_handle
JOIN   (
        SELECT  missing_index_details1.index_handle,
                COUNT(*) AS index_key_column_count,
                SUM(COL_LENGTH(missing_index_details1."statement", dm_db_missing_index_columns1.column_name )) AS index_key_column_bytes
        FROM    missing_index_details AS missing_index_details1
        CROSS
        APPLY   sys.dm_db_missing_index_columns (missing_index_details1.index_handle) AS dm_db_missing_index_columns1
        WHERE   dm_db_missing_index_columns1.column_usage = 'EQUALITY'
           OR   dm_db_missing_index_columns1.column_usage = 'INEQUALITY'
        GROUP
           BY   missing_index_details1.index_handle
        ) AS "key"
  ON    missing_index_details.index_handle = "key".index_handle
JOIN   (
        SELECT  missing_index_details2.index_handle,
                COUNT(*) AS index_all_column_count,
                SUM(COL_LENGTH(missing_index_details2."statement", dm_db_missing_index_columns2.column_name )) AS index_all_column_bytes
        FROM    missing_index_details AS missing_index_details2
        CROSS
        APPLY   sys.dm_db_missing_index_columns (missing_index_details2.index_handle) AS dm_db_missing_index_columns2
        GROUP
           BY   missing_index_details2.index_handle
        ) AS "all"
  ON    missing_index_details.index_handle = "all".index_handle
ORDER
   BY	agg_missing_index_impact.rn ASC;

Rate

5 (2)

Share

Share

Rate

5 (2)