Авторы: Thomas Kejser, Lindsey Allen, Arvind Rao и Michael Thomassy

При участии и с рецензиями: Mike Ruthruff, Lubor Kollar, Prem Mehra, Burzin Patel, Michael Thomassy, Mark Souza, Sanjay Mishra, Peter Scharlock, Stuart Ozer, Kun Cheng и Howard Yin

Введение

Недавно, мы проводили лабораторные испытания в Microsoft Enterprise Engineering Center, при которых использовалась большая рабочая нагрузка, характерная для OLTP систем. Целью этой лабораторной работы было взять Microsoft SQL Server c интенсивной рабочей нагрузкой и посмотреть,  что случится при увеличении числа процессоров с 64 до 128  (примечание: эта конфигурация была ориентирована на релиз Microsoft SQL Server 2008 R2). Рабочая нагрузка представляла собой бльшое количесво одновеменных  операций вставки, направляемых в несколько больших таблиц.

Как только мы начали масштабировать  нагрузку до 128 процессорных ядер, сразу же в статистике ожиданий стали доминировать  блокировоки PAGELATCH_UP и PAGELATCH_EX. Средняя продолжительность ожиданияй была десятки миллисекунд, и таких ожиданий было очень много. Такое их количество оказалось для нас неожиданностью, ожидалось, что их продолжительность не будет превышать несколько миллисекунд.

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

Диагностика проблемы

Когда в sys.dm_os_wait_stats наблюдается большое число ожиданий PAGELATCH, с помощью sys.dm_os_waiting_tasks можно определить сессию и ресурс, который задача ожидает, например, с помощью этого сценария:

SELECT session_id, wait_type, resource_description

FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE 'PAGELATCH%'

Пример результата:              

В столбце resource_description указаны местоположения страниц, к которым ожидают доступ сессии, местоположение представлено в таком формате:

<database_id>:<file_id>:<page_id>

Опираясь на значения в столбце resource_description, можно составить довольно сложный запрос, который предоставит выборку всех попавших в список ожидания страниц:

SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms   

, s.name AS schema_name , o.name AS object_name ,

i.name AS index_name 

FROM sys.dm_os_buffer_descriptors bd JOIN (     

SELECT *          

, CHARINDEX(':', resource_description) AS file_index

, CHARINDEX(':', resource_description 

, CHARINDEX(':', resource_description)) AS page_index         

 , resource_description AS rd

FROM sys.dm_os_waiting_tasks wt

WHERE wait_type LIKE 'PAGELATCH%'        

)AS wt

     ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)

     AND bd.file_id = SUBSTRING(wt.rd, wt.file_index, wt.page_index)

     AND bd.page_id = SUBSTRING(wt.rd, wt.page_index, LEN(wt.rd))

JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id

JOIN sys.partitions p ON au.container_id = p.partition_id

JOIN sys.indexes i ON  p.index_id = i.index_id AND p.object_id = i.object_id

JOIN sys.objects o ON i.object_id = o.object_id

JOIN sys.schemas s ON o.schema_id = s.schema_id

Запрос показал, что ожидаемые страницы относятся к кластеризованному индексу, определённому первичным ключом таблицы с представленной ниже структурой:

CREATE TABLE HeavyInsert (   

ID INT PRIMARY KEY CLUSTERED    

, col1 VARCHAR(50)

) ON [PRIMARY]

Что происходит, почему возникает очередь ожиданий к страницам данных индекса - всё это будет рассмотрено в этой технической заметке.

Основная информация

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

• 1. В журнале транзакций создаётся запись о том, что строка изменилась.
• 2. Осуществляется поиск в В-дереве местонахождения той страницы, куда должна будет попасть новая запись.
• 3. Осуществляется наложение на эту страницу краткой блокировки PAGELATCH_EX, чтобы воспрепятствовать другим изменениям на этой странице.
• 4. Осуществляется добавление строки в страницу и, если это необходимо, осуществляется пометка этой страницы как «грязной».
• 5. Осуществляется снятие краткой блокировки со страницы.

В итоге, страница  будет сброшена на диск процессом контрольной точкой или отложенной записи.

Однако, что произойдет если все вставляемые строки попадают на одну и ту же страницу? В данном случае можно наблюдать рост очереди к этой странице. Даже учитывая, что краткая блокировка весьма непродолжительна, она может стать причиной конкуренции при высокой параллельной рабочей нагрузке. У нашего клиента, первый и единственный столбец в индексе являлся монотонно возрастающим ключом. Из-за этого, каждая новая вставка шла на ту же самую страницу в конце В-дерева, пока эта страница не была заполнена. Рабочие нагрузки, которые используют в качестве первичного ключа IDENTITY или другие столбцы с последовательно увеличивающимися значениями, также могут столкнуться с подобной проблемой, если одновременно выполняемая нагрузка достаточно высока.

Решение

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

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

Помните, что местом конкуренции является одна страница в В-дерева. Если бы только  было возможно использовать для этого несколько В-деревьев для  одной таблицы!. К счастью, такая возможность есть, это: Секционированные таблицы и индексы. Таблица может быть секционирована таким способом, чтобы новые строки размещались в нескольких  секциях.

Сначала нужно создать функцию и схему секционирования:

CREATE PARTITION FUNCTION pf_hash (INT) AS RANGE LEFT FOR VALUES (0,1,2)

CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY])

Представленный выше пример использует четыре секции. Число необходимых секций зависит от числа активных процессов, выполняющих операции INSERT в описанную выше таблицу. Есть некоторая сложность в секционировании таблицы с помощью хэш-столбца,например в том, что всякий раз, когда происходит выборка строк из таблицы, будут затронуты все секции. Это означает, что придётся обращаться более чем к одному В-дереву, т.е. не будет отброшенных оптимизатором за ненадобностью ненужных секций. Связанная сэтим дополнительная нагрузка на процессоры и некоторое увеличение времени ожиданий процессоров, побуждает минимизировать число планируемых секций (их должно быть минимальное количество, при котором не наблюдается PAGELATCH). В рассматриваемом нами случае, в системе нашего клиента имелось достаточно много резерва в утилизации процессоров, так что было вполне возможно допустить небольшую потерю времени для инструкций SELECT, и при этом увеличить до необходимых объёмов норму инструкций INSERT.

Далее нам требуется столбец, который поможет распределить вставки  по четырем секциям. Такого столбца изначально в сценарии Microsoft Enterprise Engineering Center не было. Однако, его всегда достаточно просто создать. Используя  тот факт, что столбец ID монотонно увеличивается с приращением равным единице, и здесь легко применима довольно простая хеш-функция:

CREATE TABLE HeavyInsert_Hash(   

ID INT NOT NULL    , col1 VARCHAR(50)    , HashID AS ID % 4 PERSISTED NOT NULL)

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

CREATE UNIQUE CLUSTERED INDEX CIX_Hash

ON HeavyInsert_Hash (ID, HashID) ON ps_hash(HashID)

Используя новую схему таблицы с секционированием вместо первоначального варианта таблицы, мы сумели избавиться от очередей PAGELATCH и повысить скорость вставки. Этого удалось достичь за счёт балансировки одновременных вставок между несколькими секциями, где каждая секция имеет своё В-дерево. Нам  удалось повысить производительность вставки на 15 процентов, и избавиться от большой очереди PAGELATCH к горячей странице индекса одной таблицы. При этом у нас остался достаточно большой резерв процессоров, что делает возможным дальнешую отимизацию, если   мы захотим применить аналогичный прием для другой таблицы, тоже с высокой нормой вставки.

Строго говоря, суть этой уловки в оптимизации логической схемы первичного ключа таблицы. Однако, потому что ключ просто стал длиннее на величину хеш-функции относительно изначального ключа, дубликатов для столбца ID удалось избежать.

Уникальные индексы по единственному столбцу таблицы зачастую становятся причиной проблем с очередями PAGELATCH. Но даже если эту проблему удастся устранить, у таблицы может оказаться другой, некластеризованный индекс, который будет испытвать аналогичную проблему. Как правило, проблема наблюдается для уникальных ключей на единственном столбце, где каждая вставка попадает на одну и ту же страницу. Если и у других таблиц индексы испытывают высокую конкуренцию с PAGELATCH, можно применить тот же прием с секционированием к индексам таких таблиц, используя такой же  хэш-ключ в качестве первичного ключа.

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

Пример: Чтобы отбросить ненужные секции, можно внести следующие изменения в сценарий:

 SELECT * FROM HeavyInsert_Hash 

 WHERE ID = 42

Который после изменений будет выглядеть так:

 WHERE ID = 42 AND HashID  = 42 % 4

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