Hint di Query Store

  • Articolo

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

Questo articolo illustra come applicare hint per la query usando Query Store. Gli hint di Query Store in Database SQL di Azure offrono un metodo semplice per modellare i piani di query senza cambiare il codice dell'applicazione.

Gli hint di Query Store sono disponibili in Database SQL di Azure e Istanza gestita di SQL di Azure. Gli hint di Query Store sono anche una funzionalità introdotta in SQL Server in SQL Server 2022 (16.x).

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.

Guardare questo video per una panoramica sugli hint di Query Store:

Panoramica

Idealmente, Query Optimizer seleziona un piano di esecuzione ottimale per una query.

Se non è selezionato un piano ottimale, è possibile che uno sviluppatore o un amministratore di database debba ottimizzare manualmente per condizioni specifiche. Gli hint per la query vengono specificati tramite la clausola OPTION e possono essere usati per influire sul comportamento di esecuzione delle query. Sebbene gli hint di query forniscano soluzioni localizzate a vari problemi correlati alle prestazioni, richiedono una riscrittura del testo della query originale. Gli amministratori di database e gli sviluppatori potrebbero non riuscire ad apportare modifiche direttamente al codice Transact-SQL per inserire un hint per la query. Transact-SQL potrebbe essere hardcoded in un'applicazione o generato automaticamente dall'applicazione. In precedenza uno sviluppatore doveva fare affidamento sulle guide di piano che possono risultare complesse da usare.

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

Quando usare gli hint di Query Store

Come suggerisce il nome, questa funzionalità è un’estenzione di e dipende da Query Store. Query Store consente di acquisire query, piani di esecuzione e statistiche di runtime associate. Query Store semplifica notevolmente l'esperienza complessiva dei clienti per l'ottimizzazione delle prestazioni. SQL Server 2016 (13.x) ha introdotto prima Query Store e ora è abilitato per impostazione predefinita in SQL Server 2022 (16.x), Istanza gestita di SQL di Azure e database SQL di Azure.

The workflow for Query Store Hints.

Esempi in cui gli hint di Query Store possono essere utili per i problemi di prestazioni a livello di query:

  • Ricompilare una query per ogni esecuzione.
  • Limitare le dimensioni della concessione di memoria per un'operazione di inserimento bulk.
  • Limitare il massimo grado di parallelismo durante l’aggiornamento delle statistiche.
  • Usare un hash join anziché un join a cicli annidati.
  • Usare il livello di compatibilità 110 per una query specifica mantenendo il resto nel database a livello di compatibilità 150.
  • Disabilitare l'ottimizzazione degli obiettivi di riga per una query SELECT TOP.

Per usare gli hint di Query Store:

  1. Identificare query_id Query Store dell'istruzione di query da modificare. È possibile farlo in vari modi:
    • Eseguire query sulle viste del catalogo di Query Store.
    • Usare report predefiniti di Query Store di SQL Server Management Studio.
    • Usare le informazioni dettagliate prestazioni delle query nel portale di Azure per il database SQL di Azure
  2. Eseguire quindi sys.sp_query_store_set_hints con query_id e la stringa di hint per la query che si desidera applicare alla query. Questa stringa può contenere uno o più hint per la query. Per informazioni complete, vedere sys.sp_query_store_set_hints.

Dopo la creazione, gli hint di Query Store vengono salvati in modo permanente e resistono a riavviati e failover. Gli hint di Query Store eseguono l'override degli hint a livello di istruzione hardcoded e degli hint della guida di piano esistenti.

Se un hint di query è in contrasto con l'ottimizzazione delle query, l'esecuzione di query non viene bloccata e l'hint non viene applicato. Nei casi in cui un hint provocherebbe un errore di query, l'hint viene ignorato e i dettagli dell'ultimo errore possono essere visualizzati in sys.query_store_query_hints.

Stored procedure di sistema degli hint di Query Store

Per creare o aggiornare gli hint, usare sys.sp_query_store_set_hints. Gli hint vengono specificati in un formato stringa valido N'OPTION (...)'.

  • Quando si crea un hint di Query Store, se non esiste alcun hint di Query Store per uno specifico query_id, viene creato un nuovo hint di Query Store.
  • Quando si crea o si aggiorna un hint di Query Store, se esiste già un hint di Query Store per uno specifico query_id, l'ultimo valore specificato sostituisce i valori specificati in precedenza per la query associata.
  • Se non esiste query_id, viene generato un errore.

Nota

Per un elenco completo degli hint supportati, vedere sys.sp_query_store_set_hints.

Per rimuovere gli hint associati a query_id, usare sys.sp_query_store_clear_hints.

Attributi XML del piano di esecuzione

Quando vengono applicati hint, il set di risultati seguente viene visualizzato nell'elemento StmtSimple del piano di esecuzione in formato XML:

Attributo Descrizione
QueryStoreStatementHintText Hint effettivi di Query Store applicati alla query
QueryStoreStatementHintId Identificatore univoco di un hint per la query
QueryStoreStatementHintSource Origine dell'hint di Query Store (es: "Utente")

Nota

Questi elementi XML sono disponibili tramite l'output dei comandi Transact-SQL SET STATISTICS XML e SET SHOWPLAN XML.

Hint e interoperabilità delle funzionalità di Query Store

  • Gli hint di Query Store eseguono l'override di altri hint a livello di istruzione hardcoded e guide di piano.
  • Le query vengono sempre eseguite. Gli hint di Query Store opposti vengono ignorati per non causare un errore.
  • Se gli hint di Query Store sono in contrasto, SQL Server non blocca l'esecuzione delle query e l'hint di Query Store non viene applicato.
  • Parametrizzazione semplice: gli hint di Query Store non sono supportati per le istruzioni idonee alla parametrizzazione semplice.
  • Parametrizzazione forzata: l'hint RECOMPILE non è compatibile con la parametrizzazione forzata impostata a livello di database. Se il database ha impostato la parametrizzazione forzata e l'hint RECOMPILE fa parte della stringa di hint impostata in Query Store per una query, SQL Server ignora l'hint RECOMPILE e applicherà eventuali altri hint, se applicati.
  • Gli hint di Query Store creati manualmente sono esclusi dalla pulizia. L'hint e la query non verranno rimossi da Query Store tramite la conservazione automatica dei criteri di acquisizione.
    • Le query possono essere rimosse manualmente dall’utente, che rimuoverebbero anche l'hint di Query Store associato.
    • Gli hint di Query Store generati automaticamente dal Feedback sulle stima di cardinalità sono soggetti alla pulizia tramite la conservazione automatica dei criteri di acquisizione.
    • Feedback sul grado di parallelismo e Feedback sulle concessioni di memoria modellano il comportamento della query senza usare gli hint di Query Store. Quando le query vengono rimosse attraverso la conservazione automatica dei criteri di acquisizione, vengono rimossi anche i dati relativi ai feedback sulle concessioni di memoria e sulle stime di cardinalità.
    • È possibile creare manualmente lo stesso hint di Query Store implementato dal feedback sulle stima di cardinalità e quindi la query con l'hint non sarà più soggetta alla rimozione attraverso la conservazione automatica dei criteri di acquisizione.

Hint e gruppi di disponibilità di Query Store

Per altre informazioni, vedere Query Store per repliche secondarie.

  • Prima di SQL Server 2022 (16.x), gli hint di Query Store possono essere applicati alla replica primaria di un gruppo di disponibilità.
  • A partire da SQL Server 2022 (16.x), quando è abilitato Query Store per le repliche secondarie, anche gli hint di Query Store sono compatibili con la replica per le repliche secondarie nei gruppi di disponibilità.
  • È possibile aggiungere un hint di Query Store a una replica o a un set di repliche specifico quando è abilitato Query Store per le repliche secondarie. In sys.sp_query_store_set_query_hints questo valore viene impostato dal parametro @query_hint_scope, introdotto in SQL Server 2022 (16.x).
  • Trovare i set di repliche disponibili eseguendo query su sys.query_store_replicas.
  • Trovare i piani forzati nelle repliche secondarie con sys.query_store_plan_forcing_locations.

Procedure consigliate per Hint di Query Store

  • Completare la manutenzione degli indici e delle statistiche prima di valutare le query per potenziali nuovi hint di Query Store.
  • Testare il database dell'applicazione sul livello di compatibilità più recente prima di usare gli hint di Query Store.
    • Ad esempio, l'ottimizzazione del piano sensibile ai parametri (PSP) è stata introdotta in SQL Server 2022 (16.x) (livello di compatibilità 160), che usa più piani attivi per query per gestire distribuzioni di dati non univoche. Se l'ambiente non può usare il livello di compatibilità più recente, gli hint di Query Store che usano l'hint RECOMPILE possono essere usati in qualsiasi livello di compatibilità di supporto.
  • Gli hint di Query Store sostituiscono il comportamento del piano di query di SQL Server. È consigliabile usare gli hint di Query Store solo quando è necessario risolvere i problemi relativi alle prestazioni.
  • È consigliabile rivalutare gli hint di Query Store, gli hint a livello di istruzione, le guide di piano e i piani forzati di Query Store ogni volta che cambiano le distribuzioni dei dati e durante le migrazioni di database. Le modifiche apportate alla distribuzione dei dati potrebbero generare piani di esecuzione non ottimali negli hint di Query Store.

Esempi

R. Demo di Hint di Query Store

La procedura dettagliata seguente degli hint di Query Store in database SQL di Azure usa un database importato tramite un file BACPAC (con estensione bacpac). Informazioni su come importare un nuovo database in un server database SQL di Azure, vedere Avvio rapido: Importare un file BACPAC in un database.

-- ************************************************************************ --
-- Query Store hints demo

-- Demo uses "PropertyMLS" database which can be imported from BACPAC here:
-- https://github.com/microsoft/sql-server-samples/tree/master/samples/features/query-store

-- Email QSHintsFeedback@microsoft.com for questions\feedback
-- ************************************************************************ --

/*
    Demo prep, connect to the PropertyMLS database
*/

ALTER DATABASE [PropertyMLS] SET QUERY_STORE CLEAR;
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE  (QUERY_CAPTURE_MODE = ALL);
GO

-- Should be READ_WRITE
SELECT actual_state_desc
FROM sys.database_query_store_options;
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints.
    Checking if any already exist (should be none).
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
     The PropertySearchByAgent stored procedure has a parameter
     used to filter AgentId.  Looking at the statistics for AgentId,
     you will see that there is a big skew for AgentId 101.
*/
SELECT	hist.range_high_key AS [AgentId],
        hist.equal_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'NCI_Property_AgentId';


-- Show actual query execution plan to see plan compiled.
-- Agent with many properties will have a scan with parallelism.
EXEC [dbo].[PropertySearchByAgent] 101;

-- Agents with few properties still re-use this plan (assuming no recent plan eviction).
EXEC [dbo].[PropertySearchByAgent] 4;


/*
    Now let's find the query_id associated with this query.
*/
SELECT query_sql_text, q.query_id
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%' and query_sql_text not like N'%query_store%';
GO

/*
     We can set the hint associated with the query_id returned in the previous result set, as below.
     Note, we can designate one or more query hints
*/
EXEC sp_query_store_set_hints @query_id=5, @value = N'OPTION(RECOMPILE)';
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see two different plans, one for AgentId 101 and one for AgentId 4.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
    We can remove the hint using sp_query_store_clear_query_hints
*/
EXEC sp_query_store_clear_hints @query_id = 10;
GO

/*
    That Query Store Hint is now removed
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see one plan again.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

B. Identificare una query in Query Store

Nell'esempio seguente vengono eseguite query sys.query_store_query_text e sys.query_store_query per restituire query_id per un frammento di testo di query eseguito.

In questa demo la query che si sta tentando di ottimizzare è nel database di esempio SalesLT:

SELECT * FROM SalesLT.Address as A 
INNER JOIN SalesLT.CustomerAddress as CA
on A.AddressID = CA.AddressID
WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC;

Query Store non riflette immediatamente i dati delle query nelle viste di sistema.

Identificare la query nelle viste del catalogo di sistema di Query Store:

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
INNER JOIN sys.query_store_query q ON 
    qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%PostalCode =%'  
  AND query_sql_text not like N'%query_store%';
GO

Negli esempi seguenti, l'esempio di query precedente nel database SalesLT è stato identificato come query_id 39.

Una volta identificato, applicare l'hint per imporre una dimensione massima della concessione di memoria in percentuale del limite di memoria configurato a query_id:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';

È anche possibile applicare hint di query con la sintassi seguente, ad esempio l'opzione per forzare lo strumento di stima della cardinalità legacy:

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

È possibile applicare più hint per la query con un elenco separato da virgole:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';

Esaminare l'hint di Query Store in posizione query_id 39:

SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc 
FROM sys.query_store_query_hints 
WHERE query_id = 39;

Rimuovere infine l'hint da query_id 39 usando sp_query_store_clear_hints.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Contenuto correlato