Solucionar problemas de bloqueio causados por bloqueios de compilação

  • Artigo

Este artigo descreve como solucionar problemas e resolve problemas de bloqueio causados por bloqueios de compilação.

Versão original do produto: SQL Server
Número de KB original: 263889

Resumo

No Microsoft SQL Server, apenas uma cópia de um plano de procedimento armazenado geralmente está em cache por vez. Impor isso requer a serialização de algumas partes do processo de compilação e essa sincronização é realizada em parte usando bloqueios de compilação. Se muitas conexões estiverem executando simultaneamente o mesmo procedimento armazenado e um bloqueio de compilação precisar ser obtido para esse procedimento armazenado sempre que ele for executado, as IDs de sessão (SPIDs) poderão começar a bloquear umas às outras enquanto tentam obter um bloqueio de compilação exclusivo no objeto.

A seguir estão algumas características típicas do bloqueio de compilação que podem ser observadas na saída de bloqueio:

  • waittype para as SPIDs de sessão bloqueada e (geralmente) de bloqueio é LCK_M_X (exclusiva) e waitresource é do formulário OBJECT: dbid: object_id [[COMPILE]], onde object_id está a ID do objeto do procedimento armazenado.

  • Os bloqueadores têm waittype NULL, status executáveis. Os bloqueadores têm waittypeLCK_M_X (bloqueio exclusivo), status dormindo.

  • Embora a duração do incidente de bloqueio possa ser longa, não há um único SPID que esteja bloqueando os outros SPIDs por um longo tempo. Há bloqueio de rolagem. Assim que uma compilação é concluída, outra SPID assume a função de bloqueador de cabeça por vários segundos ou menos, e assim por diante.

As seguintes informações são de um instantâneo sys.dm_exec_requests de durante esse tipo de bloqueio:

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 Na coluna (6:834102), 6 é a ID do banco de dados e 834102 é a ID do objeto. Essa ID de objeto pertence a um procedimento armazenado, não a uma tabela.

Mais informações

A recompilação de procedimento armazenado é uma explicação para compilar bloqueios em um procedimento armazenado ou gatilho. A solução nesse caso é reduzir ou eliminar os recompiles.

Cenários adicionais que levam a compilar bloqueios

  1. O procedimento armazenado é executado sem nome totalmente qualificado

    • 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 dbo do usuário possui objeto dbo.mystoredproc e outro usuário, Harry, executa esse procedimento armazenado usando o comando exec mystoredproc, a pesquisa de cache inicial pelo nome do objeto falhará porque o objeto não é qualificado pelo proprietário. (Ainda não se sabe se existe outro procedimento armazenado nomeadoHarry.mystoredproc. Portanto, SQL Server não pode ter certeza de que o plano dbo.mystoredproc armazenado em cache para é o correto a ser executado.) SQL Server obtém um bloqueio de compilação exclusivo no procedimento e faz os preparativos para compilar o procedimento. Isso inclui a resolução do nome do objeto para uma ID do objeto. Antes de SQL Server compilar o plano, SQL Server usa essa ID do objeto para executar uma pesquisa mais precisa do cache do procedimento e pode localizar um plano compilado anteriormente, mesmo sem qualificação do proprietário.

    Se um plano existente for encontrado, SQL Server reutilizará o plano armazenado em cache e não compilar o procedimento armazenado. No entanto, a falta de forças de qualificação do proprietário SQL Server executar uma segunda pesquisa de cache e obter um bloqueio de compilação exclusivo antes que o programa determine que o plano de execução armazenado em cache existente pode ser reutilizado. Obter o bloqueio e executar pesquisas e outros trabalhos necessários para chegar a esse ponto pode introduzir um atraso para os bloqueios de compilação que levam ao bloqueio. Isso é especialmente verdadeiro se muitos usuários que não são o proprietário do procedimento armazenado executarem simultaneamente o procedimento sem fornecer o nome do proprietário. Mesmo que você não veja SPIDs aguardando bloqueios de compilação, a falta de qualificação do proprietário pode introduzir atrasos na execução do procedimento armazenado e causar alta utilização da CPU.

    A sequência de eventos a seguir será registrada em uma sessão de evento estendido SQL Server quando esse problema ocorrer.

    Nome do Evento Texto
    rpc_starting mystoredproc
    sp_cache_miss mystoredproc
    sql_batch_starting mystoredproc
    sp_cache_hit mystoredproc
    ... ...

    sp_cache_miss ocorre quando a pesquisa de cache pelo nome falha, mas, em seguida, um plano armazenado em cache correspondente foi finalmente encontrado em cache depois que o nome do objeto ambíguo foi resolvido para uma ID de objeto e há um sp_cache_hit evento.

    A solução para esse problema de bloqueio de compilação é garantir que as referências aos procedimentos armazenados sejam qualificadas pelo proprietário. (Em vez de executivo mystoredproc, use exec dbo.mystoredproc.) Embora a qualificação do proprietário seja importante por motivos de desempenho, você não precisa qualificar o proc armazenado com o nome do banco de dados para impedir a pesquisa de cache adicional.

    O bloqueio causado por bloqueios de compilação pode ser detectado usando métodos padrão de solução de problemas de bloqueio.

  2. O procedimento armazenado é prefixado com sp_

    Se o nome do procedimento armazenado começar com o sp_ prefixo e não estiver no banco de dados master, você verá sp_cache_miss antes que o cache seja atingido para cada execução, mesmo que você qualifique o procedimento armazenado pelo proprietário. Isso ocorre porque o sp_ prefixo informa SQL Server que o procedimento armazenado é um procedimento armazenado pelo sistema e os procedimentos armazenados pelo sistema têm regras de resolução de nomes diferentes. (O local preferencial está no banco de dados master.) Os nomes dos procedimentos armazenados criados pelo usuário não devem começar com sp_.

  3. O procedimento armazenado é invocado usando um caso diferente (superior/inferior)

    Se um procedimento qualificado pelo proprietário for executado usando um caso diferente (superior ou inferior) do caso usado para criá-lo, o procedimento poderá disparar um evento CacheMiss ou solicitar um bloqueio COMPILE. Eventualmente, o procedimento usa o plano armazenado em cache e não é recompilado. Mas a solicitação de um bloqueio COMPILE às vezes pode causar uma situação de cadeia de bloqueio se houver muitos SPIDs que estão tentando executar o mesmo procedimento usando um caso diferente do caso que foi usado para criá-lo. Isso é verdadeiro independentemente da ordem de classificação ou ordenação que está sendo usada no servidor ou no banco de dados. O motivo desse comportamento é que o algoritmo que está sendo usado para localizar o procedimento no cache é baseado em valores de hash (para desempenho) e os valores de hash podem ser alterados se o caso for diferente.

    A solução alternativa é soltar e criar o procedimento usando o mesmo caso que o usado quando o aplicativo executa o procedimento. Você também pode garantir que o procedimento seja executado de todos os aplicativos usando o caso correto (superior ou inferior).

  4. O procedimento armazenado é invocado como um evento language

    Se você tentar executar um procedimento armazenado como um Evento de Linguagem em vez de como um RPC, SQL Server deverá analisar e compilar a consulta de evento de idioma, determine que a consulta está tentando executar o procedimento específico e tente encontrar um plano em cache para esse procedimento. Para evitar essa situação em que SQL Server deve analisar e compilar o evento de idioma, verifique se a consulta é enviada ao SQL como um RPC.

    Para obter mais informações, confira a seção Procedimentos Armazenados do Sistema no artigo Livros Online Criando um Procedimento Armazenado.

Referências

O comando OPEN SYMMETRIC KEY impede o cache do plano de consulta