Diagnóstico y resolución de la contención de bloqueos por subproceso en SQL Server

Este artículo proporciona información detallada sobre cómo identificar y resolver problemas relacionados con la contención de spinlocks en aplicaciones de SQL Server en sistemas de alta concurrencia.

Contexto

En el pasado, los equipos con Windows Server estándar solo han utilizado uno o dos chips de microprocesador/CPU, y las CPU se han diseñado con un solo procesador o "núcleo". Los aumentos en la capacidad de procesamiento de equipos se han logrado mediante CPUs más rápidas, posible, en gran parte, gracias a los avances en la densidad del transistor. Después de la "Ley de Moore", la densidad del transistor o el número de transistores que se pueden colocar en un circuito integrado se han duplicado constantemente cada dos años desde el desarrollo de la primera CPU de chip único de uso general en 1971. En los últimos años, el enfoque tradicional de aumentar la capacidad de procesamiento de equipos con CPU más rápidas se ha aumentado mediante la creación de equipos con varias CPU. En el momento de escribir esto, la arquitectura de CPU de Intel Nehalem admite hasta ocho núcleos por CPU, que, al usarse en un sistema de ocho sockets, puede duplicarse a 128 procesadores lógicos mediante la tecnología de multithreading simultáneo (SMT). En las CPU de Intel, SMT se denomina Hyper-Threading. A medida que aumenta el número de procesadores lógicos en equipos compatibles con x86, los problemas relacionados con la simultaneidad aumentan a medida que los procesadores lógicos compiten por los recursos. En esta guía se describe cómo identificar y resolver determinados problemas de contención de recursos observados al ejecutar aplicaciones de SQL Server en sistemas de alta simultaneidad con algunas cargas de trabajo.

En esta sección, analizamos las lecciones aprendidas por el equipo de SQLCAT al diagnosticar y resolver problemas de contención de bloqueos de tipo spinlock (bloqueo por subproceso). La contención de interbloqueos es un tipo de problema de simultaneidad observado en cargas de trabajo de clientes reales en sistemas a gran escala.

Síntomas y causas de contención de spinlock

En esta sección se describe cómo diagnosticar los problemas de contención de spinlock, que es perjudicial para el rendimiento de las aplicaciones OLTP en SQL Server. El diagnóstico y resolución de problemas de spinlock deben considerarse un tema avanzado, ya que requiere conocimientos de herramientas de depuración y de los elementos internos de Windows.

Los bloqueos giratorios son primitivos de sincronización ligeros que se usan para proteger el acceso a las estructuras de datos. Los spinlocks no son exclusivos de SQL Server. El sistema operativo los usa cuando solo se necesita acceso a una estructura de datos determinada durante un breve tiempo. Cuando un hilo que intenta adquirir un spinlock no puede obtener acceso, se ejecuta en un bucle comprobando periódicamente si el recurso está disponible en lugar de ceder inmediatamente. Después de un período de tiempo, un subproceso que espera en un spinlock cederá antes de poder adquirir el recurso. El rendimiento permite que otros subprocesos que se ejecuten en la misma CPU. Este comportamiento se conoce como retroceso y se describe con más detalle más adelante en este artículo.

SQL Server utiliza interbloqueos para proteger el acceso a algunas de sus estructuras de datos internas. Los spinlocks se usan dentro del motor para serializar el acceso a determinadas estructuras de datos de forma similar a los cerrojos. La principal diferencia entre un pestillo y un candado de giro es el hecho de que los candados de giro giran (ejecutan un bucle) durante un período de tiempo mientras comprueban la disponibilidad de una estructura de datos, mientras que un hilo que intenta adquirir acceso a una estructura protegida por un pestillo cede inmediatamente si el recurso no está disponible. El rendimiento requiere el cambio de contexto de un subproceso fuera de la CPU para que otro subproceso pueda ejecutarse. Se trata de una operación relativamente costosa y para los recursos que se mantienen durante una corta duración, es más eficaz en general permitir que un subproceso se ejecute en un bucle comprobando periódicamente la disponibilidad del recurso.

Los ajustes internos del motor de base de datos introducidos en SQL Server 2022 (16.x) hacen que los spinlocks sean más eficientes.

Síntomas

En cualquier sistema de alta simultaneidad ocupado, es normal ver la contención activa en estructuras a las que se accede con frecuencia que están protegidas por interbloqueos. Este uso solo se considera problemático cuando la contención supone una sobrecarga significativa de CPU. Las estadísticas de spinlock se exponen mediante la vista de administración dinámica (DMV) en SQL Server. Por ejemplo, esta consulta produce la salida siguiente:

SELECT *
FROM sys.dm_os_spinlock_stats
ORDER BY spins DESC;

Las estadísticas expuestas por esta consulta se describen de la siguiente manera:

A efectos de esta discusión, las estadísticas de interés particular son el número de colisiones, giros y eventos de retroceso que se producen dentro de un período específico cuando el sistema está bajo carga pesada. Cuando un subproceso intenta acceder a un recurso protegido por un interbloqueo, se produce una colisión. Cuando se produce una colisión, el recuento de colisiones se incrementa y el subproceso comenzará a girar en un bucle y comprobará periódicamente si el recurso está disponible. Cada vez que el subproceso gira (bucles) se incrementa el número de giros.

Los giros por colisión son una medida de la cantidad de giros que ocurren mientras un hilo mantiene un cerrojo de giro, y le indica cuántos giros se están realizando mientras los hilos están sosteniendo el cerrojo de giro. Por ejemplo, pocas vueltas por colisión y un alto recuento de colisiones significa que hay una pequeña cantidad de vueltas que se producen bajo el cerrojo de giro y hay muchos subprocesos que compiten por él. Una gran cantidad de vueltas significa que el tiempo invertido en el código de bloqueo activo es relativamente largo (es decir, el código pasa por un gran número de entradas en un contenedor hash). A medida que aumenta la contención (aumentando el número de colisiones), también aumenta el número de giros.

Los retrocesos se pueden considerar de una manera similar a los giros. Por diseño, para evitar un exceso de residuos de CPU, los bloqueos por subproceso no continúan girando indefinidamente hasta que puedan acceder a un recurso mantenido. Para asegurarse de que un cerrojo giratorio no use excesivamente los recursos de la CPU, estos mecanismos retroceden o dejan de girar y entran en estado de espera. Los spinlocks retroceden independientemente de si alguna vez obtienen la propiedad del recurso objetivo. Esto se hace para permitir que otros subprocesos se programe en la CPU con la esperanza de que esto permita que se produzca un trabajo más productivo. El comportamiento predeterminado para el motor es girar primero durante un intervalo de tiempo constante antes de realizar un retroceso. El intento de obtener un bloqueo por subproceso requiere que se mantenga un estado de simultaneidad de caché, que es una operación intensiva de CPU en relación con el costo de la CPU del giro. Por lo tanto, los intentos de obtener un interbloqueo se realizan con moderación y no se realizan cada vez que un subproceso gira. En SQL Server se mejoraron determinados tipos de interbloqueo (por ejemplo, LOCK_HASH) utilizando un intervalo exponencialmente creciente entre los intentos de adquirir el interbloqueo (hasta un límite determinado), lo que a menudo reduce el efecto en el rendimiento de la CPU.

En el diagrama siguiente se proporciona una vista conceptual del algoritmo de interbloqueo:

Escenarios típicos

La contención de spinlocks puede producirse por diversas razones que podrían no estar relacionadas con las decisiones de diseño de la base de datos. Dado que los spinlocks regulan el acceso a las estructuras de datos internas, la contención de spinlocks no se manifiesta de la misma manera que la contención de pestañas de búfer, que se ve directamente afectada por las opciones de diseño del esquema y los patrones de acceso a datos.

El síntoma asociado principalmente a la contención de spinlock es un consumo elevado de CPU como resultado del gran número de giros y muchos subprocesos que intentan adquirir el mismo spinlock. En general, esto se ha observado en sistemas con 24 y más núcleos de CPU, y normalmente en sistemas con más de 32 núcleos de CPU. Como se indicó antes, algún nivel de contención en los bloqueos por subprocesos es normal para sistemas OLTP de alta simultaneidad con una carga significativa y a menudo hay un gran número de giros (miles de millones o billones) notificados desde la sys.dm_os_spinlock_stats DMV en sistemas que se han estado ejecutando durante mucho tiempo. De nuevo, observar un gran número de giros de cualquier tipo de spinlock no proporciona suficiente información para determinar que hay un impacto negativo en el rendimiento de la carga de trabajo.

Una combinación de varios de los siguientes síntomas podría indicar contención de spinlock. Si se cumplen todas estas condiciones, realice una investigación más exhaustiva sobre posibles problemas de contención de spinlock.

• Se observa un gran número de giros y retrocesos para un tipo de interbloqueo determinado.

• El sistema está experimentando un uso intensivo de la CPU o picos en el consumo de CPU. En escenarios intensivos de CPU, verá esperas de alta señal en SOS_SCHEDULER_YIELD (notificadas por la DMV sys.dm_os_wait_stats).

• El sistema está experimentando una alta simultaneidad.

• El uso y los giros de CPU se incrementan desproporcionadamente al rendimiento.

Un fenómeno común fácilmente diagnosticado es una divergencia significativa en el rendimiento y el uso de CPU. Muchas cargas de trabajo OLTP tienen una relación entre (rendimiento o número de usuarios en el sistema) y el consumo de CPU. Los giros altos observados junto con una divergencia significativa del consumo y el rendimiento de la CPU pueden ser una indicación de contención de bloqueo por subproceso que introduce sobrecarga de CPU. Lo importante que hay que tener en cuenta aquí es que también es habitual ver este tipo de divergencia en los sistemas cuando ciertas consultas se vuelven más costosas a lo largo del tiempo. Por ejemplo, las consultas que se emiten en conjuntos de datos que realizan lecturas más lógicas a lo largo del tiempo pueden dar lugar a síntomas similares.

Algunas de las causas más comunes del aumento del consumo de CPU son:

• Las consultas que se vuelven más costosas con el tiempo debido al crecimiento de los datos subyacentes, lo que da lugar a la necesidad de realizar lecturas lógicas adicionales de datos residentes en memoria.
• Cambios en los planes de consulta que dan lugar a una ejecución poco óptima.

Ejemplos

En el ejemplo siguiente, hay una relación casi lineal entre el consumo de CPU y el rendimiento, medida por transacciones por segundo. Es normal ver cierta divergencia aquí porque se generan gastos generales a medida que la carga de trabajo aumenta. Como se muestra aquí, esta divergencia se convierte en significativa. También hay una caída precipitada en el rendimiento una vez que el consumo de CPU alcanza los 100%.

Al medir el número de giros en intervalos de 3 minutos, podemos ver un aumento más exponencial que el aumento lineal de los giros, lo que indica que la contención de interbloqueo podría ser problemática.

Como se indicó anteriormente, los spinlocks son más comunes en sistemas de alta concurrencia que están bajo alta carga.

Algunos de los escenarios propensos a este problema son:

• Problemas de resolución de nombres causados por un error al calificar completamente los nombres de los objetos. Para obtener más información, vea Descripción del bloqueo de SQL Server causado por bloqueos de compilación. Este problema específico se describe con más detalle en este artículo.

• Contención de cubos de hash de bloqueo en el administrador de bloqueos para cargas de trabajo que acceden con frecuencia al mismo bloqueo (por ejemplo, un bloqueo compartido en una fila consultada con frecuencia). Este tipo de contención surge como un cerrojo rotativo del tipo LOCK_HASH. En un caso concreto, hemos detectado que este problema se produjo como resultado de patrones de acceso modelados incorrectamente en un entorno de prueba. En este entorno, más de los números esperados de subprocesos accedían constantemente a la misma fila exacta debido a parámetros de prueba configurados incorrectamente.

• Alta tasa de transacciones DTC cuando hay un alto grado de latencia entre los coordinadores de transacciones MSDTC. Este problema específico se documenta con detalle en la entrada de blog de SQLCAT Resolución de esperas relacionadas con DTC y optimización de la escalabilidad de DTC.

Diagnosticar la contención de cerrojos de espera activa

En esta sección se proporciona información para diagnosticar la contención de spinlocks en SQL Server. Las herramientas principales para diagnosticar la contención de spinlocks son:

El proceso técnico general para diagnosticar la contención de spinlocks en SQL Server es:

1. Paso 1: Determine que hay contención que podría estar relacionada con el bloqueo por subproceso.

2. Paso 2: Capturar estadísticas de sys.dm_os_spinlock_stats para encontrar el tipo de spinlock que experimenta la mayor contención.

3. Paso 3: Obtén los símbolos de depuración para sqlservr.exe (sqlservr.pdb) y colócalos en el mismo directorio que el archivo de servicio de SQL Server .exe (sqlservr.exe) para la instancia de SQL Server.\ Para ver las pilas de llamadas de los eventos de retroceso, debes tener los símbolos para la versión específica de SQL Server que estás ejecutando. Los símbolos de SQL Server están disponibles en el servidor de símbolos de Microsoft. Para obtener más información sobre cómo descargar símbolos del servidor de símbolos de Microsoft, vea Depuración con símbolos.

4. Paso 4: Utiliza los Eventos Extendidos de SQL Server para trazar los eventos de retroceso de los tipos de spinlock de interés. Los eventos que se van a capturar son spinlock_backoff y spinlock_backoff_warning.

Los eventos extendidos proporcionan la capacidad de hacer un seguimiento de los eventos de retroceso y capturar la pila de llamadas para aquellas operaciones que con mayor frecuencia intentan obtener el cerrojo de giro. Al analizar la pila de llamadas, es posible determinar qué tipo de operación contribuye a la contención de cualquier bloqueo de giro específico.

Tutorial de diagnóstico

En la siguiente guía se muestra cómo usar las herramientas y técnicas para diagnosticar un problema de contención de spinlock en un escenario real. Esta guía se basa en una interacción del cliente que ejecuta una prueba de referencia para simular aproximadamente 6,500 usuarios simultáneos en un servidor con 8 sockets, 64 núcleos físicos y 1 TB de memoria.

Síntomas

Se observaron picos periódicos en la CPU, que insertaron el uso de la CPU en casi 100%. Se observó una divergencia entre el rendimiento y el consumo de CPU que conduce al problema. En el momento en que se produjo el gran pico de CPU, se estableció un patrón de un gran número de iteraciones durante períodos de uso intensivo de CPU, a intervalos concretos.

Este fue un caso extremo en el que la contención fue tal que creó una configuración en convoy de bloqueo activo. Un embotellamiento se produce cuando los subprocesos ya no pueden avanzar en la gestión de la carga de trabajo, sino que invierten todos los recursos de procesamiento en intentar obtener acceso al bloqueo. El registro del monitor de rendimiento muestra esta divergencia entre el rendimiento del registro de transacciones y el consumo de CPU y, en última instancia, el gran pico en el uso de la CPU.

Después de consultar sys.dm_os_spinlock_stats para determinar la existencia de contención significativa en SOS_CACHESTORE, se usó un script de eventos extendidos para medir el número de eventos de retroceso para los tipos de interbloqueo.

La manera más sencilla de cuantificar el impacto de los giros es examinar el número de eventos de retroceso expuestos por sys.dm_os_spinlock_stats durante el mismo intervalo de 1 minuto para el/los tipo(s) de spinlock con el mayor número de giros. Este método es el mejor para detectar una contención significativa, ya que indica cuándo los hilos agotan el límite de giro mientras esperan adquirir el bloqueo de giro. El siguiente script muestra una técnica avanzada que utiliza eventos extendidos para medir y identificar los eventos relacionados de retroceso y las rutas de código específicas donde se encuentra la contención.

Para obtener más información sobre los eventos extendidos en SQL Server, vea Información general sobre eventos extendidos.

Guión

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

Al analizar el resultado, podemos ver las pilas de llamadas para las rutas de código más comunes para los SOS_CACHESTORE spins. El script se ejecutó un par de veces diferentes durante el tiempo en que el uso de la CPU era elevado para comprobar la coherencia en las pilas de llamadas devueltas. Las pilas de llamadas con el número más alto de cubos de ranura son comunes entre las dos salidas (35.668 y 8.506). Estas pilas de llamadas tienen un recuento de ranuras que es dos órdenes de magnitud mayor que la entrada siguiente más alta. Esta condición indica una ruta de código relevante.

Salida 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>

Salida 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>

En el ejemplo anterior, las pilas más interesantes tienen los recuentos de ranuras más altos (35,668 y 8,506), que, de hecho, tienen un recuento de ranuras mayor que 1000.

Ahora la pregunta podría ser "¿qué hago con esta información"? En general, se requiere un conocimiento profundo del motor de SQL Server para usar la información de pila de llamadas, por lo que, en este momento, el proceso de solución de problemas se mueve a un área gris. En este caso concreto, examinando las pilas de llamadas, podemos ver que la ruta de acceso del código en la que se produce el problema está relacionada con las búsquedas de seguridad y metadatos (como se muestra en los siguientes marcos de pila CMEDCatalogOwner::GetProxyOwnerBySID & CMEDProxyDatabase::GetOwnerBySID)).

De forma aislada, es difícil usar esta información para resolver el problema, pero nos da algunas ideas sobre dónde centrarse en la solución de problemas adicional para aislar el problema más adelante.

Dado que este problema parecía estar relacionado con las rutas de acceso de código que realizan comprobaciones relacionadas con la seguridad, decidimos ejecutar una prueba en la que el usuario de la aplicación que se conectaba a la base de datos tenía sysadmin privilegios. Aunque esta técnica nunca se recomienda en un entorno de producción, en nuestro entorno de prueba resultó ser un paso de solución de problemas útil. Cuando las sesiones se ejecutaron con privilegios elevados (sysadmin), los picos de CPU relacionados con la contención desaparecieron.

Opciones y soluciones alternativas

Es evidente que la solución de problemas de contención de spinlocks puede ser una tarea no trivial. No hay "un enfoque común". El primer paso para solucionar y resolver cualquier problema de rendimiento es identificar la causa principal. El uso de las técnicas y herramientas descritas en este artículo es el primer paso para realizar el análisis necesario para comprender los puntos de contención relacionados con los spinlocks.

A medida que se desarrollan nuevas versiones de SQL Server, el motor sigue mejorando la escalabilidad mediante la implementación de código que está mejor optimizado para sistemas de alta simultaneidad. SQL Server ha introducido muchas optimizaciones para sistemas de alta simultaneidad, siendo una de ellas una disminución progresiva para los puntos de contención más comunes. Hay mejoras a partir de SQL Server 2012 que han mejorado específicamente esta área aprovechando los algoritmos de retroceso exponencial para todos los spinlocks dentro del motor.

Al diseñar aplicaciones de gama alta que necesitan un rendimiento y una escala extremos, considere cómo mantener la ruta de acceso del código necesaria en SQL Server lo más corto posible. Una ruta de acceso de código más corta significa que el motor de base de datos realiza menos trabajo y, naturalmente, evitará puntos de contención. Muchos procedimientos recomendados tienen un efecto secundario al reducir la cantidad de trabajo necesario para el motor y, por tanto, optimizar el rendimiento de la carga de trabajo.

Tomando un par de mejores prácticas mencionadas anteriormente en este artículo como ejemplos:

• Nombres de objetos completamente calificados: La calificación completa de los nombres de todos los objetos eliminará la necesidad de que SQL Server ejecute las rutas de código necesarias para resolver los nombres. Hemos observado puntos de contención también en el tipo de SOS_CACHESTORE spinlock encontrado al no usar nombres completamente calificados en llamadas a procedimientos almacenados. Si estos nombres no se califican por completo, SQL Server necesita buscar el esquema predeterminado del usuario, lo que resulta en que se requiera una ruta de acceso de código más larga para ejecutar el SQL.

• Consultas con parámetros: Otro ejemplo consiste en usar consultas con parámetros y llamadas a procedimientos almacenados para reducir el trabajo necesario para generar planes de ejecución. Esto de nuevo da como resultado una ruta de acceso de código más corta para su ejecución.

• LOCK_HASH Contención: La contención en ciertas estructuras de bloqueo o en colisiones de buckets de hash es inevitable en algunos casos. Aunque el motor de SQL Server crea particiones en la mayoría de las estructuras de bloqueo, todavía hay ocasiones en las que la adquisición de un bloqueo resulta en acceder al mismo bucket de hash. Por ejemplo, una aplicación accede a la misma fila por muchos subprocesos simultáneamente (es decir, datos de referencia). Estos tipos de problemas se pueden abordar mediante técnicas que escalan horizontalmente estos datos de referencia dentro del esquema de la base de datos o usan el control de concurrencia optimista y el bloqueo optimizado cuando sea posible.

La primera línea de defensa para optimizar las cargas de trabajo de SQL Server es siempre las prácticas de optimización estándar (por ejemplo, indexación, optimización de consultas, optimización de E/S, etc.). Sin embargo, además del ajuste estándar que uno realizaría, seguir prácticas que reducen la cantidad de código necesario para realizar operaciones es un enfoque importante. Incluso cuando se siguen las mejores prácticas, todavía existe la posibilidad de que se produzca una contención de spinlock en sistemas ocupados de alta concurrencia. El uso de las herramientas y técnicas de este artículo puede ayudar a aislar o descartar estos tipos de problemas y determinar cuándo es necesario interactuar con los recursos adecuados de Microsoft para ayudar.

Apéndice: Automatizar la captura de volcado de memoria

El siguiente script de eventos extendidos ha demostrado ser útil para automatizar la recopilación de volcados de memoria cuando la contención de spinlocks se vuelve significativa. En algunos casos, se requieren volcados de memoria para realizar un diagnóstico completo del problema o son solicitados por los equipos de Microsoft para llevar a cabo un análisis detallado.

El siguiente script SQL se puede usar para automatizar el proceso de captura de volcados de memoria para ayudar a analizar la contención de spinlocks.

/*
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: Captura de estadísticas de spinlock a lo largo del tiempo

El siguiente script se puede usar para examinar estadísticas de spinlock durante un período de tiempo específico. Cada vez que se ejecuta, devolverá la diferencia entre los valores actuales y los valores anteriores recopilados.

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

Contenido relacionado

• Herramientas de supervisión y optimización del rendimiento