Наиболее распространённые классы кратких блокировок и их значение

Автор: Paul Randal, Most common latch classes and what they mean

Я проводил опрос о распространённых кратких блокировках (их ещё называют защёлками - latch) на экземплярах SQL Server по всему миру. Я получил информацию почти с 600 серверов, и если вы помните, я дал вам код для вывода основных нестраничных защёлок, ожидаемых во время ожиданий LATCH_XX. Нестраничные защёлки — это те, которые не являются ни PAGELATCH_XX (ожидание доступа к копии страницы файла данных в памяти), ни PAGEIOLATCH_XX (ожидание чтения страницы файла данных с диска в память).

Каждая нестраничная структура данных в SQL Server, которая должна быть потокобезопасной (т. е. может быть доступна нескольким потокам), должна иметь связанный с ней механизм синхронизации — либо защёлку, либо спин-блокировку. Я уже писал о спин-блокировках — см. здесь — и они используются для некоторых структур данных, доступ к которым происходит настолько часто и настолько короткое время, что даже затраты на захват защёлки слишком высоки. Доступ ко всем остальным структурам данных контролируется через защёлки.

Используя анализ статистики ожиданий, если вы видите, что типы ожиданий LATCH_EX или LATCH_SH входят в число 3–4 наиболее распространённых типов, конкуренция за защёлку может вносить вклад в проблемы производительности. Если это так, вам нужно использовать sys.dm_os_latch_stats, чтобы выяснить, какая защёлка является наиболее распространённой, используя код, подобный приведённому ниже (с примером вывода):

/*============================================================================
  File:     LatchStats.sql
 
  Summary:  Снимок статистики защёлок
 
  SQL Server Versions: 2005 и новее
------------------------------------------------------------------------------
  Автор: Paul S. Randal, SQLskills.com
 
  (c) 2015, SQLskills.com. Все права защищены.
 
  Для получения дополнительных скриптов и примеров кода посетите
    http://www.SQLskills.com
 
  Вы можете изменять этот код для своих *некоммерческих* целей. Вы можете
  публиковать изменённый код при условии включения этого уведомления об авторских
  правах и указания авторства, но вы должны получить предварительное разрешение
  перед публикацией этого кода в блоге.
   
  ЭТОТ КОД И ИНФОРМАЦИЯ ПРЕДОСТАВЛЯЮТСЯ "КАК ЕСТЬ" БЕЗ КАКИХ-ЛИБО ГАРАНТИЙ,
  ЯВНЫХ ИЛИ ПОДРАЗУМЕВАЕМЫХ, ВКЛЮЧАЯ, НО НЕ ОГРАНИЧИВАЯСЬ, ПОДРАЗУМЕВАЕМЫМИ
  ГАРАНТИЯМИ ТОВАРНОЙ ПРИГОДНОСТИ И/ИЛИ ПРИГОДНОСТИ ДЛЯ ОПРЕДЕЛЁННОЙ ЦЕЛИ.
============================================================================*/
 
WITH [Latches] AS
    (SELECT
        [latch_class],
        [wait_time_ms] / 1000.0 AS [WaitS],
        [waiting_requests_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_latch_stats
    WHERE [latch_class] NOT IN (
        N'BUFFER')
    AND [wait_time_ms] > 0
)
SELECT
    MAX ([W1].[latch_class]) AS [LatchClass], 
    CAST (MAX ([W1].[WaitS]) AS DECIMAL(14, 2)) AS [Wait_S],
    MAX ([W1].[WaitCount]) AS [WaitCount],
    CAST (MAX ([W1].[Percentage]) AS DECIMAL(14, 2)) AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (14, 4)) AS [AvgWait_S]
FROM [Latches] AS [W1]
INNER JOIN [Latches] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- порог процента
GO

LatchClass                        Wait_S  WaitCount  Percentage  AvgWait_S
——————————— ——- ———- ———– ———-
LOG_MANAGER                       221.43  4659       45.81       0.0475
ACCESS_METHODS_HOBT_VIRTUAL_ROOT  199.56  7017       41.28       0.0284
FGCB_ADD_REMOVE                   35.17   1047       7.27        0.0336
DBCC_OBJECT_METADATA              26.85   256490     5.55        0.0001

Вы также можете увидеть класс защёлки, которая ожидается, в столбце resource_description DMV sys.dm_os_waiting_tasks, когда столбец wait_type содержит LATCH_XX.

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

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

ACCESS_METHODS_DATASET_PARENT

ACCESS_METHODS_SCAN_RANGE_GENERATOR

Эти две защёлки используются во время параллельных просмотров для предоставления каждому потоку диапазона идентификаторов страниц для просмотра. Ожидания LATCH_XX для этих защёлок обычно появляются вместе с ожиданиями CXPACKET и PAGEIOLATCH_XX (если сканируемые данные не находятся в памяти). Используйте стандартные методы устранения проблем с параллелизмом для дальнейшего исследования (например, оправдано ли использование параллелизма? Возможно, увеличьте cost threshold for parallelism, уменьшите MAXDOP, используйте подсказку MAXDOP, используйте Resource Governor для ограничения DOP с помощью рабочей группы с ограничением MAX_DOP. Изменился ли план с поиска по индексу на параллельное сканирование таблицы из-за достижения пороговой точки или перекомпиляции плана с нетипичным параметром хранимой процедуры или плохой статистикой? НЕ реагируйте автоматически, устанавливая серверный MAXDOP в 1 — это один из худших советов, которые я вижу в Интернете).

ACCESS_METHODS_HOBT_COUNT

Эта защёлка используется для записи изменений количества страниц и строк для HoBt (Heap-or-B-tree — куча или B-дерево) в метаданные механизма хранения. Конкуренция указывала бы на большое количество небольших параллельных операций DML на одной таблице.

LOG_MANAGER

Если вы видите эту защёлку, почти наверняка это происходит из-за того, что журнал транзакций растёт, потому что не может быть очищен/усечён по какой-то причине. Найдите базу данных, в которой журнал растёт, и выясните, что мешает очистке журнала, используя: SELECT [log_reuse_wait_desc] FROM sys.databases WHERE [name] = N'yourdbname'.

TRACE_CONTROLLER

Эта защёлка используется SQL Trace для множества различных целей, включая просто генерацию событий трассировки. Конкуренция за эту защёлку означала бы, что на сервере выполняется несколько трассировок, отслеживающих множество событий, — т. е. вы «перетрассируете».

DBCC_MULTIOBJECT_SCANNER

Эта защёлка появляется в Enterprise Edition, когда команды DBCC CHECK* могут выполняться параллельно. Она используется потоками для запроса следующей страницы файла данных для обработки. В конце прошлого года она была идентифицирована как основная точка конкуренции внутри DBCC CHECK*, и была проделана работа по снижению конкуренции и ускорению DBCC CHECK*. Дополнительную информацию см. в статье базы знаний 2634571.

ACCESS_METHODS_HOBT_VIRTUAL_ROOT

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

FGCB_ADD_REMOVE

FGCB расшифровывается как File Group Control Block. Эта защёлка требуется всякий раз, когда файл добавляется или удаляется из файловой группы, когда файл увеличивается (вручную или автоматически), при пересчёте весов пропорционального заполнения и при циклическом переборе файлов в файловой группе в рамках циклического распределения. Если вы видите эту защёлку, наиболее частой причиной является большое количество автоматических расширений файлов. Это также может быть связано с файловой группой, содержащей много файлов (например, первичная файловая группа в tempdb), где тысячи параллельных подключений выполняют операции выделения. Веса пропорционального заполнения пересчитываются каждые 8192 операции выделения, поэтому существует вероятность замедления при частых пересчётах для многих файлов.

DATABASE_MIRRORING_CONNECTION

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

NESTING_TRANSACTION_FULL

Эта защёлка, наряду с NESTING_TRANSACTION_READONLY, используется для управления доступом к структурам описания транзакций (называемым XDES) для параллельных вложенных транзакций. _FULL используется для транзакции, которая «активна», т. е. она изменила базу данных (обычно для построения/перестроения индекса), и это делает описание _READONLY очевидным. Запрос, включающий параллельный оператор, должен запускать суб-транзакцию для каждого используемого параллельного потока — эти транзакции являются суб-транзакциями параллельной вложенной транзакции. При конкуренции за них я бы исследовал нежелательный параллелизм, но у меня нет определённого утверждения «обычно это такая-то проблема». Также ознакомьтесь с комментариями для получения информации о том, что иногда они также являются проблемой при использовании RCSI.