Procedure consigliate per la gestione di Query Store

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

Questo articolo illustra la gestione di QueryStore di SQL Server e le relative funzionalità.

• Per altre informazioni su configurazione e gestione con Query Store, vedere Monitorare le prestazioni con Query Store.

Configurazioni predefinite di Query Store nel database SQL di Azure

Questa sezione descrive le impostazioni di configurazione predefinite ottimali in database SQL di Azure progettate per garantire un funzionamento affidabile di Query Store e delle funzionalità dipendenti. La configurazione predefinita è ottimizzata per la raccolta di dati continua, ossia per un tempo minimo di OFF/READ_ONLY. Per altre informazioni su tutte le opzioni di Query Store disponibili, vedere Opzioni ALTER DATABASE SET (Transact-SQL).

Se si desidera mantenere le impostazioni personalizzate, usare ALTER DATABASE con le opzioni dell'archivio query per riportare la configurazione allo stato precedente. Vedere Procedure consigliate per Query Store per informazioni su come scegliere i parametri di configurazione ottimali.

Impostare la modalità di acquisizione di Query Store ottimale

Mantenere i dati più rilevanti nell'archivio query. La tabella seguente descrive gli scenari tipici per ogni modalità di acquisizione di Query Store:

Mantenere i dati più rilevanti in Query Store

Configurare Query Store in modo che contenga solo i dati rilevanti per garantire l'esecuzione ininterrotta e la risoluzione ottimale dei problemi con un impatto minimo sul normale carico di lavoro.

La tabella seguente riporta le procedure consigliate:

Criteri di acquisizione personalizzati

Una volta abilitata la modalità di acquisizione CUSTOM di Query Store, le configurazioni aggiuntive di Query Store sono disponibili in una nuova impostazione di criteri di acquisizione di Query Store per ottimizzare la raccolta dati in un server specifico.

Le nuove impostazioni personalizzate definiscono che cosa accade entro la soglia di tempo per i criteri di acquisizione interni. Si tratta di un limite di tempo durante il quale vengono valutate le condizioni configurabili e, se si verifica una di tali condizioni, la query è idonea per l'acquisizione da parte di Query Store.

Modalità di acquisizione Query Store: specifica i criteri di acquisizione delle query per Query Store.

• All: vengono acquisite tutte le query. Questa opzione è l’impostazione predefinita in SQL Server 2016 (13.x) e SQL Server 2017 (14.x).
• Auto: le query poco frequenti e le query con durata di compilazione ed esecuzione non significativa vengono ignorate. Le soglie per la durata del runtime, della compilazione e del conteggio esecuzioni vengono determinate internamente. A partire da SQL Server 2019 (15.x) questa è l’opzione predefinita.
• None: Query Store smette di acquisire nuove query.
• Custom: consente un maggiore controllo e la capacità di ottimizzare i criteri di raccolta dati. Le nuove impostazioni personalizzate definiscono che cosa accade entro la soglia di tempo per i criteri di acquisizione interni. Si tratta di un limite di tempo durante il quale vengono valutate le condizioni configurabili e, se si verifica una di tali condizioni, la query è idonea per l'acquisizione da parte di Query Store.

L'ottimizzazione di un criterio di acquisizione personalizzato appropriato per l'ambiente deve essere considerato quando:

• Il database è molto grande.
• Il database dispone di un numero elevato di query ad hoc univoche.
• Il database presenta limitazioni di dimensioni o di crescita specifiche.

Esempi di criteri di acquisizione personalizzati

L'esempio seguente imposta QUERY_CAPTURE_MODE su AUTO e una modalità di acquisizione personalizzata. I seguenti impostano i criteri di acquisizione personalizzati sul valore predefinito in SQL Server 2022 (16.x). Valutare di modificare questi valori per ridurre il numero di query acquisite e quindi ridurre il footprint su disco di Query Store. È consigliabile modificare gradualmente questi valori con piccoli incrementi.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

La query di esempio seguente modifica un Query Store esistente per usare un criterio di acquisizione personalizzato che sovrascrive le impostazioni predefinite per EXECUTION_COUNT e TOTAL_COMPILE_CPU_TIME_MS.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 100,
        TOTAL_COMPILE_CPU_TIME_MS = 10000
      )
    );

Dimensioni massime di Query Store

A partire da SQL Server 2019 (15,x), il valore massimo predefinito di Query Store è di 1000 MB. Nelle versioni precedenti il valore predefinito era di 100 MB. L'aumento del limite massimo di dimensioni di Query Store è appropriato in un database occupato con molti piani di query univoci. La regolazione dei criteri di acquisizione (vedere la sezione precedente) è una considerazione più importante per limitare le dimensioni su disco di Query Store e impedire a Query Store di accedere alla modalità READ_ONLY. Mentre Query Store raccoglie query, piani di esecuzione e statistiche, le sue dimensioni nel database aumentano fino a quando non viene raggiunto questo limite. A quel punto, Query Store passa automaticamente alla modalità operativa READ_ONLY e smette di raccogliere nuovi dati. Questo si riflette negativamente sull'accuratezza dell'analisi delle prestazioni.

• In SQL Server e Istanza gestita di SQL di Azure, il limite MAX_STORAGE_SIZE_MB non viene applicato rigorosamente.
• In database SQL di Azure il valore massimo MAX_STORAGE_SIZE_MB consentito è di 10.240 MB.

Le dimensioni di archiviazione vengono controllate solo quando Query Store scrive i dati su disco. Questo intervallo viene impostato dall'opzione DATA_FLUSH_INTERVAL_SECONDS o dall'opzione della finestra di dialogo di Query Store in Management Studio Intervallo di scaricamento dati.

• Il valore predefinito dell'intervallo è 900 secondi (o 15 minuti).
• Se Query Store ha violato il limite MAX_STORAGE_SIZE_MB tra i controlli delle dimensioni di archiviazione, passa alla modalità di sola lettura.
• Se è abilitata la SIZE_BASED_CLEANUP_MODE, viene attivato anche il meccanismo di pulizia per applicare il limite MAX_STORAGE_SIZE_MB.
  • Dopo la cancellazione di spazio sufficiente, la modalità di Query Store tornerà automaticamente in READ_WRITE.

Per altre informazioni, vedere le OPZIONI ALTER DATABASE SET MAX_STORAGE_SIZE_MB.

Intervallo di scaricamento dati (minuti)

L'intervallo di scaricamento dati definisce la frequenza prima che le statistiche di runtime raccolte vengano mantenute su disco. In SQL Server Management Studio il valore è espresso in minuti, ma in Transact-SQL è espresso in secondi. L'impostazione predefinita è 15 minuti (900 secondi).

• L'aumento dell'intervallo di scaricamento dei dati può ridurre l'impatto complessivo di I/O sull'archiviazione di Query Store. Questo causa un più complesso carico di lavoro di I/O di archiviazione, con un minore impatto sull'utilizzo del disco. Valutare la possibilità di usare un valore più elevato se il carico di lavoro non genera un numero elevato di query e piani diversi o se è possibile attendere più tempo per salvare i dati in modo permanente prima dell'arresto di un database.
• La riduzione dell'intervallo di scaricamento dei dati riduce la quantità di dati di Query Store che andrebbero persi in caso di arresto, perdita di alimentazione o failover. Può anche uniformare l'impatto delle operazioni di I/O di archiviazione da Query Store scrivendo su disco con più frequenza ma con meno dati.

Modificare le impostazioni predefinite di Query Store

Configurare l'archivio query in base al carico di lavoro e ai requisiti di risoluzione dei problemi di prestazioni. I parametri predefiniti sono sufficienti per iniziare, ma è opportuno monitorare il comportamento di Query Store nel tempo e adattare la configurazione di conseguenza.

Visualizzare le impostazioni correnti di Query Store

Visualizzare le impostazioni correnti di Query Store in SQL Server Management Studio (SSMS) o T-SQL.

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
 max_storage_size_mb, readonly_reason
FROM sys.database_query_store_options;

Lo script seguente imposta un nuovo valore per Dimensioni massime (MB):

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

Usare SQL Server Management Studio o Transact-SQL per impostare un valore diverso per Intervallo di scaricamento dati:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

Intervallo di raccolta statistiche: definisce il livello di granularità delle statistiche di runtime raccolte espresso in minuti. Il valore predefinito è 60 minuti. È possibile usare un valore inferiore se è necessaria una maggiore granularità o maggiore rapidità nel rilevare e limitare i problemi. Tenere presente che il valore influisce direttamente sulle dimensioni dei dati di Query Store. Usare SQL Server Management Studio o Transact-SQL per impostare un valore diverso per Intervallo raccolta statistiche:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

Soglia per query non aggiornate (Giorni): criteri di pulizia basati sul tempo che controllano il periodo di conservazione di statistiche di runtime persistenti e query inattive espresse in giorni. Per impostazione predefinita, Query Store è configurato in modo da conservare i dati per 30 giorni, che per alcuni scenari potrebbe essere un periodo eccessivamente lungo.

Evitare di conservare i dati cronologici che non si intende usare. Questo accorgimento riduce il ricorso allo stato di sola lettura. Le dimensioni dei dati di Query Store e il tempo necessario per rilevare e limitare il problema saranno più prevedibili. Usare Management Studio oppure lo script seguente per configurare i criteri di pulizia basati sul tempo:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));

Modalità di pulizia basata sulle dimensioni: specifica se viene eseguita la pulizia automatica dei dati quando le dimensioni dei dati di Query Store si avvicinano al limite. Attivare la pulizia basata sulle dimensioni per assicurarsi che Query Store venga sempre eseguito in modalità lettura/scrittura e possa raccoglie i dati più recenti. In presenza di carichi di lavoro eccessivi, non esiste alcuna garanzia che la pulizia di Query Store manterrà costantemente le dimensioni dei dati al di sotto del limite. È possibile che la pulizia automatica dei dati superi tale limite ed attivi (temporaneamente) la modalità di sola lettura.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

Modalità di acquisizione Query Store: specifica i criteri di acquisizione delle query per Query Store.

• All: vengono acquisite tutte le query. Questa opzione è l’impostazione predefinita in SQL Server 2016 (13.x) e SQL Server 2017 (14.x).
• Auto: le query poco frequenti e le query con durata di compilazione ed esecuzione non significativa vengono ignorate. Le soglie per la durata del runtime, della compilazione e del conteggio esecuzioni vengono determinate internamente. A partire da SQL Server 2019 (15.x) questa è l’opzione predefinita.
• None: Query Store smette di acquisire nuove query.
• Custom: consente un maggiore controllo e la capacità di ottimizzare i criteri di raccolta dati. Le nuove impostazioni personalizzate definiscono che cosa accade entro la soglia di tempo per i criteri di acquisizione interni. Si tratta di un limite di tempo durante il quale vengono valutate le condizioni configurabili e, se si verifica una di tali condizioni, la query è idonea per l'acquisizione da parte di Query Store.

Lo script seguente imposta QUERY_CAPTURE_MODE su AUTO:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

Esempi

L'esempio seguente imposta QUERY_CAPTURE_MODE su AUTO e le altre opzioni consigliate in SQL Server 2016 (13.x):

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

L'esempio seguente imposta QUERY_CAPTURE_MODE su AUTO e le altre opzioni consigliate in SQL Server 2017 (14.x) in modo da includere le statistiche di attesa:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

Nell'esempio seguente vengono impostati i criteri di acquisizione CUSTOM sulle impostazioni predefinite di SQL Server 2019 (15.x), anziché la nuova modalità di acquisizione AUTO predefinita. Per altre informazioni su opzioni e impostazioni predefinite dei criteri di acquisizione personalizzati, vedere <query_capture_policy_option_list>.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Manutenzione di Query Store

Questa sezione fornisce alcune linee guida per la gestione della funzionalità Archivio query.

Stato di Query Store

I dati della funzionalità Query Store vengono archiviati nel database utente ed è quindi previsto un limite per le dimensioni, che viene configurato con MAX_STORAGE_SIZE_MB. Se i dati in Archivio query raggiungono tale limite, lo stato cambia automaticamente da lettura/scrittura a sola lettura e la raccolta di nuovi dati viene interrotta.

Eseguire una query su sys.database_query_store_options per determinare se la funzionalità Query Store è attualmente attiva e se è in corso la raccolta delle statistiche di runtime.

SELECT actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Lo stato di Query Store è determinato dalla colonna actual_state. Se è diverso dallo stato previsto, la colonna readonly_reason contiene maggiori informazioni. Quando le dimensioni di Query Store superano la quota, la funzionalità passa alla modalità read_only e fornisce un motivo. Per informazioni sui motivi, vedere sys.database_query_store_options.

Ottenere le opzioni di Archivio query

Per informazioni dettagliate sullo stato di Archivio query, eseguire l'istruzione seguente in un database utente.

SELECT * FROM sys.database_query_store_options;

Impostare l'intervallo di Query Store

È possibile ignorare l'intervallo per l'aggregazione delle statistiche di runtime delle query (impostazione predefinita: 60 minuti). Il nuovo valore per l'intervallo viene esposto tramite la vista sys.database_query_store_options.

ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

I valori arbitrari non sono consentiti per INTERVAL_LENGTH_MINUTES. Usare uno dei seguenti intervalli: 1, 5, 10, 15, 30, 60 o 1440 minuti.

Utilizzo dello spazio di Archivio query

Per controllare le dimensioni e i limiti correnti di Archivio query, eseguire l'istruzione seguente nel database utente.

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Se lo spazio di archiviazione di Archivio query è esaurito, usare l'istruzione seguente per estenderlo.

ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

Impostare le opzioni di Query Store

È possibile impostare più opzioni di Archivio query contemporaneamente con un'unica istruzione ALTER DATABASE.

ALTER DATABASE <database name>
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

Per un elenco completo delle opzioni di configurazione, vedere Opzioni ALTER DATABASE SET (Transact-SQL).

Pulire lo spazio

Le tabelle interne di Archivio query vengono create nel filegroup PRIMARY durante la creazione del database e tale configurazione non è modificabile in un secondo momento. Se lo spazio si esaurisce, è possibile cancellare i dati di Query Store meno recenti usando l'istruzione seguente.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

In alternativa, è possibile cancellare solo dati di query ad hoc perché sono meno rilevanti per le ottimizzazioni query e l'analisi del piano, ma occupano molto spazio.

La cancellazione di dati in Query Store non è disponibile per Azure Synapse Analytics. I dati degli ultimi sette giorni vengono conservati automaticamente.

Eliminare query ad hoc

Questa operazione rimuove definitivamente le query ad hoc e interne di Query Store, in modo che Query Store non esaurisca lo spazio e non rimuova le query di cui è necessario tenere traccia.

SET NOCOUNT ON
-- This purges adhoc and internal queries from
-- the Query Store in the current database
-- so that the Query Store does not run out of space
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
    SELECT q.query_id
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
    WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
       OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
    GROUP BY q.query_id
    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
    ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

Per la cancellazione dei dati non più necessari, è possibile definire procedure personalizzate con logiche diverse.

Per rimuovere i dati non necessari, nell'esempio precedente viene usata la stored procedure estesa sp_query_store_remove_query. È anche possibile:

• Usare sp_query_store_reset_exec_stats per eliminare le statistiche di runtime per un determinato piano.
• Usare sp_query_store_remove_plan per rimuovere un piano singolo.

Contenuto correlato

• Opzioni di ALTER DATABASE SET (Transact-SQL)
• Viste del catalogo di Query Store (Transact-SQL)
• Stored procedure di Query Store (Transact-SQL)
• Uso di Query Store con OLTP in-memoria
• Guida sull'architettura di elaborazione delle query
• Hint di Query Store
• Monitorare le prestazioni tramite Query Store
• Ottimizzare le prestazioni con Query Store
• Archiviazione e analisi delle query cronologiche in Azure Synapse Analytics