/*============================================================================= 

Fetch index creation recommendation and match them with a query plan to 
try to locate the query that generated the recommendation


Creation : 06.04.2023 / TSC
Modifications:

=============================================================================*/ 
USE master;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
, PlanMissingIndexes
  AS (SELECT       [qp].[query_plan],
                   [cp].[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/MissingIndex/@Database)[1]', 'sysname') AS DatabaseName,
                   stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Schema)[1]', 'sysname') AS SchemaName,
                   stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]', 'sysname') AS TableName,
                   stmt_xml.value('(QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[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,
                   [pmi].[query_plan],
                   stmt_xml.value('(@StatementText)[1]', 'varchar(max)') 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 [MissingIndexes].[DatabaseName],
               [MissingIndexes].[SchemaName],
               [MissingIndexes].[TableName],
               [MissingIndexes].[equality_columns],
               [MissingIndexes].[inequality_columns],
               [MissingIndexes].[include_columns],
               [MissingIndexes].[UseCounts],
               [MissingIndexes].[Cost],
               [MissingIndexes].[Cost] * [MissingIndexes].[UseCounts] AS [AggregateCost],
               [MissingIndexes].[Impact],
               [MissingIndexes].[query_plan],
               [MissingIndexes].[sql_text]
  FROM MissingIndexes
 WHERE [MissingIndexes].[DatabaseName] IN('[arizona]', '[arizonaCust]', '[arizonaRep]','[hcitools]')
 --AND [MissingIndexes].[TableName]    = '[PH_prescription_line]'
 ORDER BY [AggregateCost] DESC;