Ottimizzare gli indici non cluster con suggerimenti di indici mancanti

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure

La funzionalità degli indici mancanti è uno strumento leggero per individuare gli indici che potrebbero migliorare in modo significativo le prestazioni delle query. In questo articolo viene descritto come usare i suggerimenti per l'indice mancante per ottimizzare efficacemente gli indici e migliorare le prestazioni delle query.

Limitazioni della funzionalità di indice mancante

Quando Query Optimizer genera un piano di query, analizza gli indici migliori per una determinata condizione di filtro. Se gli indici migliori non esistono, Query Optimizer genera comunque un piano di query usando i metodi di accesso meno costosi disponibili, ma archivia anche informazioni su questi indici. La funzionalità degli indici mancanti consente di accedere a tali informazioni sui migliori indici possibili in modo da poter decidere se devono essere implementati.

L'ottimizzazione delle query è un processo sensibile al tempo, quindi per la funzionalità di indice mancante esistono delle limitazioni. Le limitazioni includono:

• I suggerimenti per l'indice mancante sono basati sulle stime effettuate durante l'ottimizzazione di una singola query prima dell'esecuzione della query. I suggerimenti per l'indice mancante non vengono testati o aggiornati dopo l'esecuzione della query.
• La funzionalità di indice mancante suggerisce solo indici rowstore non cluster basati su disco. Gli indici univoci e filtrati non vengono suggeriti.
• Le colonne chiave sono suggerite, ma il suggerimento non ne specifica un ordine. Per informazioni sull'ordinamento delle colonne, vedere la sezione Applicare i suggerimenti per l'indice mancante di questo articolo.
• Le colonne incluse vengono suggerite, ma quando viene suggerito un numero elevato di queste colonne, SQL Server non esegue alcuna analisi dei vantaggi relativi alle dimensioni dell'indice risultante.
• Le richieste di indice mancante possono offrire variazioni simili di indici nella stessa tabella e nelle stesse colonne tra query. È importante rivedere i suggerimenti per gli indici e combinarli laddove possibile.
• I suggerimenti non vengono forniti per piani di query semplici.
• Le informazioni sui costi sono meno accurate per le query che coinvolgono solo predicati di disuguaglianza.
• I suggerimenti vengono raccolti per un massimo di 600 gruppi di indici mancanti. Dopo aver raggiunto questa soglia, non vengono raccolti altri dati del gruppo di indici mancanti.

A causa di queste limitazioni, i suggerimenti per l'indice mancante durante l'esecuzione di analisi degli indici, progettazione, ottimizzazione e test vengono considerati come una delle diverse origini di informazioni. I suggerimenti per l'indice mancante non sono prescrizioni per creare indici esattamente come suggerito.

Visualizzare i suggerimenti per gli indici mancanti

La funzionalità degli indici mancanti è costituita da due componenti:

• L'elemento MissingIndexes nel codice XML dei piani di esecuzione. Questo componente consente di correlare gli indici che Query Optimizer considera mancanti con le query per le quali mancano.
• Un set di viste a gestione dinamica (DMV) su cui è possibile eseguire query per restituire informazioni sugli indici mancanti. Questo componente consente di visualizzare tutti i suggerimenti sull'indice mancante per un database.

Visualizzare i suggerimenti per l'indice mancante nei piani di esecuzione

I piani di esecuzione delle query possono essere generati o ottenuti in diversi modi:

• Quando si scrive o si ottimizza una query, è possibile usare SQL Server Management Studio (SSMS) per visualizzare il piano di esecuzione stimato senza eseguire la query oppure eseguire la query e visualizzare un piano di esecuzione effettivo.
• Query Store, se abilitato, raccoglie i piani di esecuzione.
• È possibile identificare i piani di esecuzione memorizzati nella cache eseguendo query su DMV, ad esempio sys.dm_exec_text_query_plan.

Ad esempio, è possibile usare la query seguente per generare richieste di indice mancante nel database di esempio AdventureWorks.

SELECT City, StateProvinceID, PostalCode  
FROM Person.Address as a
JOIN Person.BusinessEntityAddress as ba on
    a.AddressID = ba.AddressID
JOIN Person.Person as  p on
    ba.BusinessEntityID = p.BusinessEntityID
WHERE p.FirstName like 'K%' and
    StateProvinceID = 9;  
GO 

Per generare e visualizzare le richieste di indice mancante:

1. Aprire SSMS e connettere una sessione alla copia del database di esempio AdventureWorks.

2. Incollare la query nella sessione e generare un piano di esecuzione stimato in SSMS per la query selezionando il pulsante Visualizza piano di esecuzione stimato sulla barra degli strumenti. Il piano di esecuzione verrà visualizzato in un riquadro della sessione corrente. Verrà visualizzata un'istruzione Indice mancante verde nella parte superiore del piano del grafico.

   

   Un singolo piano di esecuzione può contenere più richieste di indice mancante, ma è possibile visualizzare una sola richiesta di indice mancante nel piano di esecuzione grafico. Un'opzione per visualizzare un elenco completo di indici mancanti per un piano di esecuzione consiste nel visualizzare il codice XML del piano di esecuzione.

3. Fare clic con il pulsante destro del mouse sul piano di esecuzione e scegliere Mostra XML piano di esecuzione dal menu.

   

   Il codice XML del piano di esecuzione verrà aperto come nuova scheda all'interno di SSMS.

   Nota

   Nell'opzione di menu Dettagli indice mancante verrà visualizzato solo un singolo suggerimento di indice mancante anche se nel codice XML del piano di esecuzione sono presenti più suggerimenti. Il suggerimento per l'indice mancante visualizzato potrebbe non essere quello con il miglioramento stimato più elevato per la query.

4. Visualizzare la finestra di dialogo Trova usando i tasti di scelta rapida CTRL+F.

5. Cercare MissingIndex.

   

   In questo esempio sono presenti due elementi MissingIndex.

   • Il primo indice mancante suggerisce che la query potrebbe usare un indice nella tabella Person.Address che supporta una ricerca di uguaglianza nella colonna StateProvinceID, che include altre due colonne City e PostalCode'. Al momento dell'ottimizzazione, Query Optimizer ritiene che questo indice possa ridurre il costo stimato della query del 34,2737%.
   • Il primo indice mancante suggerisce che la query potrebbe usare un indice nella tabella Person.Person che supporta una ricerca di uguaglianza nella colonna FirstName. Al momento dell'ottimizzazione, Query Optimizer ritiene che questo indice possa ridurre il costo stimato della query del 18,1102%.

Ogni indice non cluster basato su disco nel database occupa spazio, comporta un sovraccarico per inserimenti, aggiornamenti ed eliminazioni e potrebbe richiedere la manutenzione. Per questi motivi, prima di aggiungere un indice in base a un piano di esecuzione della query è consigliabile effettuare un'analisi dettagliata delle richieste di indice mancante e degli indici esistenti in una tabella.

Visualizzare i suggerimenti per l'indice mancante nelle DMV

È possibile recuperare informazioni sugli indici mancanti eseguendo una query sugli oggetti a gestione dinamica elencati nella tabella seguente.

La query seguente usa le DMV di indice mancante per generare istruzioni CREATE INDEX. Le istruzioni di creazione dell'indice sono destinate a facilitare la creazione di un file DDL personalizzato dopo aver esaminato tutte le richieste per la tabella insieme agli indici esistenti nella tabella.

SELECT TOP 20
    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1), 
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) 
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' + 
        CONVERT (varchar, mig.index_group_handle) + '_' + 
        CONVERT (varchar, mid.index_handle) + ' ON ' + 
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') + 
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' + 
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON 
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON 
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;
GO

Questa query ordina i suggerimenti in base a una colonna denominata estimated_improvement. Il miglioramento stimato si basa su una combinazione di:

• Costo stimato delle query associate alla richiesta di indice mancante.
• Impatto stimato dell'aggiunta dell'indice. Questa è una stima della riduzione del costo della query da parte dell'indice non cluster.
• Somma delle esecuzioni di operatori di query (ricerche e analisi) eseguite per le query associate alla richiesta di indice mancante. Come illustrato in Persistenza degli indici mancanti con Query Store, queste informazioni vengono cancellate periodicamente.

Vedere Limitazioni della funzionalità di indice mancante e Come applicare suggerimenti per l'indice mancante prima di creare indici e modificare il nome dell'indice in modo che corrisponda alla convenzione di denominazione per il database.

Rendere persistenti gli indici mancanti con Query Store

I suggerimenti per l'indice mancante nelle DMV vengono cancellati da eventi quali i riavvii dell'istanza, i failover e l'impostazione offline di un database. Inoltre, quando i metadati di una tabella cambiano, tutte le informazioni sull'indice mancante relative a tale tabella vengono eliminate da questi oggetti a gestione dinamica. Le modifiche ai metadati della tabella possono verificarsi quando le colonne vengono aggiunte o eliminate da una tabella, ad esempio, o quando viene creato un indice in una colonna di una tabella. L'esecuzione di un'operazione ALTER INDEX REBUILD su un indice in una tabella cancella anche le richieste di indice mancante per quella tabella.

Analogamente, i piani di esecuzione archiviati nella cache dei piani vengono cancellati da eventi quali riavvii dell'istanza, failover e impostazione offline di un database. I piani di esecuzione potrebbero essere rimossi dalla cache a causa dell'utilizzo elevato di memoria e delle ricompilazioni.

I suggerimenti per gli indici mancanti nei piani di esecuzione possono essere salvati in modo permanente in questi eventi abilitando Query Store.

La query seguente recupera i primi 20 piani di query contenenti richieste di indice mancante dall'archivio query in base a una stima approssimativa delle letture logiche totali per la query. I dati sono limitati alle esecuzioni di query nelle ultime 48 ore.

SELECT TOP 20
    qsq.query_id,
    SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
    SUM(qrs.count_executions) AS sum_executions,
    AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads,
    SUM(qsq.count_compiles) AS sum_compiles,
    (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
        WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,    
    TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
        WHERE qsp2.query_id=qsq.query_id
        ORDER BY qsp2.plan_id DESC)) AS query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on 
    qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi on 
    qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE    
    qsp.query_plan like N'%<MissingIndexes>%'
    and qsrsi.start_time >= DATEADD(HH, -48, SYSDATETIME())
GROUP BY qsq.query_id, qsq.query_hash
ORDER BY est_logical_reads DESC;
GO

Applicare suggerimenti per l'indice mancante

Per usare in modo efficace i suggerimenti per gli indici mancanti, seguire le linee guida per la progettazione di indici non cluster. Quando si ottimizzano gli indici non cluster con suggerimenti per l'indice mancante, esaminare la struttura della tabella di base, combinare attentamente gli indici, considerare l'ordine delle colonne chiave ed esaminare i suggerimenti per le colonne incluse.

Esaminare la struttura della tabella di base

Prima di creare indici non cluster in una tabella in base ai suggerimenti di indice mancante, esaminare l'indice cluster della tabella.

Un modo per verificare la presenza di un indice cluster consiste nell'usare la stored procedure di sistema sp_helpindex. Ad esempio, è possibile visualizzare un riepilogo degli indici nella tabella Person.Address eseguendo l'istruzione seguente:

exec sp_helpindex 'Person.Address';
GO

Esaminare la colonna index_description. Una tabella può avere un solo indice cluster. Se per la tabella è stato implementato un indice cluster, il index_description il conterrà la parola "clustered".

Se non è presente alcun indice cluster, la tabella è un heap. In questo caso, verificare se la tabella è stata creata intenzionalmente come heap per risolvere un problema di prestazioni specifico. La maggior parte delle tabelle trae vantaggio dagli indici cluster: spesso le tabelle vengono implementate come heap per errore. Prendere in considerazione l'implementazione di un indice cluster in base alle linee guida per la progettazione di indici cluster.

Esaminare gli indici mancanti e gli indici esistenti per la sovrapposizione

Gli indici mancante possono offrire variazioni simili di indici nella stessa tabella e nelle stesse colonne tra query. Gli indici mancanti possono anche essere simili agli indici esistenti in una tabella. Per ottenere prestazioni ottimali, è consigliabile esaminare gli indici mancanti e gli indici esistenti per la sovrapposizione ed evitare di creare indici duplicati.

Creare uno script per gli indici esistenti in una tabella

Un modo per esaminare la definizione di indici esistenti in una tabella consiste nel creare uno script degli indici con Dettagli di Esplora oggetti:

1. Connettersi all'istanza o al database in Esplora oggetti.
2. Espandere il nodo per il database in questione in Esplora oggetti.
3. Espandere la cartella Tabelle .
4. Espandere la tabella per cui si desidera creare uno script per gli indici.
5. Selezionare la cartella Indici.
6. Se il riquadro Dettagli di Esplora oggetti non è già aperto, scegliere Dettagli di Esplora oggetti dal menu Visualizza o premere F7.
7. Selezionare tutti gli indici elencati nel riquadro Dettagli di Esplora oggetti con i tasti di scelta rapida CTRL+A.
8. Fare clic con il pulsante destro del mouse in un punto qualsiasi dell'area selezionata e scegliere l'opzione di menu Indice script come, quindi CREA in e Nuova finestra di Editor di query.

Esaminare gli indici e combinarli laddove possibile

Esaminare i suggerimenti per gli indici mancanti per una tabella come gruppo, insieme alle definizioni degli indici esistenti nella tabella. Tenere presente che quando si definiscono gli indici, in genere le colonne di uguaglianza devono essere inserite prima delle colonne di disuguaglianza e insieme devono formare la chiave dell'indice. Per determinare un ordine efficiente per le colonne di uguaglianza, ordinarle in base alla selettività a partire dalle colonne più selettive, all'estrema sinistra nell'elenco di colonne. Le colonne univoche sono più selettive, mentre le colonne con molti valori ripetuti sono meno selettive.

Aggiungere le colonne incluse all'istruzione CREATE INDEX mediante la clausola INCLUDE. L'ordine delle colonne incluse non influisce sulle prestazioni delle query. Pertanto, quando si combinano indici, le colonne incluse possono essere combinate senza preoccuparsi dell'ordine. Altre informazioni sono disponibili nelle linee guida per le colonne incluse.

Ad esempio, potrebbe essere presente una tabella, Person.Address, con un indice esistente nella colonna chiave StateProvinceID. È possibile che vengano visualizzati suggerimenti per l'indice mancante per la tabella Person.Address per le colonne seguenti:

• Filtri DI UGUAGLIANZA per StateProvinceID e City
• Filtri DI UGUAGLIANZA per StateProvinceID e City, INCLUDE PostalCode

La modifica dell'indice esistente in modo che corrisponda al secondo suggerimento, un indice con chiavi in StateProvinceID e City che include PostalCode, soddisfa probabilmente le query che hanno generato entrambi i suggerimenti per l'indice.

I compromessi sono molto comuni durante l'ottimizzazione degli indici. È probabile che per molti set di dati la colonna City sia più selettiva rispetto alla colonna StateProvinceID. Tuttavia, se l'indice esistente su StateProvinceID viene ampiamente usato e altre richieste effettuano richieste massicce in StateProvinceID e City, in generale, avere un unico indice con entrambe le colonne nella chiave (che porta a StateProvinceID) comporta un sovraccarico inferiore per il database, anche se non è la colonna più selettiva.

Gli indici possono essere modificati in diversi modi:

• È possibile usare l'istruzione CREATE INDEX con la clausola DROP_EXISTING. È possibile rinominare gli indici dopo la modifica in modo che il nome descriva ancora in modo accurato la definizione dell'indice in base alla convenzione di denominazione.
• È possibile usare l'istruzione DROP INDEX (Transact-SQL) seguita da un'istruzione CREATE INDEX.

L'ordine delle chiavi di indice è importante quando si combinano i suggerimenti per l'indice: City come colonna iniziale è diversa da StateProvinceID come colonna iniziale. Per altre informazioni, vedere le linee guida per la progettazione di indici non cluster.

Quando si creano indici, è consigliabile usare le operazioni sugli indici online quando sono disponibili.

Anche se gli indici possono migliorare notevolmente le prestazioni delle query in alcuni casi, anche gli indici hanno costi generali e di gestione. Esaminare le linee guida generali per la progettazione degli indici per valutare i vantaggi degli indici prima di crearli.

Verificare se la modifica dell'indice ha esito positivo

È importante verificare se le modifiche apportate all'indice sono state completate: Query Optimizer usa gli indici?

Un modo per convalidare le modifiche dell'indice consiste nell'usare Query Store per identificare le query con richieste di indice mancante. Prendere nota di query_id per le query. Usare la visualizzazione Query rilevate in Query Store per verificare se i piani di esecuzione sono stati modificati per una query e se l'utilità di ottimizzazione usa l'indice nuovo o modificato. Altre informazioni sulle query rilevate sono disponibili in Iniziare a risolvere i problemi di prestazioni relativi alle query.

Contenuto correlato

Altre informazioni sull'ottimizzazione dell'indice e delle prestazioni sono disponibili negli articoli seguenti:

• Architettura e guida per la progettazione degli indici di SQL Server e Azure SQL
• sys.dm_db_missing_index_details (Transact-SQL)
• Monitorare le prestazioni tramite Query Store