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

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

Voltar ao topo

Como impedir o escalonamento de bloqueios

• 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:
  260652  (http://support.microsoft.com/kb/260652/ ) Associaçã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).

DBCC TRACEON (1211, -1)				

Voltar ao topo