Разрешение конфликтов по вставке последней страницы PAGELATCH_EX в SQL Server
Оригинальная версия продукта: 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.
Вариант 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 — общие шаблоны рабочих нагрузок и рекомендации по миграции.
Ссылки
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по