Procedure consigliate per monitorare i carichi di lavoro con Query Store

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

Questo articolo descrive le procedure consigliate per l'uso di SQL Server Query Store con un carico di lavoro.

• 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.
• Per informazioni sul funzionamento dell'archivio query nel database SQL di Azure, vedere Uso dell'archivio query nel database SQL di Azure.
• In Azure Synapse Analytics Query Store non è abilitato per impostazione predefinita per i pool SQL dedicati, ma può essere abilitato. Altre opzioni di configurazione per Query Store non sono supportate. Per altre informazioni, vedere Archiviazione e analisi delle query cronologiche in Azure Synapse Analytics.

Usare la versione più recente di SQL Server Management Studio

SQL Server Management Studio ha un set di interfacce utente progettate per la configurazione di Query Store e l'utilizzo dei dati raccolti relativi al carico di lavoro. Scaricare la versione più recente di SQL Server Management Studio.

Per una rapida descrizione di come usare Query Store in scenari di risoluzione dei problemi, vedere Query Store Azure blogs.

Usare Informazioni dettagliate prestazioni query nel database SQL di Azure

Se si esegue Query Store nel database SQL di Azure, è possibile usare Informazioni dettagliate prestazioni query per analizzare il consumo delle risorse nel tempo. Anche se è possibile usare Management Studio e Azure Data Studio per ottenere il consumo dettagliato delle risorse per tutte le query, ad esempio CPU, memoria e I/O, le informazioni dettagliate sulle prestazioni delle query offrono un metodo rapido ed efficace per determinare l'impatto delle query sul consumo di DTU complessivo per il database. Per altre informazioni, vedere l'articolo relativo a Informazioni dettagliate prestazioni query del database SQL di Azure.

Usare Query Store con database di pool elastici

È possibile usare Query Store in tutti i database, anche in pool elastici e molto compressi di database SQL di Azure. Tutti i precedenti problemi correlati all'utilizzo eccessivo delle risorse che possono essersi verificati quando Query Store era abilitato per un numero elevato di database nei pool elastici sono stati risolti.

Iniziare a risolvere i problemi di prestazioni relativi alle query

Il flusso di lavoro di risoluzione dei problemi relativi a Query Store è semplice, come illustra il diagramma seguente:

Abilitare Query Store usando Management Studio, come descritto nella sezione precedente, o eseguire l'istruzione Transact-SQL seguente:

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;

La raccolta di un set di dati che rappresenti in modo accurato il carico di lavoro da parte di Query Store può richiedere del tempo. In genere, un giorno è sufficiente anche per carichi di lavoro molto complessi. Tuttavia, dopo aver abilitato la funzionalità è possibile iniziare a esplorare i dati e identificare le query che richiedono attenzione immediata. Andare alla sottocartella di Query Store nel nodo database in Esplora oggetti di Management Studio per aprire le viste di risoluzione dei problemi per scenari specifici.

Le viste Query Store in Management Studio possono essere usate con un set di metriche di esecuzione, espresse come una delle funzioni statistiche seguenti:

L'immagine seguente mostra come trovare le viste dell'archivio query:

La tabella seguente illustra quando usare ognuna delle viste dell'archivio query:

Quando si identifica una query con prestazioni non ottimali, l'azione correttiva dipende dalla natura del problema.

• Se la query è stata eseguita con più piani e l'ultimo piano è notevolmente peggiore rispetto al piano precedente, è possibile ricorrere al meccanismo di uso forzato del piano. SQL Server prova a forzare il piano in Query Optimizer. Se l'uso forzato del piano ha esito negativo, viene generato un XEvent e a query optimizer viene richiesto di ottimizzare in modo normale.

• Si può concludere che la query sia priva di un indice per l'esecuzione ottimale. Queste informazioni vengono rese disponibili all'interno del piano di esecuzione query. Creare l'indice mancante e verificare le prestazioni della query usando Query Store.

Se si esegue il carico di lavoro in SQL Database, iscriversi a Index Advisor per database SQL per ricevere automaticamente indicazioni relative agli indici.

• In alcuni casi si potrebbe applicare la ricompilazione delle statistiche, se c'è una notevole differenza tra il numero stimato di righe nel piano di esecuzione e quello effettivo.
• Riscrivere le query problematiche, ad esempio per sfruttare i vantaggi della parametrizzazione delle query o per implementare la logica ottimale.

Verificare che Query Store raccolga i dati delle query in modo continuativo

Query Store può cambiare automaticamente la modalità operativa. Monitorare regolarmente lo stato di Query Store per assicurarsi che sia in funzione e per prevenire errori dovuti a cause evitabili. Eseguire questa query per determinare la modalità operativa e visualizzare i parametri più importanti:

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

La differenza tra actual_state_desc e desired_state_desc indica che si è verificata una modifica automatica della modalità operativa. La modifica più comune è il passaggio automatico di Query Store alla modalità di sola lettura. Eccezionalmente, Query Store può passare a uno stato di errore a causa di errori interni.

Quando lo stato effettivo è di sola lettura, usare la colonna readonly_reason per determinare la causa radice. In genere, Query Store passa alla modalità di sola lettura quando viene superato il limite delle dimensioni. In questo caso, l’impostazione di readonly_reason è su 65536. Per altre situazioni, vedere sys.database_query_store_options (Transact-SQL).

Per riportare l'archivio query in modalità lettura/scrittura e attivare la raccolta dei dati, prendere in considerazione i passaggi seguenti:

• Aumentare le dimensioni massime dello spazio di archiviazione usando l'opzione MAX_STORAGE_SIZE_MB di ALTER DATABASE.

• Eseguire la pulizia dei dati dell'archivio query usando l'istruzione seguente:

  ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;
  

È possibile applicare uno o entrambi questi passaggi eseguendo questa istruzione, che ripristina in modo esplicito la modalità lettura/scrittura:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Seguire questa procedura:

• È possibile prevenire le modifiche automatiche della modalità operativa applicando le procedure consigliate. Assicurarsi che le dimensioni di Query Store siano sempre inferiori al valore massimo consentito per ridurre drasticamente il rischio di passaggio alla modalità di sola lettura. Attivare criteri basati sulle dimensioni, come descritto nella sezione Configurare Query Store, in modo che Query Store esegua automaticamente la pulizia dei dati quando le dimensioni si avvicinano al limite.
• Per assicurarsi che vengano conservati i dati più recenti, configurare criteri basati sul tempo per rimuovere regolarmente le informazioni non aggiornate.
• Infine, è consigliabile impostare la modalità di acquisizione di Query Store su Auto per escludere le query generalmente meno rilevanti per il carico di lavoro.

Stato di errore

Per recuperare Query Store, provare a impostare in modo esplicito la modalità lettura/scrittura e ricontrollare lo stato effettivo.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Se il problema persiste, il danneggiamento dei dati di Query Store è persistente sul disco.

A partire da SQL Server 2017 (14.x), è possibile recuperare Query Store eseguendo la stored procedure sys.sp_query_store_consistency_check nel database interessato. Prima di provare a eseguire l'operazione di ripristino, è necessario disabilitare Query Store. Di seguito è riportato un esempio di query da usare o modificare per eseguire il controllo della coerenza e il ripristino di QDS:

IF EXISTS (SELECT * FROM sys.database_query_store_options WHERE actual_state=3) 
BEGIN
  BEGIN TRY
    ALTER DATABASE [QDS] SET QUERY_STORE = OFF
    Exec [QDS].dbo.sp_query_store_consistency_check
    ALTER DATABASE [QDS] SET QUERY_STORE = ON
    ALTER DATABASE [QDS] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
  END TRY
 
  BEGIN CATCH 
    SELECT  
      ERROR_NUMBER() AS ErrorNumber  
      ,ERROR_SEVERITY() AS ErrorSeverity  
      ,ERROR_STATE() AS ErrorState  
      ,ERROR_PROCEDURE() AS ErrorProcedure  
      ,ERROR_LINE() AS ErrorLine  
      ,ERROR_MESSAGE() AS ErrorMessage; 
  END CATCH;   
END

Per SQL Server 2016 (13.x), è necessario cancellare i dati da Query Store come illustrato.

Se il ripristino non riesce, è possibile provare a cancellare Query Store prima di impostare la modalità lettura/scrittura.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE CLEAR;
GO

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Evitare l'uso di query senza parametri

L'uso di query senza parametri quando non è strettamente necessario non è una procedura consigliata. Un esempio è in caso di analisi ad hoc. Non è possibile usare nuovamente i piani memorizzati nella cache e questo impone a Query Optimizer di compilare query per ogni testo della query univoco. Per altre informazioni, vedere Linee guida per l'utilizzo della parametrizzazione forzata.

Inoltre, Query Store può superare rapidamente il limite di dimensioni a causa del numero potenzialmente elevato di testi delle query diversi e del conseguente numero elevato di piani di esecuzione diversi con forma simile. Questo influisce negativamente sulle prestazioni del carico di lavoro e Query Store potrebbe passare alla modalità di sola lettura o eliminare dati continuamente per tentare di gestire le query in ingresso.

Valutare le opzioni seguenti:

• Parametrizzare le query, se applicabile. Ad esempio, eseguire il wrapping delle query all'interno di una stored procedure o sp_executesql. Per altre informazioni, vedere Parametri e riutilizzo del piano di esecuzione.
• Usare l'opzione Ottimizza per carichi di lavoro ad hoc se il carico di lavoro contiene molti batch ad hoc monouso con piani di query diversi.
  • Confrontare il numero di valori query_hash distinti con il numero totale di voci in sys.query_store_query. Se il rapporto è vicino a 1, il carico di lavoro ad hoc genera query diverse.
• Applicare la parametrizzazione forzata per il database o per un subset di query, se il numero di piani di query diversi non è elevato.
  • Usare una guida di piano per forzare la parametrizzazione solo per la query selezionata.
  • Configurare la parametrizzazione forzata usando il comando dell'opzione di database Parameterization, se nel carico di lavoro è presente un numero ridotto di piani di query diversi. Un esempio è quando il rapporto tra il numero di valori query_hash distinti e il numero totale di voci in sys.query_store_query è molto inferiore a 1.
• Impostare QUERY_CAPTURE_MODE su AUTO per filtrare automaticamente le query ad hoc con un consumo di risorse ridotto.

Trovare query senza parametri in Query Store

È possibile trovare il numero di piani archiviati in Query Store usando la query seguente, usando DMV di Query Store, in SQL Server, Istanza gestita di SQL di Azure o database SQL di Azure:

SELECT count(Pl.plan_id) AS plan_count, Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
GROUP BY Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
ORDER BY plan_count desc;

L'esempio seguente crea una Sessione eventi estesi per acquisire l'evento query_store_db_diagnostics, che può essere utile per diagnosticare l'utilizzo delle risorse di query. In SQL Server questa sessione eventi estesa crea per impostazione predefinita un file di eventi nella cartella log di SQL Server. Ad esempio, in un'installazione predefinita di SQL Server 2019 (15.x) in Windows, è necessario creare il file di evento (file con estensione xel) nella cartella C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log. Per Istanza gestita di SQL di Azure, specificare invece una posizione di archiviazione BLOB di Azure. Per altre informazioni, vedere event_file XEvent per Istanza gestita di SQL di Azure. L'evento 'qds.query_store_db_diagnostics' non è disponibile per database SQL di Azure.

CREATE EVENT SESSION [QueryStore_Troubleshoot] ON SERVER 
ADD EVENT qds.query_store_db_diagnostics(
      ACTION(sqlos.system_thread_id,sqlos.task_address,sqlos.task_time,sqlserver.database_id,sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'QueryStore',max_file_size=(100))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

Con questi dati è possibile trovare il conteggio dei piani in Query Store e anche molte altre statistiche. Per conoscere la quantità di memoria usata e il numero di piani rilevati da Query Store, cercare le colonne plan_count, query_count, max_stmt_hash_map_size_kbe max_size_mb nei dati dell'evento. Se il numero di piani è superiore al normale, può indicare un aumento delle query senza parametri. Usare la query DMV di Query Store seguente per esaminare le query con parametri e le query senza parametri in Query Store.

Per le query con parametri:

SELECT qsq.query_id, qsqt.query_sql_text
FROM sys.query_store_query AS qsq 
INNER JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id= qsqt.query_text_id 
WHERE qsq.query_parameterization_type<>0 or qsqt.query_sql_text like '%@%';

Per le query senza parametri:

SELECT qsq.query_id, qsqt.query_sql_text
FROM sys.query_store_query AS qsq 
INNER JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id= qsqt.query_text_id 
WHERE query_parameterization_type=0;

Evitare il modello DROP e CREATE per gli oggetti contenitore

Query Store associa ogni voce di query a un oggetto contenitore, come una stored procedure, una funzione o un trigger. Quando si ricrea un oggetto contenitore, viene generata una nuova voce di query per lo stesso testo della query. Questo impedisce di monitorare le statistiche sulle prestazioni relative a tale query nel tempo e di ricorrere al meccanismo di uso forzato del piano. Per evitare questa situazione, usare il processo ALTER <object> per modificare la definizione dell'oggetto contenitore, quando è possibile.

Verificare regolarmente lo stato dei piani forzati

L'uso forzato del piano è un meccanismo efficace per risolvere i problemi di prestazioni delle query critiche e renderle più prevedibili. Come accade con gli hint di piano e le guide di piano, forzare un piano non garantisce che poi venga usato nelle esecuzioni successive. In genere, quando lo schema del database viene modificato in modo che gli oggetti a cui fa riferimento il piano di esecuzione vengano modificati o eliminati, l'uso forzato del piano ha esito negativo. In questo caso SQL Server opta per la ricompilazione delle query, mentre il motivo effettivo dell'errore viene esposto in sys.query_store_plan. La query seguente restituisce informazioni sui piani forzati:

USE [QueryStoreDB];
GO

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,
    force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;

Per un elenco completo dei motivi, vedere sys.query_store_plan. È anche possibile usare l'oggetto XEvent query_store_plan_forcing_failed per monitorare e risolvere gli errori di uso forzato del piano.

Evitare di rinominare i database per query con piani forzati

I piani di esecuzione fanno riferimento agli oggetti usando nomi in tre parti come database.schema.object.

Se si rinomina un database, l'uso forzato del piano ha esito negativo e questo provoca la ricompilazione in tutte le esecuzioni di query successive.

Utilizzare Query nei server mission-critical

I flag di traccia globali 7745 e 7752 possono essere usati per migliorare la disponibilità dei database tramite Query Store. Per altre informazioni, vedere Flag di traccia.

• Il flag di traccia 7745 previene il comportamento predefinito quando Query Store scrive i dati su disco prima dell'arresto di SQL Server. Questo significa che i dati di Query Store che sono stati raccolti ma non sono ancora stati salvati su disco andranno persi, per l'intervallo di tempo definito con DATA_FLUSH_INTERVAL_SECONDS.
• Il flag di traccia 7752 abilita il caricamento asincrono di Query Store. Questo consente di portare online un database ed eseguire query prima del completamento del ripristino di Query Store. Il comportamento predefinito consiste nell'eseguire un caricamento sincrono di Query Store. Il comportamento predefinito impedisce l'esecuzione delle query prima del completamento del ripristino di Query Store, ma evita anche l'esclusione di query nella raccolta di dati.

Usare Query Store nella replica geografica attiva del database SQL di Azure

Query Store in una replica geografica attiva secondaria del database SQL di Azure sarà una copia di sola lettura dell'attività nella replica primaria.

Evitare la replica geografica di livelli di database SQL di Azure non corrispondenti. Un database secondario deve avere dimensioni di calcolo uguali o molto simili al database primario ed essere incluso nello stesso livello di servizio del database primario. Cercare il tipo di attesa HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO in sys.dm_db_wait_stats, che indica la limitazione della frequenza del log delle transazioni nella replica primaria a causa del ritardo del database secondario.

Per altre informazioni sulla stima e la configurazione delle dimensioni del database SQL di Azure secondario della replica geografica attiva, vedere Configurazione del database secondario.

Adattare Query Store al proprio carico di lavoro

Le procedure consigliate e le raccomandazioni per configurare e gestire Query Store sono state approfondite in questo articolo: Procedure consigliate per gestire Query Store.

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
• Monitoraggio delle prestazioni con Query Store
• Ottimizzazione delle prestazioni con Query Store
• Archiviazione e analisi delle query cronologiche in Azure Synapse Analytics