More Refinements on the Missing Indexes Query
Greg Larson posted a really nice query to find worst performing stored procedures in your system. He put in all kinds of attributes to make it customizable, changing the definition of "worst" to different measures,etc. Great query in general. In it he linked to sys.dm_exec_plan_attributes and got the db_id attribute. Duh! So instead of spelunking through the XML to retrieve the database name, I can pull the db_id and use the DB_NAME function. Cleans things up considerably. Thanks Greg. Here's the cleaned up code: WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp) SELECT DB_NAME(CAST(pa.value AS INT)) ,s.sql_handle ,s.total_elapsed_time ,s.last_execution_time ,s.execution_count ,s.total_logical_writes ,s.total_logical_reads ,s.min_elapsed_time ,s.max_elapsed_time --,s.query_hash ,p.query_plan ,p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Table)[1]', 'NVARCHAR(256)') AS TableName ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Schema)[1]', 'NVARCHAR(256)') AS SchemaName ,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/@Impact)[1]', 'DECIMAL(6,4)') AS ProjectedImpact ,ColumnGroup.value('./@Usage', 'NVARCHAR(256)') AS ColumnGroupUsage ,ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)') AS ColumnName FROM (SELECT TOP 20 s.sql_handle ,s.plan_handle ,s.total_elapsed_time…