25

Aug

SQL Server, rallentamenti su query con parametri

Ciao a tutti

 

E’ la seconda volta che mi capita da quando lavoro, quindi diciamo pure che è raro Sorriso

ma può capitare ancora, e quindi sono qui a raccontarvi che a volte lavorando su SQL Server con una tabella di grandi dimensioni (decine di milioni di righe), dove ogni minima ottimizzazione può far risparmiare secondi e minuti di computazione sul DB, mi è capitato di eseguire la stessa esatta query con dei parametri, che ad un certo punto semplicemente diventa lentissima

 

andando a leggere i dati da .NET, posso dire che finanche usando un SqlDataReader, leggendo quindi i dati in maniera sequenziale, il rallentamento è evidente

a questo si può aggiungere che il rallentamento non è dato dalla CPU del server del DB, tantomeno dai dischi, semplicemente è come se tornasse pochi dati per volta

 

dopo vari tentativi, avendo anche modificato la dimensione del buffer di lettura del provider di SQL, ho scoperto l’arcano

 

sembra che usando una query con parametri (es. select * from tab1 inner join tab2 on xxxxx where tab1.x = @1 and tab2.y = @2), SQL Server cerchi (come sempre) di fare una cache del piano di esecuzione (il workflow delle cose che il DB fa per tornare i dati) che al variare dei parametri a volte diventa errato, specialmente usato da ASP.NET dove il processo tramite il connection-pool sembri agli occhi di SQL sempre lo stesso; il comportamento di sql quindi è quello di leggere lentamente (forse per non piantare la macchina del DB)

fortunatamente la soluzione del problema è molto semplice: basta chiedere a SQL di ricompilare la select ogni volta così da essere sempre sicuri

questo consiglio ha comunque un costo minimo di tempo dovuto alla compilazione, ma su una tabella di decine di milioni di righe, dove un piano di esecuzione sbagliato può portare ad una risposta da parte del DB anche dopo 5 minuti, perdere mezzo secondo di ricompilazione è praticamente un costo nullo!

altrimenti (in caso di poche centinaia o migliaia di righe) questo consiglio non è da considerarsi una best-practice

 

 

per richiedere la ricompilazione del piano di esecuzione della nostra select, basta aggiungere “option (recompile)” al nostro SQL

 

es:

 

SELECT *

FROM TAB1

WHERE COL1=@1

OPTION (RECOMPILE)

 

 

a presto

by Antonio Esposito on 8/25/2012