Impedance Mismatch

# re: Query ad-hoc con parametri vs query ad-hoc senza parametri

Be', il tipo ha fatto la prova usando sys.objects... Che non mi pare poi tanto da caso reale...

Voglio dire che in applicazioni di un certo tipo (gestionali con un po' di statistiche) il fatto di usare solo query parametriche fa incappare non poche volte nel problema del parameter sniffing, e comunque, lasciando stare questo "inconveniente", in contesti (sempre gestionali con un po' di statistiche) dove non vengono eseguite 100000 query "una dietro l'altra" (cavolo, una query ogni secondo per più di un giorno senza nessuna interruzione...) e dove i tempi di esecuzione sono influenzati dalla quantità di dati non proprio irrisoria, il miglior piano di esecuzione generabile rimane sempre quello elaborato ad hoc per ogni query, quindi senza nessun caching dei piani.

Questo solo per dire che non è sempre vero che l'uso di query parametriche sia da preferire, certo, bisogna sapere che si sta facendo in modo da comportarsi con cognizioni di causa, ma credo che il consiglio di usare sempre query con parametri sia la giusta regola da fornire per iniziare a muoversi con SQL Server, sapendo poi che in futuro, ci potranno essere delle necessarie eccezioni...

# re: Query ad-hoc con parametri vs query ad-hoc senza parametri

sta cosa mi sta scatenando tutta una serie di curiosita' a cascata...

mi sa che e' meglio che chiudo tutto e vado in ferie!!!!!

: )))

# re: Query ad-hoc con parametri vs query ad-hoc senza parametri

Rimanendo sempre nel generico, per me nei casi in cui un criterio (clausola where) è complesso perché deve trattare combinazioni di variabili, l'uso di query *senza* parametri può migliorare e di molto le performance, esempio:

declare @intID int

set @intID= 123

-- Query parametrica: esegue sempre un cls index scan

select * from sysobjects where id= @intID or @intID is null

-- Query senza parametri che usa solo costanti: esegue un cls index seek, a meno di @intID a null dove esegue anche lei

-- un cls index scan visto che deve restituire tutti i record

declare @strSQL varchar(100)

set @strSQL= 'select * from sysobjects where id= @intID or @intID is null'

set @strSQL= replace (@strSQL, '@intID', isnull (convert (varchar, @intID),'null'))

--print @strSQL

exec (@strSQL)

Certo è che con la tua segnalazione ho scoperto una cosa su cui non avevo mai riflettuto, e cioè il possibile consumo notevole della "fetta" di memoria che SQL dedica al caching da parte dei piani e quindi a discapito delle pagine dati.

Ma questo comportamento non accade solo per colpa dell'uso della sp_executesql che per come è fatta cacha per l'appunto i piani di esecuzione generati?

Voglio dire, se devo usare la sp_executesql quando so che in realtà non ho parametri ma solo costanti sarò ben "pirla", non è meglio usare direttamente la exec() e morta li, in modo da non andare ad allocare per nulla memoria nella cache?

In questo modo ottengo piani perfetti elaborati ogni volta e non alloco nessun piano nella cache, anzi, SQL non potrebbe accorgersi che una chiamata a sp_executesql senza parametri definiti non ha senso che generi un piano di esecuzione da mantenere in cache?

# re: Query ad-hoc con parametri vs query ad-hoc senza parametri

Per fare un esempio funzionante, cosi' magari mi spiego meglio:

use tempdb

go

set nocount on

if object_id ('ZT') is not null drop table ZT

go

create table ZT (

PK int not null identity(1,1) primary key clustered,

C varchar (100)

)

go

declare @intI int

set @intI= 1000000

while @intI>0

begin

insert into ZT (C) values (replicate(convert (varchar, @intI), 14))

set @intI= @intI - 1

end

go

dbcc freeproccache

go

set nocount on

go

declare @intI int, @vchSQL nvarchar (1000), @vch varchar (100), @dtm datetime

set @intI= 0

set @dtm= getdate()

while @intI<100

begin

set @vchSQL= 'select @vch= C from ZT where PK= @intPK or @intPK is null'

exec sp_executesql @vchSQL, N'@vch varchar(100) output, @intPK int', @vch output, @intI

set @intI= @intI+1

end

print 'Query con parametri: ' + convert (varchar, datediff(second, @dtm, getdate()))

go

dbcc freeproccache

go

set nocount on

go

declare @intI int, @vchSQL nvarchar (1000), @vch varchar (100), @dtm datetime

set @intI= 0

set @dtm= getdate()

while @intI<100

begin

set @vchSQL= 'declare @vch varchar (100) select @vch= C from ZT where PK= @intPK or @intPK is null'

set @vchSQL= replace (@vchSQL, '@intPK', convert (varchar, @intI))

if @intI=0 print @vchSQL

exec (@vchSQL)

set @intI= @intI+1

end

print 'Query con costanti: ' + convert (varchar, datediff(second, @dtm, getdate()))

go

E la query è stupida, nel senso che in circostanze reali i criteri sono sicuramente più complessi, e quindi il fatto di costuire un piano cachato con parametri che però non costituisce praticamente mai il piano migliore generabile (piano che però è unico e resta buono per "un bel po'" e quindi non comporta ulteriori ricompilazioni), in certe circostanze può rivelarsi veramente una tragedia in fatto di performance finali nella restituzione di dati da parte della query (e con l'esempio che ho fatto anche solo 100 iterazioni che accedono ad una tabella da un milione di record, portano in vantaggio la query con costanti per un 20 a 1, volendo approssimare ad 1 secondo il tempo che ci impiega a girare il batch che itera la query con sole costanti). Ed ho usato apposta la exec() in modo che ogni piano generato non venga cachato comportando quel problema che ha originato il tuo post nel blog.

Che dici?

# re: Query ad-hoc con parametri vs query ad-hoc senza parametri

Ciao AlessandroD

stai facendo un bel pò di confusione con il concetto di query parametriche.

Nessuna di quelle che hai scritto tu è parametrica! :-)

Per essere parametrica, la tua prima query, deve esser riscritta cosi:

exec sp_executesql N'select * from sysobjects where id= @intID or @intID is null', N'@intID int', 123

per query semplici SQL Server effettua l'autoparamettrizzazione, come ad esempio per questa:

select * from sysobjects where id=123

che viene convertita in

select * from sysobjects where id=@1

ma per tutte le altre è meglio aiutarlo usando sp_executesql. Of course è bene stare attenti ai problemi di parameter sniffing e di dati con distribuzioni disomogenee.

L'ultima parte del tuo post è piuttosto oscura cmq, per cercare di chiaraire, è proprio l'uso di sp_executesql che PERMETTE il riutilizzo del piani di esecuzione e quindi ne abbassa il numero e pertanto l'uso della memoria.

>Voglio dire, se devo usare la sp_executesql quando so che

>in realtà non ho parametri ma solo costanti sarò ben

>"pirla", non è meglio usare direttamente la exec() e

>morta li, in modo da non andare ad allocare per nulla

>memoria nella cache?

Questa proprio non l'ho capita. I piani in cache ci vanno cmq, (a meno che non siano piani a costo zero), e quindi quello che dobbiamo fare è cercare di riutilizzarli. sp_executesql permette il riutilizzo, delle query ad-hoc oppure l'utilizzo di EXEC() no.

Se vuoi fare una verifica basta che esegui questa query:

SELECT

text, refcounts, usecounts, cacheobjtype, objtype

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

> non alloco nessun piano nella cache,

impossibile, il piani nella cache vengono messi cmq, non puoi decidere di non metterli.

Attenzione ad una cosa. Nel primo esempio fatto da Tony, l'utilizzo di sp_executesql con dei valori "costati" è fatto solo per simulare l'utilizzo di query ad-hoc! Utilizzando EXEC() avresti ottenuto la stessa cosa. (oppure sviluppando applicazione che effettuasse lo stesso ciclo)

# re: Query ad-hoc con parametri vs query ad-hoc senza parametri

AlessandroD

Rispondo relativamente al secondo commento (quello con la query di prova).

L'esempio non credo che sia rappresentativo.

Il problema è che la query contiene

...or @intPK is null

che è "biricchino". Se infatti @intPk è null la clausola è sempre vera, quindi SQL Server deve restituire tutte le colonne. Il piano di esecuzione viene calcolato con questa previsione.

Questo è il "classico" problema del parameter sniffig e, come detto in un commento precedente, è chiaro che se si è consci che si stanno facendo query di questo genere è meglio evitare il caching del piano di esecuzione.

Cmq se questo esempio è relativo al problema della creazione di filtri dinamici, ti consiglio la luuuunga lettura del documento dell'ottimo Erland:

www.sommarskog.se/dyn-search.html

# re: Query ad-hoc con parametri vs query ad-hoc senza parametri

> impossibile, il piani nella cache vengono messi cmq, non puoi decidere di non metterli.

Attenzione ad una cosa. Nel primo esempio fatto da Tony, l'utilizzo di sp_executesql con dei valori "costati" è fatto solo per simulare l'utilizzo di query ad-hoc! Utilizzando EXEC() avresti ottenuto la stessa cosa. (oppure sviluppando applicazione che effettuasse lo stesso ciclo)

----

Le mie prove le ho fatte su SQL 2000, e ti assicuro la dbcc memorystatus produce risultati ben diversi usando exec() piuttosto che sp_executesql. In SQL 2000 non esiste il blocco CACHESTORE_SQLCP (Total), e io ho preso come riferimento il blocco Procedure Cache, visto che mi pare sia in comune anche con SQL 2005.

In questo blocco il valore per TotalProcs e TotalPages è ben diverso nei due scenari (exec e sp_executesql), a vantaggio dello scenario che usa solo exec: il numero di oggetti cachati e la quantità di pagine massime disponibili per la proccache sono molto minori rispetto allo scenario che usa sp_executesql. E a buon senso mi sembra ovvio, cioè, visto che exec() genera sicuramente un piano non riusabile (a differenza di sp_executesql), immagino che a rigor di logica la maggior parte dei piani derivanti da una exec() siano buttati via in fretta a favore di altri in arrivo, senza quindi star li a collezionarli tutti per nulla, visto che per definizioni non sono riusabili, o no?

Quindi si, hai perfettamente ragione, anche la exec cacha i piani, ma questi scadono in fretta a favore di altri, almeno così accade su SQL 2000, visto che ho appena finito di fare la stessa prova su SQL 2005, e si, in questa versione, la differenza è molto ma molto ma molto meno evidente.

Anzi, il nuovo blocco di dbcc memorystatus CACHESTORE_SQLCP (Total) usato da Tony differisce di molto poco nei due contesti, comunque sempre a favore di exec() anche se "solo" per circa 9500 kb contro i 280000 allocati (ho ridotto il ciclo a 50000 iterazioni per motivi di tempo...).

> Il problema è che la query contiene

...or @intPK is null

che è "biricchino". Se infatti @intPk è null la clausola è sempre vera, quindi SQL Server deve restituire tutte le colonne. Il piano di esecuzione viene calcolato con questa previsione.

---

Appunto e il piano genera un index scan in tutti i casi...

> Questo è il "classico" problema del parameter sniffig e, come detto in un commento precedente,

---

Mmmmh, il paramer sniffing da quello che ho capito io è un'altra cosa, non si riferisce a piani generati a fronte di una valorizzazione anomala dei parametri coinvolti? (nel senso che il valore è particolare, tipo '' o null, magari perché questi sono i valori di default dei parametri che fanno parte degli argomenti di chiamata di una sp).

Qui invece non stiamo parlando di "cattivi" piani generati a causa di parametri valorizzati in modo poco accorto, ma di piani generati male a causa del contorcimento dei criteri presenti ma necessari per non ricorrere al T-SQL dinamico.

Nell'articolo di Erland c'è proprio una sua aggiunta dovuta al nostro Marc dove per l'appunto è evidenziato come il ricorso a T-SQL dinamico abbinato a funzioni tabella inline permette di avere sempre dei piani ottimi grazie alla compilazione di un piano che non usa parametri ma solo costanti, e l'uso di funzioni tabella inline permette comunque di tenere "in sicurezza" il T-SQL dinamico.

> è chiaro che se si è consci che si stanno facendo query di questo genere è meglio evitare il caching del piano di esecuzione.

---

Il riuso del piano intendi, appena prima hai detto che non è possibile non cachare i piani :-)

Comunque nei gestionali e nelle statistiche, query di quel tipo non sono proprio rare, anzi.

O si risolvono con il T-SQL dinamico in modo accorto (senza parametri e usando exec()), oppure ci si tengono dei piani fatti veramente male, certo, si risparmia il tempo di compilazione di un po' di ms, ma ci si rimettono secondi in fatto di elaborazione reale sui dati.

P.S.: La text box per i commenti è piccolissima, quando c'è da scrivere tanto, è un bel problema... :-)

Ciao, Alessandro

# re: Query ad-hoc con parametri vs query ad-hoc senza parametri

In pausa pranzo ha raccolto un po' di dati di confronto tra il comportamento sul 2000 e sul 2005, usando sempre il primo script di Tony e confrontando il comportamento della exec() con la sp_executesql:

*** SQL 2000, 50000 iterazioni:

exec():

TotalProcs 14670 14829 14821

TotalPages 14777 14959 14934

InUsePages 7408 7491 7485

sp_executesql:

TotalProcs 48805 48386 49464

TotalPages 48868 48463 49542

InUsePages 19858 20045 20140

*** SQL 2005, 50000 iterazioni:

exec():

TotalProcs  7121 8058 8068 8036 8023 7900 7880

TotalPages  36002 40736 40786 40624 40558 39938 39839

InUsePages  0

CACHESTORE_SQLCP (Total)

SinglePage Allocator 287888 325760 326160 324864 324336 319376 318584

sp_executesql:

TotalProcs  7863 5472 7351 8279 8324 7813 7926

TotalPages  39206 26908 36651 41007 38494 38374 38888

InUsePages  0

CACHESTORE_SQLCP (Total)

SinglePage Allocator 313512 215128 293072 327920 307816 306856 310968

Chissà come mai il 2005 scombina tutto il funzionamento???

Trovo più comprensibili i dati del 2000, anche perché portano acqua al mio mulino :-D

Ciao, Alessandro