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

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

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. No momento em que este artigo foi escrito, a arquitetura de CPU do Intel Nehalem acomodava até oito núcleos por CPU. No entanto, quando usado em um sistema de oito soquetes, o processador poderá ser dobrado para acomodar 128 processadores lógicos usando uma tecnologia de SMT (multithreading simultâneo). 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.

Os sintomas e as causas da contenção de spinlock

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 não consegue obter acesso ao tentar adquirir um spinlock, ele é executado em um loop verificando de maneira periódica se o recurso está disponível, em vez de fazer uma suspensão imediata. Após algum tempo, o thread que está aguardando o spinlock será suspenso antes de poder adquirir o recurso. A suspensão permite que outros threads em execução na mesma CPU sejam executados. 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 a trava e o spinlock é o fato de que os spinlocks giram (executar um loop) por um período verificando a disponibilidade de uma estrutura de dados. Por outro lado, um thread tentando obter acesso a uma estrutura protegida por trava cederá imediatamente se recurso não estiver disponível. A suspensão requer a alternância de contexto de um thread fora da CPU para que outro thread 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

É normal ver uma contenção ativa em estruturas acessadas com frequência que são protegidas por spinlocks em um sistema ocupado e de alta simultaneidade. 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:

SELECT *
FROM sys.dm_os_spinlock_stats
ORDER BY spins DESC;

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

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. Cada vez que ocorrer uma rotação (executar um loop) do thread, a contagem de rotações será incrementada.

Rotações por colisão é uma medida da quantidade de rotações que ocorrem enquanto um spinlock está sendo mantido por um thread e informa quantas rotações ocorrem enquanto os threads estão mantendo o spinlock. Por exemplo, pequenas rotações por colisão e uma contagem alta de colisões significam que há uma pequena quantidade de rotações ocorrendo no spinlock, além de haver uma disputa de vários threads por elas. Uma grande quantidade de rotações significa que o tempo gasto com a rotação no código de spinlock teve um período relativamente longo (ou seja, o código está passando por um grande número de entradas em um bucket de hash). À medida que a contenção aumenta (aumentando assim a contagem de colisões), o número de giros também aumenta.

As retiradas podem ser consideradas semelhantes às rotações. 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 os recursos da CPU de modo excessivo, ocorre um retirada de spinlocks ou eles interrompem a rotação e ficam "suspensos". A retirada de spinlocks ocorre independentemente se eles já obtiveram 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 ocorra. O comportamento padrão do mecanismo é primeiro fazer uma rotação durante um intervalo de tempo constante antes de executar uma retirada. Tentar obter um spinlock requer a manutenção de um estado de simultaneidade de cache, que é uma operação intensiva da CPU em relação ao custo de rotação da CPU. Portanto, as tentativas de obter um spinlock são executadas com moderação e não cada vez que um thread fizer uma rotação. 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:

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 internas de dados, a contenção de spinlock não é manifestada da mesma maneira que a contenção de trava de buffer, que é diretamente afetada por escolhas de design de esquema e padrões de acesso a dados.

Um sintoma associado principalmente a uma contenção de spinlock é o alto consumo de CPU como resultado do alto número de rotações e de vários threads que tentam 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. Como dito anteriormente, é normal haver algum nível de contenção em spinlocks para sistemas OLTP de alta simultaneidade com uma carga significativa. Além disso, muitas vezes, há um alto número de rotações (bilhões/trilhões) relatadas da DMV sys.dm_os_spinlock_stats em sistemas em execução por muito tempo. Novamente, observar um alto número de rotações em um determinado tipo de spinlock não é suficiente para determinar que há um impacto negativo no desempenho da carga de trabalho.

Uma combinação de vários dos sintomas a seguir pode indicar contenção de spinlock. Caso todas essas condições sejam verdadeiras, realize uma investigação adicional sobre possíveis problemas de contenção de spinlock.

• Um alto número de rotações e retiradas é 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. As altas rotações observadas em conjunto com uma divergência significativa do consumo de CPU e a taxa de transferência podem indicar uma contenção de spinlock que introduz uma sobrecarga de 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.

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%.

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.

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.

• A contenção de buckets de hash de bloqueio no gerenciador de bloqueios de cargas de trabalho que acessam com frequência o mesmo bloqueio (como um bloqueio compartilhado em uma linha com leituras frequentes). Esse tipo de contenção aparece como um spinlock do 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 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 e resolver uma contenção de spinlock

Esta seção fornecerá informações sobre como diagnosticar uma contenção de spinlock do SQL Server. As principais ferramentas usadas para diagnosticar uma contenção de spinlock são:

O processo técnico geral para diagnosticar uma contenção de spinlock do SQL Server será:

1. Etapa 1: determinar se há uma 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: obter símbolos de depuração para o sqlservr.exe (sqlservr.pdb) e colocar os símbolos no mesmo diretório do arquivo .exe serviço SQL Server (sqlservr.exe) para a instância do SQL Server.\ Para conferir as pilhas de chamadas dos eventos de retirada, você deverá ter os símbolos da 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: usar os Eventos Estendidos do SQL Server para rastrear os eventos de retirada dos 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 eventos de retirada e capturar a pilha de chamadas para as operações que tentam veementemente obter o spinlock. Analisando 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 mostrará como usar ferramentas e técnicas para diagnosticar um problema de contenção de spinlock em um cenário real. Esse passo a passo é baseado em uma participação do cliente executando um teste de parâmetro de comparação para simular aproximadamente 6.500 usuários simultâneos em um servidor de núcleo físico de 64 bits e 8 soquetes com 1 TB de memória.

Sintomas

Foram observados picos periódicos na CPU, o que pressionou a utilização da CPU para 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.

Esse foi um caso extremo em que a contenção foi tão grande que criou uma condição de comboio de spinlock. Um comboio ocorre quando os threads não podem mais progredir atendendo à carga de trabalho. Em vez disso, eles 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.

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

O modo mais direto de quantificar o impacto das rotações é examinar o número de eventos de retirada expostos por sys.dm_os_spinlock_stats durante o mesmo intervalo de um minuto dos tipos de spinlock com o número mais alto de rotações. Esse método é o mais adequado para detectar uma contenção significativa porque ele indica quando os threads estão esgotando o limite de rotações enquanto aguardam para adquirir o spinlock. O script a seguir ilustrará uma técnica avançada que usa eventos estendidos para medir eventos de retirada relacionados, além de identificar os caminhos do código específicos em que a contenção está.

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

Script

/*
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 conferir as pilhas de chamadas dos caminhos do código mais comuns para rotações SOS_CACHESTORE. 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 duas saídas (35.668 e 8.506). Essas pilhas de chamadas têm uma contagem de slots duas ordens de magnitude maior do que a segunda maior entrada. Essa condição indica um caminho do código de interesse.

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 tinham as contagens de slots mais altas (35.668 e 8.506). Isso, na verdade, significa ter uma contagem de slots de mais de 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 examinar as pilhas de chamadas, podemos ver que o caminho do código em que o problema ocorre está relacionado a pesquisas de segurança e metadados (como fica evidente pelos registros de ativação CMEDCatalogOwner::GetProxyOwnerBySID & CMEDProxyDatabase::GetOwnerBySID) a seguir.

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 uso da CPU relacionados à contenção desapareceram.

Opções e soluções alternativas

É evidente que 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 várias otimizações para sistemas de alta simultaneidade, uma delas é a retirada exponencial dos pontos de contenção mais comuns. Do SQL Server 2012 em diante foram feitos aprimoramentos nessa área ao aproveitar 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. Também observamos pontos de contenção no tipo de spinlock SOS_CACHESTORE encontrado quando os nomes totalmente qualificados não são usados 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.

• Contenção LOCK_HASH: a contenção em determinada estrutura de bloqueio ou colisões de bucket 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 resultará no acesso ao mesmo bucket de hash. Por exemplo, um aplicativo acessa a mesma linha que vários threads de modo simultâneo (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 provou ser útil para automatizar a cobrança de despejos de memória quando uma 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 seguinte script do SQL poderá ser usado para automatizar o processo de captura de despejos de memória a fim de 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

• Monitoramento de desempenho e ferramentas de ajuste