Resolución de la contención de inserción de última página PAGELATCH_EX en SQL Server
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.
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
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de