Procedure consigliate per hint di Query Store

Articolo
03/21/2024

Si applica a: SQL Server 2022 (16.x) Database SQL di Azure Istanza gestita di SQL di Azure

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.

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 altre informazioni, vedere Hint 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 hint per la query in modo rapido e semplice.

Ad esempio è possibile usare gli hint di Query Store per trarre vantaggio da ETL senza ridistribuire il codice. Informazioni su come migliorare il caricamento bulk con gli hint 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. È consigliabile riscrivere una query più grande se si ha spesso la necessità di applicare hint di Query Store a una query. 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 informazioni sugli hint di query applicabili, vedere Hint di query supportati.

Carico elevato delle transazioni o con codice mission critical

Se le modifiche al codice sono poco pratiche a causa di requisiti di tempo di attività elevati o di caricamento transazionale, gli hint di Query Store possono applicare rapidamente hint 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 batch di query per adattare le prestazioni ai tempi di Windows per picchi eccezionali di carico di lavoro.

Sostituzione delle 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à hint di Query Store non richiede query di corrispondenza esatte 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 sostituisco gli hint a livello di istruzione hardcoded 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 Esempi di hint 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 regredite in un livello di compatibilità superiore usando il Report query regredite di Query Store, con lo strumento Ottimizzazione guidata query 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.

Considerazioni su hint di Query Store

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

Modifiche alla distribuzione dei dati

Le guide di piano, i piani forzati con Query Store e gli hint di Query Store sostituiscono il processo decisionale di Optimizer. 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 a una decisione migliore da parte di Optimizer rispetto all'hint. Questo scenario è la conseguenza più comune del comportamento forzato del piano.

Rivalutare regolarmente la strategia degli hint di Query Store

Rivalutare la strategia degli hint di Query Store esistenti nei casi seguenti:

Dopo la modifica della distribuzione dei dati di grandi dimensioni.
Quando varia l'obiettivo del livello di servizio (SLO) del database SQL di Azure o del Istanza gestita o della macchina virtuale.
Dove la correzione del piano è diventata a lungo termine. 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.

Prima di applicare gli hint di Query Store nell'ambiente di produzione, modificare con attenzione i test di carico per sistemi mission critical o sensibili.

La parametrizzazione forzata e l'hint RECOMPILE non sono supportati

L'applicazione dell'hint di query RECOMPILE 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 ha forzato la parametrizzazione impostata e l'hint RECOMPILE è parte della stringa di hint impostata in Query Store per una query, il motore di database ignorerà l'hint RECOMPILE e applicherà altri hint se sfruttati. A partire da luglio 2022, in database SQL di Azure dovrebbe anche comparire un avviso (codice errore 12461) per informare che l'hint RECOMPILE è stato ignorato.