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 spinlocks em aplicações do SQL Server em sistemas de alta concorrência.

Contexto geral

No passado, os computadores Windows Server de mercadoria utilizavam apenas um ou dois chips de microprocessador/CPU, e as CPUs eram projetadas com apenas um único processador ou "núcleo". Aumentos na capacidade de processamento de computadores foram alcançados usando CPUs mais rápidas, tornadas possíveis em grande parte através de avanços na densidade de transistores. Seguindo a "Lei de Moore", a densidade do transistor ou o número de transistores que podem ser colocados em um circuito integrado têm dobrado 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 construção de computadores com várias CPUs. No momento em que este artigo foi escrito, a arquitetura de CPU Intel Nehalem acomoda até oito núcleos por CPU, que quando usados em um sistema de oito soquetes podem ser dobrados para 128 processadores lógicos usando a tecnologia de multithreading simultâneo (SMT). 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 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 de clientes em sistemas de alta escala.

Sintomas e causas da contenção de spinlock

Esta seção descreve como diagnosticar problemas de contenção de spinlocks, os quais afetam negativamente o desempenho de aplicativos OLTP no SQL Server. O diagnóstico e a solução de problemas do Spinlock devem ser considerados um assunto avançado, que requer conhecimento de ferramentas de depuração e componentes internos do Windows.

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 uma thread tenta adquirir um spinlock e não consegue obter acesso, executa-se em um loop, verificando periodicamente se o recurso está disponível, em vez de ceder imediatamente. Depois de algum tempo, uma thread esperando num spinlock renderá antes de conseguir adquirir o recurso. A produção permite que outros threads executados na mesma CPU sejam executados. Esse comportamento é conhecido como um recuo e é discutido com mais profundidade mais adiante neste artigo.

O SQL Server utiliza spinlocks para proteger o acesso a algumas de suas estruturas de dados internas. Spinlocks são usados dentro do mecanismo para serializar o acesso a determinadas estruturas de dados de forma semelhante às travas. A principal diferença entre uma trava 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 um thread que tenta obter acesso a uma estrutura protegida por uma trava cede imediatamente se o recurso não estiver disponível. A produção requer a comutação de contexto de um thread da CPU para que outro thread possa ser executado. Esta é uma operação relativamente cara e, para recursos que são 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.

Os 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 simultaneidade ocupado, é normal ver contenção ativa em estruturas acessadas com frequência 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 do sys.dm_os_spinlock_stats Spinlock são expostas pelo Modo de Exibição de Gerenciamento Dinâmico (DMV) no SQL Server. Por exemplo, essa consulta produz a seguinte saída:

SELECT *
FROM sys.dm_os_spinlock_stats
ORDER BY spins DESC;

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

Para fins desta discussão, as estatísticas de particular interesse são o número de colisões, giros e eventos de backoff que ocorrem dentro de um período específico quando 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ão é incrementada e o thread começará a girar em um loop e verificará periodicamente se o recurso está disponível. Cada vez que a thread gira (entra em loop), a contagem de spins é incrementada.

Spins por colisão é uma medida da quantidade de spins que ocorrem enquanto um spinlock está sendo segurado por um thread, e informa quantos spins estão ocorrendo enquanto os threads estão segurando o spinlock. Por exemplo, pequenos giros por colisão e alta contagem de colisões significam que há uma pequena quantidade de giros ocorrendo sob o controlo do spinlock e existem muitas threads competindo por ela. Uma grande quantidade de giros significa que o tempo gasto girando no código spinlock é relativamente longo (ou seja, o código está passando por um grande número de entradas em um hash bucket). À medida que a contenção aumenta (aumentando assim a contagem de colisões), o número de giros também aumenta.

Os backoffs podem ser pensados de forma semelhante aos spins. Por design, para evitar desperdício excessivo de CPU, os spinlocks não continuam girando indefinidamente até que possam acessar um recurso retido. Para garantir que um spinlock não use excessivamente os recursos da CPU, os spinlocks retrocedem ou param de girar e "entrar em modo de suspensão". Os spinlocks recuam independentemente de obterem ou não a propriedade 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 aconteça. O comportamento padrão do motor é girar por um intervalo de tempo constante primeiro, antes de realizar uma retração. 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 da CPU de rotação. Portanto, as tentativas de obter um spinlock são realizadas com moderação e não são realizadas cada vez que um thread gira. No SQL Server, certos tipos de spinlock (por exemplo, LOCK_HASH) foram melhorados utilizando um intervalo exponencialmente crescente entre as tentativas de adquirir o spinlock (até um certo limite), o que geralmente reduz o efeito no desempenho da CPU.

O diagrama a seguir fornece 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 conceção do banco de dados. Como os spinlocks regulam o acesso a estruturas de dados internas, a contenção de spinlock não se manifesta da mesma forma que a contenção de bloqueio de buffer, que é diretamente afetada pelas decisões de design de esquema e padrões de acesso a dados.

O sintoma associado principalmente à contenção de spinlock é o alto consumo de CPU como resultado do grande número de spins e muitos threads tentando adquirir o mesmo spinlock. Em geral, isso tem sido observado em sistemas com 24 e mais núcleos de CPU, e mais comumente em sistemas com mais de 32 núcleos de CPU. Como afirmado anteriormente, algum nível de contenção em spinlocks é normal para sistemas OLTP de alta concorrência com carga significativa, e frequentemente há um grande número de spins (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 alto número de giros para qualquer tipo de spinlock não é informação suficiente para determinar que há impacto negativo no desempenho da carga de trabalho.

Uma combinação de vários dos seguintes sintomas pode indicar contenda de spinlock. Se todas estas condições forem satisfeitas, realize uma investigação mais aprofundada sobre possíveis problemas de contenção de spinlock.

• Um alto número de giros e backoffs são observados 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 de CPU pesada, observam-se esperas de sinal alto em SOS_SCHEDULER_YIELD (conforme relatado pela DMV sys.dm_os_wait_stats).

• O sistema está experimentando alta simultaneidade.

• O uso da CPU e os giros são aumentados de forma desproporcional à 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. Altos giros observados em conjunto com uma divergência significativa de consumo e taxa de transferência da CPU podem ser uma indicação de contenção de spinlock introduzindo sobrecarga de CPU. Uma coisa importante a notar aqui é que também é comum ver esse tipo de divergência nos sistemas quando certas consultas se tornam mais caras ao longo do tempo. Por exemplo, consultas emitidas em conjuntos de dados que executam mais leituras lógicas ao longo do tempo podem resultar em sintomas semelhantes.

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 na memória.
• Alterações nos planos de consulta resultando em execução subótima.

Exemplos

No exemplo a seguir, há uma relação quase linear entre o consumo da CPU e a taxa de transferência, medida por transações por segundo. É normal observar alguma divergência aqui, pois a sobrecarga ocorre quando qualquer tipo de carga de trabalho aumenta. Como ilustrado aqui, essa divergência torna-se significativa. Há também uma queda vertiginosa na taxa de transferência quando o consumo de CPU atinge 100%.

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

Como dito 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 hash buckets de bloqueio no gerenciador de bloqueio para cargas de trabalho que acessam frequentemente o mesmo bloqueio (como um bloqueio compartilhado em uma linha de leitura frequente). Este tipo de contenção aparece como um spinlock de tipo 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 estavam constantemente acessando exatamente a mesma linha devido a parâmetros de teste configurados incorretamente.

• Elevada taxa de transacções DTC quando há alto grau de latência entre os coordenadores de transacções MSDTC. Esse problema específico está documentado em detalhes no artigo de blog SQLCAT Resolvendo Waits relacionados 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 do SQL Server. As principais ferramentas usadas para diagnosticar a contenção de spinlock são:

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

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

2. Passo 2: Capture estatísticas de sys.dm_os_spinlock_stats para encontrar o tipo de spinlock que sofre mais contenção.

3. Etapa 3: Obtenha símbolos de depuração para sqlservr.exe (sqlservr.pdb) e coloque os símbolos no mesmo diretório do ficheiro de Serviço do SQL Server .exe (sqlservr.exe) para a instância específica do SQL Server. Para ver as pilhas de chamadas dos eventos de recesso, deverá ter símbolos para a versão particular do SQL Server que estiver a executar. Símbolos para 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: Utilize os Eventos Estendidos do SQL Server para rastrear os eventos de backoff relacionados aos tipos de spinlock de interesse. Os eventos a capturar são spinlock_backoff e spinlock_backoff_warning.

Os eventos estendidos oferecem a capacidade de rastrear os eventos de backoff e capturar a pilha de chamadas para as operações que tentam obter o spinlock com mais frequência. Ao analisar a pilha de chamadas, é possível determinar que tipo de operação está contribuindo para a contenção de um 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 do cliente executando um teste de benchmark para simular aproximadamente 6.500 usuários simultâneos em um servidor de núcleo físico de 8 soquetes e 64 com 1 TB de memória.

Sintomas

Foram observados picos periódicos na CPU, levando o uso da CPU a quase 100%. Uma divergência entre a taxa de transferência e o consumo da 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 períodos de uso pesado da CPU em intervalos específicos foi estabelecido.

Este foi um caso extremo em que a disputa foi tal que criou uma condição de comboio spinlock. Um congestionamento ocorre quando os threads já não conseguem continuar a processar a carga de trabalho, mas em vez disso, gastam todos os recursos de processamento tentando obter acesso ao lock. O log do monitor de desempenho ilustra essa divergência entre a taxa de transferência do log de transações e o consumo da CPU e, em última análise, o grande pico na utilização da CPU.

Depois de consultar sys.dm_os_spinlock_stats para determinar a existência de contenção significativa no SOS_CACHESTORE, foi usado um script de extended events para medir o número de eventos de retrocesso para os spinlocks dos tipos de interesse.

A maneira mais direta de quantificar o impacto dos spins é olhar para o número de eventos de backoff expostos pelo sys.dm_os_spinlock_stats mesmo intervalo de 1 minuto para o(s) tipo(s) de spinlock com o maior número de spinlocks. Este método é melhor para detetar 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 backoff relacionados e identificar os caminhos de código específicos onde está a contenção.

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

Guião

/*
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;

Ao analisar a saída, podemos ver as pilhas de chamadas para os caminhos de código mais comuns para as SOS_CACHESTORE rotações. 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 a maior contagem de buckets de slots 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 do que a próxima entrada mais alta. Esta condição indica um caminho de código relevante.

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 contagens de slots mais altas (35.668 e 8.506), que, na verdade, têm uma contagem de slots maior que 1.000.

Agora, a pergunta pode ser: "o que eu faço com essas informações"? Em geral, é necessário um conhecimento profundo do mecanismo do SQL Server para fazer uso das informações da pilha de chamadas e, portanto, neste ponto, o processo de solução de problemas se move para uma área cinza. Neste caso em particular, ao analisarmos as pilhas de chamadas, podemos ver que o caminho de código onde o problema ocorre está relacionado com a segurança e consultas a metadados (como é evidente pelos frames de pilha CMEDCatalogOwner::GetProxyOwnerBySID & CMEDProxyDatabase::GetOwnerBySID) a seguir.

Isoladamente, é difícil usar essas informações para resolver o problema, mas elas nos dão algumas ideias sobre onde focar a solução de problemas adicional para isolar ainda mais o problema.

Como esse problema parecia estar relacionado a caminhos de código que executam verificações relacionadas à segurança, decidimos executar um teste no qual o usuário do aplicativo que se conecta ao banco de dados recebeu sysadmin 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 existe uma "melhor abordagem comum". O primeiro passo 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 é o primeiro passo para realizar a 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 código melhor otimizado para sistemas de alta simultaneidade. O SQL Server introduziu muitas otimizações para sistemas de alta simultaneidade, uma das quais é o backoff 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 backoff exponenciais para todos os spinlocks dentro do mecanismo.

Ao projetar 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 de reduzir a quantidade de trabalho necessário do mecanismo e, portanto, resultar na otimização do desempenho da carga de trabalho.

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

• Nomes totalmente qualificados: A qualificação total de nomes de todos os objetos resultará 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 tipo de spinlock SOS_CACHESTORE encontrados quando não são utilizados nomes totalmente qualificados em chamadas para procedimentos armazenados. A falha em qualificar totalmente esses nomes resulta na necessidade de o SQL Server procurar 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 é a utilização de consultas parametrizadas e chamadas de procedimento armazenado para reduzir o trabalho necessário para gerar planos de execução. Isso novamente resulta em um caminho de código mais curto para execução.

• LOCK_HASH Contenção: Contenção em determinada estrutura de bloqueio ou colisões de hash bucket é inevitável em alguns casos. Embora o motor do SQL Server particione a maioria das estruturas de bloqueios, ainda há momentos em que a obtenção de um bloqueio leva ao acesso ao mesmo hash bucket. Por exemplo, um aplicativo acessa a mesma linha por vários 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 consultas, otimização de E/S, etc.). No entanto, além do ajuste padrão que se realizaria, seguir práticas que reduzem a quantidade de código necessária para executar operações é uma abordagem importante. Mesmo quando as melhores práticas são seguidas, ainda há uma chance de que possa ocorrer contenção de spinlock em sistemas ocupados de 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 usar os recursos certos da Microsoft para ajudar.

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

O script de eventos estendidos a seguir provou ser útil para automatizar a recolha de despejos de memória quando a contenção de spinlock se torna significativa. Em alguns casos, 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 aprofundada.

O seguinte script SQL 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: Capte estatísticas de spinlock ao longo do tempo

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

/* 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

• Ferramentas de monitoramento e ajuste de desempenho