Диагностируйте и устраняйте конфликты latch в SQL Server

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

С увеличением количества ядер ЦП на серверах возрастает степень параллелизма, что может привести к появлению точек конфликтов в структурах данных, доступ к которым в ядре СУБД должен осуществляться последовательным образом. Это в особенности справедливо для рабочих нагрузок с обработкой транзакций с высокой пропускной способностью и высокой степенью параллелизма (OLTP). Существует несколько инструментов, методов и способов решения этих проблем, а также практики, которые можно использовать в разработке приложений, которые могут помочь избежать их в целом. В этой статье описывается определенный тип состязания по структурам данных, используюющим спинлоки для сериализации доступа к этим структурам данных.

Что такое конфликт кратковременных блокировок в SQL Server?

Защёлки — это легковесные примитивы синхронизации, используемые ядром СУБД SQL Server для обеспечения согласованности структур в памяти, в том числе индексов, страниц данных и внутренних структур, таких как нелистовые страницы B-дерева. SQL Server использует кратковременные блокировки буфера для защиты страниц в буферном пуле и кратковременные блокировки ввода-вывода для защиты страниц, которые еще не загружены в буферный пул. Каждый раз, когда данные записываются на страницу или считываются со страницы в буферном пуле SQL Server, рабочий поток должен сначала захватить буферную защёлку для этой страницы. Существуют различные типы блокировок буфера, доступные для доступа к страницам в буферном пуле, включая монопольную блокировку (PAGELATCH_EX) и общую блокировку (PAGELATCH_SH). Когда SQL Server пытается получить доступ к странице, которая еще не присутствует в буферном пуле, выполняется асинхронный ввод-вывод для загрузки страницы в буферный пул. Если SQL Server должен ждать ответа от подсистемы ввода-вывода, он ожидает монопольной (PAGEIOLATCH_EX) или общей (PAGEIOLATCH_SH) блокировки ввода-вывода в зависимости от типа запроса; это делается для предотвращения загрузки другим рабочим потоком той же страницы в буферный пул с несовместимой блокировкой. Кратковременные блокировки также используются для защиты доступа к структурам во внутренней памяти, отличным от страниц буферного пула. В таких случаях применяются небуферные кратковременные блокировки.

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

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

• Общие сведения об использовании кратковременных блокировок в SQL Server.
• Средства, используемые для анализа конфликтов кратковременных блокировок.
• Способы определения того, может ли наблюдаемое количество конфликтов привести к возникновению проблем.

Мы обсудим некоторые распространенные сценарии и способы лучшего их решения для облегчения конкуренции.

Как SQL Server использует защелки?

Страница в SQL Server имеет размер 8 КБ и может содержать несколько строк. Чтобы увеличить степень параллелизма и производительность, кратковременные блокировки буфера накладываются только на время физической операции со страницей, в отличие от блокировок, которые действуют в ходе выполнения логической транзакции.

Защёлки используются внутри ядра SQL Server для обеспечения согласованности памяти, тогда как блокировки используются SQL Server для обеспечения логической согласованности транзакций. В таблице ниже сравниваются защёлки и блокировки:

Режимы защёлок SQL Server и их совместимость

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

Блокировки устанавливаются в одном из пяти различных режимов, которые соответствуют уровню доступа. Ниже приводится обобщенное описание режимов кратковременных блокировок в SQL Server:

• KP: сохраняйте защелку. Гарантирует, что ссылаемая структура не может быть уничтожена. Используется в тех случаях, когда потоку требуется просмотреть структуру в буфере. Поскольку защелка KP совместима со всеми защелками, за исключением защелки уничтожения (DT), защелка KP считается маломощной, что означает минимальное влияние на производительность при ее использовании. Поскольку защелка КП несовместима с защелкой DT, это препятствует другим потокам уничтожить указанную структуру. Например, защелка KP предотвращает уничтожение структуры, на которую она ссылается, отложенным процессом записи. Дополнительные сведения о том, как используется процесс ленивой записи для управления буферными страницами в SQL Server, см. в разделе "Запись страниц" в ядре СУБД.

• SH: Общая блокировка. Требуется для чтения указанной структуры (например, для чтения страницы данных). Несколько потоков могут одновременно получать доступ к ресурсу для чтения под разделяемой защёлкой.

• UP: обновите защёлку. Совместим с SH (общая блокировка) и KP, но не с другими, и поэтому не позволяет EX записывать в ссылочную структуру.

• EX: монопольная блокировка. Блокирует запись и чтение других потоков из указанной структуры. Например, она может использоваться для изменения содержимого страницы в целях защиты от ее разрыва.

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

Режимы защелки имеют разные уровни совместимости, например, общая защелка (SH) совместима с защелкой обновления (UP) или защелкой сохранения (KP), но несовместима с защелкой уничтожения (DT). Для одной и той же структуры можно одновременно получить несколько защёлок, если они совместимы. Когда поток пытается получить блокировку, удерживаемую в режиме, который не совместим, он помещается в очередь, чтобы ждать сигнала, указывающего, что ресурс доступен. Для защиты очереди ожидания используется спин-блокировка типа SOS_Task, обеспечивающая сериализованный доступ к очереди. Эту спин-блокировку необходимо захватить, чтобы добавить элементы в очередь. Спин-блокировка SOS_Task также сигнализирует находящимся в очереди потокам о высвобождении несовместимых кратковременных блокировок, благодаря чему ожидающие потоки могут получить совместимую кратковременную блокировку и продолжить работу. Очередь ожидания обрабатывается по принципу «первым поступил — первым обработан» (FIFO) по мере освобождения запросов на защёлку. Защёлки следуют этой системе FIFO, чтобы обеспечить справедливость и предотвратить голодание потоков.

Совместимость режима блокировки указана в следующей таблице (Да , указывает на совместимость, а не указывает на несовместимость):

Суперзащелки и подзащелки SQL Server

С увеличением распространённости систем на основе NUMA с несколькими сокетами или многоядерных систем SQL Server 2005 ввёл суперзамки, также известные как подзамки, которые эффективны только на системах с 32 или более логическими процессорами. Суперзащёлки повышают эффективность в подсистеме SQL для определённых шаблонов использования в рабочих нагрузках OLTP с высокой степенью параллельности; например, если определённые страницы имеют шаблон интенсивного общего доступа только для чтения (SH), но записываются редко. Пример страницы с таким шаблоном доступа — это корневая страница дерева B (т. е. индекс); Обработчик SQL требует, чтобы общая блокировка удерживалась на корневой странице, когда разделение страницы происходит на любом уровне в дереве B.. В рабочей нагрузке OLTP с высокой нагрузкой вставки и высоким параллелизмом количество разбиения страниц значительно увеличивается параллельно с скоростью обработки, что может снизить производительность. Суперлэтчи могут обеспечить повышенную производительность для доступа к общим страницам, где для нескольких одновременных рабочих потоков требуется SH защёлка. Для этого движок SQL Server динамически повышает защелку на такой странице до суперзащелки. Superlatch разделяет одну защелку на массив субзащёлок, по одной субзащёлке на раздел на каждое ядро процессора, при этом основная защёлка становится прокси-перенаправителем, и глобальная синхронизация состояния не требуется для защёлок только для чтения. При этом работнику, который всегда назначается конкретному ЦП, необходимо получить только общий (SH) подзащеп, назначенный локальному планировщику.

Захват совместимых кратковременных блокировок, например общей кратковременной блокировки Superlatch, требует меньше ресурсов и лучше масштабирует доступ к часто используемым страницам, чем неразделённая общая кратковременная блокировка, поскольку устранение необходимости синхронизации глобального состояния значительно повышает производительность за счёт обращения только к локальной памяти NUMA. И наоборот, получение монопольной (EX) суперблокировки дороже, чем приобретение обычной (EX) блокировки, так как SQL должен сигнализировать через все подблокировки. При наблюдении, что Superlatch использует шаблон интенсивного EX доступа, подсистема SQL может понизить его после удаления страницы из буферного пула. На следующей диаграмме показаны обычная защелка и сегментированная Superlatch:

Используйте объект SQL Server:Latches и связанные счетчики в мониторе производительности для сбора сведений о суперзакладках, включая количество суперзакладок, повышение уровня суперзакладки в секунду и понижение суперзакладки в секунду. Дополнительные сведения об объекте SQL Server:Latches и связанных счетчиках см. в разделе SQL Server, объект Latches.

Типы ожидания защёлок

Сведения о совокупном времени ожидания отслеживаются SQL Server, и к ним можно получить доступ с помощью динамического административного представления (DMW) sys.dm_os_wait_stats. SQL Server использует три типа времени ожидания для кратковременной блокировки, определяемых соответствующим параметром wait_type в динамическом административном представлении sys.dm_os_wait_stats:

• Защелка буфера (BUF): используется для обеспечения согласованности страниц индекса и данных пользовательских объектов. Они также используются для защиты доступа к страницам данных, используемым SQL Server для системных объектов. Например, страницы, отвечающие за распределение, защищены буферными защёлками. К ним относятся страницы "Свободное пространство страницы" (PFS), "Глобальная карта выделения" (GAM), "Общая глобальная карта выделения" (SGAM) и страницы карты распределения индексов (IAM). Буферные защелки регистрируются в sys.dm_os_wait_stats с помощью wait_type в PAGELATCH_*.

• Небуферная защелка (Non-BUF): используется для обеспечения согласованности любых структур в памяти, кроме страниц буферного пула. Любые ожидания для не буферных защелок сообщаются как wait_type из LATCH_*.

• Кратковременная блокировка ввода-вывода: подмножество кратковременных блокировок буфера, которые обеспечивают согласованность тех же структур, что защищаются кратковременными блокировками буфера, когда эти структуры требуют загрузки в буферный пул посредством операции ввода-вывода. Защелки ввода-вывода не позволяют другому потоку загрузить ту же страницу в буферный пул с несовместимой защелкой. Связан с wait_type из PAGEIOLATCH_*.

  Примечание.

  Если вы видите значительные PAGEIOLATCH ожидания, это означает, что SQL Server ожидает подсистемы ввода-вывода. Хотя ожидается определенное количество ожиданий PAGEIOLATCH и нормальное поведение, если среднее PAGEIOLATCH время ожидания постоянно превышает 10 миллисекунд (мс), следует изучить, почему подсистема ввода-вывода находится под давлением.

Если при просмотре DMV sys.dm_os_wait_stats вы обнаружите небуферные защелки, следует проверить sys.dm_os_latch_stats, чтобы получить подробную разбивку совокупной информации об ожиданиях для небуферных защелок. Все ожидания блокировок буфера классифицируются в BUFFER классе блокировок, а остальные классифицируются как небуферные блокировки.

Симптомы и причины конкуренции за кратковременные блокировки в SQL Server

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

Пример конфликта защёлок

На следующей схеме синяя линия представляет пропускную способность в SQL Server, измеряемую транзакциями в секунду; черная линия представляет среднее время ожидания кратковременной блокировки страницы. В этом случае каждая транзакция выполняет INSERT в кластеризованный индекс с последовательным увеличением начального значения, например, при заполнении столбца данных типа bigint. По мере увеличения числа ЦП до 32 очевидно, что общая пропускная способность снизилась, и время ожидания блокировки страницы увеличилось до примерно 48 миллисекунд, как свидетельствует черная линия. Такая обратная зависимость между пропускной способностью и временем ожидания защёлки страницы — распространённая ситуация, которую легко диагностировать.

Производительность при устранении конкуренции за latch

Как показано на следующей схеме, SQL Server больше не ограничен ожиданиями защёлок страниц, а пропускная способность возрастает на 300 %, если измерять её в транзакциях в секунду. Это было достигнуто за счет метода использования хэш-секционирования с вычисляемым столбцом, описанного далее в этой статье. Это улучшение производительности используется системами с большим числом ядер и высоким уровнем параллелизма.

Факторы, влияющие на конкуренцию за кратковременные блокировки

Конкуренция за кратковременные блокировки, снижающая производительность в средах OLTP, обычно вызвана высоким уровнем параллелизма, обусловленным одним или несколькими из следующих факторов:

Диагностика конфликтов защелок в SQL Server

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

Средства и методы для диагностики конфликтов кратковременных блокировок

Основные средства, используемые для диагностики конфликтов кратковременных блокировок:

• Счетчик производительности для мониторинга загрузки ЦП и времени ожидания защелок в SQL Server, чтобы установить связь между загрузкой ЦП и временем ожидания защелок.

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

• В некоторых случаях дампы памяти процесса SQL Server необходимо получить и проанализировать с помощью средств отладки Windows.

Технический процесс диагностики конкуренции за latch можно свести к следующим шагам:

1. Определите, что существует спор, который может быть связан с блокировкой.

2. Используйте представления DMV, представленные в приложении: скрипты конкуренции за блокировки SQL Server, чтобы определить тип затронутых блокировок и ресурсов.

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

Признаки конкуренции за защелки

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

1. Измерьте общее время ожидания во время выполнения репрезентативного теста.
2. Упорядочите их по порядку.
3. Определите пропорцию времени ожидания, связанного с блокировками.

Сведения о совокупном времени ожидания доступны в DMV sys.dm_os_wait_stats. Наиболее распространенный тип состязания за удержание блоков — это состязание за защелки буфера, которое проявляется как увеличение времени ожидания для защелок с wait_type из PAGELATCH_*. Не буферные защелки группируются по типу ожидания LATCH*. Как показано на следующей схеме, сначала следует проанализировать совокупные системные ожидания с помощью динамического административного представления sys.dm_os_wait_stats, чтобы определить, какой процент общего времени ожидания вызван буферными и небуферными защёлками. Если вы сталкиваетесь с небуферизованными защелками, необходимо также проверить DMV sys.dm_os_latch_stats.

На следующей схеме показана взаимосвязь между информацией, возвращаемой динамическими административными представлениями sys.dm_os_wait_stats и sys.dm_os_latch_stats DMV.

Дополнительные сведения о DMV sys.dm_os_wait_stats см. в справке по SQL Server sys.dm_os_wait_stats.

Для получения дополнительной информации о sys.dm_os_latch_stats DMV см. sys.dm_os_latch_stats в справке по SQL Server.

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

• Среднее время ожидания защёлки страницы стабильно растёт по мере увеличения пропускной способности: если среднее время ожидания защёлки страницы стабильно растёт по мере увеличения пропускной способности, а среднее время ожидания защёлки буфера также превышает ожидаемое время отклика диска, следует проверить текущие ожидающие задачи с помощью DMV sys.dm_os_waiting_tasks. Средние показатели могут быть вводящими в заблуждение при анализе в изоляции, поэтому важно посмотреть на систему в реальном времени, когда это возможно, чтобы понять характеристики рабочей нагрузки. В частности, проверьте, есть ли высокие ожидания по запросам PAGELATCH_EX и (или) PAGELATCH_SH на любых страницах. Чтобы диагностировать увеличение среднего времени ожидания защёлок страниц при увеличении пропускной способности, выполните следующие действия.

  • Используйте следующие примеры скриптов: Запрос к sys.dm_os_waiting_tasks с сортировкой по идентификатору сеанса или Вычисление ожиданий за период времени, чтобы просмотреть текущие ожидающие задачи и измерить среднее время ожидания защелки.

  • Используйте пример скрипта Запрос дескрипторов буфера для определения объектов, вызывающих конфликты кратковременных блокировок, чтобы определить индекс и базовую таблицу, в которых возникают конфликты.

  • Измеряйте среднее время ожидания защёлки страницы с помощью счётчика Монитор производительности MSSQL%InstanceName%\Wait Statistics\Page Latch Waits\Average Wait Time или с помощью запроса к sys.dm_os_wait_stats DMV.

  Примечание.

  Чтобы вычислить среднее время ожидания для определенного типа ожидания (возвращаемого sys.dm_os_wait_stats как wt_:type), разделите общее время ожидания (возвращаемое как wait_time_ms) на количество ожидающих задач (возвращаемое как waiting_tasks_count).

• Процент общего времени ожидания, затраченного на типы ожидания блокировки во время пиковой нагрузки: если среднее время ожидания блокировки в процентах от общего времени ожидания увеличивается в соответствии с нагрузкой приложения, то конфликты защелки могут повлиять на производительность и следует исследовать.

  Измеряйте ожидания захвата страниц и ожидания нестраничных захватов с помощью счетчиков производительности объекта SQL Server, Wait Statistics. Затем сравните значения этих счетчиков производительности со счетчиками производительности, которые связаны с производительностью ЦП, операций ввода-вывода и памяти, а также с пропускной способностью сети. Например, для оценки использования ресурсов хорошо подходят такие показатели, как количество транзакций и пакетных запросов в секунду.

  Примечание.

  Относительное время ожидания для каждого типа ожидания не включено в sys.dm_os_wait_stats DMV, так как эта DMV измеряет время ожидания с момента последнего запуска экземпляра SQL Server или сброса совокупной статистики ожидания с помощью DBCC SQLPERF. Чтобы вычислить относительное время ожидания для каждого типа ожидания, сделайте моментальный снимок sys.dm_os_wait_stats до пиковой нагрузки, после пиковой нагрузки, а затем вычислите разницу. Для этой цели можно использовать пример скрипта Вычисление ожиданий за период времени.

  Только в непроизводственной среде очистите sys.dm_os_wait_stats DMV с помощью следующей команды:

  DBCC SQLPERF ('sys.dm_os_wait_stats', 'CLEAR');
  

  Для очистки sys.dm_os_latch_stats DMV можно выполнить аналогичную команду:

  DBCC SQLPERF ('sys.dm_os_latch_stats', 'CLEAR');
  

• Пропускная способность не увеличивается, а в некоторых случаях уменьшается, так как нагрузка приложения увеличивается, а количество ЦП, доступных ДЛЯ SQL Server, увеличивается: это было показано в примере latch Contention.

• Использование ЦП не увеличивается по мере увеличения рабочей нагрузки приложения: если загрузка ЦП в системе не увеличивается, несмотря на рост пропускной способности приложения, это является индикатором того, что SQL Server чего-то ожидает, и симптоматикой блокировки защелки.

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

Анализ текущих защелок буфера ожидания

Конкуренция за защелки буфера проявляется в виде увеличения времени ожидания для защелок с wait_type значением PAGELATCH_* или PAGEIOLATCH_*, как показано в динамическом административном представлении sys.dm_os_wait_stats. Чтобы просмотреть систему в режиме реального времени, выполните в этой системе следующий запрос, чтобы объединить динамические административные представления (DMV) sys.dm_os_wait_stats, sys.dm_exec_sessions и sys.dm_exec_requests. Полученные результаты можно использовать, чтобы определить текущий тип ожидания для сеансов, выполняемых на сервере.

SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

Статистика, предоставляемая этим запросом, описана ниже.

Следующий запрос возвращает сведения для всех блокировок, отличных от буфера:

SELECT * FROM sys.dm_os_latch_stats
WHERE latch_class <> 'BUFFER'
ORDER BY wait_time_ms DESC;

Статистика, предоставляемая этим запросом, описана ниже.

Значения, возвращаемые этим DMV, являются накопительными с момента последнего перезапуска ядра СУБД или сброса DMV. Узнать время последнего запуска ядра СУБД можно в столбце sqlserver_start_time из sys.dm_os_sys_info. В системе, которая работает долгое время, это означает, что некоторые статистические данные, такие как max_wait_time_ms редко полезны. Следующую команду можно использовать, чтобы сбросить статистику ожиданий для этого DMV:

DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR);

Сценарии состязания за latch в SQL Server

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

Конфликт при вставке на последней или конечной странице

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

В следующем примере первый и второй потоки пытаются одновременно выполнить вставку записи, которая будет храниться на странице 299. С точки зрения логической блокировки нет проблем, так как используются блокировки на уровне строк, и монопольные блокировки обеих записей на одной странице могут храниться одновременно. Тем не менее, чтобы обеспечить целостность физической памяти, накладывать монопольную блокировку может одновременно только один поток, в связи с чем доступ к странице сериализуется с целью исключить потерю обновлений в памяти. В этом случае поток 1 получает эксклюзивную защелку, а поток 2 ожидает, что регистрируется ожидание PAGELATCH_EX этого ресурса в статистике ожидания. Это отображается с помощью значения wait_type в динамическом административном представлении (DMV) sys.dm_os_waiting_tasks.

Такую конкуренцию обычно называют конкуренцией "Last Page Insert" («вставка в последнюю страницу»), поскольку она возникает на крайнем правом крае B-дерева, как показано на следующей схеме:

Конфликты из-за кратковременных блокировок этого типа можно объяснить следующим образом. При вставке новой строки в индекс SQL Server используется следующий алгоритм для выполнения изменения:

1. Выполните обход B-дерева, чтобы найти нужную страницу для размещения новой записи.

2. Заблокируйте страницу с помощью PAGELATCH_EX, чтобы предотвратить ее изменение другими пользователями, и установите общие блокировки (PAGELATCH_SH) на всех неконечных страницах.

   Примечание.

   В некоторых случаях подсистема SQL требует, чтобы EX защелки были захвачены на внутренних страницах B-дерева. Например, когда происходит разделение страниц, все страницы, непосредственно затронутые, должны быть исключительно защеплены (PAGELATCH_EX).

3. Запишите в журнале, что строка была изменена.

4. На страницу добавляется строка, а сама страница помечается как "грязная".

5. Разблокируйте все страницы.

Если индекс таблицы основан на последовательно увеличивающемся ключе, каждая новая вставка переходит на ту же страницу в конце дерева B- до тех пор, пока эта страница не будет заполнена. В сценариях высокой конкурентности это может привести к возникновению конфликтов на правом краю B-дерева и может происходить на кластеризованных и некластеризованных индексах. Таблицы, затронутые этим типом состязания, в основном принимают INSERT запросы, и страницы для проблемных индексов обычно относительно плотны (например, размер строки ~ 165 байт (включая нагрузку на строку) равен ~49 строкам на страницу). В этом примере с большим количеством вставок ожидается, что произойдут PAGELATCH_EX/PAGELATCH_SH ожидания, и это является типичным наблюдением. Чтобы сравнить ожидания блокировки страницы и ожидания блокировки страницы дерева, используйте sys.dm_db_index_operational_stats DMV.

В следующей таблице обобщены основные факторы, наблюдаемые при этом типе конфликтов защёлки.

Конкуренция за защёлки в небольших таблицах с некластерным индексом и случайными вставками (таблица очереди)

Этот сценарий обычно рассматривается, когда таблица SQL используется в качестве временной очереди (например, в асинхронной системе обмена сообщениями).

В этом сценарии монопольные (EX) и общие (SH) конфликты блокировки могут возникать в следующих условиях:

• Операции вставки, выбора, обновления или удаления выполняются при высокой параллелизме.
• Размер строки относительно мал (что ведет к высокой плотности страниц).
• Число строк в таблице относительно невелико; это приводит к неглубокому B-дереву, то есть с глубиной индекса, равной двум или трём.

Конкуренция за защелки может возникать, даже если доступ распределён случайным образом по всему B-дереву, например, когда столбец с непоследовательными значениями является ведущим ключом в кластеризованном индексе. Следующий снимок экрана взят из системы, в которой наблюдается конфликт за latch этого типа. В этом примере конфликт возникает из-за высокой плотности страниц, обусловленной небольшим размером строк и относительно неглубоким B-деревом. По мере увеличения параллелизма возникает конкуренция за защёлки страниц, хотя вставки распределяются случайным образом по всему B-дереву, поскольку GUID был первым ключевым столбцом индекса.

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

В следующей таблице перечислены основные факторы, которые наблюдались при этом типе конфликтов кратковременной блокировки.

Сочетание B-дерева небольшой глубины и случайных вставок по всему индексу может приводить к разбиению страниц в B-дереве. Чтобы выполнить разделение страниц, SQL Server должен получить разделяемые (SH) блокировки на всех уровнях, а затем получить эксклюзивные (EX) блокировки на страницах в дереве B, которые участвуют в разделении страниц. Кроме того, если параллелизм высок и данные постоянно вставляются и удаляются, может происходить разделение корня B-дерева. В этом случае другим вставкам может потребоваться ждать каких-либо не буферных защелок, приобретенных на B-дереве. Это проявляется в виде большого количества ожиданий на типе блокировки ACCESS_METHODS_HOBT_VIRTUAL_ROOT, наблюдаемом в динамическом административном представлении sys.dm_os_latch_stats.

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

SELECT
    o.name AS [table],
    i.name AS [index],
    indexProperty(object_id(o.name), i.name, 'indexDepth') + indexProperty(object_id(o.name), i.name, 'isClustered') AS depth, --clustered index depth reported doesn't count leaf level
    i.[rows] AS [rows],
    i.origFillFactor AS [fillFactor],
    CASE (indexProperty(object_id(o.name), i.name, 'isClustered'))
        WHEN 1 THEN 'clustered'
        WHEN 0 THEN 'nonclustered'
        ELSE 'statistic'
    END AS type
FROM sysIndexes AS i
     INNER JOIN sysObjects AS o
         ON o.id = i.id
WHERE o.type = 'u'
      AND indexProperty(object_id(o.name), i.name, 'isHypothetical') = 0 --filter out hypothetical indexes
      AND indexProperty(object_id(o.name), i.name, 'isStatistics') = 0 --filter out statistics
ORDER BY o.name;

Конкуренция за защёлки на страницах PFS (свободного пространства страниц)

PFS обозначает свободное место на странице, SQL Server выделяет одну страницу PFS для каждых 8088 страниц (начиная с PageID = 1) в каждом файле базы данных. Каждый байт на странице PFS записывает сведения о том, сколько свободного места находится на странице, если оно выделено или нет, а также хранит ли страница записи призраков. На странице PFS содержатся сведения о страницах, доступных для выделения, если для операции вставки или обновления требуется новая страница. Страница PFS должна обновляться в нескольких случаях, включая распределение или освобождение. Поскольку для защиты страницы PFS требуется использовать защёлку обновления (UP), на страницах PFS может возникать конкуренция за защёлки, если в группе файлов имеется относительно мало файлов данных и большое количество ядер ЦП. Простой способ решения этой проблемы — увеличить число файлов в файловой группе.

Если в PAGELATCH_UP для страниц PFS или SGAM наблюдается много ожиданий tempdb, выполните следующие действия, чтобы устранить это узкое место:

1. Добавьте данные файлы в tempdb, чтобы число файлов данных tempdb было равно количеству ядер процессора на сервере.

2. Включите флаг трассировки SQL Server 1118.

Дополнительные сведения об узких местах выделения, вызванных конфликтами на системных страницах, см. в публикации блога Что такое узкие места выделения?

Функции с табличным значением и состязание за кратковременные блокировки в базе данных tempdb

Существуют и другие факторы, выходящие за рамки состязаний по распределению, которые могут вызвать блокировку на tempdb, например, при интенсивном использовании TVF в запросах.

Обработка конфликтов блокировок для разных шаблонов таблиц

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

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

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

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

Параметр. Использование столбца в таблице для распределения значений по диапазону ключей индекса

Определите в рабочей нагрузке естественное значение, которое можно использовать для распределения вставок по диапазону ключей. Например, рассмотрим сценарий банковского обслуживания банкоматов, где ATM_ID может быть хорошим кандидатом на распределение вставок в таблицу транзакций для снятия средств, так как один клиент может использовать только один банкомат за раз. Аналогичным образом, в системе точек продаж возможно Checkout_ID, или идентификатор магазина будет естественным выбором, который можно использовать для распределения вставок по диапазону ключей. Для этого метода требуется создать составной ключ индекса с ведущим ключевым столбцом либо значением определенного столбца, либо хэш этого значения в сочетании с одним или несколькими дополнительными столбцами, чтобы обеспечить уникальность. В большинстве случаев наилучших результатов можно достичь, используя хэширование значений, поскольку слишком много уникальных значений ухудшают физическую организацию. Например, в системе точек продаж можно создать хэш на основе идентификатора магазина, который представляет собой некоторый остаток от деления на количество ядер ЦП. Эта методика приведет к относительно небольшому числу диапазонов в таблице, однако этого будет достаточно для распространения вставок таким образом, чтобы избежать конфликтов кратковременных блокировок. Этот метод проиллюстрирован на следующем изображении.

Этот шаблон был реализован в лаборатории и разрешил конфликты кратковременных блокировок в системе с 32 физическими ядрами ЦП. Таблица использовалась для хранения конечного баланса в конце транзакции; для каждой бизнес-транзакции в таблицу выполнялась одна операция вставки.

Определение исходной таблицы

При использовании исходного определения таблицы наблюдалось чрезмерное состязание за защёлки на кластеризованном индексе pk_table1:

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (TransactionID, UserID);
GO

Определение переупорядоченного индекса

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

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (UserID, TransactionID);
GO

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

Следующее определение таблицы можно использовать для создания модуля, который соответствует количеству ЦП, HashValue создается с помощью последовательного увеличения значения TransactionID для обеспечения равномерного распределения по дереву B::

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

-- Consider using bulk loading techniques to speed it up
ALTER TABLE table1
    ADD [HashValue] AS (CONVERT (TINYINT, ABS([TransactionID]) % (32))) PERSISTED NOT NULL;

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (HashValue, TransactionID, UserID);
GO

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

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

Используйте хеш-секционирование с вычисляемым столбцом

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

1. Создайте новую файловую группу или используйте существующую файловую группу для хранения секций.

2. При использовании новой файловой группы равномерно распределите отдельные файлы между LUN, выбрав оптимальную схему размещения. Если шаблон доступа включает большое количество вставок, создайте одинаковое число файлов, поскольку на компьютере SQL Server имеются физические ядра ЦП.

3. CREATE PARTITION FUNCTION Используйте команду для секционирования таблиц на разделы X, где X — это количество физических ядер ЦП на компьютере SQL Server. (как минимум до 32 разделов)

   Примечание.

   Выравнивание числа разделов в соотношении 1:1 с числом ядер ЦПУ не всегда необходимо. Во многих случаях это может быть меньше числа ядер ЦП. Наличие дополнительных секций может привести к дополнительным затратам на запросы, которые должны выполнять поиск всех разделов, и в этих случаях меньше секций может помочь. В ходе тестирования SQLCAT в системах с 64 и 128 логическими процессорами на реальных клиентских рабочих нагрузках оказалось, что 32 разделов достаточно для устранения чрезмерной конкуренции за защёлки и достижения целевых показателей масштабируемости. В конечном итоге оптимальное количество секций должно быть определено с помощью тестирования.

4. Используйте команду CREATE PARTITION SCHEME:

   • Привяжите функцию секционирования к файловым группам.
   • Добавьте в таблицу хэш-столбец типа tinyint или smallint.
   • Вычислите хорошее хэш-распределение. Например, используйте HASHBYTES с модулем или BINARY_CHECKSUM.

Следующий пример скрипта можно настроить с учетом вашей реализации:

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](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_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

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

Что дает хеш-секционирование с вычисляемым столбцом

Как показано на схеме ниже, этот метод перемещает состязание с последней страницы путем перестроения индекса для хэш-функции и создания такого же количества секций, что и количество физических ядер ЦП на компьютере SQL Server. Вставки по-прежнему находятся в конце логического диапазона (последовательно увеличивающееся значение), но операция модуля хэш-значения обеспечивает разбиение вставок по разным сбалансированным деревьям, что сокращает количество узких мест. Это показано на приведенных ниже схемах.

Компромиссы при использовании хэш-секционирования

Хотя хэш-секционирование может устранить конкуренцию при вставке, при принятии решения, использовать ли этот подход, следует учитывать несколько компромиссов:

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

  

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

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

• Хеш-секционирование не позволяет использовать секционирование для других функций администрирования, таких как архивирование по скользящему окну и переключение секций.

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

Сводка по методам, используемым для устранения конфликтов кратковременных блокировок

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

Непоследовательный ключ/индекс

Преимущества.

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

Недостатки.

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

Хэш-секционирование с помощью вычисляемого столбца

Преимущества.

• Прозрачный для вставок.

Недостатки.

• Секционирование нельзя использовать для предполагаемых функций управления, таких как архивация данных с помощью параметров переключения секций.
• Может вызывать проблемы с отсечением секций для запросов SELECT/UPDATE по отдельным значениям и диапазонам, а также для запросов, выполняющих JOIN.
• Добавление сохраняемого вычисляемого столбца — это операция, выполняемая в автономном режиме.

Пошаговое руководство. Диагностика конфликтов с блокировкой

В следующем пошаговом руководстве демонстрируются средства и методы, описанные в разделах Диагностика конфликтов кратковременных блокировок SQL Server и Обработка конфликтов кратковременных блокировок для различных шаблонов таблиц для решения проблемы на примере реального использования. В этом сценарии описывается взаимодействие с клиентом для проведения нагрузочного тестирования системы точек продаж, которая имитировала приблизительно 8000 магазинов, выполняющих транзакции в приложении SQL Server, работающем на системе с 8 сокетами, 32 физическими ядрами и 256 ГБ памяти.

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

Симптом: горячие защелки

В этом случае мы наблюдали высокие ожидания для PAGELATCH_EX, где мы типично определяем высокую величину как среднее значение более 1 мс. В этом случае мы постоянно наблюдаем ожидания с превышением 20 мс.

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

Изоляция объекта, вызывающего конфликты с блокировкой

Следующий скрипт использует столбец resource_description для изоляции индекса, вызывающего PAGELATCH_EX спор:

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 AS bd
     INNER JOIN (SELECT *,
             --resource_description
             CHARINDEX(':', resource_description) AS file_index,
             CHARINDEX(':', resource_description, CHARINDEX(':', resource_description) + 1) AS page_index,
             resource_description AS rd
      FROM sys.dm_os_waiting_tasks AS 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 + 1, 1) --wt.page_index)
        AND bd.page_id = SUBSTRING(wt.rd, wt.page_index + 1, LEN(wt.rd))
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = au.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id
ORDER BY wt.wait_duration_ms DESC;

Как показано здесь, конфликт находится в таблице LATCHTEST и индексе CIX_LATCHTEST. Обратите внимание, что имена были изменены для анонимизации рабочей нагрузки.

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

Альтернативный метод изоляции объекта, который вызывает конкуренцию за защёлки

Иногда запрашивать sys.dm_os_buffer_descriptors бывает непрактично. По мере увеличения объёма памяти в системе и памяти, доступной буферному пулу, увеличивается и время, необходимое для выполнения этого DMV. В системе размером 256 ГБ выполнение этого динамического административного представления может потребовать до 10 минут или более. Доступен альтернативный метод, который в общих чертах описан следующим образом и проиллюстрирован на примере другой нагрузки, которую мы запускали в лаборатории:

1. Запросите текущие задачи, находящиеся в ожидании, используя скрипт из приложения Запрос к sys.dm_os_waiting_tasks, упорядоченный по длительности ожидания.

2. Определите ключевую страницу, на которой наблюдается конвой, что происходит, когда несколько потоков конкурируют за одну и ту же страницу. В этом примере потоки, выполняющие вставку, спорят за последнюю страницу в дереве B и ждут, пока не смогут получить блокировку EX. Это указывается посредством resource_description в первом запросе, в нашем случае 8:1:111305.

3. Включите флаг трассировки 3604, который предоставляет дополнительные сведения о странице. Используйте следующий синтаксис, с помощью DBCC PAGE, заменяя значение в скобках на значение, полученное через resource_description.

   Включите флаг трассировки 3604, чтобы включить вывод в консоль.

   DBCC TRACEON (3604);
   

   Изучите сведения о странице:

   DBCC PAGE (8, 1, 111305, -1);
   

4. Изучите выходные данные DBCC. Должен быть связанный объект метаданных, в нашем случае — 78623323.

   

5. Теперь мы можем выполнить следующую команду, чтобы определить имя объекта, вызывающего спор, который, как ожидалось, .LATCHTEST

   Примечание.

   Убедитесь, что вы находитесь в правильном контексте базы данных, в противном случае возвращается NULLзапрос.

   --get object name
   SELECT OBJECT_NAME(78623323);
   

   

Сводка и результаты

Используя методы выше, мы смогли убедиться, что состязание происходило в кластеризованном индексе с последовательно увеличивающимся значением ключа в таблице, где было получено наибольшее число вставок. Этот тип конкуренции не является редкостью для индексов с последовательным увеличением ключевого значения, например datetime, идентификатор или созданного приложением ключа TransactionID.

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

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

Приложение. Альтернативный метод

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

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

Заполняя строки до полной страницы, вы заставляете SQL выделить больше страниц, что делает их более доступными для вставок и уменьшает конфликты с блокировкой страниц EX.

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

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

ALTER TABLE mytable ADD Padding CHAR(5000) NOT NULL DEFAULT ('X');

Этот метод описан для полноты изложения; на практике SQLCAT применяла его только к небольшой таблице из 10 000 строк в рамках одного проекта по оптимизации производительности. Этот метод применяется ограниченно, поскольку увеличивает нагрузку на память SQL Server при работе с большими таблицами и может приводить к конкуренции за небуферные защёлки на нелистовых страницах. Дополнительное давление на память может быть значительным ограничением для применения этого метода. С объемами памяти, доступными на современных серверах, большая часть рабочего набора для рабочих нагрузок OLTP обычно хранится в памяти. Когда набор данных увеличивается до размера, который больше не помещается в память, происходит значительное падение производительности. Таким образом, этот прием применим только к небольшим таблицам. Этот метод не используется SQLCAT для таких сценариев, как конкуренция при вставке на последней или завершающей странице для больших таблиц.

Приложение. Скрипты разрешения блокировок SQL Server

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

Запрос sys.dm_os_waiting_tasks упорядочен по идентификатору сеанса

Следующий пример скрипта запрашивает sys.dm_os_waiting_tasks, возвращает ожидания защелки, упорядоченные по идентификатору сеанса:

-- WAITING TASKS ordered by session_id
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY session_id;

Запрос sys.dm_os_waiting_tasks упорядочен по длительности ожидания

Следующий скрипт выполняет запрос sys.dm_os_waiting_tasks, и возвращает ожидания защиты, упорядоченные по длительности.

-- WAITING TASKS ordered by wait_duration_ms
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

Вычисление ожиданий за период времени

Следующий скрипт вычисляет и возвращает время ожидания кратковременных блокировок за определённый период времени.

/* Snapshot the current wait stats and store so that this can be compared over a time period
   Return the statistics between this point in time and the last collection point in time.

   **This data is maintained in tempdb so the connection must persist between each execution**
   **alternatively this could be modified to use a persisted table in tempdb.  if that
   is changed code should be included to clean up the table at some point.**
*/
USE tempdb;
GO

DECLARE @current_snap_time AS DATETIME;
DECLARE @previous_snap_time AS DATETIME;

SET @current_snap_time = GETDATE();

IF NOT EXISTS (SELECT name
               FROM tempdb.sys.sysobjects
               WHERE name LIKE '#_wait_stats%')
    CREATE TABLE #_wait_stats
    (
        wait_type VARCHAR (128),
        waiting_tasks_count BIGINT,
        wait_time_ms BIGINT,
        avg_wait_time_ms INT,
        max_wait_time_ms BIGINT,
        signal_wait_time_ms BIGINT,
        avg_signal_wait_time INT,
        snap_time DATETIME
    );

INSERT INTO #_wait_stats (wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms, snap_time)
SELECT wait_type,
       waiting_tasks_count,
       wait_time_ms,
       max_wait_time_ms,
       signal_wait_time_ms,
       getdate()
FROM sys.dm_os_wait_stats;

--get the previous collection point
SELECT TOP 1 @previous_snap_time = snap_time
FROM #_wait_stats
WHERE snap_time < (SELECT MAX(snap_time)
                   FROM #_wait_stats)
ORDER BY snap_time DESC;

--get delta in the wait stats
SELECT TOP 10 s.wait_type,
              (e.waiting_tasks_count - s.waiting_tasks_count) AS [waiting_tasks_count],
              (e.wait_time_ms - s.wait_time_ms) AS [wait_time_ms],
              (e.wait_time_ms - s.wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_wait_time_ms],
              (e.max_wait_time_ms) AS [max_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) AS [signal_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_signal_time_ms],
              s.snap_time AS [start_time],
              e.snap_time AS [end_time],
              DATEDIFF(ss, s.snap_time, e.snap_time) AS [seconds_in_sample]
FROM #_wait_stats AS e
     INNER JOIN (SELECT *
      FROM #_wait_stats
      WHERE snap_time = @previous_snap_time) AS s
     ON (s.wait_type = e.wait_type)
WHERE e.snap_time = @current_snap_time
      AND s.snap_time = @previous_snap_time
      AND e.wait_time_ms > 0
      AND (e.waiting_tasks_count - s.waiting_tasks_count) > 0
      AND e.wait_type NOT IN ('LAZYWRITER_SLEEP', 'SQLTRACE_BUFFER_FLUSH', 'SOS_SCHEDULER_YIELD',
                              'DBMIRRORING_CMD', 'BROKER_TASK_STOP', 'CLR_AUTO_EVENT',
                              'BROKER_RECEIVE_WAITFOR', 'WAITFOR', 'SLEEP_TASK',
                              'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                              'FT_IFTS_SCHEDULER_IDLE_WAIT', 'BROKER_TO_FLUSH',
                              'XE_DISPATCHER_WAIT', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
ORDER BY (e.wait_time_ms - s.wait_time_ms) DESC;

--clean up table
DELETE FROM #_wait_stats
WHERE snap_time = @previous_snap_time;

Дескрипторы буфера запросов для определения объектов, вызывающих несоответствие блокировок

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

IF EXISTS (SELECT *
           FROM tempdb.sys.objects
           WHERE [name] LIKE '#WaitResources%')
    DROP TABLE #WaitResources;

CREATE TABLE #WaitResources
(
    session_id INT,
    wait_type NVARCHAR (1000),
    wait_duration_ms INT,
    resource_description sysname NULL,
    db_name NVARCHAR (1000),
    schema_name NVARCHAR (1000),
    object_name NVARCHAR (1000),
    index_name NVARCHAR (1000)
);
GO

DECLARE @WaitDelay AS VARCHAR (16), @Counter AS INT, @MaxCount AS INT, @Counter2 AS INT;
SELECT @Counter = 0, @MaxCount = 600, @WaitDelay = '00:00:00.100'; -- 600x.1=60 seconds

SET NOCOUNT ON;

WHILE @Counter < @MaxCount
BEGIN
   INSERT INTO #WaitResources (session_id, wait_type, wait_duration_ms, resource_description)--, db_name, schema_name, object_name, index_name)
   SELECT wt.session_id,
         wt.wait_type,
         wt.wait_duration_ms,
         wt.resource_description
   FROM sys.dm_os_waiting_tasks AS wt
   WHERE wt.wait_type LIKE 'PAGELATCH%'
         AND wt.session_id <> @@SPID;

   -- SELECT * FROM sys.dm_os_buffer_descriptors;

   SET @Counter = @Counter + 1;
   WAITFOR DELAY @WaitDelay;
END

--SELECT * FROM #WaitResources;

UPDATE #WaitResources
    SET db_name = DB_NAME(bd.database_id),
        schema_name = s.name,
        object_name = o.name,
        index_name = i.name
FROM #WaitResources AS wt
     INNER JOIN sys.dm_os_buffer_descriptors AS bd
         ON bd.database_id = SUBSTRING(wt.resource_description, 0, CHARINDEX(':', wt.resource_description))
        AND bd.file_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description) + 1, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) - CHARINDEX(':', wt.resource_description) - 1)
        AND bd.page_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) + 1, LEN(wt.resource_description) + 1)
        -- AND wt.file_index > 0 AND wt.page_index > 0
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = AU.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id;

SELECT * FROM #WaitResources
ORDER BY wait_duration_ms DESC;
GO
/*
--Other views of the same information
SELECT wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY wait_type, db_name, schema_name, object_name, index_name;
SELECT session_id, wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY session_id, wait_type, db_name, schema_name, object_name, index_name;
*/

--SELECT * FROM #WaitResources
--DROP TABLE #WaitResources;

Скрипт для хэш-секционирования

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

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](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_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

Связанный контент

• Средства мониторинга производительности и настройки