Parametro Sniffing (o Spoofing) in SQL Server

Qualche tempo fa ho avuto una query che ho eseguito molto per uno dei miei utenti. Si stava ancora evolvendo e ottimizzando, ma alla fine si è stabilizzato e ha funzionato abbastanza velocemente, quindi abbiamo creato una procedura memorizzata da esso.

Finora, così normale.

La procedura memorizzata, tuttavia, era cane lento. Nessuna differenza sostanziale tra la query e il proc, ma il cambio di velocità è stato enorme.

[In background, stiamo eseguendo SQL Server 2005.]

Un amichevole DBA locale (che non lavora più qui) ha dato uno sguardo alla procedura memorizzata e ha detto “parametro spoofing!” ( Modifica: anche se sembra che sia anche noto come “parametro sniffing”, che potrebbe spiegare la scarsità di risultati di Google quando ho provato a cercarlo.)

Abbiamo sottratto alcune delle stored procedure a una seconda, avvolto la chiamata a questo nuovo proc interiore in quella esterna preesistente, chiamata quella esterna e, presto, è stata veloce come la query originale.

Quindi, cosa dà? Qualcuno può spiegare lo spoofing dei parametri?

Credito bonus per

  • evidenziando come evitarlo
  • suggerendo come riconoscere la causa ansible
  • discutere strategie alternative, ad esempio statistiche, indici, chiavi, per mitigare la situazione

Solutions Collecting From Web of "Parametro Sniffing (o Spoofing) in SQL Server"

Cordiali saluti – devi essere a conoscenza di qualcos’altro quando lavori con SQL 2005 e proc memorizzato con parametri.

SQL Server compilerà il piano di esecuzione del processo memorizzato con il primo parametro utilizzato. Quindi se esegui questo:

usp_QueryMyDataByState 'Rhode Island' 

Il piano di esecuzione funzionerà al meglio con i dati di un piccolo stato. Ma se qualcuno si gira e corre:

 usp_QueryMyDataByState 'Texas' 

Il piano di esecuzione progettato per i dati delle dimensioni di Rhode Island potrebbe non essere altrettanto efficiente con i dati di dimensioni Texas. Ciò può produrre risultati sorprendenti quando il server viene riavviato, poiché il piano di esecuzione appena generato verrà indirizzato a qualunque parametro venga utilizzato per primo, non necessariamente il migliore. Il piano non verrà ricompilato fino a quando non ci sarà un grosso motivo per farlo, come se le statistiche venissero ricostruite.

È qui che entrano in gioco i piani di query e SQL Server 2008 offre molte nuove funzionalità che aiutano gli amministratori di database a definire un piano di query specifico sul posto a lungo termine, indipendentemente dai parametri che vengono richiamati per primi.

La mia preoccupazione è che quando hai ricostruito il tuo proc memorizzato, hai forzato la ricompilazione del piano di esecuzione. L’hai chiamato con il tuo parametro preferito, e poi ovviamente è stato veloce – ma il problema potrebbe non essere stato il proc memorizzato. Potrebbe essere stato che il proc memorizzato è stato ricompilato ad un certo punto con un insieme insolito di parametri e, quindi, un piano di query inefficiente. Potresti non aver corretto nulla e potresti dover affrontare lo stesso problema al successivo riavvio del server o ricompilazione del piano di query.

Sì, penso che intendiate lo sniffing dei parametri, che è una tecnica utilizzata dall’ottimizzatore di SQL Server per cercare di capire i valori / gli intervalli dei parametri in modo che possa scegliere il miglior piano di esecuzione per la vostra query. In alcuni casi, SQL Server svolge un lavoro scarso durante lo snifing dei parametri e non sceglie il miglior piano di esecuzione per la query.

Credo che questo articolo del blog http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx abbia una buona spiegazione.

Sembra che il DBA nel tuo esempio abbia scelto l’opzione n. 4 per spostare la query su un altro sproc in un contesto procedurale separato.

Avresti anche potuto usare il comando con ricompilare lo sproc originale o usare l’opzione di ottimizzazione per il parametro.

Un modo semplice per accelerare è riassegnare i parametri di input ai parametri locali all’inizio dello sproc, ad es

 CREATE PROCEDURE uspParameterSniffingAvoidance @SniffedFormalParameter int AS BEGIN DECLARE @SniffAvoidingLocalParameter int SET @SniffAvoidingLocalParameter = @SniffedFormalParameter --Work w/ @SniffAvoidingLocalParameter in sproc body -- ... 

Nella mia esperienza, la soluzione migliore per lo sniffing dei parametri è ‘Dynamic SQL’. Due cose importanti da notare sono che 1. dovresti usare i parametri nella tua query sql dynamic 2. dovresti usare sp_executesql (e non sp_execute), che salva il piano di esecuzione per ogni valore di parametro

Lo sniffing dei parametri è una tecnica utilizzata da SQL Server per ottimizzare il piano di esecuzione della query per una stored procedure. Quando si chiama la stored procedure per la prima volta, SQL Server analizza i valori dei parametri forniti dalla chiamata e decide quali indici utilizzare in base ai valori dei parametri.

Pertanto, quando la prima chiamata contiene parametri non tipici, SQL Server potrebbe selezionare e memorizzare un piano di esecuzione non ottimale in relazione alle seguenti chiamate della stored procedure.

Puoi ovviare a questo problema

  • utilizzando WITH RECOMPILE
  • copiare i valori dei parametri in variabili locali all’interno della stored procedure e utilizzare i locals nelle tue query.

Ho persino sentito che è meglio non usare le stored procedure ma inviare le tue query direttamente al server. Recentemente ho riscontrato lo stesso problema in cui non ho ancora una soluzione reale. Per alcune query, la copia su vars locali consente di tornare al piano di esecuzione corretto, poiché alcune query si degradano con le vars locali.

Devo ancora fare ulteriori ricerche su come SQL Server memorizza nella cache e riutilizza piani di esecuzione (sub-ottimali).

Ho avuto un problema simile. Il piano di esecuzione della mia stored procedure è durato 30-40 secondi. Ho provato a utilizzare le dichiarazioni SP nella finestra di query e sono bastati pochi ms per eseguire lo stesso. Poi ho elaborato dichiarando le variabili locali all’interno della stored procedure e trasferendo i valori dei parametri alle variabili locali. Ciò ha reso l’esecuzione SP molto veloce e ora lo stesso SP viene eseguito entro pochi millisecondi anziché 30-40 secondi.

Molto semplice e ordinato, l’ottimizzatore di query utilizza il vecchio piano di query per le query frequenti. ma in realtà anche la dimensione dei dati sta aumentando, quindi in quel momento è necessario un nuovo piano ottimizzato e si sta ancora ottimizzando le query utilizzando il vecchio piano di query. Questo è chiamato Parameter Sniffing. Ho anche creato post dettagliati su questo. Si prega di visitare questo URL: http://www.dbrnd.com/2015/05/sql-server-parameter-sniffing/

La modifica della procedura di archiviazione da eseguire come batch dovrebbe aumentare la velocità.

Selezione del file batch, ad esempio:

 exec ('select * from order where order id ='''+ @ordersID') 

Invece della normale procedura memorizzata selezionare:

 select * from order where order id = @ordersID 

Basta passare il parametro come nvarchar e dovresti ottenere risultati più rapidi.