Descrizione dei blocchi di SQL Server causato da blocchi di compilazione

Traduzione articoli Traduzione articoli
Identificativo articolo: 263889 - Visualizza i prodotti a cui si riferisce l?articolo.
Espandi tutto | Chiudi tutto

In questa pagina

Sommario

In Microsoft SQL Server, solo una copia di un piano per la stored procedure è generalmente nella cache alla volta. Questa applicazione richiede la serializzazione di alcune parti del processo di compilazione e la sincronizzazione viene eseguita in parte utilizzando blocchi di compilazione. Molte connessioni contemporaneamente in esecuzione della stessa stored procedure e un blocco di compilazione deve essere ottenuto per stored procedure ogni volta che viene eseguito, il processo di sistema (SPID) ID potrebbe iniziare bloccare un altro come ognuna di esse tenta di ottenere un blocco di compilazione esclusivo sull'oggetto.

Informazioni

Ricompilazione di stored procedure è una spiegazione di blocchi di compilazione di una stored procedure o trigger. La soluzione in questo caso è quella di ridurre o eliminare le ricompilazioni. Per una spiegazione dei motivi più comuni che potrebbe dover ricompilare una stored procedure e informazioni utili sulla riduzione della frequenza di ricompilazioni, vedere il seguente articolo della Microsoft Knowledge Base:
243586Risoluzione dei problemi di ricompilazione delle stored procedure
Un altro scenario in cui si verificano blocchi di compilazione è quando si verificano le condizioni seguenti:
  • L'utente che esegue la stored procedure non è il proprietario della procedura.
  • Il nome della stored procedure non è completamente qualificato con il nome del proprietario dell'oggetto.
Ad esempio, se l'utente "dbo" proprietario oggettodbo.MyStoredProce un altro utente, "Harry", viene eseguita la stored procedure utilizzando il comando "exec mystoredproc," la ricerca iniziale della cache dall'oggetto nome si verifica un errore perché l'oggetto non è qualificato come proprietario. (Non è ancora noto se esiste un'altra stored procedure denominata Harry.mystoredproc. SQL Server non può pertanto che il piano memorizzato nella cache per dbo.mystoredproc sia quello corretto per l'esecuzione.) SQL Server quindi Ottiene un blocco esclusivo compilazione sulla procedura e rende i preparativi per compilare la procedura. Ciò include la risoluzione del nome di oggetto per un ID di oggetto. Prima di SQL Server compila il piano, SQL Server utilizza l'ID di oggetto per eseguire una ricerca più precisa della cache delle procedure e individuare un piano compilato in precedenza anche senza qualificazione proprietario.

Se viene trovato un piano esistente, SQL Server riutilizza il piano nella cache e non effettivamente la compilazione della stored procedure. Tuttavia, la mancanza di qualificazione proprietario impone SQL Server per eseguire una ricerca nella cache seconda e ottenere un blocco esclusivo di compilazione prima che il programma determina che è possibile riutilizzare il piano di esecuzione nella cache esistente. Ottenendo il blocco e l'esecuzione di ricerche e altre operazioni necessarie per raggiungere questo punto può causare un ritardo per i blocchi di compilazione che conduce al blocco. Questo è particolarmente vero se molti utenti non proprietario della stored procedure eseguita simultaneamente la procedura senza specificare il nome del proprietario. Tenere presente che, anche se non viene visualizzato in attesa di blocchi di compilazione SPID, mancanza di qualificazione proprietario può introdurre ritardi nell'esecuzione di stored procedure e causare inutilmente elevato utilizzo della CPU.

The following sequence of events will be recorded in a SQL Server Profiler trace when this problem occurs. (To trace cache-related events, you must enable advanced events. To do this, clickOptionson theStrumentimenu, and then selectAll event classes.)

Riduci questa tabellaEspandi questa tabella
Event classText
RPC:Startingmystoredproc
SP:CacheMissmystoredproc
SP:ExecContextHitmystoredproc
SP:Startingmystoredproc
......

SP:CacheMissoccurs when the cache lookup by name fails. The followingSP:ExecContextHitindicates that a matching cached plan was ultimately found in cache after the ambiguous object name was resolved to an object ID. Depending on the circumstances,SP:CacheHitmay appear instead ofSP:ExecContextHit.

The solution to this problem of compile locking is to make sure that references to stored procedures are owner-qualified. (Instead ofexec mystoredproc, use execdbo.mystoredproc.) While owner-qualification is important for performance reasons, you do not have to qualify the stored proc with the database name to prevent the additional cache lookup.

Blocking that is caused by compile locks can be detected by using blocking scripts such as those that are defined in the following Microsoft Knowledge Base articles:
251004INF: How to monitor SQL Server 7.0 blocking
271509INF: How to monitor SQL Server 2000 blocking
The following are some typical characteristics of compile blocking that can be observed in the blocking script output:
  • lastwaittypefor the blocked and (usually) blocking SPIDs is LCK_M_X (exclusive) andwaitresourceis of the form "TAB: dbid:object_id[[COMPILE]]," where "object_id" is the object ID of the stored procedure.
  • Blockers havewaittype0x0000, status runnable. Blockees havewaittype0x000e (exclusive lock), status sleeping.
  • Although the duration of the blocking incident may be long, there is no single SPID that is blocking the other SPIDs for a long time. There is rolling blocking. As soon as one compilation is complete, another SPID takes over the role of head blocker for a several seconds or less, and so on.
The following information is from a snapshot ofsysprocessesduring this kind of blocking:
   spid  blocked  waittype  waittime  lastwaittype  waitresource
   ----  -------  --------  --------  ------------  -------------------------
   
   221    29      0x000e    2141      LCK_M_X       TAB: 6:834102 [[COMPILE]]
   228    29      0x000e    2235      LCK_M_X       TAB: 6:834102 [[COMPILE]]
    29   214      0x000e    3937      LCK_M_X       TAB: 6:834102 [[COMPILE]]
    13   214      0x000e    1094      LCK_M_X       TAB: 6:834102 [[COMPILE]]
    68   214      0x000e    1968      LCK_M_X       TAB: 6:834102 [[COMPILE]]
   214     0      0x0000       0      LCK_M_X       TAB: 6:834102 [[COMPILE]]
Nelwaitresourcecolumn ("6:834102"), 6 is the database ID and 834102 is the object ID. Be aware that this object ID belongs to a stored procedure, not to a table (despite the "TAB" lock type).

Note
  • If you are using SQL Server 2005, many of the system tables from SQL Server 2000 are now implemented as a set of views. These views are known as compatibility views, and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available in SQL Server 2000. For more information about mapping between the SQL Server 2000 system tables and the SQL Server 2005 system views, see the "Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views" topic in SQL Server 2005 Books Online.
  • If your stored procedure name starts with the "sp_" prefix and is not in the master database, you seeSP:CacheMissbefore the cache hit for each execution even if you owner-qualify the stored procedure. This is because the sp_ prefix tells SQL Server that the stored procedure is a system stored procedure, and system stored procedures have different name resolution rules. (The "preferred" location is in the master database.) The names of user-created stored procedures should not start with "sp_".
  • If an owner-qualified procedure is executed with a different case than the owner-qualified procedure was created as, the owner-qualified procedure can obtain aCacheMissor request a COMPILE lock but eventually use the cached plan. Therefore, this would not actually recompile the procedure and should not cause much of an overhead. But in certain situations, the request for a COMPILE lock can cause a "blocking chain" situation if there are many SPIDs trying to execute the same procedure with a different case than the procedure was created as. This is true regardless of the sort order or collation that is being used on the server or on the database. The reason for this behavior is that the algorithm that is being used to find the procedure in cache is based on hash values (for performance reasons), which can change if the case is different.

    The workaround is to drop and create the procedure with the same case as the procedure is executed by the application. You can also make sure that the procedure is executed from all applications that use the same case.
  • If you try to execute a stored procedure as a Language Event instead of as an RPC, SQL Server must parse and compile the language event query, determine that the query is trying to execute the particular procedure, and then try to find a plan in cache for that procedure. To avoid this situation in which SQL Server must parse and compile the language event, make sure that the query is sent to SQL as an RPC.

    For more information, see the "System Stored Procedures" section in the Books Online article "Creating a Stored Procedure."


Known issues

Here are some known issues that can prevent plan caching:
  • You use BLOB variables as a Stored Procedure parameter. Per ulteriori informazioni, fare clic sul numero dell'articolo della Microsoft Knowledge Base:
    2380435FIX: The query plan for a stored procedure is not cached if the stored procedure uses a BLOB variable and the variable is used in a string function in Microsoft SQL Server 2008
  • You use OPEN SYMMETRIC KEY in a Stored Procedure/Query Batch. For more information, see the following MSDN blog entry:
    http://blogs.msdn.com/b/sqlserverfaq/Archive/2010/09/08/Open-Symmetric-Key-Command-Prevents-query-plan-Caching.aspx

Proprietà

Identificativo articolo: 263889 - Ultima modifica: mercoledì 24 novembre 2010 - Revisione: 1.0
Le informazioni in questo articolo si applicano a:
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Chiavi: 
kbinfo kbmt KB263889 KbMtit
Traduzione automatica articoli
Il presente articolo è stato tradotto tramite il software di traduzione automatica di Microsoft e non da una persona. Microsoft offre sia articoli tradotti da persone fisiche sia articoli tradotti automaticamente da un software, in modo da rendere disponibili tutti gli articoli presenti nella nostra Knowledge Base nella lingua madre dell?utente. Tuttavia, un articolo tradotto in modo automatico non è sempre perfetto. Potrebbe contenere errori di sintassi, di grammatica o di utilizzo dei vocaboli, più o meno allo stesso modo di come una persona straniera potrebbe commettere degli errori parlando una lingua che non è la sua. Microsoft non è responsabile di alcuna imprecisione, errore o danno cagionato da qualsiasi traduzione non corretta dei contenuti o dell?utilizzo degli stessi fatto dai propri clienti. Microsoft, inoltre, aggiorna frequentemente il software di traduzione automatica.
Clicca qui per visualizzare la versione originale in inglese dell?articolo: 263889
LE INFORMAZIONI CONTENUTE NELLA MICROSOFT KNOWLEDGE BASE SONO FORNITE SENZA GARANZIA DI ALCUN TIPO, IMPLICITA OD ESPLICITA, COMPRESA QUELLA RIGUARDO ALLA COMMERCIALIZZAZIONE E/O COMPATIBILITA' IN IMPIEGHI PARTICOLARI. L'UTENTE SI ASSUME L'INTERA RESPONSABILITA' PER L'UTILIZZO DI QUESTE INFORMAZIONI. IN NESSUN CASO MICROSOFT CORPORATION E I SUOI FORNITORI SI RENDONO RESPONSABILI PER DANNI DIRETTI, INDIRETTI O ACCIDENTALI CHE POSSANO PROVOCARE PERDITA DI DENARO O DI DATI, ANCHE SE MICROSOFT O I SUOI FORNITORI FOSSERO STATI AVVISATI. IL DOCUMENTO PUO' ESSERE COPIATO E DISTRIBUITO ALLE SEGUENTI CONDIZIONI: 1) IL TESTO DEVE ESSERE COPIATO INTEGRALMENTE E TUTTE LE PAGINE DEVONO ESSERE INCLUSE. 2) I PROGRAMMI SE PRESENTI, DEVONO ESSERE COPIATI SENZA MODIFICHE, 3) IL DOCUMENTO DEVE ESSERE DISTRIBUITO INTERAMENTE IN OGNI SUA PARTE. 4) IL DOCUMENTO NON PUO' ESSERE DISTRIBUITO A SCOPO DI LUCRO.

Invia suggerimenti

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com