Risolvere i problemi di blocco causati dai blocchi di compilazione

Questo articolo descrive come risolvere e risolvere i problemi di blocco causati dai blocchi di compilazione.

Versione originale del prodotto: SQL Server
Numero KB originale: 263889

Riepilogo

In Microsoft SQL Server una sola copia di un piano di stored procedure è in genere memorizzata nella cache alla volta. L'applicazione di questa operazione richiede la serializzazione di alcune parti del processo di compilazione e questa sincronizzazione viene eseguita in parte usando blocchi di compilazione. Se molte connessioni eseguono simultaneamente la stessa stored procedure e un blocco di compilazione deve essere ottenuto per tale stored procedure ogni volta che viene eseguita, gli ID sessione (SPID) potrebbero iniziare a bloccarsi uno all'altro mentre tentano di ottenere un blocco di compilazione esclusivo sull'oggetto.

Di seguito sono riportate alcune caratteristiche tipiche del blocco di compilazione che è possibile osservare nell'output di blocco:

• waittypeper gli SPID di sessione bloccati e (in genere) bloccanti è (esclusivo) ed waitresource è LCK_M_X del formato OBJECT: dbid: object_id [[COMPILE]], dove object_id è l'ID oggetto della stored procedure.

• I blocker hanno waittype NULL, stato eseguibile. Le sessioni bloccate hanno waittypeLCK_M_X (blocco esclusivo), lo stato è in sospensione.

• Anche se la durata complessiva dell'evento imprevisto di blocco potrebbe essere lunga, non esiste una singola sessione (SPID) che blocca gli altri SPID per molto tempo. C'è blocco in sequenza; non appena viene completata una compilazione, un altro SPID assume il ruolo di blocco head per diversi secondi o meno e così via.

Le informazioni seguenti provengono da uno snapshot di sys.dm_exec_requests durante questo tipo di blocco:

session_id   blocking_session_id   wait_type   wait_time   waitresource 
----------   -------------------   ---------   ---------   ----------------------------
221           29                   LCK_M_X     2141        OBJECT: 6:834102 [[COMPILE]]
228           29                   LCK_M_X     2235        OBJECT: 6:834102 [[COMPILE]]
29            214                  LCK_M_X     3937        OBJECT: 6:834102 [[COMPILE]]
13            214                  LCK_M_X     1094        OBJECT: 6:834102 [[COMPILE]]
68            214                  LCK_M_X     1968        OBJECT: 6:834102 [[COMPILE]]
214           0                    LCK_M_X     0           OBJECT: 6:834102 [[COMPILE]]

waitresource Nella colonna (6:834102), 6 è l'ID del database e 834102 è l'ID oggetto. Questo ID oggetto appartiene a una stored procedure, non a una tabella.

Scenari che portano alla compilazione di blocchi

Gli scenari seguenti descrivono le cause dei blocchi di compilazione esclusivi nelle stored procedure o nei trigger.

La stored procedure viene eseguita senza nome completo

• L'utente che esegue la stored procedure non è il proprietario della procedura.
• Il nome della stored procedure non è completo con il nome del proprietario dell'oggetto.

Ad esempio, se l'utente dbo possiede l'oggetto dbo.mystoredproc e un altro utente, Harry, esegue questa stored procedure usando il comando exec mystoredproc, la ricerca della cache iniziale per nome oggetto ha esito negativo perché l'oggetto non è qualificato dal proprietario. Non è ancora noto se esiste un'altra stored procedure denominata Harry.mystoredproc . Di conseguenza, SQL Server non è sicuro che il piano memorizzato nella cache per dbo.mystoredproc sia quello corretto da eseguire. SQL Server ottiene quindi un blocco di compilazione esclusivo sulla procedura ed esegue le operazioni di preparazione per compilare la procedura. Ciò include la risoluzione del nome dell'oggetto in un ID oggetto. Prima che SQL Server compili il piano, SQL Server usa questo ID oggetto per eseguire una ricerca più precisa della cache delle procedure e può individuare un piano compilato in precedenza anche senza qualifica del proprietario.

Se viene trovato un piano esistente, SQL Server riutilizza il piano memorizzato nella cache e non compila effettivamente la stored procedure. Tuttavia, la mancanza di qualificazione del proprietario forza SQL Server a eseguire una seconda ricerca nella cache e ottenere un blocco di compilazione esclusivo prima che il programma determini che il piano di esecuzione memorizzato nella cache esistente possa essere riutilizzato. Ottenere il blocco ed eseguire ricerche e altri lavori necessari per raggiungere questo punto può introdurre un ritardo per i blocchi di compilazione che causano il blocco. Ciò vale soprattutto se molti utenti che non sono il proprietario della stored procedure, eseguono contemporaneamente la procedura senza specificare il nome del proprietario. Anche se non vengono visualizzati SPID in attesa di blocchi di compilazione, la mancanza di qualificazione del proprietario può introdurre ritardi nell'esecuzione della stored procedure e causare un utilizzo elevato della CPU.

La sequenza di eventi seguente viene registrata in una sessione di eventi estesi di SQL Server quando si verifica questo problema.

Nome evento	Testo
rpc_starting	mystoredproc
sp_cache_miss	mystoredproc
sql_batch_starting	mystoredproc
sp_cache_hit	mystoredproc
...	...

sp_cache_miss si verifica quando la ricerca nella cache in base al nome ha esito negativo, ma un piano memorizzato nella cache corrispondente è stato trovato nella cache dopo che il nome dell'oggetto ambiguo è stato risolto in un ID oggetto ed è presente un sp_cache_hit evento.

La soluzione a questo problema di blocco della compilazione consiste nell'assicurarsi che i riferimenti alle stored procedure siano qualificati dal proprietario. (Invece di exec mystoredproc, usare exec dbo.mystoredproc.) Anche se la qualificazione del proprietario è importante per motivi di prestazioni, non è necessario qualificare la stored proc con il nome del database per impedire la ricerca nella cache aggiuntiva.

È possibile rilevare il blocco causato dai blocchi di compilazione usando metodi standard di risoluzione dei problemi di blocco.

La stored procedure viene ricompilata frequentemente

La ricompilazione è una spiegazione per i blocchi di compilazione in una stored procedure o un trigger. I modi per fare in modo che una stored procedure ricompila includa EXECUTE... WITH RECOMPILE, CREATE PROCEDURE ...WITH RECOMPILEo usando sp_recompile. Per altre informazioni, vedere Ricompilare una stored procedure. In questo caso, la soluzione consiste nel ridurre o eliminare la ricompilazione.

La stored procedure è preceduta da sp_**

Se il nome della stored procedure inizia con il sp_ prefisso e non si trova nel database master, viene visualizzato sp_cache_miss prima dell'hit della cache per ogni esecuzione anche se si qualifica la stored procedure. Questo perché il sp_ prefisso indica a SQL Server che la stored procedure è una stored procedure di sistema e le stored procedure di sistema hanno regole di risoluzione dei nomi diverse. Il percorso preferito si trova nel database master. I nomi delle stored procedure create dall'utente non devono iniziare con sp_.

La stored procedure viene richiamata usando un caso diverso (superiore /lower)

Se una procedura qualificata dal proprietario viene eseguita usando una lettera diversa (maiuscola o inferiore) dal caso usato per crearla, la procedura può attivare un evento CacheMiss o richiedere un blocco COMPILE. Per illustrare, notare il caso di lettera diverso usato in CREATE PROCEDURE dbo.SalesData ... e EXEC dbo.salesdata. Infine, la procedura usa il piano memorizzato nella cache e non viene ricompilata. Tuttavia, la richiesta di un blocco COMPILE può talvolta causare una situazione di catena di blocco descritta in precedenza. La catena di blocco può verificarsi se sono presenti molte sessioni (SPID) che tentano di eseguire la stessa procedura usando un caso diverso rispetto al caso usato per crearlo. Questo vale indipendentemente dall'ordinamento o dalle regole di confronto utilizzate nel server o nel database. Il motivo di questo comportamento è che l'algoritmo usato per trovare la routine nella cache si basa sui valori hash (per le prestazioni) e i valori hash possono cambiare se il caso è diverso.

La soluzione consiste nell'eliminare e creare la procedura usando la stessa lettera maiuscola usata quando l'applicazione esegue la procedura. È anche possibile assicurarsi che la procedura venga eseguita da tutte le applicazioni usando la distinzione tra maiuscole e minuscole (superiore o inferiore).

La stored procedure viene richiamata come evento Language

Se si tenta di eseguire una stored procedure come evento di linguaggio anziché come RPC, SQL Server deve analizzare e compilare la query dell'evento del linguaggio, determinare che la query sta tentando di eseguire la procedura specifica e quindi provare a trovare un piano nella cache per tale procedura. Per evitare questa situazione in cui SQL Server deve analizzare e compilare l'evento del linguaggio, assicurarsi che la query venga inviata a SQL Server come RPC. Ad esempio, nel codice .NET è possibile usare SqlCommand.CommandType.StoredProcedure per garantire un evento RPC.

Stored procedure o sp_executesql usa un parametro stringa maggiore di 8 KB

Se si chiama una stored procedure o sp_executesql e si passa un parametro stringa maggiore di 8 KB, SQL Server usa un tipo di dati BLOB (Binary Large Object) per archiviare il parametro. Di conseguenza, il piano di query per questa esecuzione non viene salvato in modo permanente nella cache dei piani. Pertanto, ogni esecuzione della stored procedure o sp_executesql deve acquisire un blocco di compilazione per compilare un nuovo piano. Questo piano viene rimosso al termine dell'esecuzione. Per altre informazioni, vedere la nota nella memorizzazione nella cache del piano di esecuzione e il riutilizzo relativi ai valori letterali stringa maggiori di 8 KB. Per evitare il blocco di compilazione in questo scenario, ridurre le dimensioni del parametro a meno di 8 KB.

Riferimenti

Il comando OPEN SYMMETRIC KEY impedisce la memorizzazione nella cache del piano di query