Pratiche migliori per i suggerimenti di Query Store

Articolo
2025-04-04

Si applica a: SQL Server 2022 (16.x) database SQL di Azure 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 su configurazione e gestione con Query Store, vedere Monitorare le prestazioni con 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 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.

Attenzione

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 Suggerimenti per le 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 da 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. Si dovrebbe considerare una riscrittura più ampia della query se si ha spesso la necessità di applicare hint di Query Store a una query. 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à dei suggerimenti di Query Store non richiede l'esatta corrispondenza delle query per influire sul risultato del piano di query. 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 utile quando non è disponibile un livello di compatibilità del database più recente, ad esempio, a causa di specifiche fornitore o ritardi di test maggiori. 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:

EXEC 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 è quando 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 regressioni a causa di un livello di compatibilità più elevato utilizzando il report delle query regredite di Query Store, lo strumento Query Tuning Advisor durante una migrazione o altri dati di telemetria delle applicazioni a livello di query. 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 un impatto sui carichi di lavoro critici dell'applicazione.

Nota

Al momento, l'hint per la query ABORT_QUERY_EXECUTION (anteprima) è disponibile solo nel database SQL di Azure.

Ad esempio, per bloccare l'esecuzione futura di query_id 39, eseguire l'istruzione seguente:

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

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, puoi cancellare il suggerimento passando il valore query_id alla procedura memorizzata sys.sp_query_store_clear_hints.

È possibile usare la query di esempio seguente per trovare tutte le query in Query Store bloccate con le viste di sistema, a partire dalla vista di sistema sys.query_store_query_hints (Transact-SQL):

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 avere esito positivo. Ciò significa che è possibile bloccare l'esecuzione futura di query scadute o annullate.
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 può essere utile ora, ma non in futuro. Ad esempio, un hint di Query Store che favorisce una query nella distribuzione dei dati precedenti può essere controproducente se le operazioni DML su larga scala modificano i dati. Una nuova distribuzione dei dati può portare l'ottimizzatore a prendere una decisione migliore rispetto a un suggerimento. Questo scenario è la conseguenza più comune del comportamento forzato del piano.

Rivalutare regolarmente la strategia degli hint di Query Store

Rivaluta la strategia esistente degli hint di Query Store nei casi seguenti:

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 degli hint di query con gli hint di Query Store non è supportata quando l'opzione di 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.