Risolvere i problemi di blocco causati dai blocchi di compilazione

  • Articolo

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 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 tramite blocchi di compilazione. Se molte connessioni eseguono contemporaneamente la stessa stored procedure e deve essere ottenuto un blocco di compilazione per tale stored procedure ogni volta che viene eseguita, gli ID sessione (SPID) potrebbero iniziare a bloccarsi a vicenda mentre ognuno tenta di ottenere un blocco di compilazione esclusivo sull'oggetto.

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

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

  • I blocchi hanno waittype NULL, stato eseguibile. I blocchi hanno waittypeLCK_M_X (blocco esclusivo), stato di sospensione.

  • Anche se la durata dell'evento imprevisto di blocco può essere lunga, non esiste un singolo SPID che blocca gli altri SPID per un lungo periodo di tempo. C'è un blocco in sequenza. Non appena una compilazione viene completata, un altro SPID assume il ruolo di blocco della testa 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.

Ulteriori informazioni

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

Scenari aggiuntivi che comportano la 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 di un oggetto dbo.mystoredproc e un altro utente, Harry, esegue questa stored procedure usando il comando exec mystoredproc, la ricerca iniziale della cache in base al nome dell'oggetto ha esito negativo perché l'oggetto non è qualificato dal proprietario. (Non è ancora noto se esiste un'altra stored procedure denominataHarry.mystoredproc. Pertanto, SQL Server non può essere sicuro che il piano memorizzato nella cache per dbo.mystoredproc sia quello corretto da eseguire.) SQL Server quindi ottiene 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 di SQL Server compilare 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 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 di 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 può essere riutilizzato. Il recupero 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 comportano il blocco. Ciò è particolarmente vero se molti utenti che non sono proprietari della stored procedure eseguono contemporaneamente la procedura 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ò introdurre ritardi nell'esecuzione della stored procedure e causare un utilizzo elevato della CPU.

    La sequenza di eventi seguente verrà registrata in una sessione di eventi estesi 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 per nome ha esito negativo, ma un piano memorizzato nella cache corrispondente è stato infine 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. Anziché exec mystoredproc, usare exec dbo.mystoredproc. Anche se la qualificazione del proprietario è importante per motivi di prestazioni, non è necessario qualificare la procedura archiviata con il nome del database per impedire la ricerca nella cache aggiuntiva.

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

  2. 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 del riscontro della cache per ogni esecuzione, anche se si qualifica la stored procedure come proprietario. Questo perché il sp_ prefisso indica SQL Server che la stored procedure è una stored procedure di sistema e che le stored procedure di sistema hanno regole di risoluzione dei nomi 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 usando un caso diverso (superiore /inferiore)

    Se una routine qualificata dal proprietario viene eseguita usando un caso diverso (superiore o inferiore) dal caso usato per crearla, la routine può attivare un evento CacheMiss o richiedere un blocco COMPILE. Alla fine, 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 se sono presenti molti SPID che tentano di eseguire la stessa procedura usando un caso diverso da quello usato per crearlo. Ciò vale indipendentemente dall'ordinamento o dalle regole di confronto usate nel server o nel database. Il motivo di questo comportamento è che l'algoritmo usato per trovare la routine nella cache è basato 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 usando lo stesso caso usato quando l'applicazione esegue la routine. È anche possibile assicurarsi che la procedura venga eseguita da tutte le applicazioni usando la maiuscola o la minuscola 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 stia tentando di eseguire la routine specifica e quindi cercare 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 a SQL come RPC.

    Per altre informazioni, vedere la sezione Stored procedure di sistema nell'articolo Creazione di una stored procedure nella documentazione online.

Riferimenti

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