Usare l’opzione OPTION (RECOMPILE) per una dichiarazione

Ho iniziato questa serie con il post intitolato: Piccole Tabelle Bobby, Iniezione SQL e EXECUTE AS. Poi sono passato a discutere alcune delle differenze con il post intitolato: EXEC e sp_executesql – come sono diversi?

Oggi, voglio affrontare alcuni commenti e continuare con alcuni consigli e trucchi usando questi comandi.

Prima di tutto – avremmo potuto aiutare le prestazioni dell’istruzione sp_executesql?

Sì…

Se sappiamo che un’istruzione restituisce una quantità variabile di dati (in base ai parametri forniti) allora possiamo usare la caratteristica di SQL Server 2005 WITH RECOMPILE per dire a SQL Server che l’istruzione che viene eseguita dovrebbe avere il proprio piano creato e che i piani precedenti (se esistono) non dovrebbero riutilizzare l’istruzione. Dice anche a SQL Server che questo particolare piano è un “piano monouso” che non dovrebbe essere riutilizzato dagli utenti successivi. Per vedere la combinazione di tutte queste cose – userò alcuni dei DMV che tracciano la cache del piano e l’utilizzo del piano.

DBCC FREEPROCCACHE
GO

SELECT st.text, qs.EXECUTION_COUNT -, qs.*, cp.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS cp
WHERE st.text like ‘%FROM dbo.member%’
GO

In questo momento, questa query ritorna 0 righe.

Eseguo quanto segue e poi ricontrollo la cache del piano:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‘SELECT * FROM dbo.member WHERE lastname LIKE @lastname’
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)’, ‘Tripp’
GO

Ora abbiamo una riga per il nostro piano di query parametrizzato:

text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 1

Quindi, cosa ci mostra… ci mostra che c’è un piano nella cache per questa dichiarazione. E, se siamo interessati a vedere il piano, possiamo rimuovere il cp.* commentato nella query di cui sopra per ottenere la colonna cp.query_plan. Clicchiamo su un XML showplan e SSMS andrà DIRETTAMENTE in una finestra grafica del piano di query (dal 2008 in poi):

E, ancora una volta, vediamo il piano ottimale (usare l’indice e fare un bookmark lookup) perché questa query è altamente selettiva (solo 1 riga).

Eseguiamo di nuovo lo stesso esatto statement – usando il valore di Anderson solo per arrivare al punto in cui eravamo la settimana scorsa:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‘SELECT * FROM dbo.member WHERE lastname LIKE @lastname’
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)’, ‘Anderson’
GO

E, vediamo che usa lo stesso ESATTO piano (guardando showplan). Infatti, possiamo vederlo anche controllando la nostra cache dei piani:

text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 2

E, mentre sappiamo che questo piano (per usare l’indice) è buono per il valore altamente selettivo di ‘Tripp’, non è buono per il valore di Anderson poiché ci sono molte righe che corrispondono. Se lo sospettavamo e/o lo sapevamo quando stavamo eseguendo (dal client) allora potremmo usare l’OPZIONE (RECOMPILE) per forzare SQL Server a ottenere un nuovo piano:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‘SELECT * FROM dbo.member WHERE lastname LIKE @lastname OPTION (RECOMPILE)’
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)’, ‘Anderson’
go

Il piano di query come risultato di questa esecuzione è:

E questo è il piano più ottimale dato questo parametro. A questo punto, le domande (IMO) sono:

1. Qualcuno sarebbe davvero in grado di stimare programmaticamente che un parametro inviato dal cliente è “atipico” e/o che merita una ricompilazione? E, credo di poter rispondere sì – per alcuni parametri – come quelli con un carattere jolly. Ma, se stiamo parlando solo di due valori diversi contro una singola colonna, questo sarebbe indovinare le statistiche dei dati.
2. Questa particolare dichiarazione dovrebbe essere sempre eseguita per ricompilare e non salvare mai un piano?
3. Cosa ha fatto SQL Server con il piano stesso?

Andrò a rispondere al #3 per primo, perché questo è facile da rispondere. Usando lo stesso statement, interrogherò di nuovo la cache del piano:

text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 2

Anche se è la terza volta che eseguiamo questo statement, questa esecuzione finale NON è stata messa nella cache. È stata usata solo per l’esecuzione con OPTION (RECOMPILE). E NON influenzerà le esecuzioni future. Se torno indietro ed eseguo senza l’OPZIONE (RECOMPILE) allora otterrò il piano precedente (per usare l’indice).

Ora, le altre due domande – queste sono molto più interessanti ed è qui che penso che le stored procedure dovrebbero essere usate. Personalmente, penso che gli sviluppatori che conoscono i dati e conoscono l’applicazione – saranno molto più bravi a creare il codice GIUSTO specialmente quando capiscono tutte le opzioni a loro disposizione.

Ecco il mio modo di pensare alle stored procedure e alla ricompilazione:

• Se so che una particolare dichiarazione restituisce sempre lo stesso numero di righe e usa lo stesso piano (e, lo saprei dai test), allora creerò la stored procedure normalmente e lascerò che il piano venga memorizzato nella cache.
• Se so che una particolare dichiarazione varia selvaggiamente da un’esecuzione all’altra e il piano ottimale varia (di nuovo, dovrei saperlo dai test di più esecuzioni campione), allora creerò la stored procedure normalmente e userò OPTION (RECOMPILE) per assicurarmi che il piano della dichiarazione non sia memorizzato nella cache o salvato con la stored procedure. Ad ogni esecuzione quella stored procedure avrà parametri diversi e l’istruzione particolarmente cattiva otterrà un nuovo piano ad ogni esecuzione.

Tuttavia, questo è anche il punto in cui le cose diventano più difficili. Ho visto spesso procedure memorizzate in cui le persone cercano di usare la logica condizionale per suddividere i diversi tipi di parametri e questo di solito non funziona bene come ci si aspetta (lo scriverò nel prossimo blog). E, questo è sempre dove alcuni decidono di voler costruire dinamicamente la dichiarazione che viene eseguita – ora, abbiamo bisogno di determinare se dobbiamo usare sp_executesql o EXEC. E ci sono davvero un paio di opzioni a questo punto. Alla fine, in uno o due post in più – vi mostrerò finalmente dove EXEC è un chiaro vincitore rispetto a sp_executesql perché anche l’OPZIONE (RECOMPILE) non sempre aiuta TUTTI i tipi di piani (e specialmente uno dei tipi di piani più comuni che vedo).