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

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

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

Resumo

No Microsoft SQL Server, apenas uma cópia de um plano de procedimento armazenado geralmente está no 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 à medida que cada uma tenta 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 a sessão bloqueada e (geralmente) bloqueando SPIDs é LCK_M_X (exclusivo) e waitresource tem o formato OBJECT: dbid: object_id [[COMPILE]], onde object_id é o ID do objeto do procedimento armazenado.

• Os bloqueadores têm waittype NULL, status executável. As sessões bloqueadas têm waittypeLCK_M_X (bloqueio exclusivo), status em suspensão.

• Embora a duração geral do incidente de bloqueio possa ser longa, não há nenhuma sessão única (SPID) que esteja bloqueando os outros SPIDs por um longo tempo. Há bloqueio contínuo; assim que uma compilação é concluída, outro SPID assume o papel de bloqueador de cabeça por vários segundos ou menos, e assim por diante.

As informações a seguir são de um instantâneo durante sys.dm_exec_requests 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 é o ID do banco de dados e 834102 é o ID do objeto. Essa ID de objeto pertence a um procedimento armazenado, não a uma tabela.

Cenários que levam a bloqueios de compilação

Os cenários a seguir descrevem as causas de bloqueios de compilação exclusivos em procedimentos armazenados ou gatilhos.

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

Se um plano existente for encontrado, o SQL Server reutilizará o plano armazenado em cache e não compilará o procedimento armazenado. No entanto, a falta de qualificação do proprietário força o SQL Server a 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 leva 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 é registrada em uma sessão de Evento Estendido do SQL Server quando esse problema ocorre.

sp_cache_miss ocorre quando a pesquisa de cache por nome falha, mas um plano em cache correspondente foi encontrado no 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 a procedimentos armazenados sejam qualificadas pelo proprietário. (Em vez de exec mystoredproc, use exec dbo.mystoredproc.) Embora a qualificação do proprietário seja importante por motivos de desempenho, você não precisa qualificar o processo armazenado com o nome do banco de dados para evitar a pesquisa de cache extra.

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

O procedimento armazenado é recompilado com freqüência

A recompilação é uma explicação para bloqueios de compilação em um procedimento armazenado ou gatilho. As maneiras de fazer com que um procedimento armazenado seja recompilado incluem EXECUTE... WITH RECOMPILE, CREATE PROCEDURE ...WITH RECOMPILE, ou usando sp_recompile. Para obter mais informações, confira Recompilar um procedimento armazenado. A solução neste caso é reduzir ou eliminar a recompilação.

O procedimento armazenado é prefixado com sp_**

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

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

Se um procedimento qualificado pelo proprietário for executado usando uma letra maiúscula e minúscula diferente (maiúscula ou minúscula) da ocorrência usada para criá-lo, o procedimento poderá disparar um evento CacheMiss ou solicitar um bloqueio COMPILE. Para ilustrar, observe as diferentes letras maiúsculas e minúsculas usadas em CREATE PROCEDURE dbo.SalesData ... versus EXEC dbo.salesdata. 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 descrita anteriormente. A cadeia de bloqueio pode ocorrer se houver muitas sessões (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 as maiúsculas e minúsculas forem diferentes.

A solução é descartar e criar o procedimento usando as mesmas letras maiúsculas e minúsculas usadas quando o aplicativo executa o procedimento. Você também pode garantir que o procedimento seja executado em todos os aplicativos usando as letras maiúsculas e minúsculas corretas (maiúsculas ou minúsculas).

O procedimento armazenado é invocado como um evento de linguagem

Se você tentar executar um procedimento armazenado como um evento de linguagem em vez de como um RPC, o SQL Server deverá analisar e compilar a consulta de evento de linguagem, determinar se a consulta está tentando executar o procedimento específico e, em seguida, tentar encontrar um plano no cache para esse procedimento. Para evitar essa situação em que o SQL Server deve analisar e compilar o evento de linguagem, verifique se a consulta é enviada ao SQL Server como um RPC. Por exemplo, no código .NET, você pode usar SqlCommand.CommandType.StoredProcedure para garantir um evento RPC.

O procedimento armazenado ou sp_executesql usa um parâmetro de cadeia de caracteres maior que 8 KB

Se você chamar um procedimento armazenado ou sp_executesql e passar um parâmetro de cadeia de caracteres maior que 8 KB, o SQL Server usará um tipo de dados BLOB (objeto binário grande) para armazenar o parâmetro. Como resultado, o plano de consulta para essa execução não é mantido no cache de planos. Portanto, cada execução do procedimento armazenado ou sp_executesql precisa adquirir um bloqueio de compilação para compilar um novo plano. Esse plano é descartado quando a execução é concluída. Para obter mais informações, consulte a observação em Cache e reutilização do plano de execução em relação a literais de cadeia de caracteres maiores que 8 KB. Para evitar o bloqueio de compilação nesse cenário, reduza o tamanho do parâmetro para menos de 8 KB.

Referências

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