ID do artigo: 263889 - Última revisão: quarta-feira, 24 de novembro de 2010 - Revisão: 1.0

Descrição do SQL Server de bloqueio causados por bloqueios de compilação

Vista lado a ladoClique aqui para exibir o artigo traduzido e o artigo original em inglês, lado a lado.
Tradução automáticaExibir Aviso de Isenção de Tradução Automática
Exibir os produtos aos quais esse artigo se aplica.
Dica do SistemaEste artigo aplica-se a um sistema operativo diferente do que está a utilizar. Foi desactivado o conteúdo do artigo, que pode não ser relevante para si.

Nesta página

Expandir tudo | Recolher tudo

Sumário

No Microsoft SQL Server, somente uma cópia de um plano de procedimento armazenado costuma no cache ao mesmo tempo. Impor isso requer a serialização de algumas partes do processo de compilação e a sincronização é realizada em parte pelo uso de bloqueios de compilação. Se o número máximo de conexões ao mesmo tempo estejam executando o mesmo procedimento armazenado e um bloqueio de compilação deve ser obtido para esse procedimento armazenado toda vez que ele está sendo executado, IDs (SPIDs) de processo do sistema pode começar a bloquear um ao outro à medida que cada um deles tenta obter um bloqueio exclusivo de compilação no objeto.
Voltar para o início

Mais Informações

Recompilação do procedimento armazenado é uma explicação para bloqueios de compilação em um disparador ou procedimento armazenado. Nesse caso, a solução é reduzir ou eliminar as recompilações. Para obter uma explicação das razões mais comuns para um procedimento armazenado talvez precise ser recompilado e algumas informações úteis na redução da freqüência de recompilações, consulte o seguinte artigo da Base de dados de Conhecimento da Microsoft:
243586  (http://support.microsoft.com/kb/243586/ ) Solucionando problemas de recompilação do procedimento armazenado
Outro cenário em que os bloqueios de compilação ocorrerem é quando as seguintes condições forem verdadeiras:
  • O usuário que executa o procedimento armazenado não é o proprietário do procedimento.
  • O nome do procedimento armazenado não é totalmente qualificado com o nome do proprietário do objeto.
Por exemplo, se o usuário "dbo" é o proprietário de objetodbo.mystoredproce "Jaime", de outro usuário executa esse procedimento armazenado usando o comando "exec mystoredproc," a pesquisa de cache inicial por falha de nome de objeto porque o objeto não está qualificada por proprietário. (Ele ainda não é conhecido se existe outro procedimento armazenado denominado Harry.mystoredproc. Portanto, do SQL Server não pode ser-se de que o plano armazenado em cache para dbo.mystoredproc é o correto para execução.) SQL Server, em seguida, obtém um bloqueio exclusivo de compilação no procedimento e faz com que as preparações para compilar o procedimento. Isso inclui a resolução do nome de objeto para uma identificação de objeto. Antes do SQL Server compila o plano, o SQL Server usa a identificação de objeto para realizar uma pesquisa mais precisa do cache de procedimento e possa localizar um plano compilado anteriormente, mesmo sem qualificação do proprietário.

Se um plano existente for encontrado, o SQL Server reutiliza o plano em cache e, na verdade, não compila o procedimento armazenado. No entanto, a falta de qualificação de proprietário força do SQL Server para executar uma segunda pesquisa de cache e obter um bloqueio exclusivo de compilação antes do programa determina que o plano de execução de cache existente pode ser reutilizado. Como obter o bloqueio e executar pesquisas e qualquer outro trabalho necessário para alcançar esse ponto podem introduzir um atraso de bloqueios de compilação ao bloqueio de clientes potenciais. Isso é especialmente verdadeiras se muitos usuários que não serão proprietário do procedimento armazenado, simultaneamente, execute o procedimento sem fornecer o nome do proprietário. Lembre-se de que mesmo se você não conseguir ver os SPIDs aguardando bloqueios de compilação, a falta de qualificação de proprietário pode apresentar atrasos na execução do procedimento armazenado e causar desnecessariamente alta utilização da 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 theFerramentasmenu, and then selectAll event classes.)

Recolher esta tabelaExpandir esta tabela
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:
251004  (http://support.microsoft.com/kb/251004/ ) INF: How to monitor SQL Server 7.0 blocking
271509  (http://support.microsoft.com/kb/271509/ ) INF: 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]]
Nawaitresourcecolumn ("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).

Anotações
  • 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."


Voltar para o início

Known issues

Here are some known issues that can prevent plan caching:
  • You use BLOB variables as a Stored Procedure parameter. Para obter mais informações, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:
    2380435  (http://support.microsoft.com/kb/2380435/ ) FIX: 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 (http://blogs.msdn.com/b/sqlserverfaq/archive/2010/09/08/open-symmetric-key-command-prevents-query-plan-caching.aspx)

Voltar para o início
A informação contida neste artigo aplica-se 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
Voltar para o início
Palavras-chave: 
kbinfo kbmt KB263889 KbMtpt
Voltar para o início
Tradução automáticaTradução automática
IMPORTANTE: Este artigo foi traduzido por um sistema de tradução automática (também designado por Machine Translation ou MT), não tendo sido portanto traduzido ou revisto por pessoas. A Microsoft possui artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais, com o objetivo de oferecer em português a totalidade dos artigos existentes na base de dados de suporte. No entanto, a tradução automática não é sempre perfeita, podendo conter erros de vocabulário, sintaxe ou gramática. A Microsoft não é responsável por incoerências, erros ou prejuízos ocorridos em decorrência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza atualizações freqüentes ao software de tradução automática (MT). Obrigado.
Clique aqui para ver a versão em Inglês deste artigo: 263889  (http://support.microsoft.com/kb/263889/en-us/ )
Voltar para o início