Essendo giorno 12 del comando DBCC mese a SteveStedman.com, il comando DBCC descritto oggi è DBCC FREEPROCCACHE.
Descrizione:
DBCC FREEPROCCACHE viene utilizzato per eliminare tutti i piani di query analizzati dalla memoria. Questo è comunemente usato negli ambienti di sviluppo, ma non così comune in un ambiente di produzione.
L’uso in un ambiente di sviluppo è comune, ad esempio quando si lavora sulla sintonizzazione delle prestazioni o sulla parametrizzazione delle query. È possibile cancellare la cache della procedura con DBCC FreeProcCache, eseguire il programma o la pagina Web che potrebbe utilizzare il database, quindi vedere cosa c’è nella cache della procedura. Questo può essere utile per trovare query che potrebbero dover essere parametrizzate. Un altro modo per utilizzare sarebbe quello di scoprire quali query vengono eseguite da qualche programma. Per fare ciò inizieresti lavorando con un database che non viene utilizzato da altri, cancellando la cache delle procedure con DBCC FreeProcCache, quindi esegui il programma che stai cercando di capire, quindi guarda cosa c’è nella cache, di nuovo questo è qualcosa che potrebbe essere fatto in un ambiente di sviluppo o test, ma non consiglierei di farlo in produzione.
L’uso in un ambiente di produzione dovrebbe essere raro, questa è una delle cose comuni da provare quando SQL Server ha difficoltà. Se sei il punto che SQL Server è estremamente lento a rispondere e non sei stato in grado di trovare la causa, una cosa da provare è liberare la cache della procedura con DBCC FreeProcCache e vedere se questo risolve il problema.
Sintassi DBCC FreeProcCache:
dbcc freeproccache
Esempio:
Il seguente esempio proviene da un ambiente di sviluppo che utilizza il database AdventureWorks2012.
Per prima cosa ci colleghiamo a AdventureWorks2012 e vediamo cosa c’è nella cache.
USE AdventureWorks2012;GOSELECT size_in_bytes, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;
Qui vediamo che c’è molto nella cache. Successivamente puliamo la cache con DBCC FreeProcCache e diamo un’altra occhiata a ciò che è nella cache.
DBCC FREEPROCCACHE;SELECT size_in_bytes, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;
Dopo aver eseguito DBCC FreeProcCache puoi vedere che non è rimasto nulla nella cache.
Quando viene eseguita la query successiva, sarà necessario analizzarla anziché utilizzare una query già analizzata nella cache. Questo richiederà un po ‘ più di tempo rispetto a se ci fosse già un piano analizzato da eseguire. Consente di eseguire 3 query, quindi dare un’occhiata alla cache.
GOSELECT FirstName, MiddleName, LastName FROM person.Person p WHERE FirstName like 'John';GOSELECT FirstName, MiddleName, LastName FROM person.Person p WHERE FirstName like 'Mary';GOSELECT FirstName, MiddleName, LastName FROM person.Person p WHERE FirstName like 'Bill';
Si noti l’istruzione GO tra ogni query. Questo indica a SSMS di eseguire ogni query come batch separato. Senza l’istruzione GO, le 3 query sarebbero state analizzate come un singolo batch.
Qui vediamo i risultati delle tre query. I primi due hanno restituito i risultati e il terzo non aveva righe nel set di risultati. Ora diamo un’occhiata alla cache
SELECT size_in_bytes, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;
Di seguito vediamo un totale di 20 elementi nella cache ora. l’elemento superiore evidenziato in blu è la query che abbiamo usato per vedere cosa c’era nella cache, il secondo blocco evidenziato in rosso contiene le 3 query dall’alto e il terzo resto sono query eseguite da SQL o altre query di supporto. Ad esempio, la riga 13 è dm_exec_sql_text che viene chiamata dalla query sopra che controlla il piano.
Se volessimo filtrare questo solo per le query che avevamo scritto, potresti farlo aggiungendo un testo WHERE COME clause clausola alla query come mostrato qui.
SELECT size_in_bytes, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS stWHERE text LIKE N'SELECT FirstName%';
Qui vediamo che vengono visualizzate solo le tre query e che ognuna di queste tre occupa circa 40K di memoria su SQL Server. 
Quindi perché ci sono tre copie della stessa istruzione SELECT, questo sembra un po ‘ dispendioso. In effetti, per ulteriori informazioni, vedere un post precedente chiamato Quanta memoria cache di procedura occupa un’istruzione SQL? Ci sono modi per correggere questo.
Utilizzo di DBCC FreeProcCache per un handle di piano specifico
Se si desidera cancellare solo un handle di piano singolo e non tutti gli handle di piano, è possibile utilizzare il parametro opzionale chiamato @handle.
Per ottenere l’handle del piano, iniziamo modificando la nostra query precedente per mostrarci cosa c’è nella cache del piano. Potresti tralasciare la clausola WHERE sul tuo sistema, ma ce l’ho qui per mostrarci solo le tre domande in questione dall’alto.
SELECT size_in_bytes, text, plan_handle FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS stWHERE text LIKE N'SELECT FirstName%';
Qui vediamo gli stessi tre piani di query precedenti, con una colonna di aggiunta chiamata plan_handle. Per liberare un singolo handle del piano, basta copiare l’handle numerico del piano e aggiungerlo alla query DBCC FreeProcCache.
DBCC FREEPROCCACHE(0x060007000100FF3310B8DA7D0600000001000000000000000000000000000000000000000000000000000000);SELECT size_in_bytes, text, plan_handle FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS stWHERE text LIKE N'SELECT FirstName%';
Dove vediamo solo 2 delle tre query originali nella cache del piano.
Quanto è grande la cache delle procedure?
È possibile eseguire la seguente query per verificare la dimensione della cache della procedura.
SELECT count(*) AS NumPlans, objtype as Type,SUM(size_in_bytes)/(1024.0*1024.0) AS size_in_mbFROM sys.dm_exec_cached_plansGROUP BY objtype;
Che produce i seguenti risultati sul mio server di test.
Rapporti sullo stato del database e la cache del piano
È inoltre possibile visualizzare la cache del piano utilizzando l’applicazione Rapporti sullo stato del database come mostrato qui.
Note:
Per ulteriori informazioni vedere TSQL Wiki DBCC freeproccache.
DBCC Comando mese a SteveStedman.com è divertente quasi quanto mangiare gelatina.
Steve e il team di Stedman Solutions sono qui per tutte le esigenze di SQL Server.
Contct noi oggi per il vostro libero 30 minuti di consultazione..
Siamo pronti ad aiutare!
