Diagnóstico y resolución de 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 desde el diagnóstico y la resolución de problemas de contención de bloqueos por subproceso. La contienda de spinlocks es un tipo de problema de concurrencia observado en cargas de trabajo reales de clientes en sistemas a gran escala.

Síntomas y causas de la contención de bloqueos por subproceso

En esta sección se describe cómo diagnosticar problemas de la contención de bloqueos por subproceso, que es perjudicial para el rendimiento de las aplicaciones OLTP en SQL Server. El diagnóstico y la solución de problemas de bloqueo por subproceso se deben considerar un tema avanzado, que requiere conocimiento de las herramientas de depuración y 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 subproceso que intenta adquirir un bloqueo por subproceso no puede obtener acceso, se ejecuta en un bucle que se comprueba periódicamente para determinar si el recurso está disponible en lugar de suspenderlo inmediatamente. Tras un período de tiempo, un subproceso que espera en un bloqueo por subproceso se suspenderá antes de que pueda adquirir el recurso. La suspensión permite que se ejecuten otros subprocesos en ejecución 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 bloqueo temporal y un bloqueo por subproceso es el hecho de que estos últimos giran (se ejecutan un bucle) durante un período de tiempo para comprobar la disponibilidad de una estructura de datos, mientras que un subproceso que intente obtener acceso a una estructura protegida por un bloqueo temporal se suspende 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 los sistemas de alta simultaneidad con mucha actividad, es normal ver la contención activa en las estructuras a las que se accede con frecuencia y que están protegidas por bloqueos por subproceso. Este uso solo se considera problemático cuando la contención presenta una sobrecarga de CPU significativa. Las estadísticas de bloqueo por subproceso se exponen en la vista de administración dinámica (DMV) sys.dm_os_spinlock_stats dentro de 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 bloqueo por subproceso, 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 (en bucle), 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, pocos giros por colisión y un número elevado de colisiones implican que hay una pequeña cantidad de giros en el bloqueo por subproceso y que hay muchos subprocesos que compiten por él. Una gran cantidad de giros significa que el tiempo dedicado a girar en el código del bloqueo por subproceso es relativamente prolongado (es decir, el código pasa por un gran número de entradas en un cubo de hash). A medida que aumenta la contención (lo que aumenta el número de colisiones), también aumenta el número de giros.

Los retrocesos pueden considerarse de forma similar a los giros. Por diseño, para evitar el exceso de desperdicio de CPU, los bloqueos por subproceso no siguen girando indefinidamente hasta que puedan acceder a un recurso retenido. Para garantizar que un spinlock no utilice excesivamente los recursos de la CPU, los spinlocks retroceden, es decir, dejan de girar y entran en suspensión. Los spinlocks retroceden independientemente de si llegan a obtener o no la propiedad del recurso de destino. 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 de gran consumo de CPU en relación con el costo de CPU del giro. Por lo tanto, los intentos de obtener un bloqueo por subproceso 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.

El siguiente diagrama ofrece una vista conceptual del algoritmo de bloqueo por subproceso:

Escenarios típicos

La contienda de spinlock puede producirse por diversas razones que pueden no estar relacionadas con las decisiones de diseño de la base de datos. Dado que los bloqueos por subproceso acceden a estructuras de datos internas, la contención de bloqueos por subproceso no se manifiesta de la misma manera que la contención de bloqueos temporales de búfer, que se ve afectada directamente por las opciones de diseño de esquemas y los patrones de acceso a datos.

El síntoma asociado principalmente a la contención de bloqueos por subproceso es un uso elevado de CPU como resultado del gran número de giros y los muchos subprocesos que intentan adquirir el mismo bloqueo por subproceso. 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. Tal y como se indicó antes, cierto nivel de contención en bloqueos por subproceso es normal en sistemas OLTP de alta simultaneidad con una carga significativa y, a menudo, hay un gran número de giros (miles de millones o billones) que se indican en la DMV sys.dm_os_spinlock_stats en sistemas que se han ejecutado 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 una contienda de spinlock. Si se cumplen todas estas condiciones, realice una investigación más profunda sobre los posibles problemas de contención de bloqueos por subproceso.

• Se observa un gran número de giros e interrupciones para un tipo determinado de bloqueo por subproceso.

• El sistema está experimentando un uso intensivo de la CPU o picos en el consumo de CPU. En escenarios con elevada carga de la CPU, se observan altas esperas de señal en SOS_SCHEDULER_YIELD (según informa el 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. Un alto número de giros junto con una divergencia significativa del consumo y el rendimiento de CPU pueden ser una señal de contención de bloqueos por subproceso que supone una sobrecarga de la 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 a intervalos de 3 minutos, podemos observar un aumento más exponencial que lineal en los giros, lo que indica que la contienda de spinlock 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 de lectura frecuente). Este tipo de contienda se manifiesta como un spinlock de 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 bloqueos por subproceso

En esta sección se ofrece información sobre la contención de bloqueos por subproceso de SQL Server. Las herramientas principales que se usan para diagnosticar la contención de bloqueos por subproceso son las siguientes:

El proceso técnico general para diagnosticar la contención de bloqueos por subproceso de SQL Server es el siguiente:

1. Paso 1: Determina si existe una contienda que pueda estar relacionada con los spinlocks.

2. Paso 2: Recopila estadísticas de sys.dm_os_spinlock_stats para identificar el tipo de spinlock que presenta mayor contienda.

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 rastrear 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 permiten realizar un seguimiento de los eventos de retroceso y capturar la pila de llamadas de aquellas operaciones que intentan obtener el spinlock con mayor frecuencia. Mediante el análisis de la pila de llamadas, es posible determinar qué tipo de operación contribuye a la contención de cualquier bloqueo por subproceso concreto.

Tutorial de diagnóstico

En el siguiente tutorial se muestra cómo usar las herramientas y técnicas para diagnosticar un problema de contención de bloqueos por subproceso en un escenario real. Este tutorial se basa en una interacción con el cliente que ejecuta un banco de pruebas de rendimiento para simular aproximadamente 6500 usuarios simultáneos en un servidor de 8 sockets y 64 núcleos físicos con 1 TB de memoria.

Síntomas

Se observaron picos periódicos de CPU, que elevaron la utilización de la CPU hasta casi el 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.

Se trataba de un caso extremo en el que la contención era tal que creaba una condición de convoy de bloqueos por subproceso. Un convoy se produce cuando los subprocesos ya no pueden dar servicio a la carga de trabajo, sino que emplean todos los recursos de procesamiento que intentan 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.

Tras consultar sys.dm_os_spinlock_stats para determinar la existencia de una contienda significativa en SOS_CACHESTORE, se utilizó un script de eventos extendidos para medir el número de eventos de retroceso de los tipos de spinlock de interés.

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 mejor para detectar una contención significativa porque indica cuando los subprocesos están agotando el límite de giros mientras esperan adquirir el bloqueo por subproceso. En el siguiente script se muestra una técnica avanzada que utiliza eventos extendidos para medir los eventos de interrupción relacionados e identificar las rutas de acceso al código específicas en las que 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.

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;

Al analizar los resultados, podemos ver las pilas de llamadas de las rutas de código más comunes para los giros de SOS_CACHESTORE. 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 mayor número de cubos de ranuras son comunes entre las dos salidas (35 668 y 8506). Estas pilas de llamadas tienen un recuento de ranuras que es dos órdenes de magnitud mayor que el de la siguiente entrada 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), desaparecieron los picos de CPU relacionados con la contienda.

Opciones y soluciones alternativas

Claramente, la solución de problemas de contención de bloqueos por subproceso puede ser una tarea nada sencilla. 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 las herramientas que se describen en este artículo es el primer paso para realizar el análisis necesario para comprender los puntos de contención relacionados con el bloqueo por subproceso.

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 incorporado muchas optimizaciones para sistemas de alta simultaneidad, una de las cuales es una interrupción exponencial de los puntos de contención más comunes. A partir de SQL Server 2012 se introdujeron mejoras que optimizaron específicamente esta área concreta, aprovechando algoritmos de retroceso exponencial para todos los spinlocks 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. También hemos observado puntos de contienda en el tipo de spinlock SOS_CACHESTORE cuando no se utilizan nombres completos en las llamadas a procedimientos almacenados. Si no se usan nombres completos, es necesario que SQL Server busque el esquema predeterminado del usuario, lo que da como resultado que se necesite una ruta de acceso al código más larga para ejecutar la instrucción 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: En algunos casos, es inevitable que se produzca contención en determinadas estructuras de bloqueo o colisiones de cubetas de hash. Aunque el motor de SQL Server crea particiones de la mayoría de las estructuras de bloqueo, todavía hay ocasiones en las que la adquisición de un bloqueo produce el acceso al mismo cubo de hash. Por ejemplo, una aplicación accede a la misma fila por muchos subprocesos simultáneamente (es decir, datos de referencia). Este tipo de problemas pueden abordarse mediante técnicas que, o bien distribuyan horizontalmente estos datos de referencia dentro del esquema de la base de datos, o bien utilicen un control de concurrencia optimista y un bloqueo optimizado siempre que 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, sigue existiendo la posibilidad de que se produzca contienda de spinlock en sistemas muy concurridos y con 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.

Anexo: Automatización de 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 bloqueos por subproceso se vuelve significativa. En algunos casos, se requieren volcados de memoria para realizar un diagnóstico completo del problema o los equipos de Microsoft los solicitan para llevar a cabo un análisis en profundidad.

El siguiente script de SQL se puede usar para automatizar el proceso de captura de volcados de memoria para ayudar a analizar la contención de bloqueos por subproceso:

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

Anexo: Captura de estadísticas de bloqueo por subproceso 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