Диагностика и устранение конфликтов кратковременных блокировок на SQL Server
В этом руководство описано, как обнаружить и устранить проблемы, связанные с конфликтами кратковременных блокировок, которые возникают при работе приложений SQL Server в системах с высоким уровнем параллелизма с некоторыми рабочими нагрузками.
С увеличением количества ядер ЦП на серверах возрастает степень параллелизма, что может привести к появлению точек конфликтов в структурах данных, доступ к которым в ядре СУБД должен осуществляться последовательным образом. Это в особенности справедливо для рабочих нагрузок с обработкой транзакций с высокой пропускной способностью и высокой степенью параллелизма (OLTP). Существует целый ряд средств, приемов и методов для решения этих проблем, а также рекомендации по разработке приложений, при соблюдении которых эти проблемы можно исключить. В этой статье обсуждаются определенные типы конфликтов в структурах данных, для сериализации доступа к которым используются спин-блокировки.
Заметка
Эта статья подготовлена группой консультантов по Microsoft SQL Server (SQLCAT) на основе принятого процесса определения и устранения проблем, связанных с конфликтами кратковременных блокировок страниц в приложениях SQL Server в системах с высоким уровнем параллелизма. Рекомендации и лучшие методики, описанные здесь, основаны на практическом опыте разработки и развертывания реальных систем OLTP.
Что такое конфликт кратковременных блокировок в SQL Server?
Кратковременные блокировки — это упрощенные примитивы синхронизации, используемые ядром SQL Server для обеспечения согласованности структур в памяти, включая следующие: индекс, страницы данных и внутренние структуры, такие как неконечные страницы в сбалансированном дереве. 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 и обеспечивают согласованность памяти, тогда как блокировки используются в SQL Server для обеспечения логической согласованности транзакций. В таблице ниже сравниваются кратковременные блокировки и блокировки:
| Структура | Назначение | Управление | Влияние на производительность | Предоставляется |
|---|---|---|---|---|
| Кратковременная блокировка | Обеспечивает согласованность структур в памяти. | Только ядро SQL Server. | Слабо влияет на производительность. Чтобы обеспечить максимальную степень параллелизма и производительность, кратковременные блокировки буфера накладываются только на время физической операции со структурой в памяти, в отличие от блокировок, которые действуют в ходе выполнения логической транзакции. | sys.dm_os_wait_stats (Transact-SQL) — предоставляет сведения о типах ожидания PAGELATCH, PAGEIOLATCH и LATCH (LATCH_EX, LATCH_SH используются для группировки всех ожиданий небуферных кратковременных блокировок). sys.dm_os_latch_stats (Transact-SQL) — предоставляет сведения об ожиданиях небуферных кратковременных блокировок. sys.dm_db_index_operational_stats (Transact-SQL) — это динамическое административное представление предоставляет агрегированные ожидания для каждого индекса, что позволяет устранять проблемы с производительностью, связанные с кратковременными блокировками. |
| Заблокировать | Гарантирует согласованность транзакций. | Может управляться пользователем. | В связи с тем, что блокировки удерживаются во время выполнения транзакции, их влияние на производительность выше по сравнению с кратковременными блокировками. | sys.dm_tran_locks (Transact-SQL). sys.dm_exec_sessions (Transact-SQL). |
Режимы кратковременных блокировок 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) по мере высвобождения запросов на кратковременную блокировку. Соблюдение этого принципа обработки позволяет обеспечить равноправие потоков и исключить их нехватку.
В следующей таблице приводятся сведения о совместимости режимов кратковременной блокировки (Да указывает на совместимость, а Нет — на несовместимость):
| Режим кратковременной блокировки | КП | SH | UP | EX | DT |
|---|---|---|---|---|---|
| КП | Y | Y | Y | Y | N |
| SH | Y | Y | Y | N | N |
| UP | Y | Y | N | N | N |
| EX | Y | N | N | N | N |
| DT | N | N | N | N | N |
Кратковременные блокировки SQL Server и superLatch и sublatch
С ростом популярности систем с несколькими сокетами/ядрами на базе NUMA в SQL Server 2005 были предоставлены кратковременные блокировки superLatches, известные также как sublatch, которые применяются только в системах с 32 или более логическими процессорами. Кратковременные блокировки superLatch улучшают эффективность ядра SQL для определенных шаблонов использования в высокопараллельных рабочих нагрузках OLTP. Например, если некоторые страницы имеют шаблон с большим доступом только для чтения (SH), но записываются довольно редко. Примером страницы с такой схемой доступа является корневая страница сбалансированного дерева (т. е. индекс). Ядро SQL требует, чтобы общая кратковременная блокировка хранилась на корневой странице, когда разбиение на страницы происходит на любом уровне сбалансированного дерева. В рабочей нагрузке OLTP с большим количеством операций вставки и высокой степенью параллелизма число разбиений страниц будет увеличиваться в общем объеме с пропускной способностью, что может привести к снижению производительности. Кратковременные блокировки могут обеспечить повышенную производительность при доступе к общим страницам, где несколько параллельно работающих рабочих потоков нуждаются в кратковременных блокировках SH. Для этого подсистема SQL Server будет динамически повышать уровень кратковременной блокировки на такой странице до кратковременной блокировки superLatch. Кратковременная блокировка superLatch секционирует одну кратковременную блокировку на массив структур кратковременной блокировки sublatch, по одной кратковременной блокировке sublatch на ядро ЦП, при этом основная кратковременная блокировка становится промежуточным перенаправителем, а для кратковременных блокировок только для чтения не требуется глобальная синхронизация состояний. При этом рабочая роль, которая всегда назначается определенному ЦП, должна получить только общую кратковременную блокировку sublatch (SH), назначенную локальному планировщику.
Заметка
В документации по SQL Server термин "сбалансированное дерево" обычно используется в отношении индексов. В индексах rowstore SQL Server реализует B+-дерево. Это не относится к индексам columnstore или хранилищам данных в памяти. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.
Для получения совместимых кратковременных блокировок, таких как общая кратковременная блокировка superLatch, требуется меньше ресурсов, а доступ к активным страницам при этом масштабируется лучше, чем при использовании несекционированной общей кратковременной блокировки, поскольку отсутствие требования синхронизировать глобальные состояния значительно повышает производительность за счет доступа только к локальной памяти NUMA. И наоборот, получение монопольной кратковременной блокировки superLatch (EX) является более дорогостоящим, чем получение обычной кратковременной блокировки EX, так как SQL должен давать сигнал для всех кратковременных блокировок sublatch. Когда для кратковременной блокировки superLatch используется шаблон с большим доступом к EX, ядро SQL может понизить его после удаления страницы из буферного пула. На следующей диаграмме показана нормальная кратковременная блокировка и секционированная кратковременная блокировка superLatch:
Используйте объект SQL Server:Latches и связанные с ними счетчики в мониторе производительности для сбора сведений о кратковременных блокировках superLatch, включая количество кратковременных блокировок superLatch, количество повышений и понижений уровня кратковременной блокировки superLatch в секунду. Дополнительные сведения об объекте 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_statswait_typePAGELATCH_*.Кратковременная блокировка без буферизации (не BUF). Используется для обеспечения согласованности любых структур в памяти, отличных от страниц буферного пула. Все ожидания не буферных блокировок будут сообщаться как
wait_typeLATCH_*.Кратковременная блокировка ввода-вывода. Подмножество кратковременных блокировок буфера, которые гарантируют согласованность тех же структур, которые защищаются кратковременными блокировками буфера, когда эти структуры нуждаются в загрузке в буферный пул с операцией ввода/вывода. Кратковременные блокировки ввода-вывода не позволяют другому потоку загружать одну и ту же страницу в буферный пул с несовместимой кратковременной блокировкой. Связан с
wait_typePAGEIOLATCH_*.Заметка
Если вы видите значительные ожидания PAGEIOLATCH, это означает, что SQL Server ожидает подсистему ввода-вывода. Несмотря на то, что ожидается некоторое количество ожиданий PAGEIOLATCH и нормальное поведение, если среднее время ожидания PAGEIOLATCH превышает 10 миллисекунд, следует выяснить, почему подсистема ввода-вывода недостаточно нагружена.
Если при просмотре динамического административного представления sys.dm_os_wait_stats вы обнаружите кратковременные блокировки без буфера, необходимо проверить sys.dm_os_latch_stats, чтобы получить подробные сведения о совокупном ожидании для таких блокировок. Все ожидания кратковременной блокировки буфера классифицируются в классе кратковременной блокировки буфера, остальные используются для классификации кратковременных блокировок, не относящихся к буферу.
Симптомы и причины конфликтов кратковременных блокировок SQL Server
В системе с высокой степенью параллелизма обычно наблюдается активное состязание для структур, к которым часто обращаются и которые защищены кратковременными блокировками и другими механизмами управления в SQL Server. Считается проблематичным, когда интенсивности и времени ожидания, связанных с получением кратковременной блокировки страницы, достаточно для уменьшения использования ресурсов (ЦП), что снижает пропускную способность.
Пример конфликтов кратковременных блокировок
На следующей схеме синяя линия представляет пропускную способность в SQL Server, измеряемую транзакциями в секунду; черная линия представляет среднее время ожидания кратковременной блокировки страницы. В этом случае каждая транзакция выполняет операцию вставки INSERT в кластеризованный индекс с последовательно увеличивающимся начальным значением, например при заполнении столбца IDENTITY типа данных bigint. По мере увеличения количества процессоров до 32 становится очевидно, что общая пропускная способность уменьшилась, а время ожидания кратковременной блокировки страницы увеличилось примерно на 48 миллисекунд, на что указывает черная линия. Такое обратное отношение между пропускной способностью и временем ожидания кратковременной блокировки страницы является распространенным сценарием, который легко диагностировать.
Производительность при разрешении конфликтов кратковременных блокировок
Как показано на следующей схеме, SQL Server больше не прослушивается в ожидании кратковременных блокировок страниц, а пропускная способность увеличивается на 300 %, измеряемую транзакциями в секунду. Это было достигнуто за счет метода использования хэш-секционирования с вычисляемым столбцом, описанного далее в этой статье. Это улучшение производительности используется системами с большим числом ядер и высоким уровнем параллелизма.
Факторы, влияющие на конфликты кратковременных блокировок
Конфликты кратковременных блокировок, которые негативно сказываются на производительности в средах OLTP, обычно вызываются высокой степенью параллелизма, связанной с одним или несколькими из следующих факторов.
| Множитель | Сведения |
|---|---|
| Большое число логических процессоров, используемых SQL Server | Конфликты кратковременных блокировок могут возникать в любой многоядерной системе. В SQLCAT слишком много конфликтов кратковременных блокировок, что влияет на производительность приложений, не превышающих допустимые уровни, чаще всего наблюдалось в системах с процессорами с 16 и более ядрами и может увеличиваться по мере доступности дополнительных ядер. |
| Конструкция схемы и шаблоны доступа | Глубина сбалансированного дерева, кластеризованного и некластеризованного индекса, размер и плотность строк на страницу, а также шаблоны доступа (операции чтения/записи и удаления) — все это факторы, которые могут повысить конкуренцию за кратковременные блокировки страниц. |
| Высокая степень параллелизма на уровне приложения | Чрезмерное состязание за кратковременные блокировки страниц обычно происходит вместе с высоким уровнем параллельных запросов от уровня приложения. Существуют определенные методики программирования, которые также могут познакомиться с большим количеством запросов к определенной странице. |
| Расположение логических файлов, используемых базами данных SQL Server | Логическая структура файлов может повлиять на уровень конфликтов кратковременных блокировок страниц, вызванный такими структурами распределения, как свободное место на странице (PFS), страницы глобальной карты распределения (GAM), страницы общей глобальной карты распределения (SGAM) и страницы карты распределения индекса (IAM). Дополнительные сведения см. в разделе "Мониторинг и устранение неполадок TempDB: узкие места выделения". |
| Производительность подсистемы ввода-вывода | Значительные ожидания PAGEIOLATCH означают, что SQL Server ожидает подсистему ввода-вывода. |
Диагностика конфликтов кратковременных блокировок в SQL Server
В этом разделе содержатся сведения о диагностике кратковременных блокировок SQL Server, помогающие определить, подвержена ли проблемам ваша среда.
Средства и методы для диагностики конфликтов кратковременных блокировок
Основные средства, используемые для диагностики конфликтов кратковременных блокировок:
Монитор производительности для мониторинга загрузки ЦП и времени ожидания в SQL Server и определения того, существует ли связь между временем использования ЦП и кратковременной блокировкой.
Динамические административные представления SQL Server, которые можно использовать для определения конкретного типа кратковременной блокировки, вызывающей проблемы и влияющей на ресурс.
В некоторых случаях дампы памяти процесса SQL Server необходимо получить и проанализировать с помощью средств отладки Windows.
Заметка
Этот уровень расширенного устранения неполадок обычно требуется только при устранении конфликтов кратковременных блокировок, не связанных с буфером. Вы можете обратиться в службу технической поддержки Майкрософт за помощью при устранении неполадок этого расширенного типа.
Технический процесс диагностики конфликтов кратковременных блокировок можно получить, выполнив следующие действия.
Убедитесь, что существуют конфликты, которые могут быть связаны с кратковременными блокировками.
Используйте представления dmV, предоставляемые в приложении: скрипты Latch Contention SQL Server, чтобы определить тип затронутых блокировок и ресурсов.
Обойти конфликт можно с помощью одного из методов, описанных в разделе Обработка конфликтов кратковременных блокировок для различных табличных шаблонов.
Индикаторы конфликтов кратковременных блокировок
Как уже отмечалось ранее, конфликты кратковременных блокировок возникают только в том случае, если конфликт и время ожидания, связанные с получением кратковременных блокировок страниц, не допускают увеличения пропускной способности, когда доступны ресурсы ЦП. Для определения приемлемого объема конфликтов требуется целостный подход, который учитывает требования к производительности и пропускной способности вместе с доступными ресурсами ввода-вывода и ЦП. В этом разделе описано, как определить влияние конфликтов кратковременных блокировок на рабочую нагрузку.
Измерьте общее время ожидания во время выполнения репрезентативного теста.
Упорядочите их по порядку.
Определите долю объектов, связанных с кратковременными блокировками.
Совокупная информация о ожидании доступна из динамического административного sys.dm_os_wait_stats представления. Наиболее распространенным типом заполнения защелки является конфликты блокировки буфера, наблюдаемое как увеличение времени ожидания при блокировке с wait_typePAGELATCH_*. Не буферные блоки группируются под типом ожидания LATCH* . Как показано на следующем рисунке, сначала нужно оценить системные ожидания в целом, используя динамическое административное представление sys.dm_os_wait_stats, и определить процент совокупного времени ожидания, вызванного кратковременными блокировками с буфером или без. При обнаружении не буферных закладок sys.dm_os_latch_stats необходимо также проверить динамическое административное представление.
На следующей схеме описывается связь между сведениями, возвращаемыми динамическими административными представлениями sys.dm_os_wait_statssys.dm_os_latch_stats .
Дополнительные сведения о динамическом административном представлении sys.dm_os_wait_stats см. в справке по SQL Server sys.dm_os_wait_stats (Transact-SQL ).
Дополнительные сведения о динамическом административном представлении sys.dm_os_latch_stats см. в справке по SQL Server sys.dm_os_latch_stats (Transact-SQL ).
Следующие показатели времени ожидания кратковременных блокировок свидетельствуют о слишком большом количестве кратковременных блокировок, которые влияют на производительность приложения:
Среднее время ожидания блокировки страницы постоянно увеличивается с пропускной способностью: если среднее время ожидания блокировки страницы постоянно увеличивается с пропускной способностью и если среднее время ожидания буфера также увеличивается выше ожидаемого времени отклика на диск, следует изучить текущие задачи ожидания с помощью
sys.dm_os_waiting_tasksdmV. Анализ средних показателей по отдельности может дать неверные результаты, поэтому для правильного понимания характеристик рабочей нагрузки необходимо по возможности рассматривать систему в реальном времени. В частности, следует проверить, не является ли время ожидания для запросов PAGELATCH_EX и (или) PAGELATCH_SH на любых страницах слишком большим. Чтобы провести диагностику в случае роста среднего времени ожидания для кратковременных блокировок страниц с увеличением пропускной способности, выполните следующие действия.- Используйте примеры скриптов Запрос к sys.dm_os_waiting_tasks с сортировкой по идентификатору сеанса или Вычисление ожиданий за период времени, чтобы проанализировать текущие ожидающие задачи и оценить среднее время ожидания для кратковременных блокировок.
- Используйте пример скрипта Запрос дескрипторов буфера для определения объектов, вызывающих конфликты кратковременных блокировок, чтобы определить индекс и базовую таблицу, в которых возникают конфликты.
- Измерение среднего времени ожидания блокировки страницы с помощью счетчика монитора производительности MSSQL%InstanceName%\Wait Statistics\Page Latch Waits\Average Waits или by running the
sys.dm_os_wait_statsDMV.
Заметка
Чтобы вычислить среднее время ожидания для определенного типа ожидания (возвращаемого
sys.dm_os_wait_statsкак wt_:type), разделите общее время ожидания (возвращаемое какwait_time_ms) на количество задач ожидания (возвращено какwaiting_tasks_count).Процент общего времени ожидания, потраченного на типы ожидания блокировки во время пиковой нагрузки: если среднее время ожидания блокировки в процентах от общей нагрузки ожидания увеличивается в соответствии с нагрузкой приложения, то конфликты защелки могут повлиять на производительность и следует исследовать.
Чтобы измерить время ожидания для страничных и нестраничных кратковременных блокировок, используйте счетчики производительности объекта SQLServer:Wait Statistics. Затем сравните значения этих счетчиков производительности со счетчиками производительности, которые связаны с производительностью ЦП, операций ввода-вывода и памяти, а также с пропускной способностью сети. Например, для оценки использования ресурсов хорошо подходят такие показатели, как количество транзакций и пакетных запросов в секунду.
Заметка
Относительное время ожидания для каждого типа ожидания не включается в
sys.dm_os_wait_statsdmV, так как этот dmW измеряет время ожидания с момента последнего запуска экземпляра SQL Server или совокупную статистику ожидания сбросили с помощью DBCC SQLPERF. Чтобы вычислить относительное время ожидания для каждого типа ожидания, сделайте моментальный снимокsys.dm_os_wait_statsдо пиковой нагрузки, после пиковой нагрузки, а затем вычислите разницу. Для этой цели можно использовать пример скрипта Вычисление ожиданий за период времени.Для непроизводственных сред снимите
sys.dm_os_wait_statsdmV с помощью следующей команды:dbcc SQLPERF ('sys.dm_os_wait_stats', 'CLEAR')Для очистки динамического административного
sys.dm_os_latch_statsпредставления можно выполнить аналогичную команду:dbcc SQLPERF ('sys.dm_os_latch_stats', 'CLEAR')Пропускная способность не увеличивается, а в некоторых случаях уменьшается, так как нагрузка приложения увеличивается, а количество ЦП, доступных для SQL Server, увеличивается: это было показано в примере latch Contention.
Использование ЦП не увеличивается по мере увеличения рабочей нагрузки приложения: если загрузка ЦП в системе не увеличивается, так как увеличение пропускной способности приложения увеличивается, это показатель того, что SQL Server ожидает что-то и симптоматическое несоответствие блокировки.
В таких случаях необходимо проанализировать возможные основные причины. Даже если выполняется каждое из вышеперечисленных условий, возможно, основная причина проблем с производительностью кроется в другом месте. В действительности в большинстве случаев неоптимальная загрузка ЦП вызвана другими типами ожиданий, например связанными с блокировкой, операциями ввода-вывода или проблемами сети. Как правило, прежде чем приступать к углубленному анализу, во всех случаях рекомендуется устранять ожидание для ресурса, на долю которого приходится большая часть от общего времени ожидания.
Анализ текущих кратковременных блокировок буфера ожидания
Манифесты блокировки буфера в виде увеличения времени ожидания для блокировок с wait_typePAGELATCH_* или PAGEIOLATCH_*, как показано в динамическом административном представлении sys.dm_os_wait_stats . Чтобы просмотреть систему в режиме реального времени, выполните следующий запрос в системе для присоединения к динамическим административным представлениям и sys.dm_exec_requests динамическим sys.dm_os_wait_statssys.dm_exec_sessions представлениям. Полученные результаты можно использовать, чтобы определить текущий тип ожидания для сеансов, выполняемых на сервере.
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 wt
JOIN sys.dm_exec_sessions es ON wt.session_id = es.session_id
JOIN sys.dm_exec_requests 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
Статистика, предоставляемая этим запросом, описана ниже.
| Статистика | Description |
|---|---|
| Session_id | Идентификатор сеанса, связанного с этой задачей. |
| Wait_type | Тип ожидания, которое записано в ядре SQL Server и препятствует выполнению текущего запроса. |
| Last_wait_type | Если запрос был блокирован ранее, в столбце содержится тип последнего ожидания. Не допускает значение NULL. |
| Wait_duration_ms | Общее время ожидания в миллисекундах, связанное с ожиданием этого типа, с момента запуска экземпляра SQL Server или сброса совокупной статистики ожидания. |
| Blocking_session_id | Идентификатор сеанса, блокирующего данный запрос. |
| Blocking_exec_context_id | Идентификатор контекста выполнения, связанного с этой задачей. |
| Resource_description | В столбце resource_description приведена конкретная ожидаемая страница в следующем формате: <database_id>:<file_id>:<page_id>. |
Следующий запрос возвращает сведения обо всех небуферных кратковременных блокировках:
select * from sys.dm_os_latch_stats where latch_class <> 'BUFFER' order by wait_time_ms desc;
Статистика, предоставляемая этим запросом, описана ниже.
| Статистика | Description |
|---|---|
| latch_class | Тип кратковременной блокировки, которая записана в ядре SQL Server и препятствует выполнению текущего запроса. |
| waiting_requests_count | Количество ожиданий кратковременных блокировок этого класса с момента перезапуска SQL Server. Этот счетчик увеличивается в начале ожидания кратковременной блокировки. |
| wait_time_ms | Общее время ожидания в миллисекундах, связанное с ожиданием для этого типа кратковременной блокировки. |
| max_wait_time_ms | Максимальное время в миллисекундах, связанное с ожиданием для всех запросов для этого типа кратковременной блокировки. |
Заметка
Это динамическое административное представление возвращает значения, которые накапливаются с момента последнего сброса представления или перезапуска ядра СУБД. Узнать время последнего запуска ядра СУБД можно в столбце sqlserver_start_time из sys.dm_os_sys_info. В системе, которая работает долгое время, это означает, что некоторые статистические данные, такие как max_wait_time_ms редко полезны. Чтобы сбросить статистику ожидания для этого динамического административного представления, можно использовать следующую команду:
DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR);
Сценарии конфликтов кратковременных блокировок в SQL Server
В следующих сценариях наблюдалось чрезмерное количество конфликтов кратковременных блокировок.
Конфликты вставки последней страницы
В OLTP часто создается кластеризованный индекс для столбца идентификаторов или дат. Благодаря этому поддерживается эффективная физическая организация индекса, что позволяет значительно повысить производительность связанных с ним операций чтения и записи. Тем не менее такая схема может привести к возникновению непредвиденных конфликтов кратковременных блокировок. Чаще всего эта проблема возникает при работе с большой таблицей с небольшими строками, когда выполняется вставка в индекс, содержащий последовательно увеличивающийся ключевой столбец, например возрастающий ключ, который задается целым числом или значением даты и времени. В этом сценарии в приложении крайне редко, за исключением операций архивирования, выполняются операции обновления или удаления.
В следующем примере первый и второй потоки пытаются одновременно выполнить вставку записи, которая будет храниться на странице 299. С точки зрения логической блокировки проблем не возникает, поскольку будут использоваться блокировки на уровне строк и монопольные блокировки для обеих записей на одной странице могут храниться одновременно. Тем не менее, чтобы обеспечить целостность физической памяти, накладывать монопольную блокировку может одновременно только один поток, в связи с чем доступ к странице сериализуется с целью исключить потерю обновлений в памяти. В этом случае поток 1 получает монопольную кратковременную блокировку, а поток 2 находится в состоянии ожидания, в связи с чем в статистике ожидания для этого ресурса регистрируется ожидание PAGELATCH_EX. Это отображается через wait_type значение в динамическом административном представлении sys.dm_os_waiting_tasks .
Такую ситуацию часто называют конфликтом вставки последней страницы, поскольку он возникает на крайней правой границе сбалансированного дерева, как показано на следующем рисунке:
Возникновение конфликтов кратковременных блокировок этого типа можно объяснить следующим образом. При вставке новой строки в индекс SQL Server будет использовать следующий алгоритм для выполнения изменения:
Выполняется просмотр сбалансированного дерева, чтобы найти нужную страницу для размещения новой записи.
На страницу накладывается кратковременная блокировка PAGELATCH_EX, запрещающая ее изменение, а на все неконечные страницы накладываются общие блокировки (PAGELATCH_SH).
Заметка
В некоторых случаях ядру SQL также требуется наложить кратковременные блокировки EX на неконечные страницы сбалансированного дерева. Например, при разбиении на страницы на все напрямую затрагиваемые страницы необходимо накладывать монопольную кратковременную блокировку (PAGELATCH_EX).
В журнал заносится запись об изменении страницы.
На страницу добавляется строка, а сама страница помечается как "грязная".
Со всех страниц снимаются кратковременные блокировки.
Если индекс таблицы построен на основе последовательно возрастающего ключа, каждая новая операция вставки будет выполняться применительно к одной и той же странице в конце сбалансированного дерева, пока эта страница не будет заполнена. В сценариях с высокой степенью параллелизма это может вызвать конфликты на крайней правой границе сбалансированного дерева в кластеризованных и некластеризованных индексах. Таблицы, затронутые этим типом состязания, в основном принимают INSERTs, и страницы для проблемных индексов обычно относительно плотны (например, размер строки ~ 165 байт (включая нагрузку на строки) равен ~49 строкам на страницу). В этом примере с большим количеством вставок ожидается, что будут ожидания PAGELATCH_EX/PAGELATCH_SH, и это обычное наблюдение. Чтобы проверить ожидание блокировки страницы и ожиданий sys.dm_db_index_operational_stats блокировки на странице дерева, используйте dmV.
В следующей таблице перечислены основные факторы, которые наблюдались при этом типе конфликтов кратковременной блокировки.
| Множитель | Типичные наблюдения |
|---|---|
| Логические процессоры, используемые SQL Server | Такое состязание за кратковременные блокировки происходит в основном на системах с 16-ядерными и более процессорами, а чаще всего на системах с 32-ядерными и более процессорами. |
| Конструкция схемы и шаблоны доступа | Используется последовательно увеличивающееся значение идентификатора в качестве первого столбца в индексе в таблице для транзакционных данных. Индекс имеет увеличивающийся первичный ключ с большим количеством вставок. Индекс имеет по крайней мере одно последовательное увеличение значения столбца. Обычно размер небольшой строки с большим количеством строк на страницу. |
| Наблюдаемый тип ожидания | Множество потоков состязаются за один и тот же ресурс и имеют эксклюзивное (EX) или общее (SH) время ожидания для кратковременной блокировки, связанное с одним и тем же resource_description в динамическом административном представлении sys.dm_os_waiting_tasks. Время ожидания возвращается по Запросу sys.dm_os_waiting_tasks, упорядоченному по длительности ожидания. |
| Конструктивные факторы, которые следует учитывать | Рассмотрите возможность изменения порядка столбцов индекса, как описано в стратегии устранения непоследовательных последовательностей, если можно гарантировать, что вставки будут распределяться по сбалансированному дереву по всему времени. Если используется стратегия защиты хэш-секций, она исключает возможность использования секционирования для любых других целей, таких как архивирование скользящих окон. Использование стратегии по устранению рисков хэш-секционирования может привести к проблемам с исключением секций для запросов SELECT, используемых приложением. |
Состязание за кратковременные блокировки в небольших таблицах с некластеризованным индексом и случайными вставками (таблица очередей)
Этот сценарий обычно возникает, когда таблица SQL используется в качестве временной очереди (например, в асинхронной системе обмена сообщениями).
В этом сценарии состязание за кратковременные блокировки между монопольными (EX) и общими (SH) кратковременными блокировками могут возникать при следующих условиях.
- Операции вставки, выбора, обновления или удаления выполняются при высоком уровне параллелизма.
- Размер строки относительно мал (что ведет к высокой плотности страниц).
- Число строк в таблице относительно мало; это ведет к неполному сбалансированному дереву, определяемому с глубиной индекса, равной двум или трем.
Заметка
Даже в сбалансированных деревьях с более высокой глубиной, чем эта, могут возникать состязания с этим типом шаблона доступа, если частота языка обработки данных (DML) и параллелизма системы достаточно высоки. Уровень состязания за кратковременные блокировки может стать таким же, как при увеличении параллелизма, когда в системе доступны процессоры с 16 или более ядрами.
Состязание за кратковременные блокировки может возникать, даже если доступ осуществляется случайным образом через сбалансированное дерево, например, когда непоследовательный столбец является ведущим ключом в кластеризованном индексе. Следующий снимок экрана относится к системе с таким типом состязания. В этом примере состязание происходит из-за плотности страниц, вызываемых небольшим размером строки и относительно неглубокого сбалансированного дерева. При увеличении параллелизма конкуренция на страницах происходит, несмотря на то, что вставки происходят случайным образом по сбалансированному дереву, так как идентификатор GUID был первым столбцом в индексе.
На следующем снимке экрана ожидание происходит на страницах данных буфера и страницах свободного места (PFS). Дополнительные сведения о состязании на странице PFS см. в следующей записи блога сторонних разработчиков в SQLSkills: Benchmarking: Несколько файлов данных на SSD. Даже при увеличении числа файлов данных состязание за кратковременные блокировки было распространено на страницах данных буфера.
В следующей таблице перечислены основные факторы, которые наблюдались при этом типе конфликтов кратковременной блокировки.
| Множитель | Типичные наблюдения |
|---|---|
| Логические процессоры, используемые SQL Server | Состязание за кратковременные блокировки происходит в основном на компьютерах с 16-ядерными ЦП. |
| Конструкция схемы и шаблоны доступа | Высокая частота операций вставки, выбора, обновления и удаления для небольших таблиц. Поверхностное сбалансированное дерево (глубина индекса, равная двум или трем). Малый размер строки (много записей на страницу). |
| Уровень параллелизма | Состязание за кратковременные блокировки будут наблюдаться только при высоком уровне параллельных запросов от уровня приложения. |
| Наблюдаемый тип ожидания | Обратите внимание на ожидание буфера (PAGELATCH_EX и PAGELATCH_SH) и кратковременной блокировки, отличной от ACCESS_METHODS_HOBT_VIRTUAL_ROOT, из-за разбиения корневых объектов. Кроме того, PAGELATCH_UP ожидается на страницах PFS. Дополнительные сведения об ожиданиях кратковременных блокировок, не относящихся к буферу, см. в разделе sys.dm_os_latch_stats (Transact-SQL) в справке SQL Server. |
Сочетание неполного сбалансированного дерева и случайных вставок по индексу может вызвать разбиение страниц в сбалансированном дереве. Чтобы выполнить разбиение страницы, SQL Server должен получить общие кратковременные блокировки (SH) на всех уровнях, а затем монопольные кратковременные блокировки (EX) на страницах сбалансированного дерева, участвующих в разбиении страниц. Кроме того, если параллелизм имеет высокий уровень и данные постоянно вставляются и удаляются, могут возникнуть корневые разбиения сбалансированного дерева. В этом случае для других операций вставки может потребоваться подождать, когда в сбалансированном дереве будут получены кратковременные блокировки, не являющиеся буферами. На это будет указывать большое количество ожиданий кратковременной блокировки типа ACCESS_METHODS_HOBT_VIRTUAL_ROOT, наблюдаемой в динамическом административном представлении sys.dm_os_latch_stats.
Следующий скрипт можно изменить, чтобы определить глубину сбалансированного дерева для индексов в затронутой таблице.
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 i
join sysObjects 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 страниц (начиная с идентификатора страницы = 1) в каждом файле базы данных. Каждый байт на странице PFS записывает сведения, включая объем свободного пространства на странице, если он выделен, а также то, хранятся ли на странице фантомные записи. На странице PFS содержатся сведения о страницах, доступных для выделения, если для операции вставки или обновления требуется новая страница. Необходимо обновить страницу PFS в ряде сценариев, в том числе когда происходит выделение памяти или освобождение ресурсов. Поскольку для защиты страницы PFS требуется использование кратковременной блокировки обновления (UP), состязание за кратковременные блокировки на страницах PFS может произойти, если в файловой группе имеется несколько файлов данных и большое количество ядер ЦП. Простой способ решения этой проблемы — увеличить число файлов в файловой группе.
Предупреждение
Увеличение количества файлов в файловой группе может негативно сказаться на производительности определенных нагрузок, например загрузки с большим количеством операций крупной сортировки, которые сбрасывают память на диск.
Если для страниц PFS или SGAM в базе данных tempdb обнаружено много ожиданий PAGELATCH_UP, выполните следующие действия, чтобы устранить эти узкие места:
Добавьте файлы данных в tempdb, чтобы количество файлов данных tempdb равнялось количеству процессорных ядер на сервере.
Включите флаг трассировки 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 % случайным, поскольку не все пользователи находятся в сети одновременно, но распределение было достаточно случайным, чтобы снизить излишнее состязание за кратковременные блокировки. Одно из недостатков переупорядочения определения индекса заключается в том, что любые запросы SELECT к этой таблице должны быть изменены для использования как UserID, так и ИД транзакции в качестве предикатов равенства.
Внимание
Перед запуском в рабочей среде убедитесь, что вы тщательно протестировали все изменения в тестовой среде.
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
Использование хэш-значения в качестве первого столбца в первичном ключе
Следующее определение таблицы может использоваться для создания остатка от деления для количества процессоров. Хэш-значение генерируется с помощью последовательно увеличивающегося значения ИД транзакции для обеспечения равномерного распределения по сбалансированному дереву:
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 в качестве первого столбца в подходе с применением индекса позволяет использовать секционирование для других функций, но эта методика также может привести к потенциальным недостаткам большего числа разбиений на страницы, низкой физической организации и низкой плотности страниц.
Заметка
Использование идентификаторов GUID в качестве ведущих ключевых столбцов индексов — это отдельная тема для обсуждения. Подробное обсуждение достоинств и недостатков этого метода выходит за рамки этой статьи.
Использование хэш-секционирования с вычисляемым столбцом
Секционирование таблиц в SQL Server можно использовать для устранения чрезмерного состязания за кратковременные блокировки. Создание схемы хэш-секционирования с вычисляемым столбцом для секционированной таблицы — распространенный способ, реализуемый посредством следующих шагов.
Создайте новую файловую группу или используйте существующую файловую группу для хранения секций.
При использовании новой файловой группы равномерно распределите отдельные файлы по LUN, учитывая оптимальный макет. Если шаблон доступа включает большое количество вставок, создайте одинаковое число файлов, поскольку на компьютере SQL Server имеются физические ядра ЦП.
Чтобы секционировать таблицы в секции X, где X — число физических ядер ЦП на компьютере SQL Server, используйте команду CREATE PARTITION FUNCTION (не менее чем до 32 секций).
Заметка
Выравнивание количества секций по количеству ядер ЦП в пропорции 1:1 требуется не всегда. Во многих случаях это может быть значение меньше, чем количество ядер ЦП. Наличие большего количества секций может привести к увеличению объема запросов, которые должны выполнять поиск во всех секциях, и в этих случаях чем меньше секций, тем лучше. При тестировании SQLCAT на 64- и 128-разрядных логических системах ЦП с реальными рабочими нагрузками клиентов 32 секций достаточно для устранения чрезмерных конфликтов кратковременных блокировок и достижения целевых показателей масштабирования. В конечном итоге оптимальное количество секций должно быть определено с помощью тестирования.
Используйте команду 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. Вставки по-прежнему находятся в конце логического диапазона (последовательно увеличивающееся значение), но операция модуля хэш-значения обеспечивает разбиение вставок по разным сбалансированным деревьям, что сокращает количество узких мест. Это показано на приведенных ниже схемах.
Компромиссы при использовании хэш-секционирования
Хотя хэш-секционирование может устранить конфликты при вставке, при принятии решения о необходимости использования этого метода следует учитывать несколько компромиссов.
Запросы SELECT в большинстве случаев необходимо изменить, чтобы включить хэш-секцию в предикат и привести к плану запроса, который не обеспечивает исключение секций при выдаче этих запросов. На следующем снимке экрана показан недопустимый план без удаления секции после реализации хэш-секционирования.
Он устраняет возможность удаления секций в некоторых других запросах, например в отчетах на основе диапазона.
При присоединении хэш-секционированной таблицы к другой таблице для выполнения исключения секций вторая таблица должна быть секционирована с тем же ключом, а хэш-ключ должен быть частью критериев соединения.
Секционирование хэша предотвращает использование секционирования для других функций управления, таких как архивирование окон и функции переключения секций.
Хэш-секционирование — это эффективная стратегия устранения чрезмерного состязания за кратковременные блокировки, так как она увеличивает общую пропускную способность системы за счет уменьшения конкуренции при вставке. Поскольку есть несколько компромиссов, это может быть неоптимальным решением для некоторых шаблонов доступа.
Сводка по методам, используемым для устранения конфликтов кратковременных блокировок
В следующих двух разделах содержатся сводные сведения о приемах, которые можно использовать для устранения чрезмерных конфликтов кратковременных блокировок.
Непоследовательный ключ/индекс
Преимущества.
- Позволяет использовать другие функции секционирования, такие как архивирование данных с помощью схемы скользящих окон и функции переключения секций.
Недостатки.
- Возможные проблемы при выборе ключа или индекса, чтобы обеспечить достаточное равномерное распределения вставок все время.
- GUID в качестве начального столбца можно использовать для обеспечения равномерного распространения с той лишь оговоркой, что это может привести к чрезмерным операциям разбиения страниц.
- Случайные вставки в сбалансированном дереве могут привести к слишком большому числу операций разбиения на страницы и появлению конфликтов кратковременных блокировок на неконечных страницах.
Хэш-секционирование с помощью вычисляемого столбца
Преимущества.
- Прозрачность для вставок.
Недостатки.
- Секционирование нельзя использовать для предполагаемых функций управления, таких как архивирование данных с помощью параметров переключения секций.
- Может вызвать проблемы с исключением секций для запросов, включая отдельные запросы и запросы выбора/обновления, основанные на диапазонах, а также запросы, выполняющие соединение.
- Добавление материализованных вычисляемых столбцов — это автономная операция.
Совет
Дополнительные методы см. в записи блога Ожидания PAGELATCH_EX и большое количество вставок.
Пошаговое руководство. Диагностика конфликтов с блокировкой
В следующем пошаговом руководстве демонстрируются средства и методы, описанные в разделах Диагностика конфликтов кратковременных блокировок SQL Server и Обработка конфликтов кратковременных блокировок для различных шаблонов таблиц для решения проблемы на примере реального использования. В этом сценарии описывается взаимодействие с клиентами для выполнения нагрузочного тестирования системы точек продаж. В тестировании имитируется примерно 8000 магазинов, выполняющих транзакции с приложением SQL Server, которое работает на системе с 8 сокетами, 32 физическими ядрами и 256 ГБ памяти.
На следующей диаграмме показана конфигурация оборудования, используемая для тестирования системы точек продаж:
Симптом: горячие защелки
В этом случае мы наблюдали высокие значения ожиданий для PAGELATCH_EX, где обычно определяется высокий уровень (в среднем более 1 мс). В этом случае мы постоянно наблюдаем ожидания с превышением 20 мс.
После того как мы определили, что состязание за кратковременные блокировки было проблематичным, необходимо определить, что вызвало состязание за кратковременную блокировку.
Изоляция объекта, вызывающего состязание за кратковременные блокировки
В следующем скрипте используется столбец resource_description, чтобы определить, какой индекс вызвал состязание PAGELATCH_EX:
Заметка
Столбец resource_description, возвращаемый этим скриптом, предоставляет описание ресурса в формате <DatabaseID, FileID, PageID> , где имя базы данных, связанной с DatabaseID, можно определить, передав значение DatabaseID в функцию DB_NAME ().
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 *
--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 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))
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
order by wt.wait_duration_ms desc;
Как показано здесь, состязание находится в таблице LATCHTEST, а имя индекса — CIX_LATCHTEST. Обратите внимание, что имена были изменены для анонимизации рабочей нагрузки.
Для более продвинутого сценария, в котором опрос выполняется несколько раз и используется временная таблица для определения общего времени ожидания через настраиваемый период, обратитесь к разделу Дескрипторы буфера запросов для определения объектов, вызывающих конфликты кратковременных блокировок в приложении.
Альтернативный способ — изоляция объекта, вызывающего состязания за кратковременные блокировки
Иногда запрос может быть непрактичным sys.dm_os_buffer_descriptors. Так как память в системе, доступная для буферного пула, увеличивается, также увеличивается и время, необходимое для выполнения этого динамического административного представления. Выполнение этого динамического административного представления в системе с 256 ГБ ОЗУ может занять до 10 минут или более. Доступен альтернативный метод, который описан ниже и демонстрируется с другой рабочей нагрузкой, которая выполнялась в лаборатории.
Запросите текущие ожидающие задачи, используя сценарий приложения Запрос sys.dm_os_waiting_tasks, упорядоченный по длительности ожидания.
Определите ключевую страницу, на которой наблюдалось сопровождение, что происходит, если несколько потоков будут выделены на одной странице. В этом примере потоки, выполняющие вставку, выполняются на последней странице сбалансированного дерева и ожидают, пока они не смогут получить кратковременную блокировку EX. Это обозначается с помощью resource_description в первом запросе, в нашем случае это "8:1:111305".
Включите флаг трассировки 3604, который предоставляет дополнительные сведения о странице с помощью DBCC PAGE со следующим синтаксисом, подставив значение, полученное с помощью resource_description, в круглых скобках:
--enable trace flag 3604 to enable console output dbcc traceon (3604); --examine the details of the page dbcc page (8,1, 111305, -1);Изучите выходные данные DBCC. В нашем примере это должен быть связанный идентификатор ObjectID метаданных ("78623323").
Теперь можно выполнить следующую команду, чтобы определить имя объекта, вызывающего состязание, которое, как и ожидалось, LATCHTEST.
Заметка
Убедитесь, что вы используете правильный контекст базы данных, иначе запрос вернет значение NULL.
--get object name select OBJECT_NAME (78623323);
Сводка и результаты
Используя методы выше, мы смогли убедиться, что состязание происходило в кластеризованном индексе с последовательно увеличивающимся значением ключа в таблице, где было получено наибольшее число вставок. Этот тип состязаний редко используется для индексов с последовательно увеличивающимися значениями ключа, такими как DateTime, Identity или transactionID, генерируемых приложением.
Чтобы устранить эту проблему, мы использовали хэш-секционирование с использованием вычисляемого столбца и наблюдали повышение производительности на 690 %. В следующей таблице приведены сводные данные о производительности приложения до и после реализации хэш-секционирования с использованием вычисляемого столбца. После удаления узкого места, связанного с кратковременной блокировкой, использование ЦП значительно повышается вместе с пропускной способностью, как и ожидалось.
| Измерение | Перед хэш-секционированием | После хэш-секционирования |
|---|---|---|
| Бизнес-транзакций/с | 36 | 249 |
| Среднее время ожидания кратковременной блокировки страницы | 36 миллисекунд | 0,6 миллисекунд |
| Ожиданий кратковременных блокировок/с | 9,562 | 2,873 |
| Загруженность процессора SQL | 24 % | 78 % |
| Запросов пакетов SQL/с | 12,368 | 47,045 |
Как видно из приведенной выше таблицы, правильная идентификация и устранение проблем с производительностью, вызванных чрезмерным состязанием за кратковременные блокировки страниц, может оказать положительное влияние на общую производительность приложения.
Приложение. Альтернативный метод
Одна из возможных стратегий избежать чрезмерного состязания за кратковременные блокировки страниц заключается в том, чтобы заполнить строки столбцами типа CHAR, чтобы каждая строка использовала всю страницу. Эту стратегию можно применять, если общий размер данных мал и необходимо устранить состязание за кратковременные блокировки страниц, вызванные приведенным ниже сочетанием факторов:
- малый размер строки;
- поверхностное сбалансированное дерево;
- шаблон доступа с высокой скоростью случайных операций вставки, выбора, обновления и удаления;
- небольшие таблицы, например временные таблицы очередей.
При заполнении строк для размещения полной страницы требуется, чтобы SQL выделил дополнительные страницы, что делает доступными для вставок больше страниц и уменьшает состязание за эксклюзивные кратковременные блокировки страниц.
Заполнение строк, чтобы каждая строка занимала всю страницу
Для заполнения строк, чтобы они занимали всю страницу, можно использовать аналогичный приведенному ниже скрипт.
ALTER TABLE mytable ADD Padding CHAR(5000) NOT NULL DEFAULT ('X');
Заметка
Используйте наименьший возможный символ, при котором одна строка занимает всю страницу, чтобы уменьшить требования к ЦП для значения заполнения и к дополнительному пространству, необходимому для записи строки в журнал. В высокопроизводительной системе на счету каждый байт.
Этот метод описан для полноты картины. На практике SQLCAT использует его только на небольшой таблице с 10 000 строками на одно задействование производительности. Этот метод имеет ограниченное применение из-за того, что он увеличивает нагрузку на память SQL Server для больших таблиц и может привести к состязанию кратковременных блокировок без буфера на неконечных страницах. Нехватка памяти может быть дополнительным значительно ограничивающим фактором для применения этой методики. С объемами памяти, доступными на современных серверах, большая часть рабочего набора для рабочих нагрузок OLTP обычно хранится в памяти. Когда набор данных увеличивается до размера, который больше не умещается в памяти, возникает значительное снижение производительности. Таким образом, этот прием применим только к небольшим таблицам. Этот метод не используется SQLCAT для таких сценариев, как состязание за вставку последней или конечной страницы для больших таблиц.
Внимание
Применение этой стратегии может вызвать большое количество ожиданий для типа кратковременной блокировки ACCESS_METHODS_HOBT_VIRTUAL_ROOT, так как это может привести к большому числу разбиений страниц, происходящих на неконечных уровнях сбалансированного дерева. В этом случае SQL Server должен получить общие кратковременные блокировки (SH) на всех уровнях, за которыми следуют эксклюзивные (EX) кратковременные блокировки на страницах сбалансированного дерева, на которых можно разделить страницы. Проверьте динамическое административное представление sys.dm_os_latch_stats на наличие большого числа ожиданий кратковременной блокировки типа ACCESS_METHODS_HOBT_VIRTUAL_ROOT после заполнения строк.
Приложение. Скрипты разрешения блокировок 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 wt
JOIN sys.dm_exec_sessions es ON wt.session_id = es.session_id
JOIN sys.dm_exec_requests 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 wt
JOIN sys.dm_exec_sessions es ON wt.session_id = es.session_id
JOIN sys.dm_exec_requests 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 datetime;
declare @previous_snap_time 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 e
inner join (
select * from #_wait_stats
where snap_time = @previous_snap_time
) 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 varchar(16), @Counter INT, @MaxCount INT, @Counter2 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 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 wt
JOIN sys.dm_os_buffer_descriptors 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
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;
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);
Далее
Дополнительные сведения о средствах наблюдения за производительностью см. в статье Средства контроля и настройки производительности.
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по