Diagnosticar e resolver contenções de trava no SQL Server

Este guia descreve como identificar e resolver problemas de contenção de trava observados durante a execução de aplicativos do SQL Server em sistemas de alta simultaneidade com algumas cargas de trabalho.

À medida que o número de núcleos de CPU nos servidores continua aumentando, o aumento associado na simultaneidade pode introduzir pontos de contenção em estruturas de dados que precisam ser acessadas de maneira serial no mecanismo de banco de dados. Isso vale especialmente para cargas de trabalho de processamento de transações (OLTP) com alta taxa de transferência/alta simultaneidade. Há várias ferramentas, técnicas e maneiras de abordar esses desafios, bem como práticas que podem ser seguidas na criação de aplicativos que podem ajudar a evitá-los completamente. Este artigo aborda um tipo específico de contenção em estruturas de dados que usam spinlocks para serializar o acesso a essas estruturas de dados.

O que é a contenção de trava do SQL Server?

Travas são primitivos de sincronização leves usados pelo mecanismo do SQL Server para garantir a consistência das estruturas na memória, incluindo índices, páginas de dados e estruturas internas (como páginas não folha em uma Árvore B). O SQL Server usa travas de buffer para proteger páginas no pool de buffers e travas de E/S para proteger páginas ainda não carregadas no pool de buffers. Sempre que dados são gravados ou lidos de uma página no pool de buffers do SQL Server, um thread de trabalho precisa adquirir uma trava de buffer para a página primeiro. Há vários tipos de trava de buffer disponíveis para acessar páginas no pool de buffers, incluindo trava exclusiva (PAGELATCH_EX) e trava compartilhada (PAGELATCH_SH). Quando o SQL Server tenta acessar uma página que ainda não está presente no pool de buffers, uma E/S assíncrona é postada para carregar a página no pool de buffers. Se o SQL Server precisar aguardar a resposta do subsistema de E/S, ele aguardará uma trava de E/S exclusiva (PAGEIOLATCH_EX) ou compartilhada (PAGEIOLATCH_SH) dependendo do tipo de solicitação; isso é feito para impedir que outro thread de trabalho carregue a mesma página no pool de buffers com uma trava incompatível. As travas também são usadas para proteger o acesso a estruturas de memória internas que não sejam as páginas do pool de buffers; elas são conhecidas como "travas de não buffer".

Contenção em travas de página é o cenário mais comum encontrado em sistemas com várias CPUs e, sendo assim, a maior parte deste artigo se concentra nesses sistemas.

A contenção de trava ocorre quando vários threads tentam adquirir simultaneamente travas incompatíveis na mesma estrutura na memória. Como uma trava é um mecanismo de controle interno, o mecanismo do banco de dados SQL determina automaticamente quando usá-las. Uma vez que o comportamento das travas é determinístico, as decisões do aplicativo, incluindo o design de esquema, podem afetar esse comportamento. Este artigo tem a finalidade de fornecer as seguintes informações:

• Informações de contexto sobre como as travas são usadas pelo SQL Server.
• Ferramentas usadas para investigar a contenção de trava.
• Como determinar se a quantidade de contenção observada é problemática.

Discutimos alguns cenários comuns e a melhor maneira de lidar com eles para aliviar a contenção.

Como o SQL Server usa travas?

Uma página no SQL Server tem 8 KB e pode armazenar várias linhas. Para aumentar a simultaneidade e o desempenho, as travas de buffer são mantidas apenas pela duração da operação física na página, diferentemente dos bloqueios, que são mantidos pela duração da transação lógica.

As travas são internas ao mecanismo do SQL e são usadas para fornecer consistência de memória, enquanto os bloqueios são usados pelo SQL Server para fornecer consistência transacional lógica. A seguinte tabela compara travas e bloqueios:

Compatibilidade e modos de trava do SQL Server

É esperado que haja alguma contenção de trava como parte normal da operação do mecanismo do SQL Server. É inevitável que várias solicitações de trava simultâneas com diferentes níveis de compatibilidade ocorram em um sistema de alta concorrência. O SQL Server impõe a compatibilidade de travas exigindo que as solicitações de trava incompatíveis aguardem em uma fila até que as solicitações de trava pendentes sejam concluídas.

Há cinco modos diferentes de aquisição de travas, que estão relacionados ao nível de acesso. Os modos de trava do SQL Server podem ser resumidos da seguinte maneira:

• KP: trava de manutenção. Garante que a estrutura referenciada não possa ser destruída. Usado quando um thread deseja examinar uma estrutura de buffer. Como a trava KP é compatível com todas as travas, exceto a trava de destruição (DT), a trava KP é considerada leve, o que significa que o efeito no desempenho ao usá-la é mínimo. Como a trava KP é incompatível com a trava DT, ela impede que qualquer outro thread destrua a estrutura referenciada. Por exemplo, uma trava KP impede que a estrutura que referencia seja destruída pelo processo lazy writer. Para obter mais informações sobre como o processo lazy writer é usado com o gerenciamento de páginas de buffer do SQL Server, consulte Gravar páginas no Mecanismo de Banco de Dados.

• SH: trava compartilhada. Necessário para ler a estrutura referenciada (por exemplo, ler uma página de dados). Vários threads podem acessar simultaneamente um recurso para leitura em uma trava compartilhada.

• UP: trava de atualização. Compatível com SH (trava compartilhada) e KP, mas não com outras. Sendo assim, não permite que uma trava EX seja gravada na estrutura referenciada.

• EX: trava exclusiva. Impede que outros threads gravem e leiam na estrutura referenciada. Um exemplo de uso seria modificar o conteúdo de uma página para proteção de página interrompida.

• DT: trava de destruição. Deve ser adquirida antes de destruir o conteúdo da estrutura referenciada. Por exemplo, uma trava DT precisa ser adquirida pelo processo lazy writer para liberar uma página limpa antes de adicioná-la à lista de buffers livres disponíveis para uso por outros threads.

Os modos de fecho têm níveis diferentes de compatibilidade, por exemplo, um fecho compartilhado (SH) é compatível com um fecho de atualização (UP) ou de preservação (KP), mas incompatível com um fecho de destruição (DT). Várias travas podem ser adquiridas simultaneamente na mesma estrutura, desde que sejam compatíveis. Quando um thread tenta adquirir uma trava mantida em um modo que não é compatível, ele é colocado em uma fila para aguardar um sinal indicando que o recurso está disponível. Um spinlock do tipo SOS_Task é usado para proteger a fila de espera impondo o acesso serializado à fila. Esse spinlock deve ser adquirido para adicionar itens à fila. O spinlock SOS_Task também sinaliza aos threads na fila quando travas incompatíveis são liberadas, permitindo que os threads em espera adquiram uma trava compatível e continuem funcionando. A fila de espera é processada de maneira PEPS (primeiro a entrar, primeiro a sair) conforme as solicitações de trava são liberadas. As travas seguem esse sistema PEPS para garantir a imparcialidade e para evitar a privação dos threads.

A compatibilidade do modo de travamento está listada na tabela a seguir (Sim indica compatibilidade e Não indica incompatibilidade):

SuperLatches e subtravas do SQL Server

Com a presença crescente de sistemas multi-núcleos/soquetes baseados em NUMA, o SQL Server 2005 introduziu superlatches, também conhecidos como sublatches, que são eficazes apenas em sistemas com 32 ou mais processadores lógicos. As SuperLatches melhoram a eficiência do mecanismo SQL para determinados padrões de uso em cargas de trabalho OLTP altamente simultâneas; por exemplo, quando determinadas páginas têm um padrão de acesso compartilhado pesado somente leitura (SH), mas são raramente gravadas. Um exemplo de uma página com esse padrão de acesso é uma página raiz de árvore B (ou seja, índice) ; o mecanismo SQL requer que uma trava compartilhada seja mantida na página raiz quando ocorre uma divisão de página em qualquer nível na árvore B. Em uma carga de trabalho OLTP de alta simultaneidade e com uso intenso de inserções, o número de divisões de página aumenta amplamente em paralelo com a taxa de transferência, o que pode prejudicar o desempenho. As SuperLatches podem permitir um aumento no desempenho para o acesso a páginas compartilhadas, em que vários threads de trabalho de execução simultânea exigem travas de SH. Para realizar isso, o mecanismo do SQL Server promove dinamicamente uma trava em uma página para uma Superlatch. Uma SuperLatch particiona uma trava em uma matriz de estruturas de subtrava, com uma subtrava por partição por núcleo de CPU, em que a trava principal se torna um redirecionador de proxy e a sincronização de estado global não é necessária para travas somente leitura. Ao fazer isso, a função de trabalho, que sempre é atribuída a uma CPU específica, só precisa adquirir a subtrava compartilhada (SH) atribuída ao agendador local.

A aquisição de travas compatíveis, como uma SuperLatch compartilhada, usa menos recursos e dimensiona o acesso a páginas frequentes melhor do que uma trava compartilhada não particionada, pois a remoção do requisito de sincronização do estado global aprimora significativamente o desempenho ao acessar somente a memória NUMA local. Por outro lado, a aquisição de uma SuperLatch exclusiva (EX) Superlatch é mais cara do que a aquisição de uma trava EX comum, uma vez que o SQL precisa sinalizar todas as subtravas. Quando é observado que uma SuperLatch usa um padrão de acesso EX pesado, o mecanismo do SQL pode rebaixá-la após a página ser descartada do pool de buffers. O diagrama a seguir mostra uma trava normal e uma Superlatch particionada.

Use o objeto SQL Server:Latches e os contadores associados no Monitor de Desempenho para coletar informações sobre Superlatches, incluindo o número de Superlatches, promoções de Superlatch por segundo e rebaixamentos de Superlatch por segundo. Para obter mais informações sobre o objeto SQL Server:Latches e contadores associados, consulte SQL Server, objeto Latches.

Tipos de tempo de espera de trava

Informações de espera cumulativas são monitoradas pelo SQL Server e podem ser acessadas usando a Exibição de gerenciamento dinâmico (DMW) sys.dm_os_wait_stats. O SQL Server emprega três tipos de tempo de espera de trava, conforme definido pelo elemento wait_type correspondente na DMV sys.dm_os_wait_stats:

• Trava de buffer (BUF): usada para garantir a consistência das páginas de dados e índice para objetos de usuário. Eles também são usados para proteger o acesso a páginas de dados que o SQL Server usa para objetos do sistema. Por exemplo, páginas que gerenciam alocações são protegidas por travas de buffer. Eles incluem as páginas PFS (Espaço Livre de Página), GAM (Mapa de Alocação Global), Mapa de Alocação Global Compartilhado (SGAM) e Mapa de Alocação de Índice (IAM). As travas de buffer são relatadas em sys.dm_os_wait_stats com wait_type de PAGELATCH_*.

• Trava de não buffer (não BUF): usada para garantir a consistência de qualquer estrutura na memória que não sejam as páginas de pool de buffers. Esperas por bloqueios não buffer são relatadas como um wait_type de LATCH_*.

• Trava de E/S: um subconjunto de travas de buffer que garantem a consistência das mesmas estruturas protegidas por travas de buffer quando essas estruturas exigem o carregamento para o pool de buffers com uma operação de E/S. As travas de E/S impedem que outro thread carregue a mesma página no pool de buffers com uma trava incompatível. Associado a um wait_type de PAGEIOLATCH_*.

  Observação

  Se você notar esperas significativas PAGEIOLATCH, isso indica que o SQL Server está aguardando o subsistema de E/S. Embora seja esperado um número de esperas de PAGEIOLATCH e seja um comportamento normal, se o tempo médio de espera de PAGEIOLATCH estiver consistentemente acima de 10 milissegundos (MS), investigue por que o subsistema de E/S está sob pressão.

Se ao examinar a DMV sys.dm_os_wait_stats você encontrar travas de não buffer, sys.dm_os_latch_stats precisará ser examinado para obter um detalhamento de informações de espera cumulativas para travas de não buffer. Todos os tempos de espera de trava de buffer são classificados na classe de trava BUFFER, sendo os restantes usados para classificar travas de não buffer.

Sintomas e causas da contenção de trava no SQL Server

Em um sistema de alta concorrência movimentado, é normal observar competição ativa em estruturas que são frequentemente acessadas e protegidas por travas e outros mecanismos de controle no SQL Server. É considerado problemático quando a contenção e o tempo de espera associados à aquisição de trava para uma página são suficientes para reduzir a utilização de recursos (CPU), o que dificulta o desempenho.

Exemplo de contenção de trava

No diagrama a seguir, a linha azul representa a taxa de transferência no SQL Server, conforme medida pelas Transações por segundo; a linha preta representa o tempo médio de espera da trava da página. Nesse caso, cada transação executa um INSERT em um índice clusterizado com um valor inicial que aumenta sequencialmente, por exemplo, ao popular uma coluna IDENTITY com o tipo de dados bigint. À medida que o número de CPUs aumenta para 32, é evidente que a taxa de transferência geral diminuiu e o tempo de espera da trava de página aumentou para aproximadamente 48 milissegundos, conforme evidenciado pela linha preta. Essa relação inversa entre a taxa de transferência e o tempo de espera de trava da página é um cenário comum que é facilmente diagnosticado.

Desempenho quando a contenção de trava é resolvida

Como ilustra o diagrama a seguir, o SQL Server deixa de sofrer gargalos com os tempos de espera de trava de página e a taxa de transferência aumenta em 300%, conforme medido pelas transações por segundo. Isso foi feito usando a técnica Usar o particionamento de hash com uma coluna computada, descrita posteriormente neste artigo. Esse aprimoramento de desempenho é voltado para sistemas com um alto número de núcleos e um alto nível de simultaneidade.

Fatores que afetam a contenção de trava

Normalmente, a contenção de trava que prejudica o desempenho em ambientes OLTP é causada por uma alta simultaneidade relacionada a um ou mais dos seguintes fatores:

Diagnosticar contenção de trava do SQL Server

Esta seção fornece informações para diagnosticar a contenção de travas do SQL Server para determinar se ela é problemática para seu ambiente.

Ferramentas e métodos para diagnosticar a contenção de trava

As principais ferramentas usadas para diagnosticar a contenção de trava são:

• O Monitor de Desempenho, para monitorar a utilização da CPU e os tempos de espera dentro do SQL Server e estabelecer se há uma relação entre a utilização da CPU e os tempos de espera de trava.

• As DMVs do SQL Server, que podem ser usadas para determinar o tipo específico de trava que está causando o problema e o recurso afetado.

• Em alguns casos, despejos de memória do processo do SQL Server precisam ser obtidos e analisados com as ferramentas de depuração do Windows.

O processo técnico para diagnosticar a contenção de trava pode ser resumido nas seguintes etapas:

1. Determinar se há uma contenção que pode estar relacionada a uma trava.

2. Use as exibições DMV fornecidas no Apêndice: scripts de contenção de trava do SQL Server para determinar o tipo de trava e os recursos afetados.

3. Aliviar a contenção usando uma das técnicas descritas em Lidando com a contenção de trava em diferentes padrões de tabela.

Indicadores de contenção de trava

Conforme mencionado anteriormente, a contenção de trava é problemática apenas quando a contenção e o tempo de espera associado à aquisição de travas de página impedem o aumento da taxa de transferência quando recursos de CPU estão disponíveis. Determinar uma quantidade aceitável de contenção requer uma abordagem holística, que considere os requisitos de desempenho e taxa de transferência em conjunto com os recursos de CPU e E/S disponíveis. Esta seção orienta você a determinar o impacto da contenção de travas na carga de trabalho da seguinte maneira:

1. Meça os tempos de espera gerais durante um teste representativo.
2. Classifique sua ordem.
3. Determine a proporção de tempos de espera relacionados a travas.

Informações de espera cumulativas estão disponíveis a partir do DMV sys.dm_os_wait_stats. O tipo mais comum de contenção de trava é a contenção de trava de buffer, observada como um aumento nos tempos de espera para travas com um wait_type de PAGELATCH_*. Travas de não buffer são agrupadas sob o tipo de espera LATCH*. Como ilustra o diagrama a seguir, você deve, primeiramente, fazer uma análise cumulativa das esperas do sistema usando a DMV sys.dm_os_wait_stats para determinar o percentual do tempo de espera geral causado por travas de buffer e de não buffer. Se você encontrar travas de não buffer, a DMV sys.dm_os_latch_stats também deverá ser examinada.

O diagrama a seguir descreve a relação entre as informações retornadas pelas DMVs sys.dm_os_wait_stats e sys.dm_os_latch_stats.

Para obter mais informações sobre a sys.dm_os_wait_stats DMV, consulte sys.dm_os_wait_stats na ajuda do SQL Server.

Para obter mais informações sobre a sys.dm_os_latch_stats DMV, consulte sys.dm_os_latch_stats na ajuda do SQL Server.

As seguintes medidas de tempo de espera de trava são indicadores de que o excesso de contenção de trava está afetando o desempenho do aplicativo:

• O tempo de espera de trava médio da página aumenta consistentemente com a produtividade: se os tempos de espera de trava médios aumentarem consistentemente com a produtividade e se os tempos de espera de trava médio de buffer também aumentarem para além dos tempos de resposta de disco esperados, examine as tarefas de espera atuais usando a DMV sys.dm_os_waiting_tasks. As médias podem ser enganosas se analisadas isoladamente, portanto, é importante examinar o sistema ativo quando possível para entender as características da carga de trabalho. Especificamente, verifique se há longas esperas em PAGELATCH_EX e/ou PAGELATCH_SH solicitações em qualquer página. Siga estas etapas para diagnosticar um aumento nos tempos médios de espera de trava com a taxa de transferência:

  • Use os scripts de exemplo Consultar sys.dm_os_waiting_tasks ordenadas pela ID da Sessão ou Calcular esperas ao longo de um período para examinar as tarefas atuais com espera e medir o tempo de espera médio de trava.

  • Use o script de exemplo Consultar descritores de buffer para determinar objetos que causam contenção de trava para determinar o índice e a tabela subjacente em que a contenção está ocorrendo.

  • Meça o tempo de espera de trava médio usando o contador do Monitor de Desempenho MSSQL%InstanceName%\Estatísticas de espera\Página de tempo de espera\Tempo de espera médio ou executando a DMV sys.dm_os_wait_stats.

  Observação

  Para calcular o tempo médio de espera de um tipo de espera específico (retornado por sys.dm_os_wait_stats como wt_:type), divida o tempo total de espera (retornado como wait_time_ms) pelo número de tarefas de espera (retornadas como waiting_tasks_count).

• Percentual do tempo de espera total gasto em tipos de espera de trava durante o pico de carga: se o tempo de espera de trava médio como um percentual do tempo de espera geral aumentar em paralelo com a carga de aplicação, a contenção de trava poderá estar afetando o desempenho e deverá ser investigada.

  Meça os tempos de espera de trava de página e os tempos de espera de trava que não são de página com os contadores de desempenho SQL Server, objeto Wait Statistics. Em seguida, compare os valores desses contadores de desempenho com os contadores de desempenho associados à CPU, à E/S, à memória e à taxa de transferência de rede. Por exemplo, transações/s e solicitações em lote/s são duas boas medidas de utilização de recursos.

  Observação

  O tempo de espera relativo para cada tipo de espera não está incluído na sys.dm_os_wait_stats DMV porque essa DMW mede os tempos de espera desde a última vez em que a instância do SQL Server foi iniciada ou as estatísticas de espera cumulativas foram redefinidas usando DBCC SQLPERF. Para calcular o tempo de espera relativo para cada tipo de espera, faça um instantâneo de sys.dm_os_wait_stats antes e depois da carga de pico e calcule a diferença. O script de exemplo Calcular esperas ao longo de um período pode ser usado para essa finalidade.

  Para um ambiente que não é de produção apenas, limpe a sys.dm_os_wait_statsDMV com o seguinte comando:

  DBCC SQLPERF ('sys.dm_os_wait_stats', 'CLEAR');
  

  Um comando semelhante pode ser executado para limpar a DMV sys.dm_os_latch_stats:

  DBCC SQLPERF ('sys.dm_os_latch_stats', 'CLEAR');
  

• A produtividade não aumenta e, em alguns casos, diminui conforme a carga de aplicação aumenta e o número de CPUs disponíveis para o SQL Server aumenta: isso foi ilustrado no Exemplo de contenção da trava.

• A utilização da CPU não aumenta à medida que a carga de trabalho do aplicativo aumenta: se a utilização da CPU no sistema não aumentar à medida que a simultaneidade impulsionada pela taxa de transferência do aplicativo aumenta, esse é um indicador de que o SQL Server está aguardando algo e sintomático de contenção de trava.

Analise a causa raiz. Mesmo que cada uma das condições anteriores seja verdadeira, ainda é possível que a causa raiz dos problemas de desempenho esteja em outro lugar. Na verdade, na maioria dos casos, a utilização de CPU abaixo do ideal é causada por outros tipos de espera, como bloqueio em travas, esperas relacionadas a entrada/saída ou problemas relacionados à rede. Como regra geral, é sempre melhor resolver a espera de recursos que representa a maior proporção do tempo de espera geral antes de prosseguir com uma análise mais detalhada.

Analisar travas de buffer de espera atuais

A contenção de trava do buffer é manifestada como um aumento nos tempos de espera para travas com um wait_type de PAGELATCH_* ou PAGEIOLATCH_*, conforme exibido na DMV sys.dm_os_wait_stats. Para ver o sistema em tempo real, execute a seguinte consulta em um sistema para ingressar nas DMVs sys.dm_os_wait_stats, sys.dm_exec_sessions e sys.dm_exec_requests. Os resultados podem ser usados para determinar o tipo de espera atual para as sessões em execução no servidor.

SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

As estatísticas expostas por essa consulta serão descritas da seguinte maneira:

A consulta a seguir retorna informações para todas as travas não buffer:

SELECT * FROM sys.dm_os_latch_stats
WHERE latch_class <> 'BUFFER'
ORDER BY wait_time_ms DESC;

As estatísticas expostas por essa consulta serão descritas da seguinte maneira:

Os valores retornados por essa DMV são cumulativos desde a última vez que o mecanismo de banco de dados foi reiniciado ou que a DMV foi redefinida. Use a coluna sqlserver_start_time em sys.dm_os_sys_info para localizar a última hora de inicialização do mecanismo de banco de dados. Em um sistema que está sendo executado há muito tempo, isso significa que algumas estatísticas, como max_wait_time_ms, raramente são úteis. O seguinte comando pode ser usado para redefinir as estatísticas de espera para essa DMV:

DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR);

Cenários de contenção de trava do SQL Server

Observou-se que os cenários a seguir causam excesso de contenção de trava.

Contenção de inserção de última página/página à direita

Uma prática de OLTP comum é criar um índice clusterizado em uma coluna de identidade ou data. Isso ajuda a manter uma boa organização física do índice, que pode beneficiar muito o desempenho de leituras e gravações nele. No entanto, esse design de esquema pode levar à contenção de trava de maneira não intencional. Esse problema é visto com mais frequência em tabelas grandes com linhas pequenas, bem como em inserções em um índice que contém uma coluna de chave à esquerda que aumenta sequencialmente, como um inteiro crescente ou uma chave de data e hora. Nesse cenário, o aplicativo raramente (ou nunca) executa atualizações ou exclusões, sendo a exceção as operações de arquivamento.

No exemplo a seguir, os threads um e dois desejam executar uma inserção de um registro que será armazenado na página 299. De uma perspectiva de bloqueio lógica, não há nenhum problema, pois são usados bloqueios no nível da linha e bloqueios exclusivos em nos dois registros na mesma página podem ser mantidos ao mesmo tempo. No entanto, para garantir a integridade da memória física, apenas um thread por vez pode adquirir uma trava exclusiva, para que o acesso à página seja serializado a fim de evitar a perda de atualizações na memória. Nesse caso, o thread 1 adquire a trava exclusiva e o thread 2 espera, registrando uma espera de PAGELATCH_EX para esse recurso nas estatísticas de espera. Isso é exibido por meio do valor wait_type na DMV sys.dm_os_waiting_tasks.

Essa contenção é conhecida como contenção de "Inserção de última página", porque ocorre na borda na extrema direita da árvore B, conforme exibido no seguinte diagrama:

Esse tipo de contenção de trava pode ser explicado da maneira a seguir. Quando uma nova linha é inserida em um índice, o SQL Server usa o seguinte algoritmo para executar a modificação:

1. Percorrer a árvore B para localizar a página correta para manter o novo registro.

2. Trave a página com PAGELATCH_EX, impedindo que outros a modifiquem, e adquira travas compartilhadas (PAGELATCH_SH) em todas as páginas não folha.

   Observação

   Em alguns casos, o Mecanismo do SQL requer que travas EX sejam adquiridas também nas páginas de árvore B não folha. Por exemplo, quando ocorre uma divisão de página, quaisquer páginas diretamente afetadas precisam ser travadas exclusivamente (PAGELATCH_EX).

3. Registrar uma entrada de log indicando que a linha foi modificada.

4. Adicionar a linha à página e marcar a página como suja.

5. Destravar todas as páginas.

Se o índice de tabela for baseado em uma chave sequencialmente crescente, cada nova inserção irá para a mesma página no final da árvore B, até que essa página esteja cheia. Em cenários de alta simultaneidade, isso pode causar contenção na borda na extrema direita da árvore B e pode ocorrer em índices clusterizados e não clusterizados. As tabelas afetadas por esse tipo de contenção primariamente aceitam consultas INSERT, e as páginas para os índices problemáticos costumam ser relativamente densas (por exemplo, um tamanho de linha de aproximadamente 165 bytes (incluindo a sobrecarga de linha) é igual a aproximadamente 49 linhas por página). Neste exemplo de inserção pesada, esperamos que ocorram esperas de PAGELATCH_EX/PAGELATCH_SH, e essa é a observação típica. Para examinar os tempos de espera de trava de Página versus tempos de espera de trava de Página de Árvore, use a sys.dm_db_index_operational_stats DMV.

A seguinte tabela seguir resume os principais fatores observados com esse tipo de contenção de trava:

Contenção de trava em tabelas pequenas com um índice não clusterizado e inserções aleatórias (tabela de fila)

Normalmente, esse cenário é visto quando uma tabela SQL é usada como fila temporária (por exemplo, em um sistema de mensagens assíncronas).

Nesse cenário, contenções de trava exclusiva (EX) e compartilhada (SH) podem ocorrer nas seguintes condições:

• As operações inserir, selecionar, atualizar ou excluir ocorrem sob alta simultaneidade.
• O tamanho da linha é relativamente pequeno (levando a páginas densas).
• O número de linhas na tabela é relativamente pequeno, levando a uma árvore B superficial, definida por uma profundidade de índice de dois ou três.

A contenção de trava pode ocorrer mesmo quando o acesso é aleatório na árvore B, por exemplo, quando uma coluna não sequencial é a chave inicial em um índice clusterizado. A captura de tela a seguir é de um sistema que está apresentando esse tipo de contenção de trava. Nesse exemplo, a contenção é decorrente da densidade de páginas causada por um tamanho de linha pequeno e por uma árvore B relativamente superficial. Conforme a simultaneidade aumenta, a contenção de trava nas páginas ocorre mesmo quando as inserções são aleatórias na árvore B, já que um GUID era a coluna inicial do índice.

Na captura de tela a seguir, as esperas ocorrem nas páginas de dados do buffer e nas páginas PFS (Pages Free Space). Mesmo quando o número de arquivos de dados aumentou, a contenção de trava permaneceu predominante nas páginas de dados de buffer.

A seguinte tabela seguir resume os principais fatores observados com esse tipo de contenção de trava:

A combinação de uma árvore B superficial e inserções aleatórias em todo o índice tende a causar divisões de página na árvore B. Para executar uma divisão de página, o SQL Server deve adquirir travas compartilhadas (SH) em todos os níveis e, em seguida, adquirir travas exclusivas (EX) em páginas na árvore B envolvidas nas divisões de página. Além disso, quando a simultaneidade é alta e os dados são inseridos e excluídos continuamente, podem ocorrer divisões de raiz de B-tree. Nesse caso, outras inserções podem precisar esperar por qualquer trava de não buffer adquirida na árvore B. Isso se manifesta como um grande número de esperas no tipo de trava ACCESS_METHODS_HOBT_VIRTUAL_ROOT observado na DMV sys.dm_os_latch_stats.

O script a seguir pode ser modificado para determinar a profundidade da árvore B para os índices na tabela afetada.

SELECT
    o.name AS [table],
    i.name AS [index],
    indexProperty(object_id(o.name), i.name, 'indexDepth') + indexProperty(object_id(o.name), i.name, 'isClustered') AS depth, --clustered index depth reported doesn't count leaf level
    i.[rows] AS [rows],
    i.origFillFactor AS [fillFactor],
    CASE (indexProperty(object_id(o.name), i.name, 'isClustered'))
        WHEN 1 THEN 'clustered'
        WHEN 0 THEN 'nonclustered'
        ELSE 'statistic'
    END AS type
FROM sysIndexes AS i
     INNER JOIN sysObjects AS o
         ON o.id = i.id
WHERE o.type = 'u'
      AND indexProperty(object_id(o.name), i.name, 'isHypothetical') = 0 --filter out hypothetical indexes
      AND indexProperty(object_id(o.name), i.name, 'isStatistics') = 0 --filter out statistics
ORDER BY o.name;

Contenção de trava em páginas PFS (Page Free Space)

O PFS significa Espaço Livre de Páginas, o SQL Server aloca uma página PFS para cada 8.088 páginas (começando por PageID = 1) em cada arquivo de banco de dados. Cada byte na página PFS registra informações, incluindo a quantidade de espaço livre na página, se ela está alocada ou não e se a página armazena registros fantasmas. A página PFS contém informações sobre as páginas disponíveis para alocação quando uma nova página é necessária para uma operação de inserção ou de atualização. A página PFS deve ser atualizada em vários cenários, incluindo quando ocorrem alocações ou desalocações. Como o uso de uma trava de atualização (UP) é necessário para proteger a página PFS, a contenção de trava em páginas PFS pode ocorrer quando você tem relativamente poucos arquivos de dados em um grupo de arquivos e um grande número de núcleos de CPU. Uma forma simples de resolver isso é aumentar o número de arquivos por grupo de arquivos.

Se muitas esperas de PAGELATCH_UP forem observadas para páginas PFS ou SGAM no tempdb, conclua estas etapas para eliminar esse gargalo:

1. Adicione arquivos de dados a tempdb de modo que o número de arquivos de dados tempdb seja igual ao número de núcleos de processamento em seu servidor.

2. Habilitar o flag de rastreamento do SQL Server 1118.

Para saber mais sobre os gargalos de alocação causados pela contenção em páginas do sistema, confira a postagem no blog O que é o gargalo de alocação?

Funções com valor de tabela e contenção de trava em tempdb

Outros fatores além da contenção de alocação podem causar contenção de trava em tempdb, como o uso intenso de TVF as consultas.

Lidar com a contenção de trava para diferentes padrões de tabela

As seções a seguir descrevem técnicas que podem ser usadas para resolver ou encontrar soluções alternativas para problemas de desempenho relacionados ao excesso de contenção de trava.

Usar uma chave de índice inicial não sequencial

Um método de lidar com contenção de trava é substituir uma chave de índice sequencial por uma não sequencial, para distribuir uniformemente as inserções em um intervalo de índice.

Normalmente, isso é feito por meio de uma coluna principal no índice que distribui a carga de trabalho proporcionalmente. Há duas opções aqui:

Opção: usar uma coluna dentro da tabela para distribuir os valores entre o intervalo de chaves de índice

Avalie sua carga de trabalho para encontrar um valor natural que possa ser usado para distribuir as inserções pelo intervalo de chaves. Por exemplo, considere um cenário com caixas eletrônicos em um banco, em que ATM_ID pode ser um bom candidato para distribuir inserções em uma tabela de transações com os saques, uma vez que apenas um cliente pode usar um caixa eletrônico por vez. De maneira semelhante, em um sistema de ponto de vendas, talvez Checkout_ID ou uma ID de Loja fosse um valor natural que poderia ser usado para distribuir inserções entre um intervalo de chaves. Essa técnica requer a criação de uma chave de índice composta com a coluna de chave principal sendo o valor da coluna identificada ou algum hash desse valor combinado com uma ou mais colunas extras para fornecer exclusividade. Na maioria dos casos, um hash do valor funciona melhor, pois muitos valores distintos resultam em uma organização física ruim. Por exemplo, em um sistema de pontos de vendas, é possível criar, com base na ID da Loja, um hash que é um módulo e que se alinha com o número de núcleos de CPU. Essa técnica resultaria em um número relativamente pequeno de intervalos dentro da tabela, mas seria suficiente para distribuir inserções de maneira a evitar a contenção de trava. A imagem a seguir ilustra essa técnica.

Esse padrão foi implementado durante uma participação em um laboratório de desempenho e resolveu a contenção de trava em um sistema com 32 núcleos físicos de CPU. A tabela foi usada para armazenar o saldo de fechamento ao final de uma transação; cada transação de negócios realizou uma inserção na tabela.

Definição da tabela original

Ao usar a definição da tabela original, era observada contenção de trava excessiva no índice clusterizado pk_table1:

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (TransactionID, UserID);
GO

Definição do índice reordenado

A reordenação das colunas chave do índice, tendo UserID como a coluna inicial da chave primária, resultou em uma distribuição quase aleatória das inserções nas páginas. A distribuição resultante não era 100% aleatória, pois nem todos os usuários ficam online ao mesmo tempo, mas a distribuição era aleatória o suficiente para aliviar o excesso de contenção de trava. Uma limitação da reordenação da definição de índice é que qualquer consulta select nessa tabela precisa ser modificada para usar UserID e TransactionID como predicados de igualdade.

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (UserID, TransactionID);
GO

Usando um valor de hash como a coluna inicial na chave primária

A definição de tabela a seguir pode ser usada para gerar um modulo que se alinha ao número de CPUs, HashValue é gerada usando o valor TransactionID sequencialmente crescente para garantir uma distribuição uniforme em toda a Árvore B:

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

-- Consider using bulk loading techniques to speed it up
ALTER TABLE table1
    ADD [HashValue] AS (CONVERT (TINYINT, ABS([TransactionID]) % (32))) PERSISTED NOT NULL;

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (HashValue, TransactionID, UserID);
GO

Opção: usar um GUID como a coluna de chave inicial do índice

Se não houver um separador natural, uma coluna GUID poderá ser usada como uma coluna de chave principal do índice para garantir a distribuição uniforme de inserções. Embora a abordagem de usar o GUID como a coluna inicial na chave do índice permita o uso do particionamento para outros recursos, essa técnica também pode introduzir possíveis desvantagens decorrentes de ter mais divisões de página, de uma organização física deficiente e das baixas densidades de página.

Usar o particionamento de hash com uma coluna computada

O particionamento de tabela dentro do SQL Server pode ser usado para atenuar o excesso de contenção de trava. A criação de um esquema de partição de hash com uma coluna computada em uma tabela particionada é uma abordagem comum que pode ser executada com estas etapas:

1. Crie um grupo de arquivos ou use um existente para armazenar as partições.

2. Se for usar um novo grupo de arquivos, equilibre igualmente os arquivos individuais no LUN, tomando cuidado para usar um layout ideal. Se o padrão de acesso envolver uma alta taxa de inserções, crie a mesma quantidade de arquivos que existe em núcleos de CPU físicos no computador do SQL Server.

3. Use o CREATE PARTITION FUNCTION comando para particionar as tabelas em partições X , em que X é o número de núcleos físicos de CPU no computador do SQL Server. (pelo menos 32 partições)

   Observação

   Um alinhamento 1:1 do número de partições para o número de núcleos de CPU nem sempre é necessário. Em muitos casos, isso pode ser algum valor menor do que o número de núcleos de CPU. Ter mais partições pode resultar em mais sobrecarga para consultas que precisam pesquisar todas as partições e, nesses casos, menos partições podem ajudar. Nos testes do SQLCAT com sistemas com 64 e 128 CPUs lógicas com cargas de trabalho de clientes reais, 32 partições foram suficientes para resolver a contenção de trava excessivas e atingir as metas de escala. Em última instância, o número ideal de partições deve ser determinado por meio de testes.

4. Use o comando CREATE PARTITION SCHEME:

   • Associe a partição aos grupos de arquivos.
   • Adicione uma coluna de hash do tipo tinyint ou smallint à tabela.
   • Calcule uma boa distribuição de hash. Por exemplo, use HASHBYTES com modulo ou BINARY_CHECKSUM.

O seguinte script de exemplo pode ser personalizado para sua implementação:

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

Esse script pode ser usado para fazer a partição de hash de uma tabela com problemas causados pela Contenção de inserção de última página/página à direita. Essa técnica move a contenção da última página particionando a tabela e distribuindo as inserções entre partições de tabela com uma operação de módulo do valor de hash.

O que o particionamento de hash com uma coluna computada faz

Como ilustra o diagrama a seguir, essa técnica move a contenção da última página recriando o índice na função de hash e criando um número de partições igual ao número de núcleos de CPU físicos no computador do SQL Server. As inserções ainda vão para o final do intervalo lógico (um valor com aumento sequencial), mas a operação de módulo do valor de hash garante que as inserções sejam divididas entre as diferentes árvores B, o que alivia o gargalo. Isso é ilustrado nos seguintes diagramas:

Compensações ao usar o particionamento hash

Embora o particionamento hash possa eliminar a contenção em inserções, há várias compensações a serem consideradas ao decidir entre usar ou não usar essa técnica:

• Na maioria das vezes, as consultas select precisam ser modificadas para incluir a partição hash no predicado e levar a um plano de consulta que não fornece nenhuma eliminação de partição quando essas consultas são emitidas. A captura de tela a seguir mostra um plano inadequado, sem eliminação de partição após a implementação do particionamento hash.

  

• Ele elimina a possibilidade de eliminação de partição em algumas outras consultas, como em relatórios baseados em intervalos.

• Ao unir uma tabela particionada por hash a outra tabela, para obter a eliminação de partição, a segunda tabela precisa ser particionada por hash na mesma chave, e a chave de hash deve fazer parte do critério de junção.

• O particionamento hash impede o uso do particionamento por outros recursos de gerenciamento, como as funcionalidades arquivamento de janela deslizante e troca de partição.

O particionamento hash é uma estratégia eficaz para mitigar o excesso de contenção de trava, pois aumenta a taxa de transferência geral do sistema, aliviando a contenção em inserções. Como há alguns compromissos envolvidos, pode não ser a solução ideal para certos padrões de acesso.

Resumo das técnicas usadas para solucionar a contenção de trava

As duas seções seguintes fornecem um resumo das técnicas que podem ser usadas para resolver a contenção de trava excessiva:

Chave/índice não sequencial

Vantagens:

• Permite o uso de outros recursos de particionamento, como o arquivamento de dados usando um esquema de janela deslizante e a funcionalidade de troca de partição.

Desvantagens:

• Possíveis desafios ao escolher uma chave/índice para garantir uma distribuição "próxima o suficiente" de uniforme das inserções o tempo todo.
• O GUID como coluna inicial pode ser usado para garantir a distribuição uniforme, com a limitação de que pode resultar em operações excessivas de divisão de página.
• Inserções aleatórias na árvore B podem resultar em muitas operações de divisão de página e levar à contenção de trava em páginas não folha.

Particionamento hash com coluna computada

Vantagens:

• Transparente para inserções.

Desvantagens:

• O particionamento não pode ser usado para recursos de gerenciamento pretendidos, como arquivamento de dados usando opções de comutador de partição.
• Pode causar problemas de eliminação de partição para consultas, incluindo seleção/atualização individual e baseada em intervalo e consultas que executam uma junção.
• A adição de uma coluna computada persistente é uma operação offline.

Passo a passo: diagnosticar uma contenção de trava

O passo a passo a seguir demonstra as ferramentas e técnicas descritas em Diagnosticando a contenção de trava no SQL Server e Lidando com a contenção de trava para diferentes padrões de tabela para resolver um problema em um cenário do mundo real. Este cenário descreve um engajamento do cliente para executar testes de carga de um sistema de ponto de vendas, que simulava aproximadamente 8.000 lojas realizando transações contra um aplicativo do SQL Server em execução em um sistema de 8 soquetes e 32 núcleos físicos com 256 GB de memória.

O seguinte diagrama detalha o hardware usado para testar o sistema de ponto de vendas:

Sintoma: travas frequentes

Nesse caso, observamos altos tempos de espera para PAGELATCH_EX, onde normalmente definimos como "altos" aqueles que têm uma média de mais de 1 ms. Nesse caso, observamos consistentemente esperas que ultrapassavam 20 ms.

Após determinarmos que a contenção de trava era problemática, partimos para a determinação do que estava causando a contenção de trava.

Isolar o objeto que está causando a contenção de trava

O script a seguir usa a coluna resource_description para isolar qual índice estava causando a contenção PAGELATCH_EX :

SELECT wt.session_id,
       wt.wait_type,
       wt.wait_duration_ms,
       s.name AS schema_name,
       o.name AS object_name,
       i.name AS index_name
FROM sys.dm_os_buffer_descriptors AS bd
     INNER JOIN (SELECT *,
             --resource_description
             CHARINDEX(':', resource_description) AS file_index,
             CHARINDEX(':', resource_description, CHARINDEX(':', resource_description) + 1) AS page_index,
             resource_description AS rd
      FROM sys.dm_os_waiting_tasks AS wt
      WHERE wait_type LIKE 'PAGELATCH%') AS wt
     ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)
        AND bd.file_id = SUBSTRING(wt.rd, wt.file_index + 1, 1) --wt.page_index)
        AND bd.page_id = SUBSTRING(wt.rd, wt.page_index + 1, LEN(wt.rd))
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = au.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id
ORDER BY wt.wait_duration_ms DESC;

Como mostrado aqui, a contenção está na tabela LATCHTEST e no nome de índice CIX_LATCHTEST. Observe que os nomes foram alterados para manter o anonimato da carga de trabalho.

Para ver um script mais avançado que sonda repetidamente e usa uma tabela temporária para determinar o tempo de espera total durante um período configurável, confira Descritores de buffer de consulta para determinar objetos que causam contenção de trava no apêndice.

Técnica alternativa para isolar o objeto que está causando contenção de trava

Às vezes pode ser impraticável consultar sys.dm_os_buffer_descriptors. Conforme a memória no sistema e a memória disponível para o pool de buffers aumenta, também aumenta o tempo necessário para executar essa DMV. Em um sistema de 256 GB, pode levar até 10 minutos ou mais para que essa DMV seja executada. Uma técnica alternativa está disponível. Ela é descrita de modo geral da seguinte maneira e é ilustrada com uma carga de trabalho diferente, que executamos no laboratório:

1. Consulte as tarefas com espera atuais usando o script da Apêndice Consultar sys.dm_os_waiting_tasks ordenado pela duração da espera.

2. Identifique a página principal em que um comboio é observado, o que acontece quando vários threads estão competindo pela mesma página. Neste exemplo, os threads que executam a inserção estão competindo pela página inicial na árvore B e aguardarão até que possam adquirir uma trava EX. Isso é indicado pelo resource_description na primeira consulta, em nosso caso 8:1:111305.

3. Habilite o sinalizador de rastreamento 3604, que expõe informações adicionais sobre a página por meio da seguinte sintaxe: DBCC PAGE substitua o valor obtido por meio do resource_description pelo valor entre parênteses.

   Habilite o sinalizador de rastreamento 3604 para habilitar a saída do console:

   DBCC TRACEON (3604);
   

   Examine os detalhes da página:

   DBCC PAGE (8, 1, 111305, -1);
   

4. Examinar a saída do DBCC. Deve haver um ObjectID de Metadados associado, em nosso caso 78623323.

   

5. Agora podemos executar o comando a seguir para determinar o nome do objeto que causa a contenção, que é LATCHTESTconforme o esperado.

   Observação

   Verifique se você está no contexto correto do banco de dados, caso contrário, a consulta retornará NULL.

   --get object name
   SELECT OBJECT_NAME(78623323);
   

   

Resumo e resultados

Usando a técnica acima, pudemos confirmar que a contenção estava ocorrendo em um índice clusterizado com um valor de chave com aumento sequencial na tabela, que de longe recebia o número mais alto de inserções. Esse tipo de contenção não é incomum para índices com valor de chave com aumento sequencial, como datetime, identidade ou uma TransactionID gerada por aplicativo.

Para resolver o problema, usamos o particionamento hash com uma coluna computada e observamos um aprimoramento de desempenho de 690%. A tabela a seguir resume o desempenho do aplicativo antes e depois de implementar o particionamento hash com uma coluna computada. A utilização da CPU aumenta bastante, em paralelo com a taxa de transferência, conforme esperado após a remoção do gargalo de contenção de trava:

Como pode ser visto na tabela anterior, identificar e resolver corretamente problemas de desempenho causados por contenção excessiva de trava de página pode ter um efeito positivo no desempenho geral do aplicativo.

Apêndice: técnica alternativa

Uma estratégia possível para evitar o excesso de contenção de trava de página é acrescentar linhas a uma coluna char para garantir que cada linha use uma página inteira. Essa estratégia é uma opção quando o tamanho geral dos dados é pequeno e você precisa lidar com a contenção de trava de página EX causada pela seguinte combinação de fatores:

• Tamanho de linha pequeno
• Árvore B superficial
• Padrão de acesso com alta taxa de operações de inserção, seleção, atualização e exclusão aleatórias
• Tabelas pequenas, por exemplo, tabelas de fila temporárias

Preenchendo as linhas para ocupar uma página inteira, você exige que o SQL aloque mais páginas, disponibilizando mais páginas para inserções e reduzindo a contenção de trava de página EX.

Preencha as linhas para garantir que cada linha ocupe uma página inteira

Um script semelhante ao seguinte pode ser usado para preencher linhas para ocupar uma página inteira:

ALTER TABLE mytable ADD Padding CHAR(5000) NOT NULL DEFAULT ('X');

Essa técnica é explicada para fins de integridade; na prática, o SQLCAT só usou isso em uma pequena tabela com 10.000 linhas em uma só participação de desempenho. Essa técnica tem aplicação limitada porque aumenta a pressão de memória no SQL Server para tabelas grandes e pode resultar em contenção de trava de não buffer em páginas que não são folhas. A pressão de memória extra pode ser um fator limitante significativo para a aplicação dessa técnica. Com a quantidade de memória disponível em um servidor moderno, uma grande proporção do conjunto de trabalho para cargas de trabalhos OLTP normalmente é mantida na memória. Quando o conjunto de dados aumenta para um tamanho que não cabe mais na memória, ocorre uma queda significativa no desempenho. Portanto, essa técnica só se aplica a tabelas pequenas. Essa técnica não é usada pelo SQLCAT para cenários como a contenção de inserção de última página/página à direita para tabelas grandes.

Apêndice: scripts de contenção de trava do SQL Server

Esta seção contém scripts que podem ser usados para ajudar a diagnosticar e solucionar problemas de contenção de trava.

Consulta sys.dm_os_waiting_tasks ordenada pela ID da sessão

O seguinte script de exemplo consulta sys.dm_os_waiting_tasks e retorna esperas de trava ordenadas pela ID da sessão:

-- WAITING TASKS ordered by session_id
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY session_id;

Consulta sys.dm_os_waiting_tasks ordenada pela duração da espera

O seguinte script de exemplo consulta sys.dm_os_waiting_tasks e retorna os tempos de espera de trava ordenados segundo a duração da espera:

-- WAITING TASKS ordered by wait_duration_ms
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

Calcular esperas ao longo de um período

O script a seguir calcula e retorna os tempos de espera de trava ao longo de um período.

/* Snapshot the current wait stats and store so that this can be compared over a time period
   Return the statistics between this point in time and the last collection point in time.

   **This data is maintained in tempdb so the connection must persist between each execution**
   **alternatively this could be modified to use a persisted table in tempdb.  if that
   is changed code should be included to clean up the table at some point.**
*/
USE tempdb;
GO

DECLARE @current_snap_time AS DATETIME;
DECLARE @previous_snap_time AS DATETIME;

SET @current_snap_time = GETDATE();

IF NOT EXISTS (SELECT name
               FROM tempdb.sys.sysobjects
               WHERE name LIKE '#_wait_stats%')
    CREATE TABLE #_wait_stats
    (
        wait_type VARCHAR (128),
        waiting_tasks_count BIGINT,
        wait_time_ms BIGINT,
        avg_wait_time_ms INT,
        max_wait_time_ms BIGINT,
        signal_wait_time_ms BIGINT,
        avg_signal_wait_time INT,
        snap_time DATETIME
    );

INSERT INTO #_wait_stats (wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms, snap_time)
SELECT wait_type,
       waiting_tasks_count,
       wait_time_ms,
       max_wait_time_ms,
       signal_wait_time_ms,
       getdate()
FROM sys.dm_os_wait_stats;

--get the previous collection point
SELECT TOP 1 @previous_snap_time = snap_time
FROM #_wait_stats
WHERE snap_time < (SELECT MAX(snap_time)
                   FROM #_wait_stats)
ORDER BY snap_time DESC;

--get delta in the wait stats
SELECT TOP 10 s.wait_type,
              (e.waiting_tasks_count - s.waiting_tasks_count) AS [waiting_tasks_count],
              (e.wait_time_ms - s.wait_time_ms) AS [wait_time_ms],
              (e.wait_time_ms - s.wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_wait_time_ms],
              (e.max_wait_time_ms) AS [max_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) AS [signal_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_signal_time_ms],
              s.snap_time AS [start_time],
              e.snap_time AS [end_time],
              DATEDIFF(ss, s.snap_time, e.snap_time) AS [seconds_in_sample]
FROM #_wait_stats AS e
     INNER JOIN (SELECT *
      FROM #_wait_stats
      WHERE snap_time = @previous_snap_time) AS s
     ON (s.wait_type = e.wait_type)
WHERE e.snap_time = @current_snap_time
      AND s.snap_time = @previous_snap_time
      AND e.wait_time_ms > 0
      AND (e.waiting_tasks_count - s.waiting_tasks_count) > 0
      AND e.wait_type NOT IN ('LAZYWRITER_SLEEP', 'SQLTRACE_BUFFER_FLUSH', 'SOS_SCHEDULER_YIELD',
                              'DBMIRRORING_CMD', 'BROKER_TASK_STOP', 'CLR_AUTO_EVENT',
                              'BROKER_RECEIVE_WAITFOR', 'WAITFOR', 'SLEEP_TASK',
                              'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                              'FT_IFTS_SCHEDULER_IDLE_WAIT', 'BROKER_TO_FLUSH',
                              'XE_DISPATCHER_WAIT', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
ORDER BY (e.wait_time_ms - s.wait_time_ms) DESC;

--clean up table
DELETE FROM #_wait_stats
WHERE snap_time = @previous_snap_time;

Consultar descritores de buffer para determinar os objetos que causam contenção de trava

O script a seguir consulta os descritores de buffer para determinar quais objetos estão associados aos tempos de espera de trava mais longos.

IF EXISTS (SELECT *
           FROM tempdb.sys.objects
           WHERE [name] LIKE '#WaitResources%')
    DROP TABLE #WaitResources;

CREATE TABLE #WaitResources
(
    session_id INT,
    wait_type NVARCHAR (1000),
    wait_duration_ms INT,
    resource_description sysname NULL,
    db_name NVARCHAR (1000),
    schema_name NVARCHAR (1000),
    object_name NVARCHAR (1000),
    index_name NVARCHAR (1000)
);
GO

DECLARE @WaitDelay AS VARCHAR (16), @Counter AS INT, @MaxCount AS INT, @Counter2 AS INT;
SELECT @Counter = 0, @MaxCount = 600, @WaitDelay = '00:00:00.100'; -- 600x.1=60 seconds

SET NOCOUNT ON;

WHILE @Counter < @MaxCount
BEGIN
   INSERT INTO #WaitResources (session_id, wait_type, wait_duration_ms, resource_description)--, db_name, schema_name, object_name, index_name)
   SELECT wt.session_id,
         wt.wait_type,
         wt.wait_duration_ms,
         wt.resource_description
   FROM sys.dm_os_waiting_tasks AS wt
   WHERE wt.wait_type LIKE 'PAGELATCH%'
         AND wt.session_id <> @@SPID;

   -- SELECT * FROM sys.dm_os_buffer_descriptors;

   SET @Counter = @Counter + 1;
   WAITFOR DELAY @WaitDelay;
END

--SELECT * FROM #WaitResources;

UPDATE #WaitResources
    SET db_name = DB_NAME(bd.database_id),
        schema_name = s.name,
        object_name = o.name,
        index_name = i.name
FROM #WaitResources AS wt
     INNER JOIN sys.dm_os_buffer_descriptors AS bd
         ON bd.database_id = SUBSTRING(wt.resource_description, 0, CHARINDEX(':', wt.resource_description))
        AND bd.file_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description) + 1, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) - CHARINDEX(':', wt.resource_description) - 1)
        AND bd.page_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) + 1, LEN(wt.resource_description) + 1)
        -- AND wt.file_index > 0 AND wt.page_index > 0
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = AU.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id;

SELECT * FROM #WaitResources
ORDER BY wait_duration_ms DESC;
GO
/*
--Other views of the same information
SELECT wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY wait_type, db_name, schema_name, object_name, index_name;
SELECT session_id, wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY session_id, wait_type, db_name, schema_name, object_name, index_name;
*/

--SELECT * FROM #WaitResources
--DROP TABLE #WaitResources;

Script de particionamento hash

O uso desse script é descrito em Usar o particionamento de hash com uma coluna computada e deve ser personalizado para sua implementação.

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

Conteúdo relacionado

• Ferramentas para monitoramento e ajuste de desempenho