Compartilhar via

Diagnosticar e resolver a contenção de spinlock no SQL Server

Este artigo fornece informações detalhadas sobre como identificar e resolver problemas relacionados à contenção de spinlock em aplicativos do SQL Server em sistemas de alta simultaneidade.

Observação

As recomendações e as melhores práticas documentadas aqui se baseiam na experiência do mundo real durante o desenvolvimento e a implantação de sistemas OLTP do mundo real. Ele foi publicado originalmente pela equipe da SQLCAT (Equipe de Consultoria do Cliente) do Microsoft SQL Server.

Contexto

No passado, os computadores Windows Server de commodities utilizavam apenas um ou dois chips de microprocessador/CPU, e as CPUs foram projetadas com apenas um único processador ou "núcleo". Aumentos na capacidade de processamento de computador foram obtidos usando CPUs mais rápidas, possibilitadas em grande parte por meio de avanços na densidade transistor. Seguindo a "Lei de Moore", a densidade transistor ou o número de transistores que podem ser colocados em um circuito integrado dobraram consistentemente a cada dois anos desde o desenvolvimento da primeira CPU de chip único de uso geral em 1971. Nos últimos anos, a abordagem tradicional de aumentar a capacidade de processamento de computadores com CPUs mais rápidas foi aumentada pela criação de computadores com várias CPUs. A partir desta gravação, a arquitetura da CPU do Intel Nehalem acomoda até oito núcleos por CPU, que quando usada em um sistema de oito soquetes pode ser dobrada para 128 processadores lógicos usando a tecnologia SMT (multithreading simultânea). Em CPUs Intel, o SMT é chamado Hyper-Threading. À medida que o número de processadores lógicos em computadores compatíveis com x86 aumenta, os problemas relacionados à simultaneidade aumentam à medida que os processadores lógicos competem por recursos. Este guia descreve como identificar e resolver problemas específicos de contenção de recursos observados ao executar aplicativos do SQL Server em sistemas de alta simultaneidade com algumas cargas de trabalho.

Nesta seção, analisamos as lições aprendidas pela equipe do SQLCAT ao diagnosticar e resolver problemas de contenção de spinlock. A contenção de spinlock é um tipo de problema de simultaneidade observado em cargas de trabalho reais do cliente em sistemas de alta escala.

Sintomas e causas de contenção de bloqueio giratório

Esta seção descreve como diagnosticar problemas de contenção de spinlock, que prejudicam o desempenho de aplicativos OLTP no SQL Server. O diagnóstico de Spinlock e a resolução de problemas devem ser considerados um assunto avançado, que requer conhecimento de ferramentas de depuração e arquitetura do Windows.

Os spinlocks são primitivos de sincronização leves que são usados para proteger o acesso a estruturas de dados. Os spinlocks não são exclusivos do SQL Server. O sistema operacional os usa quando o acesso a uma determinada estrutura de dados é necessário apenas por um curto período de tempo. Quando um thread que tenta adquirir um spinlock não consegue obter acesso, ele é executado em um loop verificando periodicamente para determinar se o recurso está disponível em vez de gerar imediatamente. Após algum período de tempo, uma thread à espera de um spinlock cederá antes de poder adquirir o recurso. O rendimento permite que outros threads em execução na mesma CPU executem. Esse comportamento é conhecido como uma retirada e é discutido mais detalhadamente mais adiante neste artigo.

O SQL Server utiliza spinlocks para proteger o acesso a algumas de suas estruturas de dados internas. Os spinlocks são usados dentro do mecanismo para serializar o acesso a determinadas estruturas de dados de maneira semelhante às travas. A principal diferença entre um latch e um spinlock é o fato de que os spinlocks giram (executam um loop) por um período de tempo verificando a disponibilidade de uma estrutura de dados, enquanto uma thread que tenta adquirir acesso a uma estrutura protegida por um latch libera imediatamente se o recurso não estiver disponível. O yield requer a troca de contexto de um thread na CPU para que outro possa ser executado. Essa é uma operação relativamente cara e, para recursos mantidos por uma curta duração, é mais eficiente em geral permitir que um thread seja executado em um loop verificando periodicamente a disponibilidade do recurso.

Ajustes internos no Mecanismo de Banco de Dados introduzidos no SQL Server 2022 (16.x) tornam os spinlocks mais eficientes.

Sintomas

Em qualquer sistema de alta concorrência, é normal observar contenção ativa nas estruturas frequentemente acessadas que são protegidas por spinlocks. Esse uso só é considerado problemático quando a contenção introduz uma sobrecarga significativa da CPU. As estatísticas de Spinlock são expostas pela Visão de Gerenciamento Dinâmico (DMV) no SQL Server. Por exemplo, essa consulta gera a seguinte saída:

Observação

Mais detalhes sobre como interpretar as informações retornadas por esta DMV serão discutidos posteriormente neste artigo.

SELECT *
FROM sys.dm_os_spinlock_stats
ORDER BY spins DESC;

Captura de tela mostrando a saída 'sys.dm_os_spinlock_stats'.

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

Coluna Descrição
Colisões Esse valor é incrementado sempre que um thread é impedido de acessar um recurso protegido por um spinlock.
Gira Esse valor é incrementado para cada vez que um thread executa um loop enquanto aguarda que um spinlock fique disponível. Essa é uma medida da quantidade de trabalho que um thread faz ao tentar adquirir um recurso.
Spins_per_collision Razão de rotações por colisão.
Tempo de descanso Relacionado a eventos de retirada; no entanto, não é relevante para as técnicas descritas neste artigo.
Recuos Ocorre quando um thread "spin" que está tentando acessar um recurso retido determina que precisa permitir que outros threads na mesma CPU sejam executados.

Para fins dessa discussão, estatísticas de interesse específico são o número de colisões, rotações e eventos de retirada que ocorrem em um período específico em que o sistema está sob carga pesada. Quando um thread tenta acessar um recurso protegido por um spinlock, ocorre uma colisão. Quando ocorre uma colisão, a contagem de colisões é incrementada e o thread começará a girar em um loop e verificar periodicamente se o recurso está disponível. Sempre que o thread gira (loops) a contagem de rotação é incrementada.

Rotações por colisão é uma medida da quantidade de giros que ocorrem enquanto um spinlock está sendo mantido por uma thread e informa quantos giros estão ocorrendo enquanto as threads estão mantendo o spinlock. Por exemplo, pequenos giros por colisão e alta contagem de colisões significa que há uma pequena quantidade de giros ocorrendo sob o spinlock e há muitos threads disputando por ele. Uma grande quantidade de giros significa que o tempo gasto girando no código de bloqueio por spin é relativamente longo (ou seja, o código está processando um grande número de entradas em um balde de hash). À medida que a contenção aumenta (aumentando assim a contagem de colisões), o número de giros também aumenta.

Adiamentos podem ser pensados de forma semelhante aos spins. Por projeto, para evitar o desperdício excessivo de CPU, os spinlocks não continuam girando indefinidamente até que possam acessar um recurso bloqueado. Para garantir que um spinlock não use excessivamente recursos de CPU, os spinlocks recuam, param de girar e "dormem". Os spinlocks recuam independentemente de conseguirem obter a posse do recurso de destino. Isso é feito para permitir que outros threads sejam agendados na CPU, na esperança de que isso permita que um trabalho mais produtivo ocorra. O comportamento padrão do mecanismo é girar por um intervalo de tempo constante primeiro, antes de realizar um retrocesso. A tentativa de obter um spinlock requer que um estado de simultaneidade de cache seja mantido, que é uma operação intensiva de CPU em relação ao custo de CPU da rotação. Portanto, as tentativas de obter um spinlock são executadas com moderação e não são executadas sempre que um thread gira. No SQL Server, determinados tipos de spinlock (por exemplo, LOCK_HASH) foram aprimorados utilizando um intervalo exponencialmente crescente entre tentativas de adquirir o spinlock (até um determinado limite), o que geralmente reduz o efeito no desempenho da CPU.

O diagrama a seguir fornece uma exibição conceitual do algoritmo spinlock:

Diagrama mostrando uma visão conceitual do algoritmo spinlock.

Cenários típicos

A contenção de spinlock pode ocorrer por vários motivos que podem não estar relacionados às decisões de design do banco de dados. Como os spinlocks controlam o acesso a estruturas de dados internas, a contenção de spinlocks não se manifesta da mesma forma que a contenção de latch de buffer, que é diretamente afetada por escolhas de design de esquema e padrões de acesso a dados.

O sintoma principalmente associado à contenção de spinlock é o alto consumo de CPU como resultado do grande número de giros e muitos threads tentando adquirir o mesmo spinlock. Em geral, isso tem sido observado em sistemas com 24 núcleos de CPU e mais comumente em sistemas com mais de 32 núcleos de CPU. Conforme afirmado anteriormente, um certo nível de contenção em spinlocks é normal para sistemas OLTP com alta simultaneidade e carga significativa, e frequentemente há um grande número de rotações (bilhões/trilhões) relatados pela sys.dm_os_spinlock_stats DMV em sistemas que estão em execução há muito tempo. Novamente, observar um número elevado de giros para qualquer tipo de spinlock determinado não é informação suficiente para determinar que há impacto negativo no desempenho do processamento.

Uma combinação de vários dos sintomas a seguir pode indicar contenção de spinlock. Se todas essas condições forem verdadeiras, execute uma investigação mais aprofundada sobre possíveis problemas de contenção de spinlock.

  • Um alto número de giros e recuos é observado para um tipo específico de spinlock.

  • O sistema está enfrentando uma utilização pesada da CPU ou picos no consumo de CPU. Em cenários pesados de CPU, você vê altas esperas por sinal em SOS_SCHEDULER_YIELD (relatadas pela DMV sys.dm_os_wait_stats).

  • O sistema está enfrentando alta concorrência.

  • O uso e as rotaçãos da CPU são aumentados desproporcionalmente à taxa de transferência.

Um fenômeno comum facilmente diagnosticado é uma divergência significativa na taxa de transferência e no uso da CPU. Muitas cargas de trabalho OLTP têm uma relação entre (taxa de transferência/número de usuários no sistema) e consumo de CPU. Os giros altos observados em conjunto com uma divergência significativa do consumo e da taxa de transferência da CPU podem ser uma indicação de contenção de spinlock introduzindo a sobrecarga da CPU. Um importante a observar aqui é que também é comum ver esse tipo de divergência em sistemas quando determinadas consultas se tornam mais caras ao longo do tempo. Por exemplo, consultas emitidas em conjuntos de dados que executam leituras mais lógicas ao longo do tempo podem resultar em sintomas semelhantes.

Importante

É essencial descartar outras causas mais comuns de alta CPU ao solucionar esses tipos de problemas.

Mesmo que cada uma das condições anteriores seja verdadeira, ainda é possível que a causa raiz do alto consumo de CPU esteja em outro lugar. Na verdade, na grande maioria dos casos, o aumento da CPU ocorre devido a motivos diferentes da contenção de spinlock.

Algumas das causas mais comuns para o aumento do consumo de CPU incluem:

  • Consultas que se tornam mais caras ao longo do tempo devido ao crescimento dos dados subjacentes, resultando na necessidade de executar leituras lógicas adicionais de dados residentes em memória.
  • Alterações nos planos de consulta que resultam em execução abaixo do ideal.

Exemplos

No exemplo a seguir, há uma relação quase linear entre o consumo de CPU e a taxa de transferência, conforme medido pelas transações por segundo. É normal ver alguma variação aqui porque sobrecargas ocorrem à medida que o volume de trabalho aumenta. Conforme ilustrado aqui, essa divergência se torna significativa. Há também uma queda precipitada na taxa de transferência depois que o consumo de CPU atinge 100%.

Captura de tela mostrando quedas de CPU no monitor de desempenho.

Ao medir o número de giros em intervalos de 3 minutos, podemos observar um aumento mais exponencial do que linear nos giros, o que indica que a contenção de spinlock pode ser problemática.

Captura de tela mostrando um gráfico de giros em intervalos de 3 minutos.

Conforme indicado anteriormente, os spinlocks são mais comuns em sistemas de alta simultaneidade que estão sob carga pesada.

Alguns dos cenários propensos a esse problema incluem:

  • Problemas de resolução de nomes causados por uma falha na qualificação completa de nomes de objetos. Para obter mais informações, consulte Descrição do bloqueio do SQL Server causado por bloqueios de compilação. Esse problema específico é descrito com mais detalhes neste artigo.

  • Contenção para buckets de hash de bloqueio no gerenciador de bloqueios para cargas de trabalho que frequentemente acessam o mesmo bloqueio (como um bloqueio compartilhado em uma linha frequentemente lida). Esse tipo de contenção se apresenta como um tipo de spinlock LOCK_HASH. Em um caso específico, descobrimos que esse problema surgiu como resultado de padrões de acesso modelados incorretamente em um ambiente de teste. Nesse ambiente, mais do que o número esperado de threads estava constantemente acessando a mesma linha devido a parâmetros de teste configurados incorretamente.

  • Alta frequência de transações DTC quando há um elevado grau de latência entre os coordenadores de transações do MSDTC. Esse problema específico está documentado em detalhes na entrada do blog SQLCAT resolvendo esperas relacionadas ao DTC e ajustando a escalabilidade do DTC.

Diagnosticar contenção de spinlock

Esta seção fornece informações para diagnosticar a contenção de spinlock no SQL Server. As principais ferramentas usadas para diagnosticar a contenção de spinlock são:

Ferramenta Utilização
Monitor de desempenho Verifique altos níveis de uso de CPU ou divergência entre a taxa de transferência e o consumo de CPU.
Estatísticas do Spinlock Consulte o sys.dm_os_spinlock_stats DMV para procurar um alto número de spins e eventos de recuo ao longo do tempo.
Estatísticas de espera A partir da versão prévia do SQL Server 2025 (17.x), consulte os DMVs sys.dm_os_wait_stats e sys.dm_exec_session_wait_stats usando o SPINLOCK_EXT tipo de espera. Requer o sinalizador de rastreamento 8134. Para obter mais informações, consulte SPINLOCK_EXT.
Eventos estendidos do SQL Server Usado para monitorar as pilhas de chamadas de bloqueios de giro que estão enfrentando um alto número de rotações.
Despejos de memória Em alguns casos, despejos de memória do processo do SQL Server e das ferramentas de depuração do Windows. Em geral, esse nível de análise é feito quando as equipes de suporte da Microsoft estão envolvidas.

O processo técnico geral para diagnosticar a contenção do Spinlock do SQL Server é:

  1. Etapa 1: Determinar se há contenção que pode estar relacionada ao spinlock.

  2. Etapa 2: Capturar estatísticas de sys.dm_os_spinlock_stats para encontrar o tipo de spinlock que tem mais contenção.

  3. Etapa 3: Obtenha os símbolos de depuração para sqlservr.exe (sqlservr.pdb) e coloque-os no mesmo diretório do arquivo de serviço SQL Server .exe (sqlservr.exe) na instância do SQL Server.\ Para ver as pilhas de chamadas dos eventos de espera, você deve ter símbolos para a versão específica do SQL Server que está executando. Os símbolos do SQL Server estão disponíveis no Microsoft Symbol Server. Para obter mais informações sobre como baixar símbolos do Microsoft Symbol Server, consulte Depuração com símbolos.

  4. Etapa 4: Use Eventos Estendidos do SQL Server para rastrear os eventos de recuo para os tipos de spinlock de interesse. Os eventos a serem capturados são spinlock_backoff e spinlock_backoff_warning.

Os Eventos Estendidos fornecem a capacidade de rastrear os eventos de recuo e capturar a pilha de chamadas para as operações que mais frequentemente tentam obter o spinlock. Analisando a pilha de chamadas, é possível determinar que tipo de operação está contribuindo para a contenção de qualquer spinlock específico.

Passo a passo do diagnóstico

O passo a passo a seguir mostra como usar as ferramentas e técnicas para diagnosticar um problema de contenção de spinlock em um cenário do mundo real. Este passo a passo é baseado em um envolvimento com o cliente executando um teste de parâmetro de comparação para simular aproximadamente 6.500 usuários simultâneos em um servidor de 8 soquetes, 64 núcleos físicos com 1 TB de memória.

Sintomas

Foram observados picos periódicos na CPU, o que levou a utilização da CPU a quase 100%. Uma divergência entre a taxa de transferência e o consumo de CPU foi observada levando ao problema. No momento em que o grande pico de CPU ocorreu, um padrão de um grande número de giros ocorrendo durante tempos de uso intenso da CPU em intervalos específicos foi estabelecido.

Este foi um caso extremo em que a contenção foi tal que criou uma condição de comboio spinlock. Um comboio ocorre quando os threads não podem mais progredir atendendo à carga de trabalho, mas, em vez disso, gastam todos os recursos de processamento tentando obter acesso ao bloqueio. O log do monitor de desempenho ilustra essa divergência entre a taxa de transferência do log de transações e o consumo de CPU e, por fim, o grande pico na utilização da CPU.

Captura de tela mostrando um pico de CPU no monitor de desempenho.

Depois de consultar sys.dm_os_spinlock_stats para determinar a existência de contenção significativa em SOS_CACHESTORE, um script de eventos estendidos foi usado para medir o número de eventos de recuo para os tipos de spinlock de interesse.

Nome Colisões Giros Giros por colisão Retrocessos
SOS_CACHESTORE 14,752,117 942,869,471,526 63,914 67,900,620
SOS_SUSPEND_QUEUE 69,267,367 473,760,338,765 6\.840 2,167,281
LOCK_HASH 5,765,761 260,885,816,584 45,247 3,739,208
MUTEX 2,802,773 9,767,503,682 3,485 350,997
SOS_SCHEDULER 1,207,007 3,692,845,572 3,060 109,746

A maneira mais simples de quantificar o impacto dos giros é examinar o número de eventos de retirada expostos pelo sys.dm_os_spinlock_stats no mesmo intervalo de 1 minuto para o(s) tipo(s) de spinlock com o maior número de giros. Esse método é melhor detectar contenção significativa porque indica quando os threads estão esgotando o limite de rotação enquanto aguardam para adquirir o spinlock. O script a seguir ilustra uma técnica avançada que utiliza eventos estendidos para medir eventos de retrocesso relacionados e localizar os caminhos de código específicos onde a contenção se encontra.

Para obter mais informações sobre eventos estendidos no SQL Server, consulte a visão geral de Eventos Estendidos.

Roteiro

/*
This script is provided "AS IS" with no warranties, and confers no rights.

This script will monitor for backoff events over a given period of time and
capture the code paths (callstacks) for those.

--Find the spinlock types
select map_value, map_key, name from sys.dm_xe_map_values
where name = 'spinlock_types'
order by map_value asc

--Example: Get the type value for any given spinlock type
select map_value, map_key, name from sys.dm_xe_map_values
where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')

Examples:
61LOCK_HASH
144 SOS_CACHESTORE
08MUTEX

*/
--create the even session that will capture the callstacks to a bucketizer
--more information is available in this reference: http://msdn.microsoft.com/en-us/library/bb630354.aspx
CREATE EVENT SESSION spin_lock_backoff ON SERVER
ADD EVENT sqlos.spinlock_backoff (
    ACTION(package0.callstack) WHERE type = 61 --LOCK_HASH
    OR TYPE = 144 --SOS_CACHESTORE
    OR TYPE = 8 --MUTEX
) ADD TARGET package0.asynchronous_bucketizer (
    SET filtering_event_name = 'sqlos.spinlock_backoff',
    source_type = 1,
    source = 'package0.callstack'
)
WITH (
    MAX_MEMORY = 50 MB,
    MEMORY_PARTITION_MODE = PER_NODE
);

--Ensure the session was created
SELECT * FROM sys.dm_xe_sessions
WHERE name = 'spin_lock_backoff';

--Run this section to measure the contention
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = START;

--wait to measure the number of backoffs over a 1 minute period
WAITFOR DELAY '00:01:00';

--To view the data
--1. Ensure the sqlservr.pdb is in the same directory as the sqlservr.exe
--2. Enable this trace flag to turn on symbol resolution
DBCC TRACEON (3656, -1);

--Get the callstacks from the bucketizer target
SELECT event_session_address,
    target_name,
    execution_count,
    cast(target_data AS XML)
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
    ON (xst.event_session_address = xs.address)
WHERE xs.name = 'spin_lock_backoff';

--clean up the session
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = STOP;
DROP EVENT SESSION spin_lock_backoff ON SERVER;

Analisando a saída, podemos ver as pilhas de chamadas para os caminhos de código mais comuns para os SOS_CACHESTORE processos. O script foi executado algumas vezes diferentes durante o tempo em que a utilização da CPU era alta para verificar a consistência nas pilhas de chamadas retornadas. As pilhas de chamadas com o maior número de buckets de slot são comuns entre as duas saídas (35.668 e 8.506). Essas pilhas de chamadas têm uma contagem de slots que é duas ordens de magnitude maior que a próxima entrada mais alta. Essa condição indica um caminho de código de interesse.

Observação

Não é incomum ver pilhas de chamadas retornadas pelo script anterior. Quando o script foi executado por 1 minuto, observamos que pilhas de chamadas com uma contagem de slots de > 1.000 eram problemáticas, mas a contagem de slots de > 10.000 era mais provável que fosse problemática, pois é uma contagem de slots mais alta.

Observação

A formatação do resultado a seguir foi ajustada para fins de legibilidade.

Saída 1

<BucketizerTarget truncated="0" buckets="256">
<Slot count="35668" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
      SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid
      CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey
      CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      NTGroupInfo::`vector deleting destructor'
  </value>
</Slot>
<Slot count="752" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
      SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey             CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
  </value>
  </Slot>

Saída 2

<BucketizerTarget truncated="0" buckets="256">
<Slot count="8506" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep+c7 [ @ 0+0x0 SpinlockBase::Backoff Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      NTGroupInfo::`vector deleting destructor'
</value>
 </Slot>
<Slot count="190" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
       SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
   </value>
 </Slot>

No exemplo anterior, as pilhas mais interessantes têm as maiores contagens de slots (35.668 e 8.506), que, de fato, têm uma contagem de slots maior que 1.000.

Agora a pergunta pode ser: "O que faço com essas informações"? Em geral, um conhecimento profundo do mecanismo do SQL Server é necessário para usar as informações da pilha de chamadas e, assim, nesta etapa, o processo de solução de problemas entra em uma área menos clara. Nesse caso específico, ao examinarmos as pilhas de chamadas, podemos ver que o caminho do código onde o problema ocorre está relacionado a pesquisas de segurança e metadados, como é evidente pelos quadros a seguir de pilha CMEDCatalogOwner::GetProxyOwnerBySID & CMEDProxyDatabase::GetOwnerBySID).

Isoladamente, é difícil usar essas informações para resolver o problema, mas isso nos dá algumas ideias de onde concentrar solução de problemas adicionais para isolar ainda mais o problema.

Como esse problema parecia estar relacionado a caminhos de código que executam sysadmin verificações relacionadas à segurança, decidimos executar um teste no qual o usuário do aplicativo que se conecta ao banco de dados recebeu privilégios. Embora essa técnica nunca seja recomendada em um ambiente de produção, em nosso ambiente de teste ela provou ser uma etapa útil de solução de problemas. Quando as sessões foram executadas usando privilégios elevados (sysadmin), os picos de CPU relacionados à contenção desapareceram.

Opções e soluções alternativas

Claramente, solucionar problemas de contenção de spinlock pode ser uma tarefa não trivial. Não há nenhuma "melhor abordagem comum". A primeira etapa na solução de problemas e na resolução de qualquer problema de desempenho é identificar a causa raiz. Usar as técnicas e ferramentas descritas neste artigo é a primeira etapa na execução da análise necessária para entender os pontos de contenção relacionados ao spinlock.

À medida que novas versões do SQL Server são desenvolvidas, o mecanismo continua a melhorar a escalabilidade implementando um código melhor otimizado para sistemas de alta simultaneidade. O SQL Server introduziu muitas otimizações para sistemas de alta simultaneidade, sendo uma delas uma retirada exponencial para os pontos de contenção mais comuns. Há aprimoramentos a partir do SQL Server 2012 que melhoraram especificamente essa área específica aproveitando algoritmos de retirada exponencial para todos os spinlocks dentro do mecanismo.

Ao criar aplicativos high-end que precisam de desempenho e escala extremos, considere como manter o caminho de código necessário no SQL Server o mais curto possível. Um caminho de código mais curto significa que menos trabalho é executado pelo mecanismo de banco de dados e naturalmente evitará pontos de contenção. Muitas práticas recomendadas têm um efeito colateral da redução da quantidade de trabalho necessária do mecanismo e, portanto, resultam na otimização do desempenho da carga de trabalho.

Usando algumas práticas recomendadas do início deste artigo como exemplos:

  • Nomes totalmente qualificados: Nomes totalmente qualificados de todos os objetos resultarão na remoção da necessidade de o SQL Server executar caminhos de código necessários para resolver nomes. Observamos pontos de contenção também no SOS_CACHESTORE tipo de spinlock encontrado quando não utilizamos nomes totalmente qualificados em chamadas para procedimentos armazenados. A falha na qualificação completa desses nomes resulta na necessidade de o SQL Server pesquisar o esquema padrão para o usuário, o que resulta em um caminho de código mais longo necessário para executar o SQL.

  • Consultas parametrizadas: Outro exemplo é utilizar consultas parametrizadas e chamadas de procedimento armazenado para reduzir o trabalho necessário para gerar planos de execução. Isso resulta novamente em um caminho de código mais curto para execução.

  • LOCK_HASH Contenção: A contenção em determinadas estruturas de bloqueio ou em colisões de buckets de Hash é inevitável em alguns casos. Embora o mecanismo do SQL Server particione a maioria das estruturas de bloqueio, ainda há momentos em que a aquisição de um bloqueio resulta no acesso ao mesmo bucket de hash. Por exemplo, um aplicativo acessa a mesma linha por muitos threads simultaneamente (ou seja, dados de referência). Esses tipos de problemas podem ser abordados por técnicas que dimensionam esses dados de referência dentro do esquema de banco de dados ou usam controle de simultaneidade otimista e bloqueio otimizado quando possível.

A primeira linha de defesa no ajuste de cargas de trabalho do SQL Server é sempre as práticas de ajuste padrão (por exemplo, indexação, otimização de consulta, otimização de E/S etc.). No entanto, além do ajuste padrão que seria executado, as práticas a seguir que reduzem a quantidade de código necessária para executar operações são uma abordagem importante. Mesmo quando as práticas recomendadas são seguidas, ainda há a possibilidade de ocorrer contenção de spinlock em sistemas ocupados com alta simultaneidade. O uso das ferramentas e técnicas neste artigo pode ajudar a isolar ou excluir esses tipos de problemas e determinar quando é necessário envolver os recursos corretos da Microsoft para ajudar.

Apêndice: Automatizar a captura de despejo de memória

O script de eventos estendidos a seguir demonstrou ser útil para automatizar a coleta de despejos de memória quando a contenção de spinlock se torna significativa. Em alguns casos, os despejos de memória são necessários para executar um diagnóstico completo do problema ou são solicitados pelas equipes da Microsoft para executar uma análise detalhada.

O script SQL a seguir pode ser usado para automatizar o processo de captura de despejos de memória para ajudar a analisar a contenção de spinlock:

/*
This script is provided "AS IS" with no warranties, and confers no rights.

Use:    This procedure will monitor for spinlocks with a high number of backoff events
        over a defined time period which would indicate that there is likely significant
        spin lock contention.

        Modify the variables noted below before running.

Requires:
        xp_cmdshell to be enabled
            sp_configure 'xp_cmd', 1
            go
            reconfigure
            go

*********************************************************************************************************/
USE tempdb;
GO

IF object_id('sp_xevent_dump_on_backoffs') IS NOT NULL
    DROP PROCEDURE sp_xevent_dump_on_backoffs;
GO

CREATE PROCEDURE sp_xevent_dump_on_backoffs (
    @sqldumper_path NVARCHAR(max) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"',
    @dump_threshold INT = 500, --capture mini dump when the slot count for the top bucket exceeds this
    @total_delay_time_seconds INT = 60, --poll for 60 seconds
    @PID INT = 0,
    @output_path NVARCHAR(MAX) = 'c:\',
    @dump_captured_flag INT = 0 OUTPUT
)
AS
/*
    --Find the spinlock types
    select map_value, map_key, name from sys.dm_xe_map_values
    where name = 'spinlock_types'
    order by map_value asc

    --Example: Get the type value for any given spinlock type
    select map_value, map_key, name from sys.dm_xe_map_values
    where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')
*/
IF EXISTS (
        SELECT *
        FROM sys.dm_xe_session_targets xst
        INNER JOIN sys.dm_xe_sessions xs
            ON (xst.event_session_address = xs.address)
        WHERE xs.name = 'spinlock_backoff_with_dump'
        )
    DROP EVENT SESSION spinlock_backoff_with_dump
        ON SERVER

CREATE EVENT SESSION spinlock_backoff_with_dump ON SERVER
ADD EVENT sqlos.spinlock_backoff (
    ACTION(package0.callstack) WHERE type = 61 --LOCK_HASH
    --or type = 144           --SOS_CACHESTORE
    --or type = 8             --MUTEX
    --or type = 53            --LOGCACHE_ACCESS
    --or type = 41            --LOGFLUSHQ
    --or type = 25            --SQL_MGR
    --or type = 39            --XDESMGR
) ADD target package0.asynchronous_bucketizer (
    SET filtering_event_name = 'sqlos.spinlock_backoff',
    source_type = 1,
    source = 'package0.callstack'
)
WITH (
    MAX_MEMORY = 50 MB,
    MEMORY_PARTITION_MODE = PER_NODE
)

ALTER EVENT SESSION spinlock_backoff_with_dump ON SERVER STATE = START;

DECLARE @instance_name NVARCHAR(MAX) = @@SERVICENAME;
DECLARE @loop_count INT = 1;
DECLARE @xml_result XML;
DECLARE @slot_count BIGINT;
DECLARE @xp_cmdshell NVARCHAR(MAX) = NULL;

--start polling for the backoffs
PRINT 'Polling for: ' + convert(VARCHAR(32), @total_delay_time_seconds) + ' seconds';

WHILE (@loop_count < CAST(@total_delay_time_seconds / 1 AS INT))
BEGIN
    WAITFOR DELAY '00:00:01'

    --get the xml from the bucketizer for the session
    SELECT @xml_result = CAST(target_data AS XML)
    FROM sys.dm_xe_session_targets xst
    INNER JOIN sys.dm_xe_sessions xs
        ON (xst.event_session_address = xs.address)
    WHERE xs.name = 'spinlock_backoff_with_dump';

    --get the highest slot count from the bucketizer
    SELECT @slot_count = @xml_result.value(N'(//Slot/@count)[1]', 'int');

    --if the slot count is higher than the threshold in the one minute period
    --dump the process and clean up session
    IF (@slot_count > @dump_threshold)
    BEGIN
        PRINT 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(NVARCHAR(max), @PID) + ' 0 0x800 0 c:\ '''

        SELECT @xp_cmdshell = 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(NVARCHAR(max), @PID) + ' 0 0x800 0 ' + @output_path + ' '''

        EXEC sp_executesql @xp_cmdshell

        PRINT 'loop count: ' + convert(VARCHAR(128), @loop_count)
        PRINT 'slot count: ' + convert(VARCHAR(128), @slot_count)

        SET @dump_captured_flag = 1

        BREAK
    END

    --otherwise loop
    SET @loop_count = @loop_count + 1
END;

--see what was collected then clean up
DBCC TRACEON (3656, -1);

SELECT event_session_address,
    target_name,
    execution_count,
    cast(target_data AS XML)
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
    ON (xst.event_session_address = xs.address)
WHERE xs.name = 'spinlock_backoff_with_dump';

ALTER EVENT SESSION spinlock_backoff_with_dump ON SERVER STATE = STOP;
DROP EVENT SESSION spinlock_backoff_with_dump ON SERVER;
GO

/* CAPTURE THE DUMPS
******************************************************************/
--Example: This will run continuously until a dump is created.
DECLARE @sqldumper_path NVARCHAR(MAX) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"';
DECLARE @dump_threshold INT = 300; --capture mini dump when the slot count for the top bucket exceeds this
DECLARE @total_delay_time_seconds INT = 60; --poll for 60 seconds
DECLARE @PID INT = 0;
DECLARE @flag TINYINT = 0;
DECLARE @dump_count TINYINT = 0;
DECLARE @max_dumps TINYINT = 3; --stop after collecting this many dumps
DECLARE @output_path NVARCHAR(max) = 'c:\'; --no spaces in the path please :)
--Get the process id for sql server
DECLARE @error_log TABLE (
    LogDate DATETIME,
    ProcessInfo VARCHAR(255),
    TEXT VARCHAR(max)
);

INSERT INTO @error_log
EXEC ('xp_readerrorlog 0, 1, ''Server Process ID''');

SELECT @PID = convert(INT, (REPLACE(REPLACE(TEXT, 'Server Process ID is ', ''), '.', '')))
FROM @error_log
WHERE TEXT LIKE ('Server Process ID is%');

PRINT 'SQL Server PID: ' + convert(VARCHAR(6), @PID);

--Loop to monitor the spinlocks and capture dumps. while (@dump_count < @max_dumps)
BEGIN
    EXEC sp_xevent_dump_on_backoffs @sqldumper_path = @sqldumper_path,
        @dump_threshold = @dump_threshold,
        @total_delay_time_seconds = @total_delay_time_seconds,
        @PID = @PID,
        @output_path = @output_path,
        @dump_captured_flag = @flag OUTPUT

    IF (@flag > 0)
        SET @dump_count = @dump_count + 1

    PRINT 'Dump Count: ' + convert(VARCHAR(2), @dump_count)

    WAITFOR DELAY '00:00:02'
END;

Apêndice: Capturar estatísticas de spinlock ao longo do tempo

O script a seguir pode ser usado para examinar as estatísticas de spinlock durante um período de tempo específico. Sempre que ele for executado, ele retornará o delta entre os valores atuais e os valores coletados anteriormente.

/* Snapshot the current spinlock 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 DATETIME;
DECLARE @previous_snap_time DATETIME;

SET @current_snap_time = GETDATE();

IF NOT EXISTS (
    SELECT name
    FROM tempdb.sys.sysobjects
    WHERE name LIKE '#_spin_waits%'
)
CREATE TABLE #_spin_waits (
    lock_name VARCHAR(128),
    collisions BIGINT,
    spins BIGINT,
    sleep_time BIGINT,
    backoffs BIGINT,
    snap_time DATETIME
);

--capture the current stats
INSERT INTO #_spin_waits (
    lock_name,
    collisions,
    spins,
    sleep_time,
    backoffs,
    snap_time
    )
SELECT name,
    collisions,
    spins,
    sleep_time,
    backoffs,
    @current_snap_time
FROM sys.dm_os_spinlock_stats;

SELECT TOP 1 @previous_snap_time = snap_time
FROM #_spin_waits
WHERE snap_time < (
    SELECT max(snap_time)
    FROM #_spin_waits
)
ORDER BY snap_time DESC;

--get delta in the spin locks stats
SELECT TOP 10 spins_current.lock_name,
    (spins_current.collisions - spins_previous.collisions) AS collisions,
    (spins_current.spins - spins_previous.spins) AS spins,
    (spins_current.sleep_time - spins_previous.sleep_time) AS sleep_time,
    (spins_current.backoffs - spins_previous.backoffs) AS backoffs,
    spins_previous.snap_time AS [start_time],
    spins_current.snap_time AS [end_time],
    DATEDIFF(ss, @previous_snap_time, @current_snap_time) AS [seconds_in_sample]
FROM #_spin_waits spins_current
INNER JOIN (
    SELECT *
    FROM #_spin_waits
    WHERE snap_time = @previous_snap_time
    ) spins_previous
    ON (spins_previous.lock_name = spins_current.lock_name)
WHERE spins_current.snap_time = @current_snap_time
    AND spins_previous.snap_time = @previous_snap_time
    AND spins_current.spins > 0
ORDER BY (spins_current.spins - spins_previous.spins) DESC;

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

Conteúdo relacionado