Diagnosticar e resolver contenção de trava no SQL Server

Este guia descreve como identificar e resolver problemas de contenção de trava observados ao executar aplicativos do SQL Server em sistemas de alta simultaneidade com determinadas cargas de trabalho.

À medida que o número de núcleos de CPU em servidores continua a aumentar, o aumento associado na simultaneidade pode introduzir pontos de contenção em estruturas de dados que devem ser acessadas de forma serial dentro do mecanismo de banco de dados. Isso é especialmente verdadeiro para cargas de trabalho de processamento de transações com alta taxa de transferência/alta simultaneidade (OLTP). Existem várias ferramentas, técnicas e maneiras de abordar esses desafios, bem como práticas que podem ser seguidas no projeto de aplicativos que podem ajudar a evitá-los completamente. Este artigo discute 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?

Os bloqueios temporários são primitivos de sincronização leves que são utilizados pelo motor do SQL Server para garantir a consistência das estruturas na memória, incluindo o índice, páginas de dados e estruturas internas, por exemplo, páginas non-leaf numa Árvore B. O SQL Server utiliza bloqueios temporários de memória intermédia para proteger páginas no conjunto de memória intermédia e bloqueios temporários de E/S para proteger páginas ainda não carregadas no conjunto de memória intermédia. Sempre que os dados são gravados ou lidos em uma página no pool de buffers do SQL Server, um thread de trabalho deve primeiro adquirir uma trava de buffer para a página. 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 esperar que o subsistema de E/S responda, ele aguardará uma trava de E/S exclusiva (PAGEIOLATCH_EX) ou compartilhada (PAGEIOLATCH_SH), dependendo do tipo de solicitação, isso é feito para evitar 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 interna diferentes das páginas do buffer pool; estas são conhecidas como travas não-buffer.

A contenção em travas de página é o cenário mais comum encontrado em sistemas multi-CPU e, portanto, a maioria deste artigo se concentra neles.

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

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

Discutimos alguns cenários comuns e a melhor forma de lidar com eles para aliviar a discórdia.

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 durante a operação física na página, ao contrário dos bloqueios, que são mantidos durante a transação lógica.

As travas são internas ao mecanismo 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 tabela a seguir compara travas com fechaduras:

Modos de trava e compatibilidade do SQL Server

Alguma contenção de trava é esperada como uma parte normal da operação do mecanismo do SQL Server. É inevitável que múltiplos pedidos simultâneos de fecho com diferentes níveis de compatibilidade ocorram num sistema de alta concorrência. O SQL Server impõe a compatibilidade de latch exigindo que as solicitações de latch incompatíveis aguardem em uma fila até que as solicitações de latch pendentes sejam concluídas.

As travas são adquiridas em um dos cinco modos diferentes, que se relacionam com o nível de acesso. Os modos de trava do SQL Server podem ser resumidos da seguinte forma:

• KP: Mantenha o trinco. 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 outra rosca destrua a estrutura referenciada. Por exemplo, uma trava KP impede que a estrutura a que se refere seja destruída pelo processo de escrita preguiçoso. Para obter mais informações sobre como o processo de escritor preguiçoso é utilizado com o gerenciamento de páginas de buffer do SQL Server, consulte Escrever páginas no Mecanismo de Banco de Dados.

• SH: Fecho partilhado. 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 sob uma trava compartilhada.

• UP: Fecho de atualização. Compatível com SH (Trava partilhada) e KP, mas não outros e, portanto, não permite que uma EX trava escreva na estrutura referenciada.

• EX: Fecho exclusivo. Impede que outros threads escrevam ou leiam da estrutura referenciada. Um exemplo de uso seria modificar o conteúdo de uma página para proteção da página contra rasgo.

• DT: Destrua a trava. Deve ser adquirido antes de destruir o conteúdo da estrutura referenciada. Por exemplo, uma trava DT deve ser adquirida pelo processo de gravador preguiçoso para liberar uma página limpa antes de adicioná-la à lista de buffers livres disponíveis para uso por outros threads.

Os modos de trava têm diferentes níveis de compatibilidade, por exemplo, uma trava compartilhada (SH) é compatível com uma trava de atualização (UP) ou manter (KP), mas incompatível com uma trava de destruição (DT). Várias travas podem ser adquiridas simultaneamente na mesma estrutura, desde que as travas 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 acesso serializado à fila. Esse spinlock deve ser adquirido para adicionar itens à fila. O spinlock SOS_Task também sinaliza roscas na fila quando travas incompatíveis são liberadas, permitindo que as roscas de espera adquiram uma trava compatível e continuem funcionando. A fila de espera é processada em uma base de primeiro a entrar, primeiro a sair (FIFO) à medida que as solicitações de trava são liberadas. Os fechos seguem este sistema FIFO para garantir a equidade e evitar a inanição dos fios.

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

Superlatches e sublatches do SQL Server

Com a crescente presença de sistemas de soquete múltiplo e processadores multicore baseados na arquitetura NUMA, o SQL Server 2005 introduziu Superlatches, também conhecidas como sublatches, que são eficazes apenas em sistemas com 32 ou mais processadores lógicos. As supertravas melhoram a eficiência do mecanismo SQL para certos padrões de uso em cargas de trabalho OLTP altamente simultâneas; por exemplo, quando certas páginas têm um padrão de acesso compartilhado somente leitura (SH) pesado, mas são gravadas raramente. Um exemplo de uma página com tal 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 uma divisão de página ocorre em qualquer nível na árvore B. Em uma carga de trabalho OLTP com muitas inserções e alta concorrência, o número de fragmentações de páginas aumenta amplamente de acordo com o throughput, o que pode prejudicar o desempenho. As superlatches podem permitir um maior desempenho para aceder a páginas compartilhadas onde vários worker threads em execução simultânea exigem SH travas. Para fazer isso, o Mecanismo do SQL Server promove dinamicamente uma trava em tal página para um Superlatch. Uma Superlatch divide um único fecho numa matriz de estruturas de subfecho, com um subfecho por partição por cada núcleo de CPU, em que o fecho principal atua como um redirecionador de proxy e não é necessário sincronizar o estado global para fechaduras de leitura única. Ao fazer isso, o trabalhador, que é sempre atribuído a uma CPU específica, só precisa adquirir a subtranca compartilhada (SH) atribuída ao agendador local.

A aquisição de travas compatíveis, como uma Superlatch partilhada, usa menos recursos e dimensiona melhor o acesso a páginas em uso intenso do que uma trava partilhada não particionada, porque a eliminação do requisito de sincronização do estado global melhora significativamente o desempenho ao permitir o acesso apenas à memória NUMA local. Por outro lado, adquirir uma Superlatch exclusiva (EX) é mais caro do que adquirir uma EX trava comum, pois o SQL deve sinalizar em todas as subtravas. Quando se observa que um Superlatch usa um padrão de acesso pesado EX, o Mecanismo SQL pode despromovê-lo após a página ter sido descartada do pool de buffers. O diagrama a seguir mostra uma trava normal e uma Supertrava particionada:

Use o objeto SQL Server:Latches e os contadores associados no Monitor de Desempenho para recolher informações sobre Superlatches, incluindo o número de Superlatches, promoções de Superlatch por segundo e despromoções 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 espera de trava

As informações de espera cumulativa são rastreadas pelo SQL Server e podem ser acessadas usando o DMW (Dynamic Management View). sys.dm_os_wait_stats O SQL Server emprega três tipos de espera de bloqueio, conforme definido pelo correspondente wait_type na sys.dm_os_wait_stats DMV:

• Trava de buffer (BUF): usada para garantir a consistência de páginas de índice e dados 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, as páginas que fazem a gestão das alocações são protegidas por bloqueios temporários da memória intermédia. Estes incluem as páginas Espaço Livre de Página (PFS), Mapa de Alocação Global (GAM), Mapa de Alocação Global Compartilhada (SGAM) e Mapa de Alocação de Índice (IAM). Os bloqueios de buffer são relatados em sys.dm_os_wait_stats com um wait_type de PAGELATCH_*.

• Trava sem buffer (não-BUF): usada para garantir a consistência de quaisquer estruturas na memória que não sejam páginas do pool de buffers. Quaisquer esperas por travamentos não bufferizados são reportadas 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 carregamento no 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 observar esperas significativas PAGEIOLATCH, isso significa que o SQL Server está a aguardar o subsistema de E/S. Embora uma certa quantidade de esperas seja esperada e um comportamento normal, se os tempos médios PAGEIOLATCH de espera estiverem consistentemente acima de PAGEIOLATCH 10 milissegundos (ms), você deve investigar por que o subsistema de E/S está sob pressão.

Se, ao examinar o sys.dm_os_wait_stats DMV, encontrar travas sem buffer, sys.dm_os_latch_stats deve ser examinado para obter uma descrição detalhada das informações de espera cumulativa para travas sem buffer. Todas as esperas por fecho de buffer são classificadas na BUFFER classe de fecho, e as restantes são usadas para classificar fechos sem buffer.

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

Num sistema de alta simultaneidade, é comum observar contenção ativa em estruturas frequentemente acedidas e protegidas por travões, travas e outros mecanismos de controlo 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 prejudica a taxa de transferência.

Exemplo de contenção de trava

No diagrama a seguir, a linha azul representa a taxa de transferência no SQL Server, medida por 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, como ao preencher uma coluna de 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 da página aumentou para aproximadamente 48 milissegundos, como evidenciado pela linha preta. Essa relação inversa entre a taxa de transferência e o tempo de espera de travamento de 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 não é mais congestionado nas esperas de travamento de página e a taxa de transferência é aumentada em 300% conforme medido por transações por segundo. Isso foi feito com a técnica Usar particionamento de hash com uma coluna computada descrita posteriormente neste artigo. Esta melhoria de desempenho é direcionada para sistemas com alto número de núcleos e um alto nível de simultaneidade.

Fatores que afetam a contenção da trava

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

Diagnosticar a contenção de bloqueios no SQL Server

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

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

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

• Monitor de Desempenho para monitorizar a utilização da CPU e os tempos de espera no SQL Server e estabelecer se há uma relação entre a utilização da CPU e os tempos de espera por bloqueios.

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

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

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

1. Determine se há contenção que pode estar relacionada à trava.

2. Use as exibições do 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. Alivie a contenção usando uma das técnicas descritas em Manipulando contenção de trava para diferentes padrões de tabela.

Indicadores de contenção de travas

Como mencionado anteriormente, a contenção de travas só é problemática quando tanto a contenção quanto o tempo de espera associados à aquisição de travas de página impedem que a taxa de transferência aumente mesmo quando os recursos da 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 largura de banda, juntamente com os recursos de E/S e CPU disponíveis. Esta seção orienta você na determinação do impacto da contenção de trava na carga de trabalho da seguinte maneira:

1. Meça os tempos de espera globais durante um teste representativo.
2. Classifique-os em ordem.
3. Determine a proporção de tempos de espera relacionados com travas.

As informações de espera cumulativa estão disponíveis no sys.dm_os_wait_stats DMV. O tipo mais comum de contenção de trava é a contenção de trava tampão, observada como um aumento nos tempos de espera para travas com um wait_type de PAGELATCH_*. As travas sem buffer são agrupadas sob o tipo de espera LATCH*. Como o diagrama a seguir ilustra, deve-se primeiro fazer uma análise cumulativa das esperas do sistema usando o sys.dm_os_wait_stats DMV para determinar a porcentagem do tempo de espera total causado por travas de buffer ou não-buffer. Caso encontre travas sem tampão, o sys.dm_os_latch_stats Detran também deve ser examinado.

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

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

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

As seguintes medidas do tempo de espera do fecho são indicadores de que a contenção excessiva do fecho está a afetar o desempenho da aplicação.

• O tempo médio de espera no fecho de página aumenta consistentemente com a taxa de transferência: se os tempos médios de espera no fecho de página aumentarem consistentemente com a taxa de transferência e se os tempos médios de espera no fecho de buffer também excederem os tempos de resposta de disco esperados, deve-se examinar as tarefas de espera atuais usando o sys.dm_os_waiting_tasks DMV. As médias podem ser enganosas se analisadas isoladamente, por isso é importante olhar para o sistema ao vivo quando possível para entender as características da carga de trabalho. Em particular, verifique se há grandes esperas em solicitações de PAGELATCH_EX e/ou PAGELATCH_SH em alguma página. Siga estas etapas para diagnosticar o aumento dos tempos médios de espera de travamento de página com taxa de transferência:

  • Use os scripts de exemplo Query sys.dm_os_waiting_tasks Ordered by Session ID ou Calculate Waits Over a Time Period para examinar as tarefas de espera atuais e medir o tempo médio de espera de travamento.

  • Use o script de exemplo Descritores de buffer de consulta para determinar objetos que causam contenção de trava para determinar o índice e a tabela subjacente na qual a contenção está ocorrendo.

  • Meça o tempo médio de espera de travamento de página com o contador do Monitor de Desempenho MSSQL%InstanceName%\Wait Statistics\Page Latch Waits\Average Wait Time ou executando o sys.dm_os_wait_stats DMV.

  Observação

  Para calcular o tempo médio de espera para um determinado tipo de espera (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 (retornado como waiting_tasks_count).

• Porcentagem do tempo total de espera gasto em tipos de espera de trava durante o pico de carga: Se o tempo médio de espera de trava como uma porcentagem do tempo de espera geral aumentar de acordo com a carga do aplicativo, a contenção de trava pode estar afetando o desempenho e deve ser investigada.

  Meça as esperas de trava de página e os travamentos não relacionados a páginas usando os contadores de desempenho do objeto SQL Server, Estatísticas de Espera. 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, transacções/seg e pedidos em lote/seg 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 no sys.dm_os_wait_stats DMV porque este DMV mede os tempos de espera desde a última vez 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, tire um instantâneo de sys.dm_os_wait_stats antes do pico de carga, após o pico de carga, e, em seguida, calcule a diferença. O script de exemplo Calculate Waits Over a Time Period pode ser usado para essa finalidade.

  Apenas para um ambiente não-produtivo, limpe o sys.dm_os_wait_stats DMV com o seguinte comando:

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

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

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

• A taxa de transferência não aumenta e, em alguns casos, diminui à medida que a carga do aplicativo aumenta e o número de CPUs disponíveis para o SQL Server aumenta: Isso foi ilustrado em Exemplo de contenção de 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, isso é um indicador de que o SQL Server está esperando 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 subótima da CPU é causada por outros tipos de espera, como esperas devido a bloqueios, esperas relacionadas com entrada/saída ou problemas relacionados com a 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 aprofundada.

Analise o estado atual dos fechos do buffer de espera

A contenção de latch de buffer manifesta-se como um aumento nos tempos de espera para latches com um wait_type de PAGELATCH_* ou PAGEIOLATCH_*, conforme exibido na sys.dm_os_wait_stats DMV. Para visualizar o sistema em tempo real, execute a seguinte consulta neste sistema para combinar as sys.dm_os_wait_stats, sys.dm_exec_sessions e sys.dm_exec_requests DMVs. Os resultados podem ser usados para determinar o tipo de espera atual para 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 esta consulta são descritas da seguinte forma:

A consulta a seguir retorna informações para todas as travas sem buffer:

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

As estatísticas expostas por esta consulta são descritas da seguinte forma:

Os valores retornados por esta DMV são acumulativos desde a última vez que o mecanismo de banco de dados foi reiniciado ou a DMV foi redefinida. Use a sqlserver_start_time coluna no sys.dm_os_sys_info para localizar a última hora de inicialização do mecanismo de banco de dados. Em um sistema que está funcionando 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 esta DMV:

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

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

Verificou-se que os seguintes cenários causam contenção excessiva de trava.

Última página/página à direita inserir contenção

Uma prática 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, o que pode beneficiar muito o desempenho de leituras e gravações no índice. No entanto, esse design de esquema pode inadvertidamente levar à contenção de trava. Este problema é mais comumente observado com uma tabela grande e linhas pequenas, e em inserções num índice que contém uma coluna de chave principal que aumenta sequencialmente, como uma chave inteira ascendente ou chave de data/hora. Nesse cenário, o aplicativo raramente ou nunca executa atualizações ou exclusões, sendo a exceção para operações de arquivamento.

No exemplo a seguir, o thread um e o thread dois desejam executar uma inserção de um registro que será armazenado na página 299. De uma perspetiva de bloqueio lógico, não há problema, pois bloqueios de nível de linha são usados, e bloqueios exclusivos em ambos os 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 de cada vez pode adquirir um fecho exclusivo, assegurando assim um acesso sequencial à página para evitar atualizações perdidas na memória. Neste caso, o fio 1 obtém a trava exclusiva e o fio 2 espera, o que registra uma PAGELATCH_EX espera por esse recurso nas estatísticas de espera. Isso é exibido através do valor wait_type na sys.dm_os_waiting_tasks DMV.

Essa contenção é comumente chamada de contenção "Última Inserção de Página" porque ocorre na borda mais à direita da árvore B, conforme exibido no diagrama a seguir:

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

1. Percorra a árvore B para localizar a página correta para armazenar 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 que não sejam folhas.

   Observação

   Em alguns casos, o Mecanismo SQL também requer EX latches para serem adquiridos em páginas não-folha da árvore B. Por exemplo, quando ocorre uma divisão de página, todas as páginas diretamente afetadas precisam ser fechadas exclusivamente (PAGELATCH_EX).

3. Registre uma entrada de log informando que a linha foi modificada.

4. Adicione a linha à página e marque a página como suja.

5. Desbloqueie todas as páginas.

Se o índice da tabela for baseado em uma chave sequencialmente crescente, cada nova inserção vai 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 mais à direita da árvore B e pode ocorrer em índices agrupados e não clusterizados. As tabelas que são afetadas por este tipo de contenção aceitam principalmente consultas INSERT, e as páginas para os índices problemáticos são normalmente bastante densas (por exemplo, um tamanho de linha de ~165 bytes, incluindo a sobrecarga de linha, resulta em aproximadamente ~49 linhas por página). Neste exemplo de inserção pesada, esperamos que ocorram esperas nos pontos PAGELATCH_EX/PAGELATCH_SH, e esta é a observação típica. Para examinar Page Latch waits vs. Tree Page Latch waits, use o sys.dm_db_index_operational_stats DMV.

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

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

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

Neste cenário, a contenção de trava exclusiva (EX) e compartilhada (SH) pode ocorrer nas seguintes condições:

• As operações de inserção, seleção, atualização ou exclusão ocorrem sob alta simultaneidade.
• O tamanho da linha é relativamente pequeno (resultando em páginas densas).
• O número de linhas na tabela é relativamente pequeno; levando a uma árvore B rasa, definida por ter uma profundidade de índice de dois ou três.

A contenção de trava pode ocorrer mesmo se o acesso for aleatório na árvore B, como quando uma coluna não sequencial é a chave principal em um índice clusterizado. A captura de tela a seguir é de um sistema que experimenta esse tipo de contenção de trava. Neste exemplo, a contenção deve-se à densidade das páginas, causada pela pequena dimensão das linhas e por uma B-tree relativamente rasa. À medida que a simultaneidade aumenta, a contenção de travas em páginas ocorre mesmo que as inserções sejam aleatórias no B-tree, já que um GUID era a coluna principal no índice.

Na captura de tela a seguir, as esperas ocorrem em páginas de dados de buffer e páginas de espaço livre (PFS). Mesmo quando o número de ficheiros de dados foi aumentado, a contenção de lock foi predominante nas páginas de dados do buffer.

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

A combinação de uma árvore B rasa e inserções aleatórias no índice é propensa 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 continuamente inseridos e excluídos, podem ocorrer divisões de raiz da árvore B. Nesse caso, outras inserções podem ter que aguardar por quaisquer travas sem buffer adquiridas na árvore B. Isso se manifesta como um grande número de esperas no tipo de trinco ACCESS_METHODS_HOBT_VIRTUAL_ROOT observado no sys.dm_os_latch_stats DMV.

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 travamento em páginas de espaço livre (PFS)

PFS significa Page Free Space, o SQL Server aloca uma página PFS para cada 8088 páginas (começando com PageID = 1) em cada arquivo de banco de dados. Cada byte na página PFS registra informações, incluindo quanto espaço livre está na página, se está alocado 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 é exigida por uma operação de inserção ou atualização. A página PFS deve ser atualizada em vários cenários, inclusive 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 se você tiver relativamente poucos arquivos de dados em um grupo de arquivos e um grande número de núcleos de CPU. Uma maneira simples de resolver isso é aumentar o número de arquivos por grupo de arquivos.

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

1. Adicione ficheiros de dados a tempdb de modo a garantir que o número de ficheiros de dados tempdb seja igual ao número de núcleos de processador no seu servidor.

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

Para obter mais informações sobre gargalos de alocação causados por contenção nas páginas do sistema, consulte a postagem do blog O que é gargalo de alocação?

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

Existem outros fatores além da contenção de alocação que podem causar contenção de trava no tempdb, como o uso pesado de TVF dentro das consultas.

Manipular a contenção de bloqueio para diferentes padrões de tabela

As seções a seguir descrevem técnicas que podem ser usadas para resolver problemas de desempenho relacionados à contenção excessiva de fechaduras.

Usar uma chave de índice principal não sequencial

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

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

Opção: Use uma coluna dentro da tabela para distribuir valores pelo intervalo de chaves de índice

Avalie a sua carga de trabalho em busca de um valor natural que possa ser usado para distribuir operações de inserção ao longo do intervalo de chaves. Por exemplo, considere um cenário bancário em ATM em que ATM_ID pode ser um bom candidato para distribuir inserções numa tabela de transações para levantamentos, uma vez que um cliente só pode utilizar um ATM de cada vez. Da mesma forma, em um sistema de ponto de vendas, talvez Checkout_ID ou um ID de loja seria um valor natural que poderia ser usado para distribuir inserções em 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, porque muitos valores distintos resultam em má organização física. Por exemplo, em um sistema de ponto de vendas, um hash pode ser criado a partir do ID da Loja que é algum módulo, que se alinha com o número de núcleos de CPU. Esta técnica resultaria num número relativamente pequeno de intervalos dentro da tabela, no entanto, seria suficiente distribuir as inserções de forma a evitar a contenção da trava. A imagem seguinte ilustra esta técnica.

Esse padrão foi implementado durante uma sessão de laboratório de desempenho e resolveu a contenção de travamento em um sistema com 32 núcleos físicos de CPU. A tabela foi usada para armazenar o saldo de fechamento no final de uma transação; Cada transação comercial executou uma única inserção na tabela.

Definição original da tabela

Ao usar a definição original da tabela, observou-se contenção excessiva de trava no índice agrupado 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 de índice reordenada

Reordenar as colunas de chave do índice com UserID como a coluna principal na chave primária forneceu uma distribuição quase aleatória de inserções nas páginas. A distribuição resultante não foi 100% aleatória, uma vez que nem todos os usuários estão online ao mesmo tempo, mas a distribuição foi aleatória o suficiente para aliviar a contenção excessiva de trava. Uma ressalva ao reordenar a definição de índice é que quaisquer consultas de seleção nesta tabela devem ser modificadas para usar tanto UserID quanto 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 principal na chave primária

A seguinte definição de tabela pode ser usada para gerar um módulo que se alinha ao número de CPUs, HashValue é gerado usando o valor TransactionID de aumento sequencial para garantir uma distribuição uniforme através da á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: Use um GUID como a coluna de chave principal do índice

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

Usar particionamento de hash com uma coluna computada

O particionamento de tabela no SQL Server pode ser usado para atenuar a contenção excessiva de trava. Criar um esquema de particionamento de hash com uma coluna computada em uma tabela particionada é uma abordagem comum que pode ser realizada com estas etapas:

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

2. Se estiver usando 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, certifique-se de criar o mesmo número de arquivos que o número de núcleos de CPU físicos no computador SQL Server.

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

   Observação

   Um alinhamento 1:1 do número de partições com o número de núcleos de CPU nem sempre é necessário. Em muitos casos, isso pode ser um 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 SQLCAT em 64 e 128 sistemas de CPU lógicos com cargas de trabalho reais de clientes, 32 partições foram suficientes para resolver a contenção excessiva de travas e atingir metas de escala. Em última análise, o número ideal de partições deve ser determinado através de testes.

4. Use o comando CREATE PARTITION SCHEME:

   • Vincule a função de 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 script de exemplo a seguir pode ser personalizado para fins de 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);

Este script pode ser usado para realizar uma partição por hash de uma tabela que está enfrentando problemas causados pela contenção de inserção na última página/página final. Essa técnica move a contenção da última página particionando a tabela e distribuindo inserções entre partições de tabela com uma operação de módulo de valor de hash.

O que o particionamento de hash com uma coluna computada faz

Como ilustra o diagrama a seguir, esta técnica move a contenção da última página ao reconstruir o índice na função de hash e ao criar o mesmo número de partições que o número de núcleos de CPU físicos no computador SQL Server. As inserções continuam a ir para o final do intervalo lógico (um valor que aumenta sequencialmente), 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. Isto é ilustrado nos diagramas seguintes:

Compromissos ao usar o particionamento de hash

Embora o particionamento de hash possa eliminar a contenção em inserções, há vários aspectos a considerar ao decidir se deve ou não usar essa técnica.

• Na maioria dos casos, as consultas de seleção precisam ser modificadas para incluir a partição de hash no predicado, resultando em um plano de consulta que não permite a eliminação de partições quando essas consultas são emitidas. A imagem a seguir mostra um plano ruim sem a eliminação da partição após a implementação do particionamento por hash.

  

• Ele elimina a possibilidade de eliminação de partições em determinadas outras consultas, como relatórios baseados em intervalo.

• 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 dos critérios de junção.

• O particionamento de hash impede o uso de outros recursos de gestão, como arquivamento de janela deslizante e funcionalidade de troca de partição.

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

Resumo das técnicas utilizadas para lidar com a contenção de travas

As duas seções a seguir fornecem um resumo das técnicas que podem ser usadas para lidar com a contenção excessiva de travas:

Chave/índice não sequencial

Vantagens:

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

Desvantagens:

• Possíveis desafios ao escolher uma chave/índice para garantir uma distribuição quase uniforme das inserções todas as vezes.
• GUID como uma coluna principal pode ser usado para garantir uma distribuição uniforme com a ressalva de que pode resultar em operações excessivas de divisão de página.
• Inserções aleatórias em B-Tree podem resultar em muitas operações de divisão de página e causar contenção de trava em páginas não-folhas.

Particionamento de hash com coluna calculada

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 associação.
• Adicionar 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 do SQL Server e manipulando 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 envolvimento do cliente para realizar testes de carga de um sistema de ponto de vendas, que simulou aproximadamente 8.000 lojas a realizar transações numa aplicação SQL Server em execução num sistema com 8 soquetes, 32 núcleos físicos e 256 GB de memória.

O diagrama a seguir detalha o hardware usado para testar o sistema de ponto de venda:

Sintoma: Fechos quentes

Neste caso, observamos altas esperas para PAGELATCH_EX onde normalmente definimos alta como uma média de mais de 1 ms. Neste caso, observamos consistentemente esperas superiores a 20 ms.

Uma vez que determinámos que a contenção da trava era um problema, passámos a determinar a causa da contenção da trava.

Isolar o objeto que causa contenção de trava

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

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 CIX_LATCHTESTdo índice. Os nomes das notas foram alterados para tornar anónima a carga de trabalho.

Para obter um script mais avançado que efetue sondagens repetidas e utilize uma tabela temporária para determinar o tempo total de espera ao longo de um período configurável, consulte Descritores de Buffer de Consulta para Determinar Objetos que Causam Conflito de Latch no Apêndice.

Técnica alternativa para isolar o objeto que causa contenção de trava

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

1. Consulte as tarefas de espera atuais, usando o script Appendix Query sys.dm_os_waiting_tasks Ordered by Wait Duration.

2. Identifique a página-chave onde um comboio é observado, o que acontece quando vários threads estão concorrendo na mesma página. Neste exemplo, os threads que executam a inserção estão disputando na página à direita na árvore B e aguardam até que possam adquirir uma EX trava. Isso é indicado pelo resource_description na primeira consulta, no nosso caso 8:1:111305.

3. Habilite o sinalizador de rastreamento 3604, que expõe mais informações sobre a página através do DBCC PAGE na seguinte sintaxe; substitua o valor obtido através 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. Examine a saída do DBCC. Deve haver um Metadata ObjectID associado, no nosso caso 78623323.

   

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

   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 agrupado com um valor de chave sequencialmente crescente na tabela, que de longe recebeu o maior número de inserções. Esse tipo de contenção não é incomum para índices com um valor de chave que aumenta sequencialmente, como datetime, identity ou gerado por um aplicativo TransactionID.

Para resolver esse problema, usamos particionamento de hash com uma coluna computada e observamos uma melhoria de desempenho de 690%. A tabela a seguir resume o desempenho do aplicativo antes e depois de implementar o particionamento de hash com uma coluna computada. A utilização da CPU aumenta amplamente de acordo com a taxa de transferência, conforme esperado, depois que o gargalo de contenção de trava foi removido:

Como pode ser visto na tabela anterior, identificar e resolver corretamente os problemas de desempenho causados pela contenção excessiva de travamento 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 a contenção excessiva de página é preencher linhas com uma coluna char para garantir que cada linha ocupe uma página completa. Essa estratégia é uma opção quando o tamanho geral dos dados é pequeno e é necessário resolver a contenção de bloqueio de página causada pela seguinte combinação de fatores:

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

Ao preencher linhas para ocupar uma página inteira, você precisa que o SQL aloque mais páginas, disponibilizando mais páginas para inserções e reduzindo EX a contenção de travamento de página.

Adicionar espaço às linhas para garantir que cada linha ocupa 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');

Esta técnica é explicada para completude; na prática, o SQLCAT só usou isso em uma pequena tabela com 10.000 linhas em um único compromisso de desempenho. Essa técnica tem uma 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 sem buffer em páginas não folha. A pressão extra da memória pode ser um fator limitante significativo para a aplicação desta 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 trabalho 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, esta técnica é algo que só é aplicável a mesas pequenas. Essa técnica não é utilizada pelo SQLCAT para cenários como contenção de inserções na última página ou em páginas finais de 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 por ID de sessão

O seguinte script de exemplo consulta sys.dm_os_waiting_tasks, e retorna esperas de fecho 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;

A consulta sys.dm_os_waiting_tasks ordenada por duração de espera

O exemplo de script a seguir consulta sys.dm_os_waiting_tasks, e retorna esperas de trava ordenadas por duração de 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 durante um período de tempo

O script a seguir calcula e retorna as esperas de bloqueio durante um período de tempo.

/* 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 objetos que causam contenção de fecho

O script a seguir consulta 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 de hash

O uso deste script é descrito em Usar particionamento de hash com uma coluna computada e deve ser personalizado para fins de 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 de monitoramento e ajuste de desempenho