Home > Articoli > Missing Index… Cache

Missing Index… Cache

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!

 

 

 

 

 

 

 

Chi è Luca Bruni

Luca Bruni ottiene la Laurea Magistrale in Ingegneria Informatica presso il Politecnico di Milano (sede di Como) nel 2006. Ha lavorato in diverse aziende coprendo vari ruoli: Software Developer, Consultant, Project Manager. Dal 2010 lavora in qualità di Software & Data Architect presso una nota software house svizzera. Lavora con SQL Server dalla versione 2000 e da Dicembre 2016 è Contributor UGISS.

Leggi Anche

SQL Server e SQL Azure Virtual Labs

Quante volte avreste voluto provare le nuove funzionalità di SQL Server 2017, ma siete stati …