Pratiche migliori per i suggerimenti di Query Store

Si applica a: SQL Server 2022 (16.x) e versioni successive Azure SQL Database Istanza Gestita di SQL di Azure Database SQL in Microsoft Fabric

Questo articolo illustra in dettaglio le procedure consigliate per usare gli hint di Query Store. Gli hint di Query Store consentono di modellare le forme del piano di query senza modificare il codice dell'applicazione.

Per altre informazioni sulla configurazione e l'amministrazione con Query Store, vedere Monitorare le prestazioni tramite Query Store.
Per informazioni sull'individuazione di informazioni utilizzabili e sull'ottimizzazione delle prestazioni con Query Store, vedere Ottimizzazione delle prestazioni con Query Store.
Per le procedure consigliate generali in Query Store, vedere Procedure consigliate per il monitoraggio dei carichi di lavoro con Query Store.

Casi d'uso degli hint di Query Store

Ritenere ideali i seguenti casi d'uso per gli hint di Query Store. Per altre informazioni, vedere Quando usare gli hint di Query Store.

Caution

Poiché Query Optimizer di SQL Server in genere seleziona il piano di esecuzione ottimale per una query, è consigliabile usare hint solo come ultima risorsa e sempre da parte di sviluppatori e amministratori esperti di database. Per ulteriori informazioni, vedere i suggerimenti di query .

Quando non è possibile modificare il codice,

gli hint di Query Store consentono di influenzare i piani di esecuzione delle query senza modificare il codice dell'applicazione o gli oggetti di database. Nessun'altra funzionalità consente di applicare suggerimenti della query in modo rapido e semplice.

È possibile usare gli hint di Query Store, ad esempio per trarre vantaggio dai carichi di lavoro extract-transform-load (ETL), senza ridistribuire il codice. Scopri come migliorare il caricamento massivo con i suggerimenti di Query Store in questo video di 14 minuti.

Gli hint di Query Store sono metodi di ottimizzazione delle query leggeri, ma se una query diventa problematica, è consigliabile risolvere con modifiche di codice più notevoli. Se si trova regolarmente la necessità di applicare hint di Query Store a una query, è consigliabile riscrivere una query più grande. Query Optimizer di SQL Server seleziona in genere il piano di esecuzione migliore per una query. È consigliabile usare solo hint come ultima risorsa per sviluppatori esperti e amministratori di database.

Per informazioni sugli hint di query applicabili, vedere Hint di query supportati.

In condizioni di carico elevato delle transazioni o con codice mission-critical

Se le modifiche al codice sono poco pratiche a causa di requisiti di alta disponibilità o di carico transazionale, i suggerimenti del Query Store possono applicare rapidamente suggerimenti di query ai carichi di lavoro di query esistenti. L'aggiunta e la rimozione di hint di Query Store è semplice.

Gli hint di Query Store possono essere aggiunti e rimossi a lotti di query per adattare le prestazioni a periodi programmati per gestire picchi eccezionali di carico di lavoro.

Come sostituzione per le guide di piano

Prima degli hint di Query Store bisognava basarsi sulle guide di piano per eseguire attività simili, che possono essere complesse da usare. Gli hint di Query Store sono integrati con le funzionalità di Query Store di SQL Server Management Studio (SSMS) per cercare visivamente le query.

Con le guide di piano è necessario eseguire ricerche in tutti i piani usando frammenti di query. La funzionalità degli hint di Query Store non richiede query che corrispondono esattamente per influire sul piano di query risultante. Gli hint di Query Store possono essere applicati a query_id nel set di dati di Query Store.

Gli hint di Query Store sostituiscono gli hint a livello di istruzione codificati in modo rigido e le guide di piano esistenti.

Considerare un livello di compatibilità più recente

Gli hint di Query Store possono essere un metodo prezioso quando un livello di compatibilità del database più recente non è disponibile a causa di specifiche fornitore o ritardi di test maggiori, ad esempio. Quando per un database è disponibile un livello di compatibilità superiore, è consigliabile aggiornare il livello di compatibilità del database di una singola query per sfruttare le ottimizzazioni delle prestazioni e delle funzionalità più recenti di SQL Server.

Ad esempio, se si dispone di un'istanza di SQL Server 2022 (16.x) con un database con livello di compatibilità 140, è comunque possibile usare gli hint di Query Store per eseguire singole query nel livello di compatibilità 160. È possibile usare il seguente hint:

EXECUTE sys.sp_query_store_set_hints
    @query_id = 39,
    @query_hints = N'OPTION(USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160''))';

Per un tutorial completo, vedere gli Esempi di suggerimenti per Query Store.

Prendere in considerazione un livello di compatibilità precedente dopo l'aggiornamento

Un altro caso in cui gli hint di Query Store possono essere utili è la posizione in cui le query non possono essere modificate direttamente dopo la migrazione o l'aggiornamento di un'istanza di SQL Server. Usare gli hint di Query Store per applicare un livello di compatibilità precedente per una query fino a quando non può essere riscritto o risolto in altro modo per ottenere prestazioni valide nel livello di compatibilità più recente. Identificare le query outlier che hanno subito una regressione con un livello di compatibilità superiore utilizzando il report delle query regredite di Query Store, lo strumento Assistente di ottimizzazione delle query durante una migrazione o altri dati di telemetria delle applicazioni a livello di interrogazione. Per altre informazioni sulle differenze tra i livelli di compatibilità, vedere Differenze tra i livelli di compatibilità.

Dopo il test delle prestazioni del nuovo livello di compatibilità e la distribuzione di hint di Query Store in questo modo, è possibile aggiornare il livello di compatibilità dell'intero database mantenendo le query chiave problematiche sul livello di compatibilità precedente, senza modificare il codice.

Bloccare l'esecuzione futura di query problematiche

È possibile usare l'hint per la ABORT_QUERY_EXECUTION query per bloccare l'esecuzione futura di query problematiche note, ad esempio query non essenziali che causano un utilizzo elevato delle risorse e influiscono sui carichi di lavoro critici dell'applicazione.

Note

L'hint per la query ABORT_QUERY_EXECUTION è disponibile solo nel database SQL di Azure,^nell'AUTD dell'istanza gestita di SQL di Azure e in SQL Server 2025 (17.x).

Ad esempio, per bloccare l'esecuzione futura di query_id 39, eseguire sys.sp_query_store_set_hints come indicato di seguito:

EXECUTE sys.sp_query_store_set_hints
    @query_id = 39,
    @query_hints = N'OPTION (USE HINT (''ABORT_QUERY_EXECUTION''))';

Per sbloccare la stessa query, eseguire sys.sp_query_store_clear_hints:

EXECUTE sys.sp_query_store_clear_hints @query_id = 39;

Per ulteriori informazioni, vedere gli esempi di hint per Query Store.

Si applicano le considerazioni seguenti:

Quando si specifica questo hint per una query, un tentativo di esecuzione della query ha esito negativo con errore 8778, gravità 16, l'esecuzione della query è stata interrotta perché è stato specificato l'hint ABORT_QUERY_EXECUTION.
Per sbloccare una query, è possibile cancellare l'hint passando il valore query_id al parametro @query_id nella stored procedure sys.sp_query_store_clear_hints.
- Questa procedura memorizzata cancella tutte le indicazioni per una query. Se si desidera mantenere gli hint esistenti durante lo sblocco della query, usare sys.sp_query_store_set_hints, rimuovendo l'hint ABORT_QUERY_EXECUTION ma mantenendo altri hint.

È possibile usare le viste di sistema per trovare query in Query Store bloccate, come nella query di esempio seguente:

SELECT qsh.query_id,
       q.query_hash,
       qt.query_sql_text
FROM sys.query_store_query_hints AS qsh
     INNER JOIN sys.query_store_query AS q
         ON qsh.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS qt
         ON q.query_text_id = qt.query_text_id
WHERE UPPER(qsh.query_hint_text) LIKE '%ABORT[_]QUERY[_]EXECUTION%';

Per ottenere il query_id valore, è necessario registrare almeno un'esecuzione di query in Query Store. Questa esecuzione non deve necessariamente avere successo. Ciò significa che è possibile bloccare l'esecuzione futura di query scadute o annullate.
Se è necessario bloccare o sbloccare tutte le query con un hash di query specifico, è consigliabile usare uno script di automazione. Ad esempio, dbo.sp_query_store_modify_hints_by_query_hash è una stored procedure di esempio che chiama la stored procedure di sistema sys.sp_query_store_set_hints o sys.sp_query_store_clear_hints ripetutamente per tutti i valori query_id che corrispondono a un hash di query.
Se una query è già in esecuzione quando la si blocca, l'esecuzione continua. È possibile usare l'istruzione KILL per interrompere la query.
- L'esecuzione delle query terminate non viene registrata in Query Store. Se la query non è ancora presente in Query Store, è necessario consentire il completamento o il timeout della query per ottenere un valore query_id che è possibile bloccare.
Quando una query viene bloccata dall'hint ABORT_QUERY_EXECUTION, le colonne execution_type e execution_type_desc nella vista sys.query_store_runtime_stats vengono impostate rispettivamente su 4 ed Eccezione.
Come per tutti gli hint di Query Store, è necessario disporre dell'autorizzazione ALTER per il database per impostare e cancellare l'hint ABORT_QUERY_EXECUTION .

Considerazioni sui suggerimenti di Query Store

Quando si distribuiscono hint di Query Store, tenere presenti gli scenari seguenti.

Modifiche alla distribuzione dei dati

Le guide dei piani, i piani forzati tramite Query Store e gli hint di Query Store sostituiscono il processo decisionale dell'ottimizzatore. L'hint di Query Store potrebbe essere utile ora, ma non in futuro. Ad esempio, se un hint di Query Store consente una query nella distribuzione dei dati precedente, potrebbe essere controproducente se le operazioni DML su larga scala modificano i dati. Una nuova distribuzione dei dati potrebbe indurre l'ottimizzatore a prendere una decisione migliore rispetto a un suggerimento. Questo scenario è la conseguenza più comune dell'impostazione forzata del comportamento del piano.

Rivalutare regolarmente la strategia degli hint di Query Store

Rivalutate la strategia attuale degli hint di Query Store nei seguenti casi:

Dopo note modifiche nella distribuzione di grandi volumi di dati.
Quando le risorse disponibili per il database cambiano. Ad esempio, quando cambiano le dimensioni di calcolo del database SQL di Azure, dell'istanza gestita di SQL o della macchina virtuale di SQL Server.
Dove la correzione della pianificazione è diventata duratura. Gli hint di Query Store sono più indicati per le correzioni a breve termine.
Regressioni impreviste delle prestazioni.

Potenziale di impatto generale

Gli hint di Query Store influiscono su tutte le esecuzioni della query, indipendentemente dal set di parametri, dall'applicazione di origine, dall'utente o dal set di risultati. In caso di regressione accidentale delle prestazioni, gli hint di Query Store creati con sys.sp_query_store_set_hints possono essere facilmente rimossi con sys.sp_query_store_clear_hints.

Eseguire con attenzione test di carico per sistemi critici o sensibili prima di applicare gli hint di Query Store nell'ambiente di produzione.

La parametrizzazione forzata e l'hint RECOMPILE non sono supportati

L'applicazione dell'hint di RECOMPILE query con gli elementi suggeriti di Query Store non è supportata se l'opzione del database PARAMETERIZATION è impostata su FORCED. Per altre informazioni, vedere Linee guida per l'utilizzo della parametrizzazione forzata.

L'hint RECOMPILE non è compatibile con la parametrizzazione forzata impostata a livello di database. Se il database usa la parametrizzazione forzata e l'hint RECOMPILE fa parte della stringa hint impostata in Query Store per una query, il motore di database ignora l'hint RECOMPILE e applica altri hint, se specificato. Inoltre, a partire da luglio 2022 nel database SQL di Azure, viene generato un avviso (codice di errore 12461) che indica che l'hint RECOMPILE è stato ignorato.