Procedure consigliate per la gestione di Query Store

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di Azure

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

Nota

In SQL Server 2022 (16.x), Query Store è ora abilitato per impostazione predefinita per tutti i database di SQL Server appena creati per monitorare meglio la cronologia delle prestazioni, risolvere i problemi relativi al piano di query e abilitare nuove funzionalità di Query Processor.

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).

Impostazione Descrizione Default Commento
MAX_STORAGE_SIZE_MB Specifica il limite per lo spazio dati che Query Store occupa all'interno del database del cliente 100 prima di SQL Server 2019 (15.x)
1000 a partire da SQL Server 2019 (15.x)		 Applicato per i nuovi database
INTERVAL_LENGTH_MINUTES Definisce la dimensione dell'intervallo di tempo durante il quale le statistiche di runtime raccolte per i piani di query vengono aggregate e rese persistenti. Tutti i piani di query attivi hanno al massimo una riga per un periodo di tempo definito con questa configurazione 60 Applicato per i nuovi database
STALE_QUERY_THRESHOLD_DAYS Criterio di pulizia basato sul tempo che controlla il periodo di memorizzazione delle statistiche di runtime persistenti e delle query inattive 30 Applicato per i nuovi database e i database con un'impostazione predefinita precedente (367)
SIZE_BASED_CLEANUP_MODE Specifica se la pulizia automatica dei dati viene eseguita quando la dimensione dati dell'archivio query si avvicina al limite AUTO Applicato per tutti i database
QUERY_CAPTURE_MODE Specifica se vengono monitorate tutte le query o solo un sottoinsieme di esse AUTO Applicato per tutti i database
DATA_FLUSH_INTERVAL_SECONDS Specifica il periodo massimo durante il quale le statistiche di runtime acquisite vengono mantenute in memoria prima di essere scaricate su disco 900 Applicato per i nuovi database

Importante

I valori predefiniti vengono applicati automaticamente nella fase finale dell'attivazione di Query Store in database SQL di Azure. Dopo l'abilitazione, il database SQL di Azure non modificherà i valori di configurazione impostati dai clienti, a meno che non abbiano un impatto negativo sul carico di lavoro primario o sulle operazioni affidabili di Query Store.

Nota

Non è possibile disabilitare Query Store in un singolo database SQL di Azure e in un pool elastico. L'esecuzione di ALTER DATABASE [database] SET QUERY_STORE = OFF restituirà l'avviso 'QUERY_STORE=OFF' is not supported in this version of SQL Server..

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:

Modalità di acquisizione dell'archivio query Scenario
Tutte le date Analizzare accuratamente il carico di lavoro in termini di forme di query, frequenza di esecuzione e altre statistiche.

Identificare le nuove query nel carico di lavoro.

Stabilire se vengono usate query ad hoc per identificare le opportunità di parametrizzazione automatica o da parte dell'utente.

Nota: questa è la modalità di acquisizione predefinita in SQL Server 2016 (13.x) e SQL Server 2017 (14.x).
Auto Concentrare l'attenzione su query rilevanti e da correggere. Un esempio sono le query eseguite regolarmente o che hanno un consumo di risorse elevato.

Nota: in SQL Server 2019 (15.x) e versioni successive si tratta della modalità di acquisizione predefinita.
Nessuno Il set di query da monitorare è stato già acquisito in fase di esecuzione e si vuole eliminare qualsiasi distrazione introdotta da altre query.

È adatta ad ambienti di testing e di benchmarking.

È adatta ai fornitori di software che forniscono l'archivio query configurato per il monitoraggio del carico di lavoro della relativa applicazione.

Deve essere usata con cautela perché può precludere la possibilità di rilevare e ottimizzare nuove query importanti. Evitare di usare questa modalità a meno che non sia richiesta da uno scenario specifico.
Personalizzazione SQL Server 2019 (15.x) ha introdotto una modalità di acquisizione personalizzata nel comando ALTER DATABASE ... SET QUERY_STORE. Anche se l'opzione Auto è predefinita e consigliata, se esiste ancora un problema relativo all'overhead di Query Store, gli amministratori del database possono usare criteri di acquisizione personalizzati per ottimizzare ulteriormente il comportamento di acquisizione di Query Store. Per altre informazioni e indicazioni, vedere successivamente in questo articolo Criteri di acquisizione personalizzati. Per altre informazioni su questa sintassi, vedere Opzioni ALTER DATABASE SET.

Nota

I cursori, le query all'interno delle stored procedure e le query compilate in modo nativo vengono sempre acquisiti quando la modalità di acquisizione di Query Store è impostata su All, Auto o Custom. Per acquisire le query compilate in modo nativo, abilitare la raccolta delle statistiche per query usando sys.sp_xtp_control_query_exec_stats.

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:

Procedure consigliate Impostazione
Limitare i dati cronologici conservati. Configurare criteri basati sul tempo per attivare la pulizia automatica.
Escludere le query non rilevanti. Configurare la modalità di acquisizione di Query Store su Auto.
Eliminare le query meno rilevanti quando vengono raggiunte le dimensioni massime. Attivare criteri di pulizia basati sulle dimensioni.

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. Questa è l’opzione predefinita a partire da SQL Server 2019 (15.x)
  • 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.

Usare la versione più recente di SQL Server Management Studio (SSMS)

Per visualizzare le impostazioni correnti in Management Studio:

  1. In Esplora oggetti di SQL Server Management Studio fare clic con il pulsante destro del mouse su database.
  2. Selezionare Proprietà.
  3. Selezionare Query Store. Nella pagina Query Store verificare che la Modalità operativa (richiesta) sia Lettura scrittura.
  4. Modificare la Modalità di acquisizione di Query Store in Personalizzare.
  5. Si noti che i quattro campi dei criteri di acquisizione di Query Store sono ora abilitati e configurabili.

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.

Nota

L'uso del flag di traccia 7745 impedisce la scrittura su disco dei dati di Query Store nel caso di un comando di failover o arresto. Per altre informazioni, vedere Usare Query Store nei server mission-critical.

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.

Usare la versione più recente di SQL Server Management Studio (SSMS)

Per visualizzare le impostazioni correnti in Management Studio:

  1. In Esplora oggetti di SQL Server Management Studio fare clic con il pulsante destro del mouse su database.
  2. Selezionare Proprietà.
  3. Selezionare Query Store.

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.

Importante

I cursori, le query all'interno delle stored procedure e le query compilate in modo nativo vengono sempre acquisiti quando la modalità di acquisizione di Query Store è impostata su All, Auto o Custom. Per acquisire le query compilate in modo nativo, abilitare la raccolta delle statistiche per query usando sys.sp_xtp_control_query_exec_stats.

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.

Nota

Come illustrato in questa sezione, la personalizzazione delle opzioni di configurazione di Query Store per Azure Synapse Analytics non è supportata.

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