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

Статья
06/15/2023

Оригинальная версия продукта: 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

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

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

Переупоряйте определение кластеризованного индекса таким образом, чтобы ведущий столбец не был последовательным столбцом. Для этого метода требуется, чтобы кластеризованный индекс был составным индексом. Например, в таблице customer можно сделать столбец 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.

Примечание.

Секционированные таблицы можно использовать в SQL Server 2016 с пакетом обновления 1 (SP1) Standard Edition. Дополнительные сведения см. в описании секционирования таблиц и индексов в статье Выпуски и поддерживаемые функции 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. Переключение на In-Memory OLTP

Кроме того, используйте In-Memory OLTP, особенно если состязание за блокировку является высоким. Эта технология устраняет состязание за кратковременные блокировки в целом. Однако необходимо изменить и перенести определенные таблицы, в которых наблюдается состязание за блокировку страницы, в оптимизированные для памяти таблицы. С помощью помощника по оптимизации памяти и отчета по анализу производительности транзакций можно определить, возможна ли миграция и какие усилия потребуются для миграции. Дополнительные сведения о том, как In-Memory 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. Перемещение первичного ключа из столбца identity

Метод 3. Сделайте ведущий ключ не последовательным столбцом

Метод 4. Добавление непоследовательных значений в качестве начального ключа

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

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

Метод 7. Переключение на In-Memory OLTP

Ссылки

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

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