Risolvere i problemi di blocco causati dai blocchi di compilazione

In questo articolo viene descritto come 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, in genere nella cache è presente una sola copia di un piano di stored procedure alla volta. L'applicazione di questa operazione richiede la serializzazione di alcune parti del processo di compilazione e questa sincronizzazione viene eseguita in parte utilizzando blocchi di compilazione. Se molte connessioni eseguono contemporaneamente la stessa stored procedure ed è necessario ottenere un blocco di compilazione per tale stored procedure ogni volta che viene eseguita, gli ID sessione (SPID) potrebbero iniziare a bloccarsi reciprocamente mentre tentano di ottenere un blocco di compilazione esclusivo per l'oggetto.

Di seguito sono riportate alcune caratteristiche tipiche del blocco di compilazione che possono essere osservate nell'output di blocco:

  • waittype per gli SPID di sessione bloccati e (in genere) di blocco è (esclusivo) e ha il formato , dove è LCK_M_X l'ID oggetto della stored waitresource OBJECT: dbid: object_id [[COMPILE]] object_id procedure.

  • I bloccanti waittype hanno NULL, lo stato può essere eseguito. I blocchi hanno waittype LCK_M_X (blocco esclusivo), lo stato di sospensione.

  • Anche se la durata dell'evento imprevisto di blocco può essere lunga, non esiste un singolo SPID che blocchi gli altri SPID per un lungo periodo di tempo. È in esecuzione un blocco in sequenza. Non appena una compilazione è stata completata, un altro SPID assume il ruolo di head blocker per diversi secondi o meno e così via.

Le informazioni seguenti derivano da uno snapshot sys.dm_exec_requests di 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]]

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

Ulteriori informazioni

La ricompilazione delle stored procedure è una spiegazione dei blocchi di compilazione su una stored procedure o un trigger. La soluzione in questo caso consiste nel ridurre o eliminare le ricompilazioni.

Scenari aggiuntivi che portano alla compilazione di blocchi

  1. La stored procedure viene eseguita senza nome completo

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

    Ad esempio, se l'utente dbo è proprietario dell'oggetto e un altro utente, , esegue la stored procedure utilizzando il comando , la ricerca iniziale nella cache per nome oggetto ha esito negativo perché l'oggetto non è qualificato dal dbo.mystoredproc Harry exec mystoredproc proprietario. Non è ancora noto se esiste un'altra stored procedure Harry.mystoredproc denominata. Pertanto, SQL Server non è possibile verificare che il piano memorizzato nella cache sia quello dbo.mystoredproc corretto da eseguire. SQL Server ottiene quindi un blocco di compilazione esclusivo sulla routine e prepara la compilazione della routine. Ciò include la risoluzione del nome dell'oggetto in un ID oggetto. Prima SQL Server il piano, SQL Server questo ID oggetto viene utilizzato per eseguire una ricerca più precisa nella cache delle procedure e può individuare un piano compilato in precedenza anche senza la qualificazione 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 eseguire una seconda ricerca nella cache e ottenere un blocco di compilazione esclusivo prima che il programma determina che il piano di esecuzione memorizzato nella cache esistente può essere riutilizzato. L'ottenimento del blocco e l'esecuzione di ricerche e altre operazioni necessarie per raggiungere questo punto possono introdurre un ritardo per i blocchi di compilazione che causano il blocco. Ciò è particolarmente vero se molti utenti che non sono proprietari della stored procedure eseguono contemporaneamente la routine senza specificare il nome del proprietario. Anche se gli SPID non sono in attesa di blocchi di compilazione, la mancanza di qualificazione del proprietario può comportare ritardi nell'esecuzione delle stored procedure e causare un utilizzo elevato della CPU.

    La sequenza di eventi seguente verrà registrata in una sessione SQL Server evento esteso 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 per nome ha esito negativo, ma alla fine 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 nel verificare che i riferimenti alle stored procedure siano qualificati dal proprietario. Invece di mystoredproc exec, usa exec dbo.mystoredproc .) Sebbene la qualificazione del proprietario sia importante per motivi di prestazioni, non è necessario qualificare la stored procedure con il nome del database per impedire la ricerca nella cache aggiuntiva.

    Il blocco causato dai blocchi di compilazione può essere rilevato utilizzando i metodi di risoluzione dei problemi di blocco standard.

  2. La stored procedure è preceduta da sp_

    Se il nome della stored procedure inizia con il prefisso e non si trova nel database master, verrà visualizzato sp_cache_miss prima dell'hit della cache per ogni esecuzione, anche se la stored procedure è stata qualificata sp_ dal proprietario. Questo perché il prefisso indica SQL Server la stored procedure è una stored procedure di sistema e le stored procedure di sistema hanno regole di risoluzione dei nomi sp_ diverse. La posizione preferita si trova nel database master. I nomi delle stored procedure create dall'utente non devono iniziare con sp_ .

  3. La stored procedure viene richiamata utilizzando un'altra distinzione tra maiuscole e minuscole (superiore/inferiore)

    Se una routine qualificata dal proprietario viene eseguita utilizzando un caso diverso (superiore o inferiore) rispetto al caso utilizzato per crearla, la routine può attivare un evento CacheMiss o richiedere un blocco COMPILE. Infine, la routine utilizza 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 se sono presenti molti SPID che tentano di eseguire la stessa routine utilizzando un caso diverso da quello utilizzato per crearla. Ciò vale indipendentemente dall'ordinamento o dalle regole di confronto utilizzate nel server o nel database. Il motivo di questo comportamento è che l'algoritmo utilizzato 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 alternativa consiste nell'eliminare e creare la routine utilizzando lo stesso caso utilizzato quando l'applicazione esegue la routine. È inoltre possibile assicurarsi che la routine sia eseguita da tutte le applicazioni utilizzando la distinzione tra maiuscole e minuscole corretta.

  4. La stored procedure viene richiamata come evento Language

    Se si tenta di eseguire una stored procedure come evento del linguaggio anziché come RPC, SQL Server deve analizzare e compilare la query degli eventi del linguaggio, determinare che la query sta tentando di eseguire la routine specifica e quindi tentare di trovare un piano nella cache per tale routine. Per evitare questa situazione in cui SQL Server deve analizzare e compilare l'evento del linguaggio, assicurarsi che la query venga inviata SQL come RPC.

    Per ulteriori informazioni, vedere la sezione System Stored Procedures nell'articolo della documentazione online Creating a Stored Procedure.

Riferimenti

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