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

Traduções de Artigos Traduções de Artigos
Artigo: 323630 - Ver produtos para os quais este artigo se aplica.
Expandir tudo | Reduzir tudo

Nesta página

Sumário

Escalonamento de bloqueios é o processo de conversão de bloqueios de granularidade muitos (tais como bloqueios de linha ou página) em bloqueios de tabela. Microsoft SQL Server dinamicamente determina quando executar o escalonamento de bloqueios. Ao efectuar esta decisão, o SQL Server tem em consideração o número de bloqueios que são mantidos numa verificação específica, o número de bloqueios detidos por transacção completa e a memória que está a ser utilizada para bloqueios no sistema como um todo. Normalmente, o comportamento predefinido de SQL Server resulta num escalonamento de bloqueios ocorra apenas a esses pontos onde vai melhorar o desempenho ou quando é necessário reduzir memória de bloqueio do sistema excessiva para um nível mais razoável. No entanto, alguns modelos de aplicação ou consulta poderão accionar escalonamento de bloqueios ao mesmo tempo quando não é desejável e o bloqueio de tabela escalated pode impedir que outros utilizadores. Este artigo explica como determinar se escalonamento de bloqueios está a causar bloquear e como lidar com escalonamento de bloqueios não desejadas.

Mais Informação

Como determinar se escalonamento de bloqueios está a causar o bloqueio

Escalonamento de bloqueios não faz com que a maior parte dos problemas de bloqueios. Para determinar se escalonamento de bloqueios está a ocorrer à volta a hora quando tiver problemas de bloqueio, inicie um rastreio de SQL Profiler que inclui o evento : Escalonamento de bloqueios . Se não conseguir ver quaisquer eventos de Escalonamento de bloqueios: escalonamento de bloqueios não está a ocorrer no servidor e as informações neste artigo não se aplicam à sua situação.

Se o escalonamento de bloqueios está a ocorrer, verifique se o bloqueio de tabela escalated está a bloquear outros utilizadores.

Para obter mais informações sobre como identificar o Bloqueador de cabeça e como identificar o recurso de bloqueio mantido pelo Bloqueador de janelas da cabeça de impressão que está a bloquear outro ID (SPID) do processo de servidor, clique no número de artigo que se segue para visualizar o artigo na Microsoft Knowledge Base:
224453Noções sobre e resolver problemas de bloqueio de 2000 ou SQL Server 7.0
Se o bloqueio que está a bloquear outros utilizadores for diferente de um bloqueio (nível de tabela) de tabulação com um modo de bloqueio de S (partilhado) ou X (exclusivo), escalonamento de bloqueios não é o problema. Em particular, se o bloqueio de tabulação é um bloqueio tipo (tal como um modo de bloqueio de É, IU ou IX), não se trata o resultado de escalonamento de bloqueios. Se os problemas de bloqueios não estão a ser causados por escalonamento de bloqueios, consulte o artigo Q224453 passos de resolução de problemas.

Como impedir o escalonamento de bloqueios

A forma mais simples e mais segura para evitar o escalonamento de bloqueios é manter transacções pequenas e reduzir requisitos de espaço o bloqueio de consultas dispendiosas para que os limiares de escalonamento de bloqueio não forem excedidos. Existem várias formas para obter este objectivo, muitas das quais são listadas:
  • Divida operações batch grande para várias operações mais pequenas. Por exemplo, suponha que executou a consulta seguinte para remover várias centena mil registos antigos de uma tabela de auditoria e, em seguida, encontrar o que causou escalonamento de bloqueios que outros utilizadores bloqueados:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'						
    por remover estes registos algumas centenas num momento, pode reduzir drasticamente o número de bloqueios que acumular por transacção e evitar o escalonamento de bloqueios. Por exemplo:
    SET ROWCOUNT 500
    delete_more:
         DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    IF @@ROWCOUNT > 0 GOTO delete_more
    SET ROWCOUNT 0
  • Reduza bloqueio requisitos de espaço a consulta, tornando a consulta tão eficiente quanto possível. Análises grandes ou grande número de pesquisas de marcador pode aumentar as hipóteses de escalonamento de bloqueios; além disso, aumenta as hipóteses de bloqueios e, geralmente prejudica concorrência e desempenho. Depois de localizar a consulta que faz com que o escalonamento de bloqueios, procure oportunidades para criar novos índices ou para adicionar colunas a um índice existente para remover verificações de índice remissivo ou índice e para maximizar a eficiência do índice de procura. Considere a colar a consulta para uma janela de consulta de analisador de consultas para efectuar uma análise automática índice no mesmo. Para o fazer, no menu de consulta , clique em Assistente de optimização índice no SQL Server 2000 ou clique em Executar a análise de índice remissivo no SQL Server 7.0.

    Um objectivo esta optimização é efectuar índice procura devolver linhas o menor número possível para minimizar o custo de pesquisas de marcador (Maximizar a selecção do índice para a consulta específica). Se SQL Server prevê que um operador lógico marcador pesquisa poderá devolver número de linhas, poderá utilizar uma pré-OBTENÇÃO para efectuar a pesquisa de marcador. Se SQL Server utilizar pré-OBTENÇÃO para uma pesquisa de marcador, tem de aumentar o nível de isolamento de transacção de uma parte da consulta para leitura repeatable para uma parte da consulta. Isto significa que o que poderá ser semelhante para uma instrução SELECT num nível de isolamento de leitura consolidadas poderá adquirir vários milhares de bloqueios chaves (no índice agrupado e um índice agrupado), que podem fazer com que tal uma consulta excedem os limiares de escalonamento de bloqueio. Isto é especialmente importante se encontrar o bloqueio escalated é um bloqueio de tabela partilhados, que, no entanto, não é normalmente apresentado ao nível da leitura consolidadas isolamento predefinido. Se uma cláusula de marcador pesquisa WITH obtenção PRÉVIA está a causar o escalonamento, considere adicionar colunas adicionais para o índice agrupado que aparece na procurar o índice remissivo ou o operador lógico verificação do índice remissivo abaixo o operador lógico de pesquisa de marcador no plano de consulta. Poderá ser possível criar um índice de cobertura (um índice que inclui todas as colunas num índice que foram utilizados na consulta) ou, pelo menos, um índice que abrange as colunas que foram utilizadas para critérios de união ou na cláusula WHERE se incluir tudo na lista Seleccionar coluna é impraticável.

    Uma associação de ciclo aninhado também pode utilizar a pré-OBTENÇÃO e isto faz com que o mesmo comportamento de bloqueio.

    Para obter mais informações, clique no número de artigo que se segue para visualizar o artigo na Microsoft Knowledge Base:
    260652Associação de ciclo aninhado que utiliza um "BOOKMARK Proc.. pré-OBTENÇÃO .WITH" poderá conter mais de bloqueios
  • Escalonamento de bloqueios não pode ocorrer se um SPID diferente possui actualmente um bloqueio de tabela incompatível. Escalonamento de bloqueios sempre escalates um bloqueio de tabela e nunca bloqueios de página. Além disso, se uma tentativa de escalonamento de bloqueio falhar porque outro SPID contém um bloqueio TAB incompatível, a consulta que tentou escalonamento não bloqueia ao aguardar por um bloqueio de tabulação. Em vez disso, continua a adquirir bloqueios ao seu nível mais granular original (linha, chave ou página), tenta efectuar periodicamente escalonamento adicional. Por conseguinte, um método para evitar o escalonamento de bloqueios numa determinada tabela é para adquirir e manter um bloqueio numa ligação diferente que não é compatível com o tipo de bloqueio escalated. Um bloqueio IX (tipo exclusivo) ao nível da tabela não bloqueia quaisquer linhas ou páginas, mas é ainda não é compatível com um S escalated (partilhado) ou X bloqueio TAB (exclusivo). Por exemplo, suponha que tem de executar um processo que modifica um grande número de linhas na tabela tabelaprivada e que provocou o bloqueio que ocorre devido a escalonamento de bloqueios. Se sempre concluir esta tarefa em menos de uma hora, pode criar uma tarefa de Transact-SQL que contém o código seguinte e, agendar a nova tarefa para iniciar vários minutos antes de hora de início do processo:
    BEGIN TRAN
    SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
    WAITFOR DELAY '1:00:00'
    COMMIT TRAN				
    esta consulta aquisições e contém um bloqueio IX no tabelaprivada durante uma hora, o que impede o escalonamento de bloqueios na tabela durante esse período. Este processo não modificar quaisquer dados ou bloquear outras consultas (a menos que a consulta força um bloqueio de tabela com a dica TABLOCK ou se um administrador desactivou bloqueios de página ou linha, utilizando um sp_indexoption procedimento armazenado).
Além disso, pode desactivar o escalonamento de bloqueios activando o sinalizador de rastreamento 1211. No entanto, este sinalizador de rastreio desactiva todos os escalonamento de bloqueios globalmente na instância do SQL Server. Escalonamento de bloqueios tem uma finalidade muito úteis no SQL Server maximizando a eficácia das consultas que caso contrário, mais lento pelo custo de aquisição e lançar vários milhares de bloqueios. Bloquear escalonamento também ajuda a minimizar a memória necessária para manter um registo dos bloqueios. A memória do SQL Server pode atribuir dinamicamente para estruturas de bloqueio é finita, pelo que se desactivar o escalonamento de bloqueios e a memória de bloqueio for suficientemente grande, tenta atribuir bloqueios adicionais para uma consulta poderá falhar e ocorre o seguinte erro:

Erro: 1204, gravidade: 19, estado: 1
O SQL Server não consegue obter um recurso LOCK neste momento. Volte a executar a instrução quando existem menos utilizadores activos ou peça ao administrador do sistema para verificar a configuração de memória e bloqueio do SQL Server.
Nota Quando ocorre um erro de "1204", pára o processamento de declaração actual e faz com que uma recuperação de transacção activa. A anulação propriamente dito pode impedir que os utilizadores ou conduzir a uma hora de recuperação da base de dados longo se reiniciar o serviço SQL Server.

Utilizar uma sugestão de bloqueio como bloqueio linha altera apenas o plano inicial de bloqueio. Sugestões de bloqueio não impedem o escalonamento de bloqueios.

Os outros métodos de impedir escalonamento de bloqueios abordados neste artigo são opções melhor do que activar o sinalizador de rastreio. Além disso, os outros métodos resultam normalmente num melhor desempenho para a consulta de desactivar o escalonamento de bloqueios para a instância todo. A Microsoft recomenda activar este sinalizador de rastreio apenas atenuar bloquear grave é causado por escalonamento de bloqueios ao outras opções, tais como esses discutidas anteriormente neste artigo, estão a ser investigadas. Para activar um sinalizador de rastreio de modo a que está ligado sempre que SQL Server é iniciado, adicioná-lo como um parâmetro de arranque do servidor.

Para adicionar um parâmetro de arranque do servidor, clique com o botão direito do rato no servidor no SQL Enterprise Manager, clique em Propriedades e, em seguida, no separador Geral , clique em Parâmetros de inicialização e, em seguida, adicione o parâmetro seguinte (tal como mostrado):
-T1211
Tem a percorre o serviço SQL Server para um novo parâmetro de arranque entrem em vigor. Se executar a seguinte consulta no Query Analyzer o sinalizador de rastreamento entra em vigor imediatamente:
DBCC TRACEON (1211, -1)				
no entanto, se não adicionar o -T1211 parâmetro de arranque, o efeito de um comando traceon perdem quando o serviço SQL Server seja desligado e ligado. Activar o sinalizador de rastreamento impede qualquer escalations bloquear futuros, mas não inversa qualquer escalations de bloqueio que já ocorreram uma transacção activa.

Propriedades

Artigo: 323630 - Última revisão: 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 2005 Server Enterprise
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL 2005 Server Workgroup
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 revisto ou traduzido por humanos. A Microsoft tem artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais. O objectivo é simples: oferecer em Português a totalidade dos artigos existentes na base de dados do suporte. Sabemos no entanto que a tradução automática não é sempre perfeita. Esta pode conter erros de vocabulário, sintaxe ou gramática? erros semelhantes aos que um estrangeiro realiza ao falar em Português. A Microsoft não é responsável por incoerências, erros ou estragos realizados na sequência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza actualizações frequentes 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