Como resolver problemas de bloqueio causados por escalonamento de bloqueio no SQL Server

Traduções deste artigo Traduções deste artigo
ID do artigo: 323630 - Exibir os produtos aos quais esse artigo se aplica.
Expandir tudo | Recolher tudo

Neste artigo

Sumário

Escalonamento de bloqueio é o processo de conversão de muitos bloqueios refinados (como bloqueios de linha ou página) em bloqueios de tabela. Microsoft SQL Server determina dinamicamente quando executar o escalonamento de bloqueio. Ao fazer essa decisão, o SQL Server leva em conta o número de bloqueios são mantidos em uma verificação específica, o número de bloqueios mantidos por toda a transação e a memória que está sendo usada para bloqueios no sistema como um todo. Geralmente, o comportamento do padrão do SQL Server resulta em escalonamento de bloqueio ocorrendo apenas nesses pontos onde ele deve melhorar o desempenho ou quando você deve reduzir a memória do sistema excessiva de bloqueio para um nível mais razoável. No entanto, alguns designs de aplicativo ou consulta podem disparar o escalonamento de bloqueio uma vez quando não é desejável, e o bloqueio de tabela encaminhado pode bloquear outros usuários. Este artigo descreve como determinar se o escalonamento de bloqueio está causando bloqueio e como lidar com o escalonamento de bloqueio indesejáveis.

Mais Informações

Como determinar se escalonamento de bloqueio é que causam bloqueio

Escalonamento de bloqueio não causa a maioria dos problemas de bloqueio. Para determinar se o escalonamento de bloqueio está ocorrendo perto da hora quando você tiver problemas bloqueio, inicie um rastreamento SQL Profiler que inclui o evento : Escalonamento de bloqueio . Se você não vir quaisquer eventos : Escalonamento de bloqueio , escalonamento de bloqueios não está ocorrendo no servidor e as informações neste artigo não se aplicam à sua situação.

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

Para obter mais informações sobre como identificar o Bloqueador de cabeça e como identificar o recurso de bloqueio mantido pelo Bloqueador de cabeça que está bloqueando a outro processo de servidor identificações (SPIDs), clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:
224453Noções básicas sobre e resolver o SQL Server 7.0 ou problemas de bloqueio de 2000
Se o bloqueio que está bloqueando outros usuários for diferente de um bloqueio de (nível de tabela) TAB com um modo de bloqueio de S (compartilhado) ou X (exclusivo), escalonamento de bloqueio não é o problema. Em particular, se o bloqueio TAB for um intenção de bloqueio (como um modo de bloqueio de IS, IU ou IX), isso não é o resultado de escalonamento de bloqueio. Se os problemas de bloqueio não estão sendo causados por escalonamento de bloqueio, consulte o artigo Q224453 para etapas de solução de problemas.

Como evitar o escalonamento de bloqueio

A maneira mais simples e mais segura de evitar que escalonamento de bloqueio é manter transações curtas e reduzir a superfície de bloqueio de consultas caras para que os limites de escalonamento de bloqueio não são excedidos. Há várias maneiras para obter esse objetivo, muitos dos quais estão listados:
  • Divida as operações de lote grande em várias operações menores. Por exemplo, suponha que você executou a consulta a seguir para remover vários registros antigos centenas de milhares de uma tabela de auditoria e, em seguida, você descobriu que ele causado um escalonamento de bloqueio que outros usuários bloqueados:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'						
    por remover esses registros algumas centenas e ao mesmo tempo, é possível reduzir drasticamente o número de bloqueios que se acumulam por transação e evitar o escalonamento de bloqueio. Por exemplo: delete_more
    SET ROWCOUNT 500
    delete_more:
         DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    IF @@ROWCOUNT > 0 GOTO delete_more
    SET ROWCOUNT 0
  • Reduza a superfície de bloqueio da consulta fazer a consulta tão eficiente quanto possível. Grande número de pesquisas de indicador ou verificações grandes pode aumentar as chances de escalonamento de bloqueio; Além disso, ele aumenta a chance de deadlocks e geralmente afeta negativamente simultaneidade e desempenho. Após localizar a consulta que faz com que escalonamento de bloqueio, procure oportunidades para criar novos índices ou para adicionar colunas a um índice existente para remover verificações de índice ou tabela e para maximizar a eficiência do índice de procura. Considere colando a consulta em uma janela de consulta do Query Analyzer para executar uma análise do índice automático nele. Para fazer isso, no menu consulta , clique em Assistente para ajuste de índice no SQL Server 2000, ou clique em Executar análise de índice no SQL Server 7.0.

    Um objetivo dessa otimização é fazer o índice de buscas retornar como algumas linhas como possível para minimizar o custo de pesquisas de indicador (Maximizar a seletividade do índice para a consulta específica). Se o SQL Server estima que um operador lógico indicador pesquisa pode retornar várias linhas, ele pode usar uma pré-busca para executar a pesquisa de indicador. Se SQL Server usa pré-busca para uma pesquisa de indicador, ele deve aumentará o nível de isolamento transação de uma parte da consulta para leitura repetida de uma parte da consulta. Isso significa que o que pode parecer semelhante a uma instrução SELECT no nível de isolamento confirmada de leitura pode adquirir milhares de chaves bloqueios (no índice de cluster e um índice que não estão em cluster), que podem causar como uma consulta para exceder os limites de escalonamento de bloqueio. Isso é especialmente importante se você achar que o bloqueio encaminhado é um bloqueio de tabela compartilhada, que, no entanto, não é geralmente visto no nível de isolamento confirmada de leitura padrão. Se uma cláusula de indicador pesquisa WITH pré-busca está causando o escalonamento, considere adicionar colunas adicionais para o índice que não estão em cluster que aparece em atingir o índice ou o operador lógico Index Scan abaixo o operador lógico indicador pesquisa no plano de consulta. Talvez seja possível criar um índice abrangente (um índice que inclua todas as colunas em uma tabela que foram usados na consulta) ou pelo menos um índice que aborda as colunas que foram usadas para critérios de união ou na cláusula WHERE se incluindo tudo na lista Selecionar coluna é impraticável.

    Uma associação em loop aninhadas também pode usar a pré-busca e isso faz com que o mesmo comportamento de bloqueio.

    Para obter mais informações, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:
    260652Associação de loop aninhadas que usa um "BOOKMARK PROC.. .WITH pré-busca" pode manter mais bloqueios
  • Escalonamento de bloqueio não pode ocorrer se um SPID diferente no momento está mantendo um bloqueio de tabela incompatível. Escalonamento de bloqueio sempre escala para um bloqueio de tabela e nunca para bloqueios 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 escalonamento não bloqueia enquanto aguarda um bloqueio TAB. Em vez disso, ele continua a adquirir bloqueios no nível mais granular, original (linha, chave ou página), tentativas de fazer periodicamente escalação adicional. Portanto, um método para impedir que o escalonamento de bloqueios em uma tabela específica é para adquirir e segure um bloqueio em uma conexão diferente que não é compatível com o tipo de bloqueio encaminhado. Um bloqueio IX (intenção exclusiva) no nível de tabela não bloqueia qualquer linhas ou páginas, mas ele é ainda não é compatível com um encaminhado S (compartilhado) ou X bloqueio TAB (exclusivo). Por exemplo, suponha que você deve executar um trabalho em lotes que modifica um grande número de linhas na tabela minhatabela e que causou o bloqueio que ocorre por causa de escalonamento de bloqueio. Se este trabalho sempre for concluída em menos de uma hora, você pode criar um trabalho de Transact-SQL que contém o seguinte código e agendar o novo trabalho para iniciar vários minutos antes de hora de início do trabalho em lotes:
    BEGIN TRAN
    SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
    WAITFOR DELAY '1:00:00'
    COMMIT TRAN				
    esta consulta adquire e mantém um bloqueio IX em minhatabela para uma hora, que impede que o escalonamento de bloqueio na tabela durante esse tempo. Esse lote não modificar os dados ou bloquear outras consultas (a menos que outros consulta força um bloqueio de tabela com a dica TABLOCK ou se um administrador tiver desativado bloqueios de página ou linha usando um sp_indexoption procedimento armazenado).
Além disso, você pode desativar escalonamento de bloqueio, permitindo o sinalizador de rastreamento 1211. No entanto, esse sinalizador de rastreamento desativa todos os escalonamento de bloqueio globalmente na instância do SQL Server. Escalonamento de bloqueio serve uma finalidade muito útil no SQL Server por maximizar a eficiência de consultas que caso contrário diminuíam pela sobrecarga de aquisição e liberando vários milhares de bloqueios. Bloquear escalonamento também ajuda a minimizar a memória necessária para manter o controle de bloqueios. A memória do SQL Server pode alocar dinamicamente para estruturas de bloqueio é finita, portanto, se você desabilitar escalonamento de bloqueio e o bloqueio de memória aumenta grande o suficiente, tenta alocar bloqueios adicionais para qualquer consulta pode falhar e ocorrerá o seguinte erro:

Erro: 1204, gravidade: 19, estado: 1
O SQL Server não é possível obter um recurso LOCK neste momento. Execute novamente a sua instrução quando houver menos usuários ativos ou peça o administrador do sistema para verificar a configuração de bloqueio e memória do SQL Server.
Observação Quando ocorre um erro de "1204", ele pára o processamento da instrução atual e faz com que uma reversão de transação ativa. A reversão propriamente dito pode impedir que usuários ou levar a um tempo de recuperação banco de dados longo se você reiniciar o serviço do SQL Server.

Somente usar uma dica de bloqueio como ROWLOCK altera o plano inicial de bloqueio. Dicas de bloqueio não impedirão o escalonamento de bloqueio.

Os outros métodos de evitar o escalonamento de bloqueio descritos neste artigo são melhores opções de ativar o sinalizador de rastreamento. Além disso, os outros métodos geralmente resulta em melhor desempenho para a consulta que desabilitar escalonamento de bloqueio para a instância inteira. A Microsoft recomenda ativar esse sinalizador de rastreamento somente atenuar bloqueio graves que causados por escalonamento de bloqueio enquanto outras opções, como aquelas discutidas anteriormente neste artigo, estão sendo investigadas. Habilitar um sinalizador de rastreamento para que ele é ativado sempre que o SQL Server é iniciado, adicioná-lo como um parâmetro de inicialização do servidor.

Para adicionar um parâmetro de inicialização do servidor, clique com o botão direito do mouse no SQL Enterprise Manager no servidor, clique em Propriedades e em seguida, na guia Geral , clique em Parâmetros de inicialização e, em seguida, adicione o parâmetro seguinte (exatamente como mostrado):
-T1211
Você deve ciclo o serviço SQL Server para um novo parâmetro de inicialização entrem em vigor. Se você executar a seguinte consulta no Query Analyzer o sinalizador de rastreamento entra em vigor imediatamente:
DBCC TRACEON (1211, -1)				
Contudo, se você não adicionar o -T1211 parâmetro de inicialização, o efeito de um comando traceon é perdido quando o serviço do SQL Server seja alternado. Ativar o sinalizador de rastreamento impede que qualquer os escalonamentos de bloqueio futuras, mas ele não reverter qualquer os escalonamentos de bloqueio que já ocorreram em uma transação ativa.

Propriedades

ID do artigo: 323630 - Última revisão: quinta-feira, 22 de fevereiro de 2007 - Revisão: 10.3
A informação contida neste artigo aplica-se a:
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Palavras-chave: 
kbmt kbinfo KB323630 KbMtpt
Tradução automática
IMPORTANTE: Este artigo foi traduzido por um sistema de tradução automática (também designado por Machine Translation ou MT), não tendo sido portanto traduzido ou revisto por pessoas. A Microsoft possui artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais, com o objetivo de oferecer em português a totalidade dos artigos existentes na base de dados de suporte. No entanto, a tradução automática não é sempre perfeita, podendo conter erros de vocabulário, sintaxe ou gramática. A Microsoft não é responsável por incoerências, erros ou prejuízos ocorridos em decorrência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza atualizações freqüentes ao software de tradução automática (MT). Obrigado.
Clique aqui para ver a versão em Inglês deste artigo: 323630

Submeter comentários

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com