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

Resumo

O escalonamento de bloqueio é o processo de conversão de muitos bloqueios refinados (como bloqueios de linha ou de página) em bloqueios de tabela. O Microsoft SQL Server determina dinamicamente quando fazer o escalonamento de bloqueio. Quando ele toma essa decisã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 bloqueio ocorra apenas naqueles 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 bloqueio está causando bloqueio e como lidar com o escalonamento de bloqueio indesejável.

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

Determinar se o escalonamento de bloqueio está causando bloqueio

O escalonamento de bloqueio não causa a maioria dos problemas de bloqueio. Para determinar se o escalonamento de bloqueio está ocorrendo no ou perto do 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 bloqueio não está ocorrendo no 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ça e o recurso de bloqueio que é mantido pelo bloqueador principal e que está bloqueando outras IDs de processo do servidor (SPIDs), consulte INF: Entender e resolver SQL Server problemas de bloqueio.

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 bloqueio não será o problema. Em particular, se o bloqueio TAB for um bloqueio de intenção (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 bloqueio, consulte o INF: Compreender e resolver SQL Server bloquear os problemas de solução de problemas.

Impedir o escalonamento de bloqueio

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

• Divida grandes operações em lotes em várias operações menores. Por exemplo, você executa a consulta a seguir para remover mais de 100.000 registros antigos de uma tabela de auditoria e, em seguida, você 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 acumulados por transação. Isso impedirá o escalonamento de bloqueio. 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 a pegada de bloqueio da consulta tornando a consulta o mais eficiente possível. Verificações grandes ou muitas pesquisas de indicadores podem aumentar a chance de escalonamento de bloqueio. Além disso, elas aumentam a chance de impasses 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. Examine o plano de execução e potencialmente crie novos índices não clusterizados para melhorar o desempenho da consulta. Para obter mais informações, consulte SQL Server Guia de Arquitetura e Design de Índice.

  Um dos objetivos dessa otimização é fazer com que o índice busque retornar o menor número possível de linhas para minimizar o custo das pesquisas de indicador (maximizar a seletividade do índice para a consulta). Se SQL Server estimar que um operador lógico do Bookmark Lookup retornará muitas linhas, ele poderá usar uma PREFETCH cláusula para fazer a pesquisa de indicador. Se SQL Server usar PREFETCH para 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 por leitura" pode adquirir 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 bloqueio. Isso é especialmente importante se você descobrir que o bloqueio escalonado é um bloqueio de tabela compartilhado, embora estes não sejam comumente vistos no nível de isolamento padrão "confirmado por leitura". Se uma cláusula WITH PREFETCH de Pesquisa de Indicador estiver causando o escalonamento, considere adicionar colunas ao índice nãocluso que aparece no Index Seek ou ao operador lógico Index Scan abaixo do operador lógico Bookmark Lookup no plano de consulta. Talvez seja 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 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 bloqueio não poderá ocorrer se um SPID diferente estiver mantendo um bloqueio de tabela incompatível no momento. O escalonamento de bloqueio sempre aumenta 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 a escalada não será bloqueada enquanto aguarda um bloqueio TAB. Em vez disso, ele continua a adquirir bloqueios em seu nível original, mais granular (linha, chave ou página), periodicamente fazendo tentativas adicionais de escalonamento. Portanto, um método para evitar o escalonamento de bloqueio 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 intenção) no nível da tabela não bloqueia nenhuma linha ou página, mas ainda não é compatível com um bloqueio TAB S (compartilhado) ou X (exclusivo). Por exemplo, suponha que você deve executar um trabalho em lote que modifique muitas linhas na tabela de mytable e que causou o bloqueio devido ao escalonamento de bloqueio. Se esse trabalho sempre terminar em menos de uma hora, você poderá criar um trabalho Transact-SQL que contém o código a seguir e agendar o novo trabalho para iniciar vários minutos antes da hora de início do trabalho em lote:

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

  Essa consulta adquire e mantém um bloqueio IX na mytable por uma hora. Isso impede o escalonamento de bloqueio na tabela durante esse tempo. Esse lote não modifica nenhum dado ou 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).

• Eliminar o escalonamento de bloqueio 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 poderá 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 implícitas ou explícitas de tipo de dados, a função de sistema ISNULL(), uma função definida pelo usuário com a coluna passada como parâmetro ou uma computação na coluna, como WHERE CONVERT(INT, column1) = @a ou WHERE Column1*Column2 = 5. Nesses casos, a consulta não pode BUSCAR o índice existente, mesmo que 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, 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.

Desabilitar o escalonamento de bloqueio

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

• Nível da tabela: Você pode desabilitar o escalonamento de bloqueio 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 bloqueio 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 bloqueio globalmente na instância de SQL Server. O escalonamento de bloqueio serve a um propósito útil em SQL Server maximizando a eficiência das consultas que, de outra forma, são desaceleradas pela sobrecarga de adquirir e liberar vários milhares de bloqueios. O escalonamento de bloqueio também ajuda a minimizar a memória necessária para controlar os bloqueios. A memória que SQL Server pode alocar dinamicamente para estruturas de bloqueio é finita. Portanto, se você desabilitar o escalonamento de bloqueio e a memória de bloqueio aumentar 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 marcar 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 de banco de dados se você reiniciar o serviço SQL Server.

Você pode adicionar esses sinalizadores de rastreamento (-T1211 ou -T1224) usando 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 ou DBCC TRACEON (1224, -1) , o sinalizador de rastreamento entrará em vigor imediatamente.
No entanto, se você não adicionar o -T1211 ou -T1224 como parâmetro de inicialização, o efeito de um DBCC TRACEON comando será perdido quando o serviço SQL Server é reiniciado. Ativar o sinalizador de rastreamento impede quaisquer escalonamentos de bloqueio futuros, mas não reverte nenhum escalonamento de bloqueio que já ocorreu em uma transação ativa.

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

Limites de escalonamento de bloqueio

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

• 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 disparado. A memória de bloqueio é limitada a 60% do pool de buffer visível. O limite de escalonamento de bloqueio é definido em 40% da memória de bloqueio. Isso é 40% de 60% do pool de buffers, ou 24%. Se a memória de bloqueio exceder o limite de 60% (isso é muito mais provável se o escalonamento de bloqueio estiver desabilitado), todas as tentativas de alocar bloqueios adicionais falharão e 1204 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 bloqueio são melhores opções 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 bloqueio. A Microsoft recomenda que você habilite esse sinalizador de rastreamento apenas para mitigar o bloqueio severo causado pelo escalonamento de bloqueio, 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