Resolver problemas de bloqueio causados por escalonamento de bloqueio no SQL Server

Resumo

O escalonamento de bloqueios é o processo de conversão de muitos bloqueios refinados (como bloqueios de linha ou página) em bloqueios de tabela. O Microsoft SQL Server determina dinamicamente quando fazer o escalonamento de bloqueios. Ao tomar essa decisão, o SQL Server considera o número de bloqueios mantidos em uma verificação específica, o número de bloqueios mantidos por toda a transação e a memória usada para bloqueios no sistema como um todo. Normalmente, o comportamento padrão do SQL Server faz com que o escalonamento de bloqueios ocorra apenas nos momentos em que ele melhoraria o desempenho ou quando você deve reduzir a memória de bloqueio excessiva do sistema para um nível mais razoável. No entanto, alguns designs de aplicativo ou consulta podem disparar o escalonamento de bloqueio em um momento em que essa ação não é desejável e o bloqueio de tabela escalonado pode bloquear outros usuários. Este artigo discute como determinar se o escalonamento de bloqueios está causando o bloqueio e como lidar com o escalonamento de bloqueios indesejável.

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

Determinar se o escalonamento de bloqueios está causando bloqueio

O escalonamento de bloqueios não causa a maioria dos problemas de bloqueio. Para determinar se o escalonamento de bloqueios está ocorrendo no momento ou próximo ao momento em que você enfrenta problemas de bloqueio, inicie uma sessão de Eventos Estendidos que inclua o lock_escalation evento. Se você não vir nenhum lock_escalation evento, o escalonamento de bloqueios não está ocorrendo em seu servidor e as informações neste artigo não se aplicam à sua situação.

Se o escalonamento de bloqueio estiver ocorrendo, verifique se o bloqueio de tabela escalonado está bloqueando outros usuários.

Para obter mais informações sobre como identificar o bloqueador de cabeçalho e o recurso de bloqueio mantido pelo bloqueador de cabeçalho e que está bloqueando outras SPIDs (IDs de processo do servidor), consulte INF: Noções básicas e resolução de problemas de bloqueio do SQL Server.

Se o bloqueio que está bloqueando outros usuários for diferente de um bloqueio TAB (nível de tabela) que tenha um modo de bloqueio de S (compartilhado) ou X (exclusivo), o escalonamento de bloqueios não será o problema. Em particular, se o bloqueio TAB for um bloqueio intencional (como um modo de bloqueio de IS, IU ou IX), isso não será causado pelo escalonamento de bloqueio. Se os problemas de bloqueio não forem causados pelo escalonamento de bloqueios, consulte as etapas de solução de problemas do INF: Noções básicas e resolução de problemas de bloqueio do SQL Server.

Evitar o escalonamento de bloqueios

O método mais simples e seguro para evitar o escalonamento de bloqueios é manter as transações curtas e reduzir o volume de bloqueios de consultas caras para que os limites de escalonamento de bloqueios não sejam excedidos. Existem vários métodos para atingir esse objetivo, incluindo as seguintes estratégias:

• Divida operações em lotes grandes em várias operações menores. Por exemplo, você executa a seguinte consulta para remover 100.000+ registros antigos de uma tabela de auditoria e, em seguida, determina que a consulta causou um escalonamento de bloqueio que bloqueou outros usuários:

  DELETE FROM LogMessages WHERE LogDate < '20020102';
  

  Ao remover esses registros algumas centenas de cada vez, você pode reduzir drasticamente o número de bloqueios que se acumulam por transação. Isso evitará o escalonamento de bloqueios. Por exemplo, você executa a seguinte consulta:

  DECLARE @done bit = 0;
  WHILE (@done = 0)
  BEGIN
      DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';
      IF @@rowcount < 1000 SET @done = 1;
  END;
  

• Reduza o volume de bloqueio da consulta tornando-a o mais eficiente possível. Verificações grandes ou muitas pesquisas de favoritos podem aumentar a chance de escalonamento de bloqueios. Além disso, eles aumentam a chance de deadlocks e afetam negativamente a simultaneidade e o desempenho. Depois de identificar que a consulta que causa o escalonamento de bloqueios, procure oportunidades para criar novos índices ou adicionar colunas a um índice existente para remover verificações de índice ou tabela e maximizar a eficiência das buscas de índice. Revise o plano de execução e, possivelmente, crie novos índices não clusterizados para melhorar o desempenho da consulta. Para obter mais informações, consulte o Guia de Design e Arquitetura de Índices do SQL Server.

  Um objetivo dessa otimização é fazer com que as buscas de índice retornem o menor número possível de linhas para minimizar o custo das pesquisas de indicadores (maximizar a seletividade do índice para a consulta). Se o SQL Server estimar que um operador lógico de Pesquisa de Indicador retornará muitas linhas, ele poderá usar uma PREFETCH cláusula para fazer a pesquisa de indicador. Se o SQL Server usar PREFETCH uma pesquisa de indicador, ele deverá aumentar o nível de isolamento da transação de uma parte da consulta para "leitura repetível" para uma parte da consulta. Isso significa que o que pode parecer uma SELECT instrução em um nível de isolamento "confirmado de leitura" pode adquirir muitos milhares de bloqueios de chave (no índice clusterizado e em um índice não clusterizado). Isso pode fazer com que essa consulta exceda os limites de escalonamento de bloqueios. Isso é especialmente importante se você achar que o bloqueio escalonado é um bloqueio de tabela compartilhado, embora eles não sejam comumente vistos no nível de isolamento padrão "confirmado de leitura". Se uma cláusula WITH PREFETCH de Pesquisa de Indicador estiver causando o escalonamento, considere adicionar colunas ao índice não clusterizado que aparece na Busca de Índice ou ao operador lógico Verificação de Índice abaixo do operador lógico Pesquisa de Indicador no plano de consulta. Pode ser possível criar um índice de cobertura (um índice que inclui todas as colunas em uma tabela que foram usadas na consulta) ou pelo menos um índice que abrange as colunas que foram usadas para critérios de junção ou na cláusula WHERE se for impraticável incluir tudo na lista "selecionar coluna".

  Uma junção de loop aninhado também pode usar PREFETCH, e isso causa o mesmo comportamento de bloqueio.

• O escalonamento de bloqueios não poderá ocorrer se um SPID diferente estiver mantendo um bloqueio de tabela incompatível. O escalonamento de bloqueios sempre é escalonado para um bloqueio de tabela e nunca para um bloqueio de página. Além disso, se uma tentativa de escalonamento de bloqueio falhar porque outro SPID mantém um bloqueio TAB incompatível, a consulta que tentou o escalonamento não será bloqueada enquanto aguarda um bloqueio TAB. Em vez disso, ela continuará adquirindo bloqueios no nível original e mais granular (linha, chave ou página), fazendo periodicamente tentativas de escalonamento adicionais. Portanto, um método usado para evitar o escalonamento de bloqueios em uma tabela específica é adquirir e manter um bloqueio em uma conexão diferente que não seja compatível com o tipo de bloqueio escalonado. Um bloqueio IX (exclusivo de tentativa) no nível da tabela não bloqueia linhas nem páginas, mas ainda não é compatível com um bloqueio TAB S (compartilhado) ou X (exclusivo) escalonado. Por exemplo, suponha que você deva executar um trabalho em lotes que modifique muitas linhas na tabela mytable e que tenha causado o bloqueio devido ao escalonamento de bloqueios. Se esse trabalho sempre terminar em menos de uma hora, você poderá criar um trabalho Transact-SQL que contenha o código a seguir e agendar o novo trabalho para iniciar vários minutos antes da hora de início do trabalho em lotes:

  BEGIN TRAN;
  SELECT * FROM mytable WITH (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
  WAITFOR DELAY '1:00:00';
  COMMIT TRAN;
  

  Essa consulta adquire e mantém um bloqueio IX em mytable por uma hora. Isso evita o escalonamento de bloqueios na tabela durante esse tempo. Esse lote não modifica nenhum dado nem bloqueia outras consultas (a menos que a outra consulta force um bloqueio de tabela usando a dica TABLOCK ou se um administrador tiver desabilitado bloqueios de página ou linha usando ALTER INDEX).

• Elimine o escalonamento de bloqueios causado pela falta de SARGability, um termo de banco de dados relacional usado para descrever se uma consulta pode usar índices para predicados e colunas de junção. Para obter mais informações sobre SARGability, consulte Considerações de consulta do Guia de Design Interno. Por exemplo, uma consulta bastante simples que não parece estar solicitando muitas linhas — ou talvez uma única linha — ainda pode acabar verificando uma tabela/índice inteiro. Isso pode ocorrer se houver uma função ou computação no lado esquerdo de uma cláusula WHERE. Esses exemplos que não têm SARGability incluem conversões de tipo de dados implícitas ou explícitas, a função do sistema ISNULL(), uma função definida pelo usuário com a coluna passada como um parâmetro ou um cálculo na coluna, como WHERE CONVERT(INT, column1) = @a ou WHERE Column1*Column2 = 5. Nesses casos, a consulta não pode SEEK o índice existente, mesmo que ele contenha as colunas apropriadas, pois todos os valores de coluna devem ser recuperados primeiro e passados para a função. Isso leva a uma verificação de toda a tabela ou índice e resulta na aquisição de um grande número de bloqueios. Nessas circunstâncias, o SQL Server pode atingir o limite de escalonamento da contagem de bloqueios. A solução é evitar o uso de funções em colunas na cláusula WHERE, garantindo condições SARGable.

Desativar escalonamento de bloqueios

Embora seja possível desabilitar o escalonamento de bloqueios no SQL Server, não o recomendamos. Em vez disso, use as estratégias de prevenção descritas na seção Impedir escalonamento de bloqueios.

• Nível da tabela: você pode desativar o escalonamento de bloqueios no nível da tabela. Consulte ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE). Para determinar qual tabela deve ser direcionada, examine as consultas T-SQL. Se isso não for possível, use Eventos estendidos, habilite o evento lock_escalation e examine a coluna object_id. Como alternativa, use o evento Lock:Escalation e examine a coluna usando o ObjectID2 SQL Profiler.
• Nível da instância: você pode desabilitar o escalonamento de bloqueios habilitando um dos sinalizadores de rastreamento 1211 ou 1224 ou ambos para a instância. No entanto, esses sinalizadores de rastreamento desabilitam todo o escalonamento de bloqueios globalmente na instância do SQL Server. O escalonamento de bloqueios serve a uma finalidade útil no SQL Server, maximizando a eficiência das consultas que, de outra forma, seriam pela sobrecarga de adquirir e liberar vários milhares de bloqueios. O escalonamento de bloqueios também ajuda a minimizar a memória necessária para controlar os bloqueios. A memória que o SQL Server pode alocar dinamicamente para estruturas de bloqueio é finita. Portanto, se você desabilitar o escalonamento de bloqueios e a memória de bloqueio crescer o suficiente, qualquer tentativa de alocar bloqueios adicionais para qualquer consulta poderá falhar e gerar a seguinte entrada de erro:

Erro: 1204, Gravidade: 19, Estado: 1
O SQL Server não pode obter um recurso LOCK no momento. Execute novamente sua instrução quando houver menos usuários ativos ou peça ao administrador do sistema para verificar a configuração de bloqueio e memória do SQL Server.

Observação

Quando ocorre um erro 1204, ele interrompe o processamento da instrução atual e causa uma reversão da transação ativa. A reversão em si pode bloquear usuários ou causar um longo tempo de recuperação do banco de dados se você reiniciar o serviço SQL Server.

Você pode adicionar esses sinalizadores de rastreamento (-T1211 ou -T1224) usando o SQL Server Configuration Manager. Você deve reiniciar o serviço SQL Server para que um novo parâmetro de inicialização entre em vigor. Se você executar a DBCC TRACEON (1211, -1) consulta or DBCC TRACEON (1224, -1) , o sinalizador de rastreamento entrará em vigor imediatamente.
No entanto, se você não adicionar o -T1211 ou -T1224 como um parâmetro de inicialização, o efeito de um DBCC TRACEON comando será perdido quando o serviço do SQL Server for reiniciado. Ativar o sinalizador de rastreamento impede qualquer escalonamento de bloqueio futuro, mas não reverte nenhum escalonamento de bloqueio que já tenha ocorrido em uma transação ativa.

Se você usar uma dica de bloqueio, como ROWLOCK, isso alterará apenas o plano de bloqueio inicial. As dicas de bloqueio não impedem o escalonamento de bloqueio.

Limites de escalonamento de bloqueios

O escalonamento de bloqueios pode ocorrer em uma das seguintes condições:

• O limite de memória é atingido – um limite de memória de 40% da memória de bloqueio é atingido. Quando a memória de bloqueio excede 24% do pool de buffers, um escalonamento de bloqueio pode ser acionado. A memória de bloqueio é limitada a 60% do pool de buffers visível. O limite de escalonamento de bloqueio é definido em 40% da memória de bloqueio. Isso é 40% de 60% do pool de buffer, ou 24%. Se a memória de bloqueio exceder o limite de 60% (isso é muito mais provável se o escalonamento de bloqueios estiver desabilitado), todas as tentativas de alocar bloqueios adicionais falharão e 1204 os erros serão gerados.

• Um limite de bloqueio é atingido - Depois que o limite de memória é verificado, o número de bloqueios adquiridos na tabela ou índice atual é avaliado. Se o número exceder 5.000, um escalonamento de bloqueio será disparado.

Para entender qual limite foi atingido, use Eventos estendidos, habilite o evento lock_escalation e examine as colunas escalated_lock_count e escalation_cause . Como alternativa, use o evento Lock:Escalation e examine o EventSubClass valor, em que "0 - LOCK_THRESHOLD" indica que a instrução excedeu o limite de bloqueio e "1 - MEMORY_THRESHOLD" indica que a instrução excedeu o limite de memória. Além disso, examine as IntegerData colunas e IntegerData2 .

Recomendações

Os métodos discutidos na seção Impedir escalonamento de bloqueios são opções melhores do que desabilitar o escalonamento no nível da tabela ou da instância. Além disso, os métodos preventivos geralmente produzem melhor desempenho para a consulta do que desabilitar o escalonamento de bloqueios. A Microsoft recomenda que você habilite esse sinalizador de rastreamento apenas para atenuar o bloqueio severo causado pelo escalonamento de bloqueios enquanto outras opções, como as discutidas neste artigo, estão sendo investigadas.

Confira também

• Entender e resolver problemas de bloqueio do SQL Server
• Solucionar problemas de consultas de execução lenta no SQL Server