La versione DBA di un classico XKCD.

Quando si identifica che lo sniffing dei parametri è un problema, è necessario verificare se l’implementazione di suggerimenti “ricompila” aiuterà a pianificare la qualità. (Non sei sicuro di quale parametro sia lo sniffing? Impara da questo post del blog o da questo video gratuito di 30 minuti.) Devi decidere: quale suggerimento o comando usi e dove lo metti? Quali compromessi stai facendo quando si tratta di essere in grado di ottimizzare le prestazioni del tuo SQL Server in futuro?

Questo post viene eseguito attraverso le opzioni comuni che devi spingere (o colpire) SQL Server nella generazione di un nuovo piano di esecuzione. Darò alcuni pro e contro per ogni metodo e spiegherò cosa è utile e cosa vale la pena evitare.

Per mantenere le cose relativamente semplici, sto solo discutendo di come questo si applica alle stored procedure oggi–questo post non copre altre forme di query parametrizzate (o non parametrizzate). Se hai grandi domande in quelle aree, sentiti libero di suggerirlo per un post futuro nei commenti.

Disclaimer: Recompile hints può uccidere le tue prestazioni accendendo le CPU in fiamme se usato in modo errato. Maneggiare con cura!

Utili viste di gestione dinamica

Quando parlo di impatto sulla cache del piano di esecuzione, farò riferimento a due DMV:

• sys.dm_exec_query_stats – Questo DMV è utile per vedere le istruzioni principali sul tuo server SQL, indipendentemente dal fatto che facciano parte di una procedura o meno. Controlla una query di esempio qui.
• sistema.dm_exec_procedure_stats-Questo DMV è disponibile solo in SQL Server 2008 e versioni successive. Tiene traccia delle metriche di esecuzione per le stored procedure. Controlla una query di esempio nei Libri online.

Per entrambi questi DMV, avere un piano di esecuzione nella cache è legato alla possibilità di vedere le metriche di esecuzione: numero di esecuzioni totali, CPU totale e media, letture logiche, ecc. Quando un piano di esecuzione viene rimosso dalla cache a causa di ricompilazione, pressione della memoria, riavvio o altre azioni, vengono rimosse anche le metriche di esecuzione correlate.

Esecuzione di una procedura con un suggerimento di ricompilazione runtime

Un’opzione che amo per test rapidi e facili è la possibilità di chiamare una stored procedure con un suggerimento di ricompilazione al momento dell’esecuzione. Questo è fantastico perché non devi cambiare alcun codice compilato. Il tuo suggerimento si applica anche a ciò che stai facendo in fase di esecuzione.

Per fare ciò, usi semplicemente la sintassi in questo modo:

However, there’s a little bit of a gotcha. If you have nested stored procedures, the recompile hint only applies to code in the outermost procedure. È comunque possibile ottenere lo sniffing dei parametri su tutte le chiamate alle sotto-procedure all’interno della stored procedure stessa.

Quindi, mentre a volte mi piace questo per i test, se hai qualche nidificazione nelle tue stored procedure potrebbe non aiutarti a ottenere un piano di esecuzione veramente nuovo.

Utilizzo della Stored Procedure di sistema sp_recompile

Questo a volte è utile se è necessario intervenire durante un incidente di produzione se si ha un piano errato utilizzato ripetutamente dalle sessioni in arrivo. You can force recompilation of plans by running sp_recompile against an object– most common choices are a table or a stored procedure. The command looks like this (a table in this example):

Quando viene eseguito, i piani correlati non vengono immediatamente rimossi dalla cache del piano di esecuzione di SQL Server. Invece, la magia si verifica la prossima volta che vengono eseguite le query che fanno riferimento all’oggetto ricompilato. A quel punto, le statistiche di esecuzione esistenti in sys. dm_exec_query_stats verranno ripristinate per le istruzioni nel piano.

I conteggi di esecuzione in sys.dm_exec_procedure_stats non verranno necessariamente ripristinati al prossimo utilizzo se si contrassegna una tabella utilizzata da una stored procedure per la ricompilazione. Ma se si contrassegna la stored procedure stessa per la ricompilazione, verrà ripristinata al prossimo utilizzo. (Non preoccupatevi cercando di memorizzare questo, basta leggere il paragrafo successivo.)

Ci sono grandi aspetti negativi con questo. Questo comando richiede un’autorizzazione elevata — l’utente che lo esegue richiede le autorizzazioni “alter” sulla tabella. Richiede anche un alto livello di blocco per completare. Sui sistemi occupati ho visto questo comando prendere parte ad alcune brutte catene di blocco. Stai attento con esso!

Anche se questo può occasionalmente essere utile per la risoluzione dei problemi e il test, non renderlo parte del codice di produzione.

RICOMPILA i suggerimenti nelle intestazioni delle stored procedure

Quando hai procedure che vuoi generare nuovi piani, inizi a esaminare come utilizzare i suggerimenti all’interno della procedura. L’opzione che la maggior parte delle persone scopre per prima è usare ‘WITH RECOMPILE’ nell’intestazione della stored procedure, in questo modo:

L’uso di RICOMPILARE nell’intestazione della stored procedure è piuttosto drastico: la procedura non memorizzerà nella cache un piano di esecuzione quando viene eseguito. Ciò significa:

• Nessun piano di esecuzione nella cache da rivedere
• Nessuna statistica di esecuzione registrata in sys.dm_exec_query_stats
• Nessuna statistica di esecuzione registrata in sys.dm_exec_procedure_stats

Wow, che peccato. Se è necessario identificare l’impatto che questa procedura sta avendo sul server, è necessario eseguire una sorta di traccia o sessione di eventi estesi e raccogliere e interpretare i risultati. Non è veloce e l’esecuzione di traces può influire sulle prestazioni.

Tipo di grande negativo. Per questo motivo, non mi piace molto questa scelta.

RICOMPILA suggerimenti su singole istruzioni

Questa opzione è un po ‘ più di lavoro, ma ha un payoff molto migliore nel tempo. With this option you take the RECOMPILE hint and apply it only to statements in the stored procedure where you’d like to implement the recompile, like this:

Anche se è necessario utilizzare il suggerimento su tutte le istruzioni nella procedura piuttosto che nell’intestazione, questo ha ancora dei vantaggi! Mettendo il suggerimento ricompila a livello di istruzione nel proc, ottieni magicamente:

• Piani di esecuzione limitati nella cache da rivedere (ultima esecuzione)
• Statistiche di esecuzione limitate registrate in sys.dm_exec_query_stats. Otterrai solo statistiche per l’ultima esecuzione, ma la colonna plan_generation_num aumenterà, almeno dando un’idea che qualcosa è saltato.
• Statistiche di esecuzione registrate in sys.dm_exec_procedure_stats

Il fatto di ottenere alcune informazioni in questi DMV può essere super utile nel tempo. L’applicazione di suggerimenti ricompilare solo alle dichiarazioni che ne hanno bisogno è anche solo più responsabile– riduce il rischio di bruciarsi nel tempo con la masterizzazione della CPU.

DBCC FREEPROCCACHE – l’opzione nucleare

Questo non è strettamente un suggerimento di ricompilazione– ma certamente causa la ricompilazione. SQL Server ha un comando che puoi usare per dirgli “Ricomincia con nuovi piani di esecuzione”. Di solito è usato in questo modo:

This command makes everything start fresh– for every query:

• utilizzo della CPU potrebbe andare come nuovi piani sono compilati
• Esecuzione statistiche vengono cancellati in sys.dm_exec_query_stats (immediatamente)
• Esecuzione statistiche vengono cancellati in sys.dm_exec_procedure_stats (immediatamente)

Questo comando non è male, c’è un tempo e un luogo risoluzione dei problemi quando si utilizza può aiutare a ottenere la causa principale di un problema di prestazioni in fretta. Tuttavia, poiché influisce sull’intera cache e può anche influire sulle prestazioni, è necessario fare molta attenzione ad utilizzarla. NON è consigliabile utilizzare questo comando in processi regolari, processi automatizzati o codice di produzione.

A partire da SQL Server 2008, è possibile utilizzare questo comando un po ‘ più delicatamente– è possibile rimuovere un piano specifico dalla cache utilizzando “plan handle” o “sql handle”– ma ovviamente devi capire cos’è quell’handle e perché vuoi rimuoverlo. Questo potrebbe essere utile in alcune situazioni di nicchia, ma in pratica non viene molto. Puoi anche cancellare un pool di governor di risorse, ma dovresti usare resource governor.

Cosa significa tutto questo?

Quando stai testando, c’è un posto nel tuo toolkit sia per la procedura EXEC CON RICOMPILA che per la procedura sp_recompile. Usali con cura e fai attenzione alle possibili ripercussioni, in particolare con sp_recompile.

Quando stai implementando i suggerimenti nel codice, non hai i paraocchi RICOMPILATI– puoi usare anche i suggerimenti ‘OTTIMIZZA PER’ a volte con successo (anche se a volte stai ottimizzando per mediocri). E a volte, dynamic sql può anche aiutarti.

Ma se usi i suggerimenti di RICOMPILAZIONE, tieni le tue ricompilazioni a livello di istruzione e non nelle intestazioni delle tue procedure.

Per saperne di più, controlla i nostri fondamenti della classe di sniffing dei parametri.