Как стать автором
Обновить
816.2
OTUS
Цифровые навыки от ведущих экспертов

Избавляемся от PAGELATCH_EX в SQL Server

Время на прочтение 6 мин
Количество просмотров 6.5K
Автор оригинала: Esat Erkec

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

Введение

SQL Server спроектирован так, что все операции чтения и изменения данных производятся в оперативной памяти, а не напрямую на диске. Такой подход позволяет уменьшить количество физических операций ввода-вывода, а также улучшить время отклика. Нет сомнений, что в целом такая архитектура положительно сказывается на производительности SQL Server. Эта зарезервированная область памяти называется буферным пулом (buffer pool) или буферным кешем (buffer cache). За координацию изменений и целостность страниц данных и индексов в буферном пуле отвечает внутренний механизм SQL Server, называемый "latch" (защелка). Однако при большой нагрузке этот механизм может приводить к некоторым проблемам производительности.

Что такое PAGELATCH?

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

Конкуренция за последнюю страницу при вставке

Для идентификации строк используется первичный ключ, и по умолчанию для него автоматически создается кластерный индекс. Такой подход часто используется разработчиками баз данных. Если столбец дополнительно помечен как identity, то мы получаем последовательно увеличивающееся значение ключа. Кластерный индекс хранит данные в упорядоченном виде, поэтому при вставке новой строки она добавляется в конец страницы кластерного индекса, пока эта страница не будет заполнена. Если данные добавляет только один поток, то мы никогда не столкнемся с конкуренцией на последней странице, потому что подобная проблема возникает только при конкурентном доступе. При большом количестве операций INSERT возникает конкуренция за последнюю страницу, так как последняя страница блокируется одним из потоков, а все остальные ждут, пока страница не станет им доступна. Это влияет на производительность SQL Server и провоцирует появление ожиданий PAGELATCH_EX.

Давайте продемонстрируем эту проблему на очень простом примере. Сначала создадим простую таблицу.

CREATE TABLE InsertTestTable(
Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL
, Col VARCHAR(50) NOT NULL)
GO

Колонка Id — первичный ключ и кластерный индекс. Она также объявлена как identity — значение будет автоматически увеличиваться. Для создания нагрузки будем использовать утилиту SQLQueryStress. Нагрузка будет в виде INSERT.

SET NOCOUNT ON;
DECLARE @i int = 1
WHILE @i < 1000
BEGIN
INSERT INTO dbo.InsertTestTable (Col) VALUES ('Test Value')
SET @i += 1
END;

В SQLQueryStrees вводим параметры подключения к базе данных.

Устанавливаем 10 итераций (Number of Iterations) и 50 потоков (Number of Threads). Таким образом, SQLQueryStress выполнит запрос 10 раз в 50 потоках одновременно.

Запускаем SQLQueryStress и смотрим, что происходит в SQL Server.

SELECT dm_ws.wait_duration_ms,
dm_ws.wait_type,
dm_r.status,
dm_r.wait_resource,
dm_t.TEXT,
dm_qp.query_plan,
dm_ws.session_ID,
dm_es.cpu_time,
dm_es.memory_usage,
dm_es.logical_reads, 
dm_es.total_elapsed_time,
dm_es.program_name,
DB_NAME(dm_r.database_id) DatabaseName
FROM sys.dm_os_waiting_tasks dm_ws
INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t
CROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp
WHERE dm_es.is_user_process = 1

Несколько сессий находятся в состоянии suspended с типом ожидания PAGELATCH_EX. Они ждут страницу, которая в данный момент недоступна.

В колонке wait_resource есть информация о заблокированной странице:

11: Database Id (ИД базы данных)

1: File Id (ИД файла)

14339: Page Id (ИД страницы)

Заглянуть внутрь этой страницы можно с помощью команды DBCC PAGE.

DBCC TRACEON(3604)
GO
DBCC PAGE(11,1,14339,0)
GO

Информация о странице содержится в разделе PAGE HEADER.

m_type = 1 указывает, что эта страница относится в куче или к листовому уровню кластерного индекса.

IndexId = 1 указывает, что эта страница принадлежит к индексу, index id, которого равен 1.

ObjectId = 581577110 — идентификатор объекта базы данных.

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

SELECT
name AS Index_Name,
type_desc  As Index_Type,
is_unique,
OBJECT_NAME(object_id) As Table_Name 
FROM
sys.indexes
WHERE
is_hypothetical = 0 AND
index_id != 0 and object_id=581577110

Как видите, проблемный индекс относится таблице, созданной нами ранее. Теперь посмотрим варианты решения этой проблемы. А наш тест тем временем завершился за 58 секунд.

Параметр OPTIMIZE_FOR_SEQUENTIAL_KEY

Параметр OPTIMIZE_FOR_SEQUENTIAL_KEY появился в SQL Server 2019. Он ограничивает количество потоков, которым разрешено запрашивать latch, до одного на планировщик, что сокращает время нахождения в очереди runnable после получения latch. Включить этот параметр можно следующим образом:

ALTER INDEX PK__InsertTe__3214EC074999CCCE
    ON dbo.InsertTestTable SET(OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);

После включения параметра OPTIMIZE_FOR_SEQUENTIAL_KEY запустим SQLQueryStress с тем же запросом и прежними настройками.

Теперь вместо PAGELATCH_EX мы видим BTREE_INSERT_FLOW_CONTROL.

Несмотря на это, мы получили некоторое улучшение производительности.

Некластерный индекс для первичного ключа

Основное различие между кластерным и некластерным индексом состоит в том, что кластерный индекс хранит таблицу в отсортированном виде. Куча (heap), с другой стороны, не содержит кластерного индекса и, так как при вставке нет необходимости поддерживать сортировку, операции INSERT будут быстрее. Мы можем  воспользоваться этим фактом для решения нашей проблемы и уменьшить ожидания PAGELATCH_EX. Сначала удалим кластерный индекс и первичный ключ в InsertTestTable.

ALTER TABLE InsertTestTable 
DROP CONSTRAINT PK__InsertTe__3214EC074999CCCE

Потом создадим первичный ключ с некластерным индексом.

ALTER TABLE InsertTestTable
ADD CONSTRAINT PrimaryKey_Id
PRIMARY KEY NONCLUSTERED (ID)

После создания некластерного индекса запустим SQLQueryStress с прежними параметрами.

Тест завершился за 57 секунд и не сильно повлиял на производительность SQL Server при операциях INSERT.

Таблицы, оптимизированные для памяти

Вместо таблиц на диске мы можем использовать таблицы, оптимизированные для памяти (In-Memory Optimized Tables). В контексте производительности SQL Server таблицы, оптимизированные для памяти, могут помочь решить проблемы с блокировками. 

Для создания таблицы, оптимизированной для памяти, сделаем следующее:

— Добавим файловую группу

ALTER DATABASE SQLShackDemo
ADD FILEGROUP SQLShackDemo_mod CONTAINS MEMORY_OPTIMIZED_DATA;

— Добавим файл в созданную файловую группу.

ALTER DATABASE SQLShackDemo
ADD FILE (name='SQLShackDemo_mod1',
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SQLShackDemo_mod1.ndf')
TO FILEGROUP SQLShackDemo_mod

После создания файловой группы и файла создадим таблицу с DURABILITY = SCHEMA_AND_DATA. Есть два типа оптимизированных для памяти таблиц: SCHEMA_ONLY и SCHEMA_AND_DATA.

SCHEMA_ONLY

В таких таблицах при перезапуске SQL Server данные теряются, но схема сохраняется. Таблицы SCHEMA_ONLY работают очень быстро, потому что они не генерируют никаких физических операций ввода-вывода.

SCHEMA_AND_DATA

В таблицах этого типа данные и схема не теряются, они ведут себя как обычные таблицы.

Создадим таблицу, оптимизированную для памяти с некластерным индексом и первичным ключом. Таблицы, оптимизированные для памяти, не поддерживают кластерные индексы.

CREATE TABLE InMemoryInsertTestTable
(Id  INTEGER NOT NULL IDENTITY(1, 1) PRIMARY KEY NONCLUSTERED,
    Col VARCHAR(50) NOT NULL
) WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

И запустим наш тест.

Как видите, использование оптимизированных для памяти таблиц значительно улучшило производительность SQL Server для операций INSERT, и мы больше не видим ожиданий PAGELATCH_EX.

Выводы

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

  • Включить параметр OPTIMIZE_FOR_SEQUENTIAL_KEY.

  • Использовать таблицы, оптимизированные для памяти (In-memory OLTP).

  • Использовать некластерный индекс для первичного ключа.


Материал подготовлен в рамках курса "MS SQL Server Developer". Если вам интересно узнать подробнее о формате обучения и программе, познакомиться с преподавателем курса — приглашаем на день открытых дверей онлайн. Регистрация здесь.

Теги:
Хабы:
+5
Комментарии 3
Комментарии Комментарии 3

Публикации

Информация

Сайт
otus.ru
Дата регистрации
Дата основания
Численность
101–200 человек
Местоположение
Россия
Представитель
OTUS