Resolución de la contención de inserción de última página PAGELATCH_EX en SQL Server

Artículo
06/15/2023

Versión del producto original: SQL Server
Número de KB original: 4460004

En este artículo se presenta cómo resolver la contención de inserción PAGELATCH_EX de última página en SQL Server.

Síntomas

Considere los escenarios siguientes:

Tiene una columna que incluye valores secuenciales como una columna Identity o una columna DateTime que se inserta a través de la función Getdate().
Tiene un índice agrupado que tiene la columna secuencial como columna inicial.

Nota:

El escenario más común es una clave principal agrupada en una columna Identity. Con menos frecuencia, este problema se puede observar para los índices no agrupados.
La aplicación realiza operaciones INSERT o UPDATE frecuentes en la tabla.
Tiene muchas CPU en el sistema. Normalmente, el servidor tiene 16 CPU o más. Esta configuración de hardware permite que varias sesiones realicen las operaciones INSERT en la misma tabla simultáneamente.

En esta situación, puede experimentar una disminución en el rendimiento de la aplicación. Al examinar los tipos de espera en sys.dm_exec_requests, observa las esperas en el tipo de espera PAGELATCH_EX y muchas sesiones que están esperando en este tipo de espera.

Otro problema se produce si ejecuta la siguiente consulta de diagnóstico en el sistema:

seleccione session_id, wait_type, wait_time, wait_resource desde sys.dm_exec_requests donde session_id > 50 y wait_type = "pagelatch_ex"

En esta situación, puede obtener resultados similares a los siguientes.

session_id	wait_type	wait_time	wait_resource
60	PAGELATCH_EX	100	5:1:4144
75	PAGELATCH_EX	123	5:1:4144
79	PAGELATCH_EX	401	5:1:4144
80	PAGELATCH_EX	253	5:1:4144
81	PAGELATCH_EX	312	5:1:4144
82	PAGELATCH_EX	355	5:1:4144
84	PAGELATCH_EX	312	5:1:4144
85	PAGELATCH_EX	338	5:1:4144
87	PAGELATCH_EX	405	5:1:4144
88	PAGELATCH_EX	111	5:1:4144
90	PAGELATCH_EX	38	5:1:4144
92	PAGELATCH_EX	115	5:1:4144
94	PAGELATCH_EX	49	5:1:4144
101	PAGELATCH_EX	301	5:1:4144
102	PAGELATCH_EX	45	5:1:4144
103	PAGELATCH_EX	515	5:1:4144
105	PAGELATCH_EX	39	5:1:4144

Observe que hay varias sesiones esperando el mismo recurso similar al siguiente patrón:

database_id = 5, file_id = 1, page_id de base de datos = 4144

Nota:

El database_id debe ser una base de datos de usuario (el número de identificador es mayor o igual que 5). Si el database_id es 2, es posible que, en su lugar, esté experimentando el problema que se describe en Archivos, marcas de seguimiento y actualizaciones en TEMPDB.

Causa

PAGELATCH (bloqueo temporal en una página de datos o índice) es un mecanismo de sincronización de subprocesos. Se usa para sincronizar el acceso físico a corto plazo a las páginas de base de datos que se encuentran en la memoria caché del búfer.

PAGELATCH difiere de un PAGEIOLATCH. Este último se usa para sincronizar el acceso físico a las páginas cuando se leen o escriben en el disco.

Los bloqueos temporales de página son comunes en todos los sistemas porque garantizan la protección física de páginas. Un índice clúster ordena los datos por la columna de clave inicial. Por este motivo, al crear el índice en una columna secuencial, todas las nuevas inserciones de datos se producen en la misma página al final del índice hasta que se rellena esa página. Sin embargo, con una carga alta, las operaciones INSERT simultáneas pueden provocar contención en la última página del árbol B. Esta contención puede producirse en índices agrupados y no agrupados. La razón es que los índices no agrupados ordenan las páginas de nivel hoja por la clave inicial. Este problema también se conoce como contención de inserción de última página.

Para obtener más información, consulte Diagnóstico y resolución de la contención de bloqueos temporales en SQL Server.

Solución

Puede elegir una de las dos opciones siguientes para resolver el problema.

Opción 1: Ejecutar los pasos directamente en un cuaderno a través de Azure Data Studio

Nota:

Antes de intentar abrir este cuaderno, asegúrese de que Azure Data Studio está instalado en el equipo local. Para instalarlo, vaya a Información sobre cómo instalar Azure Data Studio.

Apertura de Notebook en Azure Data Studio

Opción 2: Siga los pasos manualmente

Para resolver esta contención, la estrategia general es evitar que todas las operaciones INSERT simultáneas accedan a la misma página de base de datos. En su lugar, haga que cada operación INSERT acceda a una página diferente y aumente la simultaneidad. Por lo tanto, cualquiera de los métodos siguientes que organizan los datos por una columna distinta de la columna secuencial logra este objetivo.

1. Confirme la contención en PAGELATCH_EX e identifique el recurso de contención.

Este script de T-SQL le ayuda a detectar si hay PAGELATCH_EX esperas en el sistema con varias sesiones (5 o más) con un tiempo de espera significativo (10 ms o más). También le ayuda a detectar qué objeto e índice está usando sys.dm_exec_requests y DBCC PAGE o sys.fn_PageResCracker y sys.dm_db_page_info (solo SQL Server 2019).

SET NOCOUNT ON
DECLARE @dbname SYSNAME, @dbid INT, @objectid INT, @indexid INT, @indexname SYSNAME, @sql VARCHAR(8000), @manul_identification VARCHAR(8000)

IF (CONVERT(INT, SERVERPROPERTY('ProductMajorVersion')) >= 15)
BEGIN

    DROP TABLE IF EXISTS #PageLatchEXContention

    SELECT DB_NAME(page_info.database_id) DbName, r.db_id DbId, page_info.[object_id] ObjectId, page_info.index_id IndexId
    INTO #PageLatchEXContention
    FROM sys.dm_exec_requests AS er
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
        CROSS APPLY sys.fn_PageResCracker (er.page_resource) AS r
        CROSS APPLY sys.dm_db_page_info(r.[db_id], r.[file_id], r.page_id, 'DETAILED') AS page_info
    WHERE er.wait_type = 'PAGELATCH_EX' AND page_info.database_id not in (db_id('master'),db_id('msdb'), db_id('model'), db_id('tempdb'))
    GROUP BY DB_NAME(page_info.database_id), r.db_id, page_info.[object_id], page_info.index_id
    HAVING COUNT(er.session_id) > 5 AND Max (er.wait_time) > 10

    SELECT * FROM #PageLatchEXContention
    IF EXISTS (SELECT 1 FROM #PageLatchEXContention)
    BEGIN
        DECLARE optimize_for_seq_key_cursor CURSOR FOR
            SELECT DbName, DbId, ObjectId, IndexId FROM #PageLatchEXContention
            
        OPEN optimize_for_seq_key_cursor
        FETCH NEXT FROM optimize_for_seq_key_cursor into @dbname, @dbid, @objectid , @indexid
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT 'Consider using below statement to enable OPTIMIZE_FOR_SEQUENTIAL_KEY for the indexes in the "' + @dbname + '" database' AS Recommendation
            SELECT @sql =  'select ''use ' + @dbname + '; ALTER INDEX '' + i.name + '' ON ' + OBJECT_NAME(@objectid, @dbid) + ' SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON )'' AS Corrective_Action from #PageLatchEXContention pl JOIN ' + @dbname+'.sys.indexes i ON pl.ObjectID = i.object_id WHERE object_id = ' + CONVERT(VARCHAR, @objectid) + ' AND index_id = ' + CONVERT(VARCHAR, @indexid)

            EXECUTE (@sql)
            FETCH NEXT FROM optimize_for_seq_key_cursor INTO @dbname, @dbid, @objectid , @indexid

        END

        CLOSE optimize_for_seq_key_cursor
        DEALLOCATE optimize_for_seq_key_cursor
    
    END
    ELSE
        SELECT 'No PAGELATCH_EX contention found on user databases on in SQL Server at this time'
END
ELSE
BEGIN
    
    IF OBJECT_ID('tempdb..#PageLatchEXContentionLegacy') IS NOT NULL
        DROP TABLE #PageLatchEXContentionLegacy
    
    SELECT 'dbcc traceon (3604); dbcc page(' + replace(wait_resource,':',',') + ',3); dbcc traceoff (3604)' TSQL_Command
    INTO #PageLatchEXContentionLegacy
    FROM sys.dm_exec_requests er
    WHERE er.wait_type = 'PAGELATCH_EX' AND er.database_id NOT IN (db_id('master'),db_id('msdb'), db_id('model'), db_id('tempdb'))
    GROUP BY wait_resource
    HAVING COUNT(er.session_id) > 5 AND Max (er.wait_time) > 10

    SELECT * FROM #PageLatchEXContentionLegacy
    
    IF EXISTS(SELECT 1 FROM #PageLatchEXContentionLegacy)
    BEGIN
        SELECT 'On SQL Server 2017 or lower versions, you can manually identify the object where contention is occurring using DBCC PAGE locate the m_objId = ??. Then SELECT OBJECT_NAME(object_id_identified) and locate indexes with sequential values in this object' AS Recommendation
        
        DECLARE get_command CURSOR FOR
            SELECT TSQL_Command from #PageLatchEXContentionLegacy

        OPEN get_command
        FETCH NEXT FROM get_command into @sql
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @sql AS Step1_Run_This_Command_To_Find_Object
            SELECT 'select OBJECT_NAME(object_id_identified)' AS Step2_Find_Object_Name_From_ID
            FETCH NEXT FROM get_command INTO @sql
        END

        CLOSE get_command
        DEALLOCATE get_command

        SELECT 'Follow https://learn.microsoft.com/troubleshoot/sql/performance/resolve-pagelatch-ex-contention for resolution recommendations that fits your environment best' Step3_Apply_KB_article
        
    END
    ELSE
        SELECT 'No PAGELATCH_EX contention found on user databases on in SQL Server at this time'

END

2. Elija un método para resolver el problema.

Puede usar uno de los métodos siguientes para resolver el problema. Elija el que mejor se adapte a sus circunstancias.

Método 1: Usar OPTIMIZE_FOR_SEQUENTIAL_KEY opción de índice (solo SQL Server 2019)

En SQL Server 2019, se agregó una nueva opción de índice (OPTIMIZE_FOR_SEQUENTIAL_KEY) que puede ayudar a resolver este problema sin usar ninguno de los métodos siguientes. Consulte En segundo plano en OPTIMIZE_FOR_SEQUENTIAL_KEY para obtener más información.

Método 2: Quitar la clave principal de la columna de identidad

Convierta la columna que contiene valores secuenciales en un índice no clúster y, a continuación, mueva el índice clúster a otra columna. Por ejemplo, para una clave principal en una columna de identidad, quite la clave principal agrupada y vuelva a crearla como una clave principal no agrupada. Este método es el más fácil de seguir y logra directamente el objetivo.

Por ejemplo, suponga que tiene la tabla siguiente que se definió mediante una clave principal agrupada en una columna Identity.

USE testdb;

CREATE TABLE Customers
( CustomerID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
CustomerLastName VARCHAR (32) NOT NULL,
CustomerFirstName VARCHAR(32) NOT NULL );

Para cambiar este diseño, puede quitar el índice de clave principal y volver a definirlo.

USE testdb;

ALTER TABLE Customers
DROP CONSTRAINT PK__Customer__A4AE64B98819CFF6;

ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY NONCLUSTERED (CustomerID)

Método 3: Convertir la clave inicial en una columna no secuencial

Reordene la definición de índice agrupado de forma que la columna inicial no sea la columna secuencial. Este método requiere que el índice clúster sea un índice compuesto. Por ejemplo, en una tabla customer, puede hacer que una columna CustomerLastName sea la columna inicial, seguida de CustomerID. Se recomienda probar exhaustivamente este método para asegurarse de que cumple los requisitos de rendimiento.

USE testdb;

ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY CLUSTERED (CustomerLastName, CustomerID)

Método 4: Agregar un valor no secuencial como clave inicial

Agregue un valor hash no secuencial como clave de índice inicial. Esta técnica también ayuda a distribuir las inserciones. Un valor hash se genera como un módulo que coincide con el número de CPU del sistema. Por ejemplo, en un sistema de 16 CPU, puede usar un módulo de 16. Este método distribuye las operaciones INSERT uniformemente en varias páginas de base de datos.

USE testdb;

CREATE TABLE Customers
( CustomerID BIGINT IDENTITY(1,1) NOT NULL,
CustomerLastName VARCHAR (32) NOT NULL,
CustomerFirstName VARCHAR(32) NOT NULL );

ALTER TABLE Customers
ADD [HashValue] AS (CONVERT([TINYINT], abs([CustomerID])%16)) PERSISTED NOT NULL;

ALTER TABLE Customers
ADD CONSTRAINT pk_table1
PRIMARY KEY CLUSTERED (HashValue, CustomerID);

Método 5: Usar un GUID como clave inicial

Use un GUID como columna de clave inicial de un índice para garantizar la distribución uniforme de las inserciones.

Nota:

Aunque logra el objetivo, no se recomienda este método porque presenta varios desafíos, como una clave de índice grande, divisiones de página frecuentes, baja densidad de páginas, etc.

Método 6: Usar la creación de particiones de tabla y una columna calculada con un valor hash

Use la creación de particiones de tabla y una columna calculada que tenga un valor hash para distribuir las operaciones INSERT. Dado que este método usa particiones de tabla, solo se puede usar en las ediciones Enterprise de SQL Server.

Nota:

Puede usar tablas con particiones en SQL Server 2016 SP1 Standard Edition. Para obtener más información, consulte la descripción de "Creación de particiones de tabla e índice" en el artículo Ediciones y características admitidas de SQL Server 2016.

El siguiente es un ejemplo en un sistema que tiene 16 CPU.

USE testdb;

CREATE TABLE Customers
( CustomerID BIGINT IDENTITY(1,1) NOT NULL,
CustomerLastName VARCHAR (32) NOT NULL,
CustomerFirstName VARCHAR(32) NOT NULL );

ALTER TABLE Customers
ADD [HashID] AS CONVERT(TINYINT, ABS(CustomerID % 16)) PERSISTED NOT NULL;

CREATE PARTITION FUNCTION pf_hash (TINYINT) AS RANGE LEFT FOR VALUES (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ;

CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY]);

CREATE UNIQUE CLUSTERED INDEX CIX_Hash
ON Customers (CustomerID, HashID) ON ps_hash(HashID);

Método 7: Cambiar a In-Memory OLTP

Como alternativa, use In-Memory OLTP, especialmente si la contención de bloqueos temporales es alta. Esta tecnología elimina la contención de bloqueos temporales en general. Sin embargo, tiene que rediseñar y migrar las tablas específicas, donde se observa la contención de bloqueos temporales de página, a una tabla optimizada para memoria. Puede usar el Asesor de optimización de memoria y el Informe de análisis de rendimiento de transacciones para determinar si es posible la migración y cuál sería el esfuerzo para realizar la migración. Para obtener más información sobre cómo In-Memory OLTP elimina la contención de bloqueos temporales, descargue y revise el documento en OLTP en memoria: patrones comunes de carga de trabajo y consideraciones de migración.

Referencias

PAGELATCH_EX esperas e inserciones pesadas

Compartir a través de

Resolución de la contención de inserción de última página PAGELATCH_EX en SQL Server

Síntomas

Causa

Solución

Opción 1: Ejecutar los pasos directamente en un cuaderno a través de Azure Data Studio

Opción 2: Siga los pasos manualmente

1. Confirme la contención en PAGELATCH_EX e identifique el recurso de contención.

2. Elija un método para resolver el problema.

Método 1: Usar OPTIMIZE_FOR_SEQUENTIAL_KEY opción de índice (solo SQL Server 2019)

Método 2: Quitar la clave principal de la columna de identidad

Método 3: Convertir la clave inicial en una columna no secuencial

Método 4: Agregar un valor no secuencial como clave inicial

Método 5: Usar un GUID como clave inicial

Método 6: Usar la creación de particiones de tabla y una columna calculada con un valor hash

Método 7: Cambiar a In-Memory OLTP

Referencias

Comentarios

Recursos adicionales