Eventi
Ottieni gratuitamente la certificazione in Microsoft Fabric.
19 nov, 23 - 10 dic, 23
Per un periodo di tempo limitato, il team della community di Microsoft Fabric offre buoni per esami DP-600 gratuiti.
Prepara oraQuesto browser non è più supportato.
Esegui l'aggiornamento a Microsoft Edge per sfruttare i vantaggi di funzionalità più recenti, aggiornamenti della sicurezza e supporto tecnico.
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.
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:
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.
Nota
Database SQL di Azure supporta l'ottimizzazione automatica dell'indice. L'ottimizzazione automatica degli indici utilizza l'apprendimento automatico per apprendere orizzontalmente da tutti i database in database SQL di Azure grazie all'IA e migliora in modo dinamico le azioni di ottimizzazione. L'ottimizzazione automatica degli indici include un processo di verifica per garantire un miglioramento delle prestazioni del carico di lavoro degli indici creati.
La funzionalità degli indici mancanti è costituita da due componenti:
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.I piani di esecuzione delle query possono essere generati o ottenuti in diversi modi:
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:
Aprire SSMS e connettere una sessione alla copia del database di esempio AdventureWorks.
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.
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.
Visualizzare la finestra di dialogo Trova usando i tasti di scelta rapida CTRL+F.
Cercare MissingIndex
.
In questo esempio sono presenti due elementi MissingIndex
.
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%.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.
È possibile recuperare informazioni sugli indici mancanti eseguendo una query sugli oggetti a gestione dinamica elencati nella tabella seguente.
Vista a gestione dinamica | Informazioni restituite |
---|---|
sys.dm_db_missing_index_group_stats (Transact-SQL) | Restituisce informazioni di riepilogo sui gruppi di indici mancanti, ad esempio i miglioramenti delle prestazioni che potrebbero essere ottenuti implementando un gruppo specifico di indici mancanti. |
sys.dm_db_missing_index_groups (Transact-SQL) | Restituisce informazioni su un gruppo specifico di indici mancanti, ad esempio l'identificatore di gruppo e gli identificatori di tutti gli indici mancanti contenuti in tale gruppo. |
sys.dm_db_missing_index_details (Transact-SQL) | Restituisce informazioni dettagliate su un indice mancante. Ad esempio, restituisce il nome e l'identificatore della tabella in cui manca l'indice e le colonne e i tipi di colonna che devono costituire l'indice mancante. |
sys.dm_db_missing_index_columns (Transact-SQL) | Restituisce informazioni sulle colonne di una tabella di database in cui manca un indice. |
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:
Nota
Lo script di creazione di indici nella casella degli strumenti Tiger di Microsoft esamina le DMV mancanti sugli indici e rimuove automaticamente gli indici suggeriti ridondanti, analizza gli indici a basso impatto e genera script di creazione degli indici per la revisione. Come nella query precedente, NON esegue i comandi di creazione dell'indice. Lo script di creazione dell'indice è adatto per SQL Server e l'istanza gestita di SQL di Azure. Per database SQL di Azure, è consigliabile implementare l'ottimizzazione automatica degli indici.
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.
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
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.
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.
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.
Un modo per esaminare la definizione di indici esistenti in una tabella consiste nel creare uno script degli indici con Dettagli di Esplora oggetti:
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:
StateProvinceID
e City
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:
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.
È 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.
Altre informazioni sull'ottimizzazione dell'indice e delle prestazioni sono disponibili negli articoli seguenti:
Eventi
Ottieni gratuitamente la certificazione in Microsoft Fabric.
19 nov, 23 - 10 dic, 23
Per un periodo di tempo limitato, il team della community di Microsoft Fabric offre buoni per esami DP-600 gratuiti.
Prepara ora