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

Como determinar se escalonamento de bloqueio é que causam bloqueio

Voltar para o início

Como evitar o escalonamento de bloqueio

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

DBCC TRACEON (1211, -1)				

Voltar para o início