DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 96--------------------------------------------------
       Cost of Missing Indexes
       By: srinivasma_exceldbp Date: November 9, 2014, 9:15 pm
       ---------------------------------------------------------
       -**  Missing Index Cost
       SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
       GO
       WITH XMLNAMESPACES(DEFAULT
       '
  HTML http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
       PlanMissingIndexes AS (
       SELECT
       query_plan,
       usecounts
       FROM
       sys.dm_exec_cached_plans cp
       CROSS APPLY
       sys.dm_exec_query_plan(cp.plan_handle) qp
       WHERE
       qp.query_plan.exist('//MissingIndexes') = 1
       ),
       MissingIndexes AS (
       SELECT
       
       stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/Miss
       ingIndex/@Database)[1]'
       , 'sysname') AS DatabaseName,
       
       stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/Miss
       ingIndex/@Schema)[1]'
       , 'sysname') AS SchemaName,
       
       stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/Miss
       ingIndex/@Table)[1]'
       , 'sysname') AS TableName,
       
       stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/@Imp
       act)[1]'
       , 'float') AS Impact,
       
       ISNULL(CAST(stmt_xml.value('(@StatementSubTreeCost)[1]'
       , 'VARCHAR(128)') as float),0) AS Cost,
       pmi.usecounts UseCounts,
       STUFF((SELECT DISTINCT ', ' +
       c.value('(@Name)[1]', 'sysname')
       FROM
       stmt_xml.nodes('//ColumnGroup') AS t(cg)
       CROSS APPLY cg.nodes('Column') AS r(c)
       WHERE
       cg.value('(@Usage)[1]', 'sysname') = 'EQUALITY'
       FOR  XML PATH('')), 1, 2, '') AS
       equality_columns
       ,STUFF((SELECT DISTINCT ', ' +
       c.value('(@Name)[1]', 'sysname')
       FROM stmt_xml.nodes('//ColumnGroup') AS t(cg)
       CROSS APPLY cg.nodes('Column') AS r(c)
       WHERE cg.value('(@Usage)[1]', 'sysname') =
       'INEQUALITY'
       FOR  XML PATH('')), 1, 2, '') AS
       inequality_columns
       ,STUFF((SELECT DISTINCT ', ' +
       c.value('(@Name)[1]', 'sysname')
       FROM stmt_xml.nodes('//ColumnGroup') AS t(cg)
       CROSS APPLY cg.nodes('Column') AS r(c)
       WHERE cg.value('(@Usage)[1]', 'sysname') =
       'INCLUDE'
       FOR  XML PATH('')), 1, 2, '') AS
       include_columns
       ,query_plan
       ,stmt_xml.value('(@StatementText)[1]',
       'varchar(4000)') AS sql_text
       FROM PlanMissingIndexes pmi
       CROSS APPLY query_plan.nodes('//StmtSimple') AS
       stmt(stmt_xml)
       WHERE
       stmt_xml.exist('QueryPlan/MissingIndexes') = 1
       )
       SELECT TOP 200
       DatabaseName,
       SchemaName,
       TableName,
       equality_columns,
       inequality_columns,
       include_columns,
       usecounts,
       Cost,
       Cost * UseCounts [AggregateCost],
       Impact,
       query_plan
       FROM MissingIndexes
       ORDER BY
       Cost * usecounts DESC;
       *****************************************************