Разрешение конфликтов PAGELATCH_EX вставки последней страницы в SQL Server

Статья
12/14/2024

Исходная версия продукта: SQL Server
Исходный номер базы знаний: 4460004

В этой статье описывается, как устранить проблемы вставки PAGELATCH_EX последней страницы в SQL Server.

Симптомы

Рассмотрим следующие сценарии.

У вас есть столбец, включающий последовательные значения, такие как столбец Identity или столбец DateTime, вставленный через функцию Getdate().
У вас есть кластеризованный индекс, имеющий последовательный столбец в качестве ведущего столбца.

Примечание.

Наиболее распространенным сценарием является кластеризованный первичный ключ в столбце Identity. Менее часто эта проблема может наблюдаться для некластеризованных индексов.
Приложение выполняет частые операции INSERT или UPDATE в таблице.
У вас есть много ЦП в системе. Как правило, сервер имеет 16 ЦП или более. Эта конфигурация оборудования позволяет нескольким сеансам выполнять операции INSERT с одной и той же таблицей одновременно.

В этой ситуации может возникнуть снижение производительности приложения. При проверке типов sys.dm_exec_requestsожиданий наблюдается ожидание типа ожидания PAGELATCH_EX и многих сеансов, ожидающих этого типа ожидания.

Другая проблема возникает при выполнении следующего диагностического запроса в системе:

выберите session_id, wait_type, wait_time, wait_resource откуда sys.dm_exec_requests session_id > 50 и wait_type = "pagelatch_ex".

В этой ситуации вы можете получить результаты, похожие на приведенные ниже.

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

Обратите внимание, что несколько сеансов ожидают одного ресурса, который похож на следующий шаблон:

database_id = 5, file_id = 1, база данных page_id = 4144

Примечание.

Database_id должна быть пользовательской базой данных (номер идентификатора больше или равен 5). Если database_id равно 2, может возникнуть проблема, которая рассматривается в файлах, флагах трассировки и обновлениях в TEMPDB.

Причина

PAGELATCH (блокировка на странице данных или индекса) — это механизм синхронизации потоков. Он используется для синхронизации краткосрочного физического доступа к страницам базы данных, расположенным в кэше буфера.

PAGELATCH отличается от PAGEIOLATCH. Последний используется для синхронизации физического доступа к страницам, когда они считываются или записываются на диск.

Блоки страниц распространены в каждой системе, так как они обеспечивают защиту физических страниц. Кластеризованный индекс упорядочивает данные по главному ключевому столбцу. По этой причине при создании индекса в последовательном столбце все новые вставки данных происходят на той же странице в конце индекса до заполнения этой страницы. Однако при высокой нагрузке одновременные операции INSERT могут вызвать спор на последней странице дерева B. Это состязание может возникать в кластеризованных и некластеризованных индексах. Причина заключается в том, что некластеризованные индексы упорядочивают страницы конечного уровня по главному ключу. Эта проблема также называется спорной вставкой последней страницы.

Дополнительные сведения см. в разделе "Диагностика и разрешение конфликтов с блокировкой" на SQL Server.

Решение

Чтобы устранить проблему, можно выбрать один из следующих двух вариантов.

Вариант 1. Выполнение действий непосредственно в записной книжке с помощью Azure Data Studio

Примечание.

Прежде чем пытаться открыть эту записную книжку, убедитесь, что Azure Data Studio установлена на локальном компьютере. Чтобы установить его, перейдите к разделу "Узнайте, как установить Azure Data Studio".

Откройте записную книжку в Azure Data Studio

Вариант 2. Выполните действия вручную.

Чтобы устранить эту проблему, общая стратегия заключается в том, чтобы предотвратить доступ ко всем параллельным операциям INSERT на одной странице базы данных. Вместо этого сделайте каждую операцию INSERT доступом к другой странице и увеличьте параллелизм. Поэтому любой из следующих методов, которые упорядочивают данные по столбцу, отличному от последовательного столбца, достигают этой цели.

1. Подтвердите состязание по PAGELATCH_EX и определите ресурс состязания.

Этот скрипт T-SQL помогает обнаружить, есть ли ожидания PAGELATCH_EX в системе с несколькими сеансами (5 или более) со значительным временем ожидания (10 мс или более). Кроме того, вы узнаете, какой объект и индексирование конфликтуется с использованием sys.dm_exec_requests и DBCC PAGE или sys.fn_PageResCracker и sys.dm_db_page_info (только 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. Выберите метод для устранения проблемы

Для устранения проблемы можно использовать один из следующих методов. Выберите тот, который лучше всего подходит для ваших обстоятельств.

Метод 1. Использование параметра индекса OPTIMIZE_FOR_SEQUENTIAL_KEY (только SQL Server 2019)

В SQL Server 2019 добавлен новый параметр индекса (OPTIMIZE_FOR_SEQUENTIAL_KEY), который может помочь устранить эту проблему без использования следующих методов. Дополнительные сведения см . в разделе "За кулисами" OPTIMIZE_FOR_SEQUENTIAL_KEY .

Метод 2. Перемещение первичного ключа из столбца удостоверения

Сделайте столбец, содержащий последовательные значения некластеризованного индекса, а затем переместите кластеризованный индекс в другой столбец. Например, для первичного ключа в столбце удостоверений удалите кластеризованный первичный ключ, а затем повторно создайте его как некластеризованный первичный ключ. Этот метод является самым простым и напрямую достигает цели.

Например, предположим, что у вас есть следующая таблица, определяемая с помощью кластеризованного первичного ключа в столбце 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 );

Чтобы изменить эту структуру, можно удалить индекс первичного ключа и переопределить его.

USE testdb;

ALTER TABLE Customers
DROP CONSTRAINT PK__Customer__A4AE64B98819CFF6;

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

Метод 3. Создание начального ключа несоединяемого столбца

Переупорядочение определения кластеризованного индекса таким образом, чтобы ведущий столбец не был последовательным столбцом. Этот метод требует, чтобы кластеризованный индекс был составным индексом. Например, в таблице клиента можно сделать столбец CustomerLastName ведущим, за которым следует CustomerID. Рекомендуется тщательно протестировать этот метод, чтобы убедиться, что он соответствует требованиям к производительности.

USE testdb;

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

Метод 4. Добавление последовательного значения в качестве ведущего ключа

Добавьте хэш-значение, отличное от имени ведущего ключа индекса. Этот метод также помогает распространить вставки. Хэш-значение создается в виде модуля, который соответствует количеству ЦП в системе. Например, в 16-ЦП можно использовать модуль 16. Этот метод распределяет операции INSERT равномерно на нескольких страницах базы данных.

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);

Метод 5. Использование GUID в качестве ведущего ключа

Используйте GUID в качестве ведущего ключевого столбца индекса, чтобы обеспечить равномерное распределение вставок.

Примечание.

Хотя он достигает цели, мы не рекомендуем этот метод, так как он представляет несколько проблем, включая большой ключ индекса, частые разбиения страниц, низкую плотность страниц и т. д.

Метод 6. Использование секционирования таблиц и вычисляемого столбца со значением хэша

Используйте секционирование таблиц и вычисляемый столбец с хэш-значением для распространения операций INSERT. Так как этот метод использует секционирование таблиц, он доступен только в выпусках SQL Server Enterprise.

Примечание.

Секционированные таблицы можно использовать в SQL Server 2016 с пакетом обновления 1 (SP1) выпуск Standard. Дополнительные сведения см. в описании раздела "Секционирование таблиц и индексов" в выпусках и поддерживаемых функциях SQL Server 2016.

Ниже приведен пример в системе с 16 ЦП.

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);

Метод 7. Переключение на OLTP в памяти

Кроме того, используйте OLTP в памяти, особенно если конфликты блокировки высоки. Эта технология устраняет общее несоответствие блокировок. Однако необходимо перепроектировать и перенести определенные таблицы, в которых наблюдается нарушение блокировки страниц в оптимизированную для памяти таблицу. Вы можете использовать помощник по оптимизации памяти и отчет о анализе производительности транзакций, чтобы определить, возможна ли миграция и какие усилия потребуется выполнить миграцию. Дополнительные сведения о том, как OLTP в памяти устраняет конфликты с блокировкой, скачайте и просмотрите документ в OLTP в памяти — общие шаблоны рабочих нагрузок и рекомендации по миграции.

Ссылки

PAGELATCH_EX ожиданий и тяжелых вставок

Поделиться через

Разрешение конфликтов PAGELATCH_EX вставки последней страницы в SQL Server

Симптомы

Причина

Решение

Вариант 1. Выполнение действий непосредственно в записной книжке с помощью Azure Data Studio

Вариант 2. Выполните действия вручную.

1. Подтвердите состязание по PAGELATCH_EX и определите ресурс состязания.

2. Выберите метод для устранения проблемы

Метод 1. Использование параметра индекса OPTIMIZE_FOR_SEQUENTIAL_KEY (только SQL Server 2019)

Метод 2. Перемещение первичного ключа из столбца удостоверения

Метод 3. Создание начального ключа несоединяемого столбца

Метод 4. Добавление последовательного значения в качестве ведущего ключа

Метод 5. Использование GUID в качестве ведущего ключа

Метод 6. Использование секционирования таблиц и вычисляемого столбца со значением хэша

Метод 7. Переключение на OLTP в памяти

Ссылки

Обратная связь

Дополнительные ресурсы