Una query è a volte super veloce e a volte molto lenta. Uno dei motivi principali per cui per questo problema è Parameter Sniffing. Esistono diversi approcci per affrontare il problema dell’analisi dei parametri e in questo articolo vengono illustrati i metodi efficaci per la gestione dell’analisi dei parametri nelle istruzioni SELECT contenenti una clausola TOP.
Sommario
Parameter sniffing: di cosa si tratta?
Quando una query viene inviata a SQL Server per l’esecuzione, passa attraverso alcune fasi e nella fase di ottimizzazione della query viene generato un piano di esecuzione. Il processo di generazione del piano di esecuzione è una fase molto costosa, per questo motivo, SQL Server mantiene e riutilizza i piani se possibile.
Quando si richiama una stored procedure per la prima volta, Query Optimizer genera un piano di esecuzione ottimale per la stored procedure in base ai parametri di input. Come già indicato, il piano di query generato di questa stored procedure verrà memorizzato in cache dei piani per la successiva esecuzione della stessa procedura.
In questo modo, SQL Server evita di generare un nuovo piano di esecuzione per la stessa procedura e consente di risparmiare sull’utilizzo delle risorse. Fin qui tutto sembra perfetto, ma l’altra faccia della medaglia è un po’ diversa. Tuttavia, il piano di esecuzione memorizzato nella cache potrebbe non essere ottimale per altri parametri e può influire negativamente sulle prestazioni della procedura
I sintomi espliciti del Parameter sniffing in SQL possono essere:
- Le prestazioni di esecuzione della stored procedure mostrano incoerenza. Significa che la procedura viene eseguita A volte in modo efficiente, ma in altri momenti in modo inefficiente
- Improvviso degrado delle prestazioni della procedura
- Se c’è una correzione delle prestazioni della procedura dopo l’aggiornamento delle statistiche
- Se c’è una correzione nelle prestazioni della procedura dopo la ricompilazione delle statistiche della procedura
- Se mostra prestazioni diverse per gli stessi parametri in SSMS e nell’applicazione
- Se la query di routine mostra prestazioni migliori rispetto alla stored procedure
Esempio di Parameter sniffing in SQL
Ora verifichiamo un caso di Parameter Sniffing con un esempio. Creiamo una stored procedure sul database Adventureworks.
CREATE PROCEDURE dbo.GetAllTaxAmount
@ParamProductId AS INT
AS
SELECT SUM([UnitPrice]*SalesHeader.SubTotal)
FROM Sales.SalesOrderHeaderEnlarged SalesHeader
INNER JOIN sales.SalesOrderDetailEnlarged SalesDetail ON SalesDetail.SalesOrderID = SalesHeader.SalesOrderID
WHERE SalesDetail.ProductID >@ParamProductIdDopo aver creato la procedura GetAllTaxAmount, la eseguiremo con un parametro il cui valore è 100 e analizzaremo il piano di esecuzione.
EXECUTE GetAllTaxAmount @ParamProductId = 100
Il tempo di esecuzione della stored procedure è di soli 4 secondi. A questo punto si pulirà la cache del piano di esecuzione procedurale con l’aiuto del comando FREEPROCCACHE in modo che il Query Optimizer genera un nuovo piano di query per il nuovo parametro.
DBCC FREEPROCCACHE
GO
EXECUTE GetAllTaxAmount @ParamProductId = 10000
Il nuovo piano di esecuzione è diverso dal precedente. In questo caso, possiamo renderci conto che l’ottimizzatore di query genera piani di esecuzione diversi per i diversi parametri per la routine GetAllTaxAmount. Come ultimo passaggio, richiameremo la procedura per il nostro primo parametro con valore 100.
EXECUTE GetAllTaxAmount @ParamProductId = 100
Di conseguenza, possiamo dire che il piano di esecuzione memorizzato nella cache non è ottimale per l’ultimo parametro della procedura ed il calo delle performance è nascosta in relazione al problema di sniffing dei parametri. La tabella seguente mostra la drammatica Differenza tra le statistiche sulle prestazioni del piano ottimale e del piano subottimale.
Piano ottimale | Piano non ottimale | |
| Lettura logica | 105.468 | 30.483.293 |
| Tempo di esecuzione | 1483 | 16611 |
L’attributo Parameter List dell’operatore select indica per quale valore di parametro viene eseguita l’esecuzione è stato generato un piano.
Identificazione del Parameter Sniffing
La vista a gestione dinamica sys.dm_exec_query_stats restituisce statistiche sulle prestazioni relative ai piani di query memorizzati nella cache.
Per identificare i problemi di parameter sniffing, possiamo usare questa vista ma questa vista restituisce le statistiche del piano di esecuzione in modo aggregato, per questo motivo, dobbiamo confrontare i valori minimo e massimo di consumo di risorse delle query.
A questo punto, se c’è un enorme divario tra i valori massimo e minimo di una query, possiamo sospettare un problema di sniffing dei parametri.
La query seguente è in grado di rilevare una differenza di 100 volte tra il consumo massimo e minimo di risorse di una query, pertanto è possibile rilevare quali query hanno un utilizzo diverso delle risorse.
WITH Execution_Detail AS (
SELECT SUBSTRING(ST.text, (QS.statement_start_offset / 2) + 1, ((CASE statement_end_offset
WHEN-1
THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset) / 2) + 1) AS [Query Statment],
ST.text AS 'Procedure Batch',
min_worker_time, max_worker_time,
ISNULL((max_worker_time - min_worker_time) / NULLIF(min_worker_time, 0), 0) AS LogicalCpuRatio,
min_logical_reads,max_logical_reads,
ISNULL((max_logical_reads - min_logical_reads) / NULLIF(min_logical_reads, 0), 0) AS LogicalReadsDevRatio,
max_elapsed_time, min_elapsed_time,
ISNULL((max_elapsed_time - min_elapsed_time) / NULLIF(min_elapsed_time, 0), 0) AS LogicalElapsedTimDevRatio
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST)
SELECT * FROM Execution_Detail WHERE LogicalCpuRatio >=100 AND LogicalReadsDevRatio>=100 AND LogicalElapsedTimDevRatio>=100D’altra parte, è possibile usare il report Query con variazione elevata in Query Store per identificare il Problemi di sniffing. Questo report è in grado di rilevare le query che vengono eseguite a volte molto velocemente e a volte lentamente.
- Xiaomi Redmi Note 7. Il nuovo smartphone della Redmi
- Deadlock in SQL Server 2019. Come gestirli
- DNS Google in Ubuntu 19.04. Ecco come configurarli
- Sincronizzare OneDrive su Ubuntu. Ecco come fare.
- Meta Description su WordPress 5.5. Cos’è e come usarla.
Come possiamo gestire i problemi di sniffing dei parametri?
Esistono molti modi per gestire questo problema:
- Aggiunta di un’opzione RECOMPILE durante la creazione di una stored procedure
- Utilizzo dell’hint OTTIMIZZA PER SCONOSCIUTO;
- Utilizzo dell’hint OPTIMIZE FOR per i valori dei parametri specifici
- Utilizzo di variabili locali nelle stored procedure
- Flag di traccia 4136;
- Disabilitazione dell’opzione Analisi parametri della configurazione con ambito database
La soluzione più semplice per ovviare a questo problema è disabilitare l’opzione di sniffing dei parametri a livello di database in modo da che non è necessaria alcuna modifica del codice. Tuttavia, prima di decidere di utilizzare questa opzione, dobbiamo assicurarci che questo l’opzione avvantaggia il database di test. Questa opzione è disponibile per SQL Server 2016 e versioni successive e per il la versione precedente può usare il flag di traccia 4136. È possibile trovare questo parametro nella configurazione con ambito del database.
Dopo aver disabilitato questa opzione, otterremo piani di esecuzione più stabili perché l’esecuzione non ne usa nessuno particolare parametro e richiamare la routine in corrispondenza delle statistiche medie del conteggio delle righe. Quando si esegue nuovamente la stored procedure GetAllTaxAmount, non viene visualizzato il valore compilato del parametro in la proprietà Elenco parametri.
D’altra parte, l’abilitazione di questa opzione può causare problemi di prestazioni nelle analisi dell’intervallo o problemi di tempdb.
Conclusione
In questo articolo, abbiamo visto come analizzare i problemi di Parameter Sniffing in Sql Server e come possiamo identificarlo problema facilmente.
