UGISS User Group Italiano SQL Server
Qualche settimana fa un collega mi ha sottoposto un quesito la cui risposta coinvolge alcuni concetti molto interessanti. La domanda più o meno era:
SQL Server mi dice che manca un indice, l’ho creato. Perchè eseguendo una query su sys.dm_db_missing_index_details trovo ancora l’indice come mancante? Come dico a SQL di aggiornare la DMV?
La risposta alla domanda è abbastanza semplice: una DMV non può essere aggiornata dall’utente. Unica componente con diritti di “scrittura” di questa particolare DMV è il Query Optimizer, che però va ad “aggiungere” informazioni. Se invece si vuole “svuotare” il contenuto della DMV.. beh.. non ci sono santi: serve riavviare l’istanza del server (come peraltro chiaramente riportato nella documentazione online)
E quindi? Come possiamo ricavare un’informazione in qualche modo equivalente?
A venirci in aiuto è il preziosissimo Query Plan, che grazie al lavoro del Query Optimizer può contenere anche alcune segnalazioni di warning, ovvero delle piccole anomalie che, se risolte, consentirebbero al Query Processor di eseguire la nostra query in maniera più efficiente e performante.
Così come già indicato da Sergio Govoni nell’articolo “Conversioni implicite: La plan cache ci svela quante sono e dove avvengono!” i tipi di warning che possono essere segnalati dal query optimizer sono:
- No statistics (ColumnsWithNoStatistics)
- No join predicate (NoJoinPredicate)
- Implicit conversions (PlanAffectingConvert)
- Sort on tempdb (SpillToTempDb)
- No matched indexes (UnmatchedIndexes)
- Wait (WaitWarningType)
“No matched indexes”? Proprio quello che ci serviva!!
Possiamo dunque pensare di ricavare le informazioni inerenti ad eventuali indici mancanti tramite una semplice query che vada ad esplorare lo stato attuale della plan cache!
Come? Con questa semplice query:
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
, cte AS (
SELECT QP.query_plan
, Q.N.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS Impact
, Q.N.value('(//MissingIndex/@Database)[1]', 'SYSNAME') AS DatabaseName
, Q.N.value('(//MissingIndex/@Schema)[1]', 'SYSNAME') AS SchemaName
, Q.N.value('(//MissingIndex/@Table)[1]', 'SYSNAME') AS TableName
, (
SELECT DISTINCT R.C.value('@Name', 'SYSNAME') + ', '
FROM Q.N.nodes('//ColumnGroup') AS T(CG)
CROSS APPLY T.CG.nodes('Column') AS R(C)
WHERE T.CG.value('@Usage', 'VARCHAR(10)') = 'EQUALITY'
FOR XML PATH('')
) AS EqualityColumns
, (
SELECT DISTINCT R.C.value('@Name', 'SYSNAME') + ', '
FROM Q.N.nodes('//ColumnGroup') AS T(CG)
CROSS APPLY T.CG.nodes('Column') AS R(C)
WHERE T.CG.value('@Usage', 'VARCHAR(10)') = 'INEQUALITY'
FOR XML PATH('')
) AS InequalityColumns
, (
SELECT DISTINCT R.C.value('@Name', 'SYSNAME') + ', '
FROM Q.N.nodes('//ColumnGroup') AS T(CG)
CROSS APPLY T.CG.nodes('Column') AS R(C)
WHERE T.CG.value('@Usage', 'VARCHAR(10)') = 'INCLUDE'
FOR XML PATH('')
) AS IncludeColumns
FROM sys.dm_exec_cached_plans CP
CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
CROSS APPLY QP.query_plan.nodes('//StmtSimple') AS Q(N)
WHERE Q.N.exist('QueryPlan/MissingIndexes') = 1
)
SELECT cte.query_plan
, cte.Impact
, cte.DatabaseName
, cte.SchemaName
, cte.TableName
, LEFT(cte.EqualityColumns , LEN(cte.EqualityColumns) - 1) AS EqualityColumns
, LEFT(cte.InequalityColumns, LEN(cte.InequalityColumns) - 1) AS InequalityColumns
, LEFT(cte.IncludeColumns , LEN(cte.IncludeColumns) - 1) AS IncludeColumns
FROM cte;
OK.. non proprio semplicissima, ve lo concedo!… ma il bello è che l’ho già scritta io per voi! 😉
Alla prossima!
