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

Aplica-se a: SQL Server

Número original do KB: 323630

Resumo

O escalonamento de bloqueio converte muitos bloqueios refinados (como bloqueios de linha ou página) em um único bloqueio de tabela. SQL Server dispara o escalonamento dinamicamente com base na contagem de bloqueios ou na memória de bloqueio e seu comportamento padrão melhora a taxa de transferência na maioria das cargas de trabalho. No entanto, alguns designs de consulta ou transação causam escalonamento às vezes quando o bloqueio de tabela resultante bloqueia outros usuários. Este artigo explica como determinar se o escalonamento de bloqueio está causando bloqueio em SQL Server, como impedi-lo e como (e quando) desabilitá-lo.

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 bloqueio está ocorrendo no 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 IDs de processo de servidor (SPIDs), consulte Entenda e resolva SQL Server problemas de bloqueio.

Se o bloqueio que está bloqueando outros usuários for algo 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), ele 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 de compreensão e resolução de SQL Server bloqueio.

Evitar o escalonamento de bloqueios

O método mais simples e seguro para evitar o escalonamento de bloqueio é manter as transações curtas e reduzir o volume de bloqueio de consultas caras para que os limites de escalonamento de bloqueio não sejam excedidos. Use uma ou mais das estratégias a seguir.

Dividir operações grandes em transações menores

Divida operações em lotes grandes 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, 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. Essa abordagem impede 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;

Otimizar consultas e índices para reduzir bloqueios

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 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.

Manter um bloqueio de nível de tabela compatível em uma conexão separada

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 (intenção exclusiva) 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 escalonado (exclusivo). 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. Esse bloqueio impede o escalonamento de bloqueio na tabela durante esse tempo. Esse lote não modifica dados 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).

Verifique se os predicados são sargáveis

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 ou índice inteiro. Essa situação 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 de tipo de dados implícitas ou explícitas, a função do ISNULL() sistema, uma função definida pelo usuário com a coluna passada como um 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 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, 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.

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 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 bloqueios. Microsoft recomenda que você habilite esse sinalizador de rastreamento apenas para atenuar o bloqueio severo que o escalonamento de bloqueio causa, enquanto outras opções, como as discutidas neste artigo, estão sendo investigadas.

Conteúdo relacionado

• Entender e resolver problemas de bloqueio do SQL Server
• Solucionar problemas de consultas de execução lenta no SQL Server
• Guia de Bloqueio de Transações e Controle de Versão de Linhas
• ALTERAR TABELA (Transact-SQL)