SQL Server Central: Prioritize Missing Index Recommendations (2005)

Prioritize Missing Index Recommendations (2005) - SQL Server Central

-- ---------------------------------------------------------------------------------------------------
--  Author:  Michael Smith, Minneapolis, MN
--  Date:    2007-08-17
--  
--  Purpose: To report indexes proposed by the database engine that have highest probable user impact.
--    Note that no consideration is given to 'reasonableness' of indexes-- bytes, overall size, total
--    number of indexes on a table, etc.  Intended to provide targeted starting point for holistic 
--    evaluation of indexes.
--  
--  
--  Directions:  Specify running total percent impact threshold and minimum number or results.
--  
--  
--  Many thanks to Itzik Ben-Gan for the query technique and pattern as discussed in his book,
--  Inside SQL Server 2005: T-SQL Querying.  Also, the "impact formula" is taken from SQL Server
--  Books Online [cost * impact * (scans + seeks)].
-- ---------------------------------------------------------------------------------------------------



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

DECLARE @percent_lvl  int;
DECLARE @min_rows int;

SET @percent_lvl = 50;
SET @min_rows = 20;


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
HAVING SUM(missing_index_impact_2.percent_impact) - missing_index_impact_1.percent_impact >= @percent_lvl
OR missing_index_impact_1.rn <= @min_rows
),
missing_index_details AS (
SELECT 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
FROM agg_missing_index_impact
JOIN missing_index_details
ON agg_missing_index_impact.index_handle = missing_index_details.index_handle
ORDER
BY agg_missing_index_impact.rn ASC;
-- ---------------------------------------------------------------------------------------------------
--  Author:  Michael Smith, Minneapolis, MN
--  Date:    2007-08-17
--  
--  Purpose: To report indexes proposed by the database engine that have highest probable user impact.
--    Note that no consideration is given to 'reasonableness' of indexes-- bytes, overall size, total
--    number of indexes on a table, etc.  Intended to provide targeted starting point for holistic 
--    evaluation of indexes.
--  
--  
--  Directions:  Specify running total percent impact threshold and minimum number or results.
--  
--  
--  Many thanks to Itzik Ben-Gan for the query technique and pattern as discussed in his book,
--  Inside SQL Server 2005: T-SQL Querying.  Also, the "impact formula" is taken from SQL Server
--  Books Online [cost * impact * (scans + seeks)].
-- ---------------------------------------------------------------------------------------------------



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

DECLARE @percent_lvl  int;
DECLARE @min_rows int;

SET @percent_lvl = 50;
SET @min_rows = 20;


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
HAVING SUM(missing_index_impact_2.percent_impact) - missing_index_impact_1.percent_impact >= @percent_lvl
OR missing_index_impact_1.rn <= @min_rows
),
missing_index_details AS (
SELECT 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
FROM agg_missing_index_impact
JOIN missing_index_details
ON agg_missing_index_impact.index_handle = missing_index_details.index_handle
ORDER
BY agg_missing_index_impact.rn ASC;