latch sql что это такое

13.07.202213.07.2022 admin 0 Comments

SQLShack

All about Latches in SQL Server

SQL Server locks, discussed in the article All about locking in SQL Server, which is applied on data for the duration of the logical operation to preserve logical transaction consistency. SQL Server latches, however, are a special type of low-level system locks which are held as long as the physical operation lasts on the memory page in order to protect memory consistency

SQL Server latches are an internal SQL Server mechanism that serves to protect shared memory resources, like pages and memory data structures inside the buffer pool, in order to coordinate access to those resources and protect them from corruption. Designed as an internal SQL Server mechanism that is not exposed outside the SQL Server Operating System (SQLOS), latches can be managed only by SQL Server, itself, and not by users (unlike locks that can be managed via NO LOCK hints). Every time SQL Server has to read memory, it will impose latches to the page or internal memory structure that cannot be accessed in a proper multi-threaded way. In this way, SQL Server establishes latches as a resource for the coordination of multiple physical thread execution in a SQL Server database

In the same manner as locks, SQL Server latches can come in various modes:

Similarly to locks, there is a compatibility or incompatibility component between the various latch modes. The table below gives an insight in compatibility between the various SQL Server latches

There are many different types of SQL Server latches, but essentially they can be split into three general categories: I/O latches, buffer latches, and non-buffer latches.

I/O latches

I/O Latches are acquired in situations when an outstanding I/O operation is executed over the pages stored in the buffer pool, or more precisely, when data has to be read from or written to physical storage. The SQL Server will use PAGEIOLATCH_XX wait types to report when a process is waiting for on a SQL Server I/O latch to be released

So, in situations when the page is requested to be brought from storage into a buffer pool, a PAGEIOLATCH will be acquired on that page, and if storage is not ready to be read the PAGEIOLATCH wait type count will increase

Buffer latches

In order to properly understand buffer latches, it is important to properly understand the idea behind the memory buffer pool, which is designed around the goal of maximizing SQL Server performance. The buffer pool is a physical memory range where data that is read from disk is stored in data pages. Data in SQL Server tables is stored in pages and each page has a fixed size of 8192 bytes (8 KB). Whenever a data page has to be read or written to, it will be first brought into a buffer pool. In that way, any further access to that page will be read directly from the memory buffer pool, thus improving SQL Server performance by minimizing disk IO.

This implementation of the memory pool concept in SQL Server is what drives SQL Server physical memory usage can be high even in situations where there is no SQL Server activity. The loading of data in the buffer pool is based on the First-In First-Out (FIFO) principle.

SQL Server uses the buffer manager for managing the buffer pool and it is therefore in charge of any hash tables, the pool array that contain pages and for pages stored in the buffer. The SQLOS is accessing the data stored in the memory exclusively via the buffer manager

The pages that are modified in the buffer pool due to executed insert, delete or update command, are the so called “dirty” pages, while the unmodified pages are called “clean” pages. So when the page has to be accessed in memory, the SQL OS will acquire the buffer latch on that page. But unlike a lock, the SQL Server latch will not be held for the transaction duration but rather just during the critical period of a transaction, and it will be released as soon as it is no longer needed. SQL Server will use PAGELATCH_XX wait types to report when a process is waiting for on a SQL Server buffer latch to be released

Non-buffer latches

Non-buffer latches are designed to protect and guarantee any physical memory structure other than pages stored in the buffer pool. SQL Server will use LATCH_XX wait types to report when a process is waiting for on a SQL Server buffer latch to be released. Non-buffer latches are not often encountered during, and thus those are the least documented, but here are some use cases that can lead to SQL Server contention with non-buffer latches:

So, based on the previous, in situations when LATCH_XX wait type have excessive values or those are prevalent wait types, it is good to check which non-buffer latches are prevalent in the SQL Server using the following query

SuperLatches

Starting with SQL Server 2005, superlatches (also called sublatches) were introduced to improve SQL Server efficiency in highly concurrent OLTP workloads for a certain pattern of usage (i.e. very high shared read only access to the page (SH) while write access is very low or not exists). Superlatches are used by SQL Server only in NUMA systems with 32+ logical processors. This mechanism is an efficient way of SQL Server to deal with a latch contention by dynamically promoting an array of latches to a Superlatch and thus allowing an SH mode request to the superlatch, while the containing sublatches can remain different modes. When this occurs, the superlatch becomes just a pointer to an array of SQL Server latches.

A Superlatch will behave as a single latch with sublatch structures and there can be one sublatch per partition per logical CPU core. So when a superlatch is created, the CPU worker thread will just have to acquire the shared (SH) sublatch that is assigned to the scheduler. This ensures that a shared (SH) superlatch uses less resources while at the same time access to pages is more efficient comparing to non-partitioned shared latches. The reason for this is that the superlatch do not require any synchronization of the global state as it will access only the local NUMA memory

Latch Contention

Latch contention is a frequent scenario for systems with large number of CPUs, and it is the consequence of situations when on the same in-memory structure, multiple threads are trying, concurrently, to acquire SQL Server latches that are not compatible with each other. Since SQL Server latches are controlled by an internal SQL Server mechanism, SQLOS will determine on its own when to use them. Due to the deterministic nature of SQL Server latches and their behavior, various parameters such as application design or database schema structure can significantly affect SQL Server latches

On high throughput systems which are designed for a large number of CPUs and thus, high-concurrency, active latch contention is expected as a regular occurrence of on memory structures are often accessed and protected using the latches. But the situation when latch contention and latch wait types wait time is large enough to decrease utilization of CPUs is what results in the reduced throughput

Recognizing and identifying the signs of latch contention is important, so let’s shed light on some symptoms of latch contention

The expected behavior of SQL Server latches, in relation to the transactions per second, is that transactions per second will increase along with increasing average SQL Server latch waits, that themselves increase at a slow rate that will be within the margins of the throughput. Such a situation is represented in the image below and this is the desired system behavior which indicates that logical processors are not conflicting with each other. In such a scenario, adding more logical processors means that more can be done

Situations when transactions/sec value is dropping when enabling additional logical processors while, at the same time, average SQL Server latch wait times are increasing at a greater rate than the system throughput, potentially indicate that there is a high probability that a problem with a latch contention may exist. The following image represents a typical situation where adding new logical processors worked until the certain point when longer latch wait times started to occur. This results in a situation where adding new logical processors will not have any benefits, up to a point where transactions/sec starts to negatively affect performance. This is ta typical situation where adding new logical processors actually had a negative, vs. a positive effect, as the resulting system environment will be spending a lot of time in a waiting state.

Latch contention that can affect the OLTP performance is mainly caused when high concurrency is the result of some of the following factors:

Источник

Статистика и виды ожиданий. День № 12 (PAGELATCH)

«PAGELATCH_DT, PAGELATCH_EX, PAGELATCH_KP, PAGELATCH_SH, PAGELATCH_UP»

Я часто вижу людей, которые не могут объяснить разницы в PAGEIOLATCH_X и PAGELATCH_X. А правильный ответ состоит в том, что PAGEIOLATCH относится к дисковым операциям, а PAGELATCH к памяти. Прежде чем пойдём дальше, позвольте мне объяснить что такое блокировки (latches).

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

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

ИЗ Book On-Line:

PAGELATCH_DT
Имеет место, когда задача ожидает кратковременной блокировки буфера, находящегося не в состоянии запроса ввода-вывода. Запрос на кратковременную блокировку производится в режиме удаления.

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

PAGELATCH_KP
Имеет место, когда задача ожидает кратковременной блокировки буфера, находящегося не в состоянии запроса ввода-вывода. Запрос на кратковременную блокировку производится в режиме удержания.

PAGELATCH_SH
Имеет место, когда задача ожидает кратковременной блокировки буфера, находящегося не в состоянии запроса ввода-вывода. Запрос на кратковременную блокировку производится в режиме общего доступа.

PAGELATCH_UP
Имеет место, когда задача ожидает кратковременной блокировки буфера, находящегося не в состоянии запроса ввода-вывода. Запрос на кратковременную блокировку производится в режиме обновления.

Объяснение:

Когда происходит конкуренция доступа к страницам в памяти, появляется данный вид ожиданий. Скорее всего эта страница в памяти очень часто используется. SQL Server ждёт доступа к этим данным, чтобы положить latch. Часто такой вид блокировок может наблюдаться когда происходит конкуренция внутри TempDb. Если существуют индексы, которые очень часто используются, данный вид ожиданий так же может появиться.

Уменьшение ожиданий PAGELATCH_X:

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

1. Average Latch Wait Time (ms): Время ожидания для запросов latch
2. Latch Waits/sec: Количество запросов latch, которые не могут быть наложены в данный момент
3. Total Latch Wait Time (ms): Общее количество latch в прошлую секунду

Если данные ожидания обнаружены в TempDb, то вам может помочь пост Pail Randal, который объяснит оптимальные настройки для данной базы (перейти). Флаг трассировки 1118 может помочь, но пользуйтесь им очень осторожно.

Заметка: Представленная тут информация является только моим опытом. Я настраиваю, чтобы вы читали Books On-Line. Все мои обсуждения ожиданий здесь носят общий характер и изменяются от системы к системе. Я рекомендую сначала тестировать всё на сервере разработки, прежде чем применять это на рабочем сервере.

Источник

sys.dm_os_latch_stats (Transact-SQL)

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

Имя столбца	Тип данных	Описание
latch_class	nvarchar(60)	Имя класса кратковременных блокировок.
waiting_requests_count	bigint	Число ожиданий кратковременных блокировок в данном классе. Этот счетчик увеличивается в начале ожидания кратковременной блокировки.
wait_time_ms	bigint	Общее время ожидания кратковременных блокировок в данном классе в миллисекундах.

Идентификатор узла, на котором находится данное распределение.

Разрешения

для SQL Server SQL Управляемый экземпляр требуется VIEW SERVER STATE разрешение.

Remarks

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

Очистить содержимое представления sys.dm_os_latch_stats с помощью инструкции DBCC SQLPERF можно следующим образом.

В результате все счетчики будут обнулены.

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

Кратковременных блокировок

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

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

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

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

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

Источник

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

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

Введение

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

Что такое PAGELATCH?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Параметр OPTIMIZE_FOR_SEQUENTIAL_KEY

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SCHEMA_ONLY

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

SCHEMA_AND_DATA

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

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

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

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

Выводы

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

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

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

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

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

Источник

Диагностика и устранение конфликтов кратковременных блокировок на 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 имеет размер 8 КБ и может содержать несколько строк. Чтобы увеличить степень параллелизма и производительность, кратковременные блокировки буфера накладываются только на время физической операции со страницей, в отличие от блокировок, которые действуют в ходе выполнения логической транзакции.

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

structure	Назначение	Управление	Влияние на производительность	Предоставляется
Кратковременная блокировка	Обеспечивает согласованность структур в памяти.	Только ядро 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) по мере высвобождения запросов на кратковременную блокировку. Соблюдение этого принципа обработки позволяет обеспечить равноправие потоков и исключить их нехватку.

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

Режим кратковременной блокировки	KP	SH	UP	EX	DT
KP	Да	Да	Да	Да	Нет
SH	Да	Да	Да	N	Нет
UP	Да	Да	N	N	Нет
EX	Да	N	N	N	Нет
DT	Нет	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), назначенную локальному планировщику.

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

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

Типы ожидания кратковременной блокировки

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

Симптомы и причины конфликтов кратковременных блокировок 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, предоставленные в разделе Приложение. Сценарии конфликтов кратковременных блокировок SQL Server, для определения типа кратковременной блокировки и затронутых ресурсов.

Индикаторы конфликтов кратковременных блокировок

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

Измерьте общее время ожидания во время выполнения репрезентативного теста.

Упорядочите их по порядку.

Определите долю объектов, связанных с кратковременными блокировками.

Дополнительные сведения о динамическом административном представлении sys.dm_os_wait_stats см. в разделе sys.dm_os_wait_stats (Transact-SQL) в справке по SQL Server.

Дополнительные сведения о динамическом административном представлении sys.dm_os_latch_stats см. в разделе sys.dm_os_latch_stats (Transact-SQL) в справке по SQL Server.

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

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

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

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

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

Аналогичную команду можно использовать для очистки динамического административного представления sys.dm_os_latch_stats :

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

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

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

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

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

Статистика

Описание

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 приведена конкретная ожидаемая страница в следующем формате: : :

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

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

Статистика	Описание
latch_class	Тип кратковременной блокировки, которая записана в ядре SQL Server и препятствует выполнению текущего запроса.
waiting_requests_count	Количество ожиданий кратковременных блокировок этого класса с момента перезапуска SQL Server. Этот счетчик увеличивается в начале ожидания кратковременной блокировки.
wait_time_ms	Общее время ожидания в миллисекундах, связанное с ожиданием для этого типа кратковременной блокировки.
max_wait_time_ms	Максимальное время в миллисекундах, связанное с ожиданием для всех запросов для этого типа кратковременной блокировки.

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

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

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

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

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

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

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

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

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

В журнал заносится запись об изменении страницы.

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

Со всех страниц снимаются кратковременные блокировки.

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

165 байтов с учетом дополнительных ресурсов строки соответствует примерно

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

Фактор

Типичные наблюдения

Логические процессоры, используемые SQL Server

Такое состязание за кратковременные блокировки происходит в основном на системах с 16-ядерными и более процессорами, а чаще всего на системах с 32-ядерными и более процессорами.

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

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

Индекс имеет увеличивающийся первичный ключ с большим количеством вставок.

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

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

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

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

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

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

Даже в сбалансированных деревьях с более высокой глубиной, чем эта, могут возникать состязания с этим типом шаблона доступа, если частота языка обработки данных (DML) и параллелизма системы достаточно высоки. Уровень состязания за кратковременные блокировки может стать таким же, как при увеличении параллелизма, когда в системе доступны процессоры с 16 или более ядрами.

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

На следующем снимке экрана ожидание происходит на страницах данных буфера и страницах свободного места (PFS). Дополнительные сведения о состязаниях за кратковременные блокировки для страниц PFS см. в следующей записи блога стороннего разработчика на ресурсе SQLSkills: Тестирование производительности: несколько файлов данных на 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.

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

Состязание за кратковременные блокировки на страницах свободного места (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 кластеризованного индекса наблюдалось чрезмерное состязание за кратковременные блокировки:

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

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

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

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

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

Следующее определение таблицы может использоваться для создания остатка от деления для количества процессоров. Хэш-значение генерируется с помощью последовательно увеличивающегося значения ИД транзакции для обеспечения равномерного распределения по сбалансированному дереву:

Вариант. Использование идентификатора 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Недостатки.

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

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

Недостатки.

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

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

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

Симптом. Горячие кратковременные блокировки

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

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

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

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

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

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

Альтернативный способ — изоляция объекта, вызывающего состязания за кратковременные блокировки

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

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

Изучите выходные данные DBCC. В нашем примере это должен быть связанный идентификатор ObjectID метаданных («78623323»).

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

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

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

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

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

Измерения	Перед хэш-секционированием	После хэш-секционирования
Бизнес-транзакций/с	36	249
Среднее время ожидания кратковременной блокировки страницы	36 миллисекунд	0,6 миллисекунд
Ожиданий кратковременных блокировок/с	9,562	2,873
Загруженность процессора SQL	24 %	78 %
Запросов пакетов SQL/с	12,368	47,045

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

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

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

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

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

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

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

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

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

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

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

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

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

Запрос к sys.dm_os_waiting_tasks с сортировкой по длительности ожидания

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

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

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

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

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

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

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

Дальнейшие действия

Дополнительные сведения о средствах наблюдения за производительностью см. в статье Средства контроля и настройки производительности.

Источник

SQLShack

All about Latches in SQL Server

I/O latches

Buffer latches

Non-buffer latches

SuperLatches

Latch Contention

Статистика и виды ожиданий. День № 12 (PAGELATCH)

ИЗ Book On-Line:

Объяснение:

Уменьшение ожиданий PAGELATCH_X:

sys.dm_os_latch_stats (Transact-SQL)

Разрешения

Remarks

Кратковременных блокировок

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

Введение

Что такое PAGELATCH?

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

Параметр OPTIMIZE_FOR_SEQUENTIAL_KEY

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

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

SCHEMA_ONLY

SCHEMA_AND_DATA

Выводы

Диагностика и устранение конфликтов кратковременных блокировок на SQL Server

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

Как в SQL Server используются кратковременные блокировки?

Режимы кратковременных блокировок SQL Server и совместимость

Кратковременные блокировки SQL Server и superLatch и sublatch

Типы ожидания кратковременной блокировки

Симптомы и причины конфликтов кратковременных блокировок SQL Server

Пример конфликтов кратковременных блокировок

Производительность при разрешении конфликтов кратковременных блокировок

Факторы, влияющие на конфликты кратковременных блокировок

Диагностика конфликтов кратковременных блокировок в SQL Server

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

Индикаторы конфликтов кратковременных блокировок

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

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

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

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

Состязание за кратковременные блокировки на страницах свободного места (PFS)

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

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

Использование непоследовательного ключа индекса в начале

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

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

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

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

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

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

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

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

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

Симптом. Горячие кратковременные блокировки

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

Альтернативный способ — изоляция объекта, вызывающего состязания за кратковременные блокировки

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

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

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

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

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

Запрос к sys.dm_os_waiting_tasks с сортировкой по длительности ожидания

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

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

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

Дальнейшие действия

Вам также понравится

к чему снится поймать ежа

к чему снится найти золото в море

к чему снится пожар в магазине во сне для женщины

Добавить комментарий Отменить ответ