Condividi tramite

Suggerimenti di Query Store

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

Questo articolo illustra come applicare hint di query usando il 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.

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 .

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

Overview

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

Se non è selezionato un piano ottimale, uno sviluppatore o un amministratore di database potrebbe voler 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 essere sempre in grado di apportare modifiche direttamente al codice Transact-SQL per aggiungere un hint per la query. Transact-SQL può essere incorporato 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.

Gli hint di Query Store risolvano questo problema consentendo di inserire un hint per la query in una query senza modificare direttamente il testo Transact-SQL query. 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 correlate. 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, database SQL di Azure e database SQL in Microsoft Fabric.

Flusso di lavoro per gli hint di Query Store.

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.
  • Impostare un limite alla dimensione 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.
  • Disabilita l'ottimizzazione del target di riga per una query SELECT TOP.

Per usare gli hint di Query Store:

  1. Identifica il Query Store query_id dell'istruzione di query che desideri modificare. È possibile eseguire questa operazione in diversi modi:

    • Esecuzione di query sulle viste del catalogo di Query Store (Transact-SQL).
    • Usare report predefiniti di Query Store di SQL Server Management Studio.
    • Usare l'Analisi delle prestazioni delle query nel portale di Azure per Azure SQL Database

  2. Esegui sys.sp_query_store_set_hints con query_id e la stringa di hint che desideri applicare alla query. Questa stringa può contenere uno o più suggerimenti per la query. Per informazioni complete, vedere sys.sp_query_store_set_hints.

Dopo la creazione, gli hint di Query Store vengono conservati in modo permanente e resistono a riavvii e failover. Gli hint di Query Store prevalgono sugli hint espliciti a livello di istruzione e sugli hint esistenti della guida di piano.

Se un hint di query contraddice ciò che è possibile per 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 una query, l'hint viene ignorato e i dettagli dell'errore più recenti possono essere visualizzati in sys.query_store_query_hints.

Prima di usare gli hint di Query Store

Prima di iniziare a usare gli hint di Query Store, tenere presente quanto segue.

  • Completare la manutenzione delle statistiche e la manutenzione degli indici (se necessario) prima di valutare le query per i nuovi hint di Query Store. La manutenzione delle statistiche e, in misura minore, la manutenzione degli indici potrebbero risolvere il problema che altrimenti richiederebbe un hint per la query.
  • Prima di usare gli hint di Query Store, testare il database dell'applicazione sul livello di compatibilità più recente per verificare se questo risolve il problema che richiede un hint per la query.
    • Ad esempio, il Parameter Sensitive Plan (PSP) optimisation, ovvero l'ottimizzazione del piano sensibile ai parametri, è stato introdotto in SQL Server 2022 (16.x) con il livello di compatibilità 160. 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 su qualsiasi livello di compatibilità di supporto.
  • Gli hint di Query Store sostituiscono il comportamento predefinito del piano di query del motore di database. È consigliabile usare gli hint di Query Store solo quando è necessario risolvere i problemi relativi alle prestazioni.
  • È consigliabile rivalutare i suggerimenti di Query Store, i suggerimenti a livello di istruzione, le guide di piano e i piani forzati di Query Store ogni volta che il volume e la distribuzione dei dati cambiano, e durante le migrazioni di database. Le modifiche al volume di dati e alla loro distribuzione potrebbero causare nei suggerimenti del Query Store la generazione di piani di esecuzione subottimali.

Procedure di sistema per gli 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.

Per un elenco completo degli hint supportati come hint per Query Store, vedere sys.sp_query_store_set_hints.

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

Tip

Potrebbe essere necessario impostare o cancellare hint per tutti i query_id valori corrispondenti a un hash di query.

dbo.sp_query_store_modify_hints_by_query_hash è una stored procedure di esempio che chiama la sys.sp_query_store_set_hints stored procedure di sistema o sys.sp_query_store_clear_hints in un ciclo per eseguire questa operazione.

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:

Attribute Description
QueryStoreStatementHintText Suggerimenti effettivi di Query Store applicati alla query
QueryStoreStatementHintId Identificatore univoco di un suggerimento per una query
QueryStoreStatementHintSource Origine dell'hint di Query Store (ad esempio, User)

Note

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

Suggerimenti e interoperabilità delle funzionalità di Query Store

  • Gli hint di Query Store sostituiscono altri hint hard-coded per istruzioni e guide di piano.
  • Ad eccezione dell'hint ABORT_QUERY_EXECUTION, le query con gli hint del Query Store 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, il motore di database non blocca l'esecuzione delle query, e l'hint di Query Store non viene applicato.
  • Gli hint di Query Store non sono supportati per le istruzioni che sono idonee alla parametrizzazione semplice.
  • L'hint RECOMPILE non è compatibile con la parametrizzazione forzata impostata a livello di database. Se un database ha impostato la parametrizzazione forzata e l'hint RECOMPILE fa parte degli hint di Query Store per una query, il motore di database ignora l'hint RECOMPILE e applica eventuali altri hint, se specificati.
    • Il motore di database genera un avviso (codice di errore 12461) che informa che l'hint RECOMPILE è stato ignorato.
    • Per altre informazioni sulle considerazioni sui casi d'uso della parametrizzazione forzata, vedere Linee guida per l'uso della parametrizzazione forzata.
  • Gli hint di Query Store creati manualmente sono esenti dalla pulizia di Query Store. L'hint e la query non vengono eliminati dai criteri di acquisizione della conservazione automatica.
    • Le query possono essere rimosse manualmente dagli utenti. In questo modo viene rimosso anche l'hint di Query Store associato.
    • Gli suggerimenti di Query Store generati automaticamente dal Feedback sulla stima di cardinalità sono soggetti a pulizia attraverso la gestione automatica della politica di acquisizione.
    • Feedback sui gradi di parallelismo (DOP) e Feedback sulle concessioni di memoria per le query influenzano il comportamento della query senza usare gli hint di Query Store. Quando le query vengono eliminate dai criteri di acquisizione automatica della conservazione, vengono eliminati anche i dati relativi al feedback di DOP e al feedback delle concessioni di memoria.
    • Se si crea lo stesso hint di Query Store implementato manualmente dal feedback ce, la query con l'hint non è più soggetta alla pulizia da parte dei criteri di acquisizione automatica della conservazione.

Suggerimenti di Query Store e repliche secondarie

Gli hint di Query Store non hanno alcun effetto sulle repliche secondarie, a meno che Query Store per le repliche secondarie non sia abilitato. Per ulteriori informazioni, vedere Query Store per secondari leggibili.

  • In SQL Server 2022 (16.x) e versioni precedenti, gli hint di Query Store possono essere applicati solo alla replica primaria.
  • In SQL Server 2025 (17.x) e versioni successive, quando Query Store per le repliche secondarie è abilitato, gli hint di Query Store possono essere applicati alle repliche secondarie nei gruppi di disponibilità. Per il supporto completo della piattaforma, vedere Query Store per repliche secondarie leggibili.

Dove Query Store è supportato nelle repliche secondarie:

Examples

A. Dimostrazione di suggerimenti del Query Store

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

-- ************************************************************************ --
-- 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 suggerimenti di query con la sintassi seguente, ad esempio l'opzione per forzare lo strumento legacy di stima della cardinalità:

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

È possibile applicare più suggerimenti per la query utilizzando 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 il suggerimento di Query Store per il 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;

Contenuti correlati

  • Last updated on