SQL Server

Minimize o bloqueio no SQL Server

Cherié Warren

Visão geral:

• Por que o escalonamento de bloqueios ocorre
• Evitando bloqueios desnecessários
• Otimizando suas consultas
• Monitorando o impacto do bloqueio do desempenho

O bloqueio é necessário para o suporte a atividades de leitura e de gravação simultâneas em um banco de dados, mas pode afetar de forma negativa o desempenho do sistema, às vezes de maneira sutil. Neste artigo, examinarei como otimizar seu banco de dados do SQL Server 2005 ou SQL Server 2008 para minimizar

o bloqueio, bem como para monitorar o sistema para que você possa entender melhor como é o impacto do bloqueio sobre o desempenho.

Bloqueio e escalonamento

O SQL Server® escolhe o detalhamento mais apropriado de bloqueio, com base em quantos registros são afetados e que atividade simultânea existe no sistema. Por padrão, o SQL Server seleciona o menor detalhamento de bloqueio possível, escolhendo apenas bloqueios com detalhamentos mais brutos se ele puder usar de forma mais eficiente a memória do sistema. O SQL Server irá escalonar um bloqueio se o escalonamento beneficiar todo o desempenho do sistema. Como exibido na Figura 1, os escalonamentos ocorrerão quando o número de bloqueios em uma verificação particular exceder 5 mil ou quando a memória usada para os bloqueios pelo sistema exceder a capacidade disponível:

Figure 1** Condições que causam escalonamento de bloqueio **(Clique na imagem para aumentar a exibição)

• 24% da memória não-AWE (que não é de extensão do endereço baseada em janelas) usada pelo mecanismo de banco de dados se a configuração de bloqueio for 0
• 40% da memória não-AWE usada pelo mecanismo de banco de dados se a configuração de bloqueio não for 0

Se um escalonamento realmente ocorrer, será sempre para um bloqueio de tabela.

Evitando bloqueios desnecessários

O bloqueio pode ocorrer em qualquer detalhamento de bloqueio, mas a exposição do bloqueio é aumentada quando ocorrem escalonamentos. O escalonamento de bloqueio pode ser um sinal de que o seu aplicativo foi projetado, codificado ou configurado de modo ineficiente.

Respeitar os conceitos básicos de projeto de bancos de dados (como usar um esquema normalizado com chaves estreitas e evitar operações de dados em massa em sistemas transacionais) é importante para evitar o bloqueio. Se esses princípios não forem seguidos (como separar o sistema de relatórios do sistema transacional ou processar alimentações de dados fora do horário do expediente), será difícil ajustar o sistema.

A indexação pode ser um fator crucial para se determinar quantos bloqueios são necessários para acessar os dados. Um índice pode reduzir o número de registros acessados por uma consulta, reduzindo o número de pesquisas internas que o mecanismo de banco de dados precisa executar. Por exemplo, quando você seleciona uma única linha a partir de uma tabela em uma coluna não-indexada, cada linha da tabela precisa ser temporariamente bloqueada até que o registro desejado seja identificado. Por outro lado, se essa coluna fosse indexada, apenas um único bloqueio seria necessário.

O SQL Server 2005 e o SQL Server 2008 contêm exibições de gerenciamento dinâmico (sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_details) que revelam tabelas e colunas que se beneficiariam de índices, com base em estatísticas de uso acumuladas.

A fragmentação também pode estar envolvida em problemas de desempenho, já que o mecanismo do banco de dados pode precisar acessar mais páginas do que precisaria normalmente. Além disso, estatísticas incorretas podem levar o otimizador de consultas a escolher um plano menos eficiente.

Tenha em mente que embora os índices acelerem o acesso aos dados, eles podem retardar a modificação de dados porque não apenas os dados subjacentes precisam ser alterados, mas também os índices precisam de atualização da mesma forma. Uma exibição de gerenciamento dinâmico sys.dm_db_index_usage_stats destaca com que freqüência os índices estão sendo usados. Um exemplo comum de indexação ineficiente ocorre com índices compostos, onde a mesma coluna é indexada em isolamento e em combinação. Como o SQL Server acessa índices da esquerda para a direita, o índice será usado desde que as colunas mais à esquerda sejam úteis.

Particionar tabelas pode tanto otimizar o sistema (para que haja menos exposição de bloqueio) e dividir os dados em objetos físicos separados que podem ser separadamente sustentados. Embora permitir as partições de linha seja uma maneira mais óbvia de separar os dados, particionar os dados horizontalmente é outra opção a ser considerada. Você pode escolher intencionalmente desnormalizar, dividindo uma tabela em tabelas separadas com o mesmo número de linhas e chaves, mas diferentes colunas, para reduzir as chances de processos separados desejarem acessar exclusivamente os dados ao mesmo tempo.

Quanto mais diversas as maneiras que um aplicativo possa acessar uma linha particular de dados e quanto mais colunas sejam incluídas nessa linha, mais atrativa será uma abordagem de particionamento de coluna. O enfileiramento de aplicativos e as tabelas de estado podem às vezes se beneficiar dessa abordagem. O SQL Server 2008 adiciona a capacidade de desativar os escalonamentos de bloqueio por partição (ou por tabela, quando as partições não forem ativadas para a tabela).

Otimização de consulta

A otimização de consulta representa um papel importante no aprimoramento do desempenho. Veja três abordagens que podem ser tomadas:

Reduzir a transação Uma das maneiras mais importantes de reduzir o bloqueio, bem como melhorar o desempenho geral, é garantir que as transações sejam as menores possíveis. Qualquer processamento não importante para a integridade da transação (como pesquisar dados relacionados, indexar e depurar dados) deve ser retirado para reduzir seu tamanho.

O SQL trata cada instrução como uma transação implícita. Se a instrução afetar um grande número de linhas, uma única instrução poderá ainda constituir uma transação grande, especialmente se existirem várias colunas envolvidas ou se as colunas contiverem tipos de dados grandes. Uma única instrução pode também causar divisões de páginas se o fator de preenchimento estiver alto ou se uma instrução UPDATE estiver preenchendo uma coluna com um valor mais amplo do que foi alocado. Em tais circunstâncias, pode ser útil dividir a transação em grupos de linhas, processando-os um de cada vez até ficarem concluídos. O processamento em lote deve ser considerado apenas quando a instrução individual ou grupos de instruções puderem ser divididos em menores lotes que ainda podem ser considerados completos como uma unidade de trabalho, se forem bem-sucedidos ou falharem.

Ordenar a transação Dentro da transação, o seqüenciamento intencional das instruções pode diminuir a probabilidade de bloqueio. Há dois princípios para se ter em mente. Primeiro, acesse objetos na mesma ordem dentro de todos os códigos do SQL no seu sistema. Sem uma ordem consistente, os bloqueios podem ocorrer onde dois processos concorrentes acessam dados em uma ordem diferente, causando um erro de sistema para um dos processos. Segundo, coloque objetos freqüentemente acessados ou caros de acessar no final da transação. O SQL espera para bloquear os objetos até que eles sejam necessários na transação. Atrasar o acesso aos "pontos de acesso" permite que esses objetos segurem o bloqueio por uma menor porcentagem de tempo.

Use dicas de travamento As dicas de travamento podem ser usadas no nível da sessão ou da instrução para uma tabela ou exibição específica. Um cenário típico para usar uma dica de nível de sessão seria um processamento em lote em um data warehouse, onde o desenvolvedor sabe que o processo será o único em execução em um determinado momento nesse conjunto de dados. Usando um comando como SET ISOLATION LEVEL READ UNCOMMITTED no início do procedimento armazenado, o SQL Server não reservará nenhum bloqueio de leitura, reduzindo, portanto, a sobrecarga de bloqueio geral e aumentando o desempenho.

Um cenário típico para usar as dicas de nível de instrução seria quando o desenvolvedor sabe que uma leitura suja pode ocorrer com segurança (como ao ler uma única linha de uma tabela onde outros processos simultâneos nunca precisarão da mesma linha), ou quando todos os outros esforços de ajuste de desempenho tiverem falhado (design do esquema, design do índice e manutenção e ajuste de consulta) e o desenvolvedor deseja forçar o compilador a usar um tipo específico de dica.

Dicas de bloqueio de linha podem fazer sentido se o monitoramento demonstrar que bloqueios com maior detalhamento tiverem ocorrido onde poucos registros são afetados pela consulta, uma vez que isso poderia reduzir o bloqueio. Dicas de bloqueio de tabela podem fazer sentido se o monitoramento demonstrar que bloqueios com menor detalhamento estiverem sendo mantidos (e não escalonados) quando quase todos os registros da tabela forem afetados pela consulta, uma vez que isso poderia reduzir os recursos do sistema necessários para manter os bloqueios. Observe que especificar uma dica de bloqueio não garante que o bloqueio não será escalonado quando o número de bloqueios atingir o limite da memória do sistema. No entanto, isso impedirá todos os outros escalonamentos.

Ajuste sua configuração

Conforme exibido na Figura 2, existem vários fatores a considerar ao configurar seu sistema SQL Server.

Figure 2** Como o SQL Server determina a quantidade de memória que pode ser usada para o bloqueio **(Clique na imagem para aumentar a exibição)

Memória Os bloqueios são normalmente realizados na memória não-AWE. Portanto, aumentar o tamanho da memória não-AWE aumentará a capacidade de o sistema realizar bloqueios.

Uma arquitetura de 64 bits deve ser a primeira escolha ao tentar aumentar a capacidade de bloqueio, uma vez que a arquitetura de 32 bits é limitada a 4 GB de memória não-AWE, enquanto que a de 64 bits não possui limite algum.

Em sistemas de 32 bits, você pode obter um gigabyte adicional de memória a partir do sistema operacional para o SQL Server adicionando a opção /3GB ao arquivo Boot.ini.

Configurações do SQL Server Uma variedade de configurações pode ser ajustada por sp_configure que afeta o bloqueio. A configuração de bloqueio configura quantos bloqueios podem ser realizados pelo sistema antes que ocorra um erro. Por padrão, a configuração é 0, o que significa que o servidor irá dinamicamente ajustar os bloqueios reservados com outros processos que competem por memória. O SQL irá reservar inicialmente 2.500 bloqueios e cada bloqueio consome 96 bytes de memória. A memória paginável não é utilizada.

As configurações mínimas e máximas de memória reservam a quantidade de memória usada pelo SQL Server, configurando, portanto, o servidor para manter estaticamente a memória. Como o escalonamento de bloqueio está relacionado à memória disponível, reservar a quantidade de memória de processos concorrentes pode fazer uma diferença com relação à ocorrência ou não dos escalonamentos.

Configurações de conexão Por padrão, os bloqueios que estão obstruindo não têm tempo limite, mas você pode usar a configuração @@LOCK_TIMEOUT, que faz com que um erro ocorra se o limite de espera especificado para um bloqueio ser lançado for excedido.

Sinalizadores de rastreamento Dois sinalizadores de rastreamento em particular se relacionam a escalonamentos de bloqueios. Um é o sinalizador de rastreamento 1211, que desabilita os escalonamentos de bloqueio. Se o número de bloqueios consumidos exceder a memória disponível, ocorrerá um erro. O outro é o sinalizador de rastreamento 1224, que desabilita os escalonamentos de bloqueio para instruções individuais.

Observando seu sistema

Leitura adicional

• Inside Microsoft SQL Server 2005: The Storage Engine por Kalen Delaney
• Como monitorar o bloqueio no SQL Server 2005 e no SQL Server 2000
• Script de estatísticas de desempenho do SQL Server 2005
• Como resolver problemas de bloqueio causados por escalonamento de bloqueio no SQL Server
• Escalonamento de bloqueio (mecanismo de banco de dados)

O impacto que o bloqueio exerce no desempenho geral do sistema pode ser monitorado pela sondagem de dados de estado a intervalos determinados (talvez a cada hora) e pela captura de estatísticas de execução nos bloqueios realizados. As principais informações para capturar são:

• Objeto afetado, detalhes e tipo de bloqueio
• Duração dos bloqueios e bloqueadores
• Comando SQL sendo emitido (nome do procedimento armazenado, instrução do SQL interna)
• Informações sobre a cadeia de bloqueio, onde relevante
• Como o sistema está consumindo sua capacidade de bloqueio disponível

Você pode executar um script como o da Figura 3 para capturar essas informações, escrevendo-o em uma tabela com o carimbo de data/hora relevante. E para dividir mais o ResourceId dos dados sendo bloqueados, você pode executar um script como o da Figura 4.

Figure 4 Learning more about blocked data

DECLARE @SQL                           nvarchar(max)
      , @CallingResourceType           varchar(30)
      , @Objectname                    sysname
      , @DBName                        sysname
      , @resource_associated_entity_id int

-- TODO: Set the variables for the object you wish to look up

SET @SQL = N'
USE     ' + @DbName + N'
DECLARE @ObjectId int

SELECT  @ObjectId = CASE
                    WHEN @CallingResourceType = ''OBJECT''
                    THEN @resource_associated_entity_id
                    WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'')
                    THEN (SELECT  object_id
                          FROM    sys.partitions 
                          WHERE   hobt_id = @resource_associated_entity_id)
                    WHEN @CallingResourceType = ''ALLOCATION_UNIT''
                    THEN (SELECT  CASE
                                     WHEN type IN (1, 3)
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions 
                                           WHERE   hobt_id = allocation_unit_id)
                                     WHEN type = 2
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions 
                                           WHERE   partition_id = allocation_unit_id)
                                     ELSE NULL
                                     END
                          FROM    sys.allocation_units 
                          WHERE   allocation_unit_id = @resource_associated_entity_id)
                    ELSE NULL
                    END

SELECT  @ObjectName = OBJECT_NAME(@ObjectId)'

EXEC    dbo.sp_executeSQL
        @SQL
      , N'@CallingResourceType varchar(30)
      , @resource_associated_entity_id int
      , @ObjectName sysname OUTPUT'
      , @resource_associated_entity_id = @resource_associated_entity_id
      , @CallingResourceType = @CallingResourceType
      , @ObjectName = @ObjectName OUTPUT

Figure 3 Capturing locking stats

SELECT  er.wait_time                      AS WaitMSQty
      , er.session_id                     AS CallingSpId
      , LEFT(nt_user_name, 30)            AS CallingUserName
      , LEFT(ces.program_name, 40)        AS CallingProgramName
      , er.blocking_session_id            AS BlockingSpId
      , DB_NAME(er.database_id)           AS DbName
      , CAST(csql.text AS varchar(255))   AS CallingSQL
      , clck.CallingResourceId
      , clck.CallingResourceType
      , clck.CallingRequestMode
      , CAST(bsql.text AS varchar(255))   AS BlockingSQL
      , blck.BlockingResourceType
      , blck.BlockingRequestMode
FROM    master.sys.dm_exec_requests er WITH (NOLOCK)
        JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK)
          ON er.session_id = ces.session_id
        CROSS APPLY fn_get_sql (er.sql_handle) csql
        JOIN (
-- Retrieve lock information for calling process, return only one record to 
-- report information at the session level
              SELECT  cl.request_session_id                 AS CallingSpId
                    , MIN(cl.resource_associated_entity_id) AS CallingResourceId
                    , MIN(LEFT(cl.resource_type, 30))       AS CallingResourceType
                    , MIN(LEFT(cl.request_mode, 30))        AS CallingRequestMode 
-- (i.e. schema, update, etc.)
              FROM    master.sys.dm_tran_locks cl WITH (nolock)
              WHERE   cl.request_status = 'WAIT' -- Status of the lock request = waiting
              GROUP BY cl.request_session_id
              ) AS clck
           ON er.session_id = clck.CallingSpid
         JOIN (
              -- Retrieve lock information for blocking process
              -- Only one record will be returned (one possibility, for instance, 
              -- is for multiple row locks to occur)
              SELECT  bl.request_session_id            AS BlockingSpId
                    , bl.resource_associated_entity_id AS BlockingResourceId
                    , MIN(LEFT(bl.resource_type, 30))  AS BlockingResourceType
                    , MIN(LEFT(bl.request_mode, 30))   AS BlockingRequestMode
              FROM    master.sys.dm_tran_locks bl WITH (nolock)
              GROUP BY bl.request_session_id
                    , bl.resource_associated_entity_id 
              ) AS blck
           ON er.blocking_session_id = blck.BlockingSpId
          AND clck.CallingResourceId = blck.BlockingResourceId
        JOIN master.sys.dm_exec_connections ber WITH (NOLOCK)
          ON er.blocking_session_id = ber.session_id
        CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql
WHERE   ces.is_user_process = 1
        AND er.wait_time > 0

Você também pode monitorar seu sistema quanto a escalonamentos pelo SQL Profiler (evento Lock:Escalation), a exibição de gerenciamento dinâmico dm_db_index_operational_stats (index_lock_promotion_count) ou a sondagem regular de informações de bloqueio do sistema. As informações pertinentes são coletadas do monitoramento de escalonamento se o processamento garante um escalonamento; caso contrário, os procedimentos armazenados relevantes podem identificar uma causa principal para problemas de desempenho. As tabelas com grandes quantidades de dados ou alto uso simultâneo devem ser o foco principal da avaliação.

Após coletar dados sobre travamentos, bloqueios e escalonamentos, os dados podem ser analisados para determinar o bloqueio cumulativo e o tempo de travamento (número de incidências multiplicado pela duração de incidências) por objeto. Normalmente, isso pode iniciar um ciclo iterativo de ajuste de desempenho onde as alterações são implantadas, monitoradas, analisadas e corrigidas. Às vezes, só é necessária uma simples alteração, como adicionar um índice para fazer uma melhoria de desempenho significativa que irá alterar qual área no sistema constitui o gargalo de desempenho mais penoso.

Você pode obter mais informações sobre a redução do bloqueio no SQL Server no quadro “Leitura adicional”. Se for dada uma atenção especial à manutenção das transações pequenas por todas as fases de design, codificação e estabilização, vários problemas de bloqueio poderão ser minimizados. O hardware adequado pode também reduzir significativamente a probabilidade de escalonamentos indesejados. Seja como for, a avaliação contínua do bloqueio no sistema pode rapidamente identificar problemas de desempenho na sua raiz.

Cherié Warren é uma líder de desenvolvimento sênior para a TI da Microsoft. Ela é responsável atualmente por um dos maiores bancos de dados transacionais na Microsoft. Cherié também consulta freqüentemente as causas principais e resolve problemas de desempenho relacionados a bloqueios. Ela se especializa em bancos de dados do SQL Server de nível empresarial há 10 anos.

© 2008 Microsoft Corporation e CMP Media, LLC. Todos os direitos reservados. A reprodução parcial ou completa sem autorização é proibida..