Поделиться через

Диагностика и устранение конфликтов спинлока в SQL Server

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

Замечание

Рекомендации и лучшие практики, документированные здесь, основаны на реальном опыте, накопленном в процессе разработки и внедрения систем OLTP в условиях реального мира. Она была первоначально опубликована командой microsoft SQL Server Customer Advisory Team (SQLCAT).

Предыстория

В прошлом сырьевые компьютеры Windows Server использовали только один или два микропроцессора или ЦП, и ЦП были разработаны только с одним процессором или "ядром". Увеличение емкости вычислительной обработки было достигнуто с помощью более быстрых ЦП, что стало возможным в значительной степени благодаря улучшению плотности трансисторов. После "Закона Мура", плотность трансистора или количество трансисторов, которые можно разместить на интегрированном канале, постоянно удвоились каждые два года с момента разработки первого ЦП общего назначения с одним чипом в 1971 году. В последние годы традиционный подход к увеличению емкости вычислительной обработки с более быстрым ЦП был дополнен созданием компьютеров с несколькими ЦП. На момент написания архитектура ЦП Intel Nehalem включает до восьми ядер на ЦП, которые при использовании в восьмисокетной системе могут быть увеличены до 128 логических процессоров с помощью технологии гиперпоточности (SMT). На ЦП Intel SMT называется Hyper-Threading. По мере увеличения числа логических процессоров на компьютерах, совместимых с x86, проблемы, связанные с параллелизмом, увеличиваются по мере того, как логические процессоры конкурируют за ресурсы. В этом руководстве описывается, как выявлять и устранять специфические проблемы с конкуренцией ресурсов, наблюдаемые при выполнении приложений SQL Server в многопоточных системах с определенными рабочими нагрузками.

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

Симптомы и причины блокировки спинлока

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

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

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

Внутренние изменения, внесенные в механизм работы СУБД в версии SQL Server 2022 (16.x), делают спинлоки более эффективными.

Симптомы

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

Замечание

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

SELECT *
FROM sys.dm_os_spinlock_stats
ORDER BY spins DESC;

Снимок экрана, показывающий выходные данные `sys.dm_os_spinlock_stats`.

Статистика, предоставленная этим запросом, описана следующим образом:

колонна Описание
Столкновений Это значение увеличивается каждый раз, когда поток блокирует доступ к ресурсу, защищенному спинлоком.
Вращения Это значение увеличивается каждый раз, когда поток выполняет цикл в ожидании освобождения спинлока. Это мера объема работы потока, пока он пытается получить ресурс.
Спины_на_столкновение Соотношение спинов на одно столкновение.
Время сна Однако это не относится к событиям резервирования; и не касается методов, описанных в этой статье.
Откладывания Возникает, когда поток "спиннинга", который пытается получить доступ к удерживаемом ресурсу, определил, что он должен разрешить выполнение других потоков на том же ЦП.

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

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

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

На следующей схеме представлено концептуальное представление алгоритма спинлока:

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

Типичные сценарии

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

Главный симптом, связанный с конкуренцией спинлоков, заключается в высоком потреблении ЦП из-за большого количества спинов и множества потоков, пытающихся получить один и тот же спинлок. Как правило, это наблюдалось в системах с 24 и более ядрами ЦП, и чаще всего в системах с более чем 32 ядрами ЦП. Как уже говорилось ранее, некоторый уровень спора за спинлоки является нормальным для высокопараллельных систем OLTP с значительной нагрузкой, и часто сообщается о большом количестве спинов (миллиарды/триллионы) из sys.dm_os_spinlock_stats DMV на системах, работающих в течение длительного времени. Опять же, наблюдение за большим количеством спинов любого данного типа спинлока не предоставляет достаточно информации для определения, есть ли негативное влияние на производительность рабочей нагрузки.

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

  • Большое количество спинов и откатов наблюдается для определенного типа спинлока.

  • В системе наблюдается высокая загрузка ЦП или пики потребления ЦП. При высокой нагрузке на ЦП, вы видите ожидания с высоким приоритетом на SOS_SCHEDULER_YIELD (сообщается DMV sys.dm_os_wait_stats).

  • Система испытывает высокую конкуренцию.

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

Одним из распространенных явлений, которые легко диагностировать, является значительное расхождение в пропускной способности и использовании ЦП. Многие рабочие нагрузки OLTP связаны между (пропускной способностью и числом пользователей в системе) и потреблением ЦП. Частое вращение, наблюдаемое в сочетании со значительным расхождением потребления ЦП и производительности, может быть признаком спора за спин-блокировку, что приводит к накладным расходам на ЦП. Важно отметить, что часто наблюдается такой тип расхождения в системах, когда определенные запросы становятся более затратными со временем. Например, запросы, выданные для наборов данных, выполняющих более логические операции чтения с течением времени, могут привести к аналогичным симптомам.

Это важно

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

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

Ниже приведены некоторые из наиболее распространенных причин увеличения потребления ЦП:

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

Примеры

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

Снимок экрана, показывающий снижение производительности ЦП в мониторе.

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

Снимок экрана, показывающий диаграмму спинов с интервалами в 3 минуты.

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

К некоторым сценариям, которые подвержены этой проблеме, относятся:

  • Проблемы разрешения имен, вызванные сбоем в полной квалификации имен объектов. Дополнительные сведения см. в описании блокировки SQL Server, вызванной блокировками компиляции. Эта конкретная проблема подробно описана в этой статье.

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

  • Высокая частота транзакций DTC в условиях высокой задержки между координаторами транзакций MSDTC. Эта конкретная проблема подробно описана в записи блога SQLCAT Разрешение связанного с DTC ожидания и настройка масштабируемости DTC.

Диагностика конфликтов спинлоков

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

Инструмент Использование
Мониторинг производительности. Найдите высокие условия ЦП или расхождение между пропускной способностью и потреблением ЦП.
Статистика спинлока Запросите DMV sys.dm_os_spinlock_stats, чтобы обнаружить большое количество вращений и событий обратного выхода в течение определённых периодов времени.
Статистика ожидания Начиная с предварительной версии SQL Server 2025 (17.x), запросите динамические административные представления sys.dm_os_wait_stats и sys.dm_exec_session_wait_stats с помощью типа ожидания SPINLOCK_EXT. Требуется флаг трассировки 8134. Дополнительные сведения см. в SPINLOCK_EXT.
Расширенные события SQL Server Используется для отслеживания стеков вызовов для спинлоков, которые испытывают большое количество спинов.
Дампы памяти В некоторых случаях дампы памяти процесса SQL Server и средств отладки Windows. Как правило, этот уровень анализа выполняется при задействовании групп поддержки Майкрософт.

Общий технический процесс диагностики конкуренции спинлоков в SQL Server:

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

  2. Шаг 2: Соберите статистику от sys.dm_os_spinlock_stats, чтобы определить тип спинлока, испытывающий наибольшее разногласие.

  3. Шаг 3. Получение символов отладки для sqlservr.exe (sqlservr.pdb) и размещение символов в том же каталоге, что и файл службы SQL Server .exe (sqlservr.exe) для экземпляра SQL Server.\ Для просмотра стеков вызовов для событий обратного выхода необходимо иметь символы для конкретной версии SQL Server, которую вы используете. Символы SQL Server доступны на сервере символов Майкрософт. Дополнительные сведения о том, как скачать символы с сервера символов Майкрософт, см. в разделе Отладка с помощью символов.

  4. Шаг 4: Используйте расширенные события SQL Server для трассировки событий отката, которые касаются интересующих типов спинлоков. События, которые необходимо записать, это spinlock_backoff и spinlock_backoff_warning.

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

Пошаговое руководство по диагностике

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

Симптомы

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

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

Снимок экрана, показывающий рост производительности ЦП в мониторе.

После запроса sys.dm_os_spinlock_stats, чтобы определить наличие значительных конфликтов SOS_CACHESTORE, скрипт расширенных событий использовался для измерения количества событий отката для типов спинлоков, представляющих интерес.

Имя Столкновений Спины Спины на одно столкновение Задержки
SOS_CACHESTORE 14,752,117 942,869,471,526 63,914 67,900,620
SOS_SUSPEND_QUEUE 69,267,367 473,760,338,765 6840 2,167,281
LOCK_HASH 5,765,761 260,885,816,584 45,247 3,739,208
MUTEX 2,802,773 9,767,503,682 3,485 350,997
SOS_SCHEDULER 1,207,007 3,692,845,572 3,060 109,746

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

Дополнительные сведения о расширенных событиях в SQL Server см. в обзоре расширенных событий.

Скрипт

/*
This script is provided "AS IS" with no warranties, and confers no rights.

This script will monitor for backoff events over a given period of time and
capture the code paths (callstacks) for those.

--Find the spinlock types
select map_value, map_key, name from sys.dm_xe_map_values
where name = 'spinlock_types'
order by map_value asc

--Example: Get the type value for any given spinlock type
select map_value, map_key, name from sys.dm_xe_map_values
where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')

Examples:
61LOCK_HASH
144 SOS_CACHESTORE
08MUTEX

*/
--create the even session that will capture the callstacks to a bucketizer
--more information is available in this reference: http://msdn.microsoft.com/en-us/library/bb630354.aspx
CREATE EVENT SESSION spin_lock_backoff ON SERVER
ADD EVENT sqlos.spinlock_backoff (
    ACTION(package0.callstack) WHERE type = 61 --LOCK_HASH
    OR TYPE = 144 --SOS_CACHESTORE
    OR TYPE = 8 --MUTEX
) ADD TARGET package0.asynchronous_bucketizer (
    SET filtering_event_name = 'sqlos.spinlock_backoff',
    source_type = 1,
    source = 'package0.callstack'
)
WITH (
    MAX_MEMORY = 50 MB,
    MEMORY_PARTITION_MODE = PER_NODE
);

--Ensure the session was created
SELECT * FROM sys.dm_xe_sessions
WHERE name = 'spin_lock_backoff';

--Run this section to measure the contention
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = START;

--wait to measure the number of backoffs over a 1 minute period
WAITFOR DELAY '00:01:00';

--To view the data
--1. Ensure the sqlservr.pdb is in the same directory as the sqlservr.exe
--2. Enable this trace flag to turn on symbol resolution
DBCC TRACEON (3656, -1);

--Get the callstacks from the bucketizer target
SELECT event_session_address,
    target_name,
    execution_count,
    cast(target_data AS XML)
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
    ON (xst.event_session_address = xs.address)
WHERE xs.name = 'spin_lock_backoff';

--clean up the session
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = STOP;
DROP EVENT SESSION spin_lock_backoff ON SERVER;

Анализируя выходные данные, можно увидеть стеки вызовов для наиболее распространенных путей кода для SOS_CACHESTORE спинов. Скрипт был запущен несколько раз в течение времени, когда загрузка ЦП была высокой, чтобы проверить согласованность в возвращаемых стеках вызовов. Стеки вызовов с наибольшим числом сегментов слотов являются общими для двух выходных данных (35 668 и 8506). Эти стеки вызовов имеют количество слотов, которое на два порядка величины больше, чем у следующей по величине записи. Это условие указывает на путь выполнения кода, представляющий интерес.

Замечание

Видеть стеки вызовов, возвращаемые предыдущим скриптом, — не редкость. Когда сценарий выполнялся в течение 1 минуты, мы заметили, что стеки вызовов с числом слотов > 1,000 были проблематичными, а число слотов > 10,000 было еще более проблематичным, так как это более высокое количество слотов.

Замечание

Форматирование следующих выходных данных было очищено в целях удобочитаемости.

Выходные данные 1

<BucketizerTarget truncated="0" buckets="256">
<Slot count="35668" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
      SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid
      CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey
      CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      NTGroupInfo::`vector deleting destructor'
  </value>
</Slot>
<Slot count="752" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
      SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey             CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
  </value>
  </Slot>

Выходные данные 2

<BucketizerTarget truncated="0" buckets="256">
<Slot count="8506" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep+c7 [ @ 0+0x0 SpinlockBase::Backoff Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      NTGroupInfo::`vector deleting destructor'
</value>
 </Slot>
<Slot count="190" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
       SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
   </value>
 </Slot>

В предыдущем примере наиболее интересные стеки имеют наибольшее количество слотов (35 668 и 8506), которые, на самом деле, имеют число слотов больше 1000.

Теперь вопрос может быть: "Что делать с этой информацией"? Как правило, для использования сведений о вызовах требуется глубокое знание подсистемы SQL Server, поэтому на этом этапе процесс устранения неполадок перемещается в серую область. В этом конкретном случае, просматривая стеки вызовов, мы видим, что путь кода, в котором возникает проблема, связан с безопасностью и поиском метаданных (как видно в следующих кадрах стека CMEDCatalogOwner::GetProxyOwnerBySID & CMEDProxyDatabase::GetOwnerBySID)).

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

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

Варианты и обходные пути

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

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

При разработке высокопроизводительных приложений, требующих крайней производительности и масштабирования, следует учитывать, как обеспечить максимально короткий путь кода в SQL Server. Более короткий путь кода означает, что меньше работы выполняется ядром СУБД и, естественно, избегает точек конфликтов. Многие лучшие практики имеют побочный эффект в виде сокращения объема работы, требуемого от системы, и, таким образом, оптимизируют производительность рабочей нагрузки.

Использование нескольких рекомендаций из предыдущей статьи в качестве примеров:

  • Полные имена: Полное определение имен всех объектов приведет к удалению необходимости в SQL Server выполнять пути кода, необходимые для разрешения имен. Мы также наблюдали точки состязания в типе спинлока SOS_CACHESTORE, возникающие при неиспользовании полных имен в вызовах хранимых процедур. Неполная квалификация этих имен приводит к необходимости для SQL Server определения схемы по умолчанию для пользователя, что, в свою очередь, удлиняет маршрут выполнения кода, необходимый для выполнения команды SQL.

  • Параметризованные запросы: Другой пример — использование параметризованных запросов и вызовов хранимых процедур для уменьшения работы, необходимой для создания планов выполнения. Это снова приводит к более короткому пути кода для выполнения.

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

Первая строка защиты при настройке рабочих нагрузок SQL Server — это всегда стандартные методики настройки (например, индексирование, оптимизация запросов, оптимизация ввода-вывода и т. д.). Однако, помимо обычной оптимизации, важным подходом является следование практикам, которые сокращают объем кода, необходимого для выполнения операций. Даже если соблюдаются передовые практики, всё ещё есть вероятность возникновения конфликтов спинлока на загруженных системах с высоким параллелизмом. Использование средств и методов в этой статье может помочь изолировать или исключить эти типы проблем и определить, когда необходимо привлечь нужные ресурсы Майкрософт для помощи.

Приложение. Автоматизация записи дампа памяти

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

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

/*
This script is provided "AS IS" with no warranties, and confers no rights.

Use:    This procedure will monitor for spinlocks with a high number of backoff events
        over a defined time period which would indicate that there is likely significant
        spin lock contention.

        Modify the variables noted below before running.

Requires:
        xp_cmdshell to be enabled
            sp_configure 'xp_cmd', 1
            go
            reconfigure
            go

*********************************************************************************************************/
USE tempdb;
GO

IF object_id('sp_xevent_dump_on_backoffs') IS NOT NULL
    DROP PROCEDURE sp_xevent_dump_on_backoffs;
GO

CREATE PROCEDURE sp_xevent_dump_on_backoffs (
    @sqldumper_path NVARCHAR(max) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"',
    @dump_threshold INT = 500, --capture mini dump when the slot count for the top bucket exceeds this
    @total_delay_time_seconds INT = 60, --poll for 60 seconds
    @PID INT = 0,
    @output_path NVARCHAR(MAX) = 'c:\',
    @dump_captured_flag INT = 0 OUTPUT
)
AS
/*
    --Find the spinlock types
    select map_value, map_key, name from sys.dm_xe_map_values
    where name = 'spinlock_types'
    order by map_value asc

    --Example: Get the type value for any given spinlock type
    select map_value, map_key, name from sys.dm_xe_map_values
    where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')
*/
IF EXISTS (
        SELECT *
        FROM sys.dm_xe_session_targets xst
        INNER JOIN sys.dm_xe_sessions xs
            ON (xst.event_session_address = xs.address)
        WHERE xs.name = 'spinlock_backoff_with_dump'
        )
    DROP EVENT SESSION spinlock_backoff_with_dump
        ON SERVER

CREATE EVENT SESSION spinlock_backoff_with_dump ON SERVER
ADD EVENT sqlos.spinlock_backoff (
    ACTION(package0.callstack) WHERE type = 61 --LOCK_HASH
    --or type = 144           --SOS_CACHESTORE
    --or type = 8             --MUTEX
    --or type = 53            --LOGCACHE_ACCESS
    --or type = 41            --LOGFLUSHQ
    --or type = 25            --SQL_MGR
    --or type = 39            --XDESMGR
) ADD target package0.asynchronous_bucketizer (
    SET filtering_event_name = 'sqlos.spinlock_backoff',
    source_type = 1,
    source = 'package0.callstack'
)
WITH (
    MAX_MEMORY = 50 MB,
    MEMORY_PARTITION_MODE = PER_NODE
)

ALTER EVENT SESSION spinlock_backoff_with_dump ON SERVER STATE = START;

DECLARE @instance_name NVARCHAR(MAX) = @@SERVICENAME;
DECLARE @loop_count INT = 1;
DECLARE @xml_result XML;
DECLARE @slot_count BIGINT;
DECLARE @xp_cmdshell NVARCHAR(MAX) = NULL;

--start polling for the backoffs
PRINT 'Polling for: ' + convert(VARCHAR(32), @total_delay_time_seconds) + ' seconds';

WHILE (@loop_count < CAST(@total_delay_time_seconds / 1 AS INT))
BEGIN
    WAITFOR DELAY '00:00:01'

    --get the xml from the bucketizer for the session
    SELECT @xml_result = CAST(target_data AS XML)
    FROM sys.dm_xe_session_targets xst
    INNER JOIN sys.dm_xe_sessions xs
        ON (xst.event_session_address = xs.address)
    WHERE xs.name = 'spinlock_backoff_with_dump';

    --get the highest slot count from the bucketizer
    SELECT @slot_count = @xml_result.value(N'(//Slot/@count)[1]', 'int');

    --if the slot count is higher than the threshold in the one minute period
    --dump the process and clean up session
    IF (@slot_count > @dump_threshold)
    BEGIN
        PRINT 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(NVARCHAR(max), @PID) + ' 0 0x800 0 c:\ '''

        SELECT @xp_cmdshell = 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(NVARCHAR(max), @PID) + ' 0 0x800 0 ' + @output_path + ' '''

        EXEC sp_executesql @xp_cmdshell

        PRINT 'loop count: ' + convert(VARCHAR(128), @loop_count)
        PRINT 'slot count: ' + convert(VARCHAR(128), @slot_count)

        SET @dump_captured_flag = 1

        BREAK
    END

    --otherwise loop
    SET @loop_count = @loop_count + 1
END;

--see what was collected then clean up
DBCC TRACEON (3656, -1);

SELECT event_session_address,
    target_name,
    execution_count,
    cast(target_data AS XML)
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
    ON (xst.event_session_address = xs.address)
WHERE xs.name = 'spinlock_backoff_with_dump';

ALTER EVENT SESSION spinlock_backoff_with_dump ON SERVER STATE = STOP;
DROP EVENT SESSION spinlock_backoff_with_dump ON SERVER;
GO

/* CAPTURE THE DUMPS
******************************************************************/
--Example: This will run continuously until a dump is created.
DECLARE @sqldumper_path NVARCHAR(MAX) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"';
DECLARE @dump_threshold INT = 300; --capture mini dump when the slot count for the top bucket exceeds this
DECLARE @total_delay_time_seconds INT = 60; --poll for 60 seconds
DECLARE @PID INT = 0;
DECLARE @flag TINYINT = 0;
DECLARE @dump_count TINYINT = 0;
DECLARE @max_dumps TINYINT = 3; --stop after collecting this many dumps
DECLARE @output_path NVARCHAR(max) = 'c:\'; --no spaces in the path please :)
--Get the process id for sql server
DECLARE @error_log TABLE (
    LogDate DATETIME,
    ProcessInfo VARCHAR(255),
    TEXT VARCHAR(max)
);

INSERT INTO @error_log
EXEC ('xp_readerrorlog 0, 1, ''Server Process ID''');

SELECT @PID = convert(INT, (REPLACE(REPLACE(TEXT, 'Server Process ID is ', ''), '.', '')))
FROM @error_log
WHERE TEXT LIKE ('Server Process ID is%');

PRINT 'SQL Server PID: ' + convert(VARCHAR(6), @PID);

--Loop to monitor the spinlocks and capture dumps. while (@dump_count < @max_dumps)
BEGIN
    EXEC sp_xevent_dump_on_backoffs @sqldumper_path = @sqldumper_path,
        @dump_threshold = @dump_threshold,
        @total_delay_time_seconds = @total_delay_time_seconds,
        @PID = @PID,
        @output_path = @output_path,
        @dump_captured_flag = @flag OUTPUT

    IF (@flag > 0)
        SET @dump_count = @dump_count + 1

    PRINT 'Dump Count: ' + convert(VARCHAR(2), @dump_count)

    WAITFOR DELAY '00:00:02'
END;

Приложение: Сбор данных о статистике спинлоков в течение времени

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

/* Snapshot the current spinlock 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 '#_spin_waits%'
)
CREATE TABLE #_spin_waits (
    lock_name VARCHAR(128),
    collisions BIGINT,
    spins BIGINT,
    sleep_time BIGINT,
    backoffs BIGINT,
    snap_time DATETIME
);

--capture the current stats
INSERT INTO #_spin_waits (
    lock_name,
    collisions,
    spins,
    sleep_time,
    backoffs,
    snap_time
    )
SELECT name,
    collisions,
    spins,
    sleep_time,
    backoffs,
    @current_snap_time
FROM sys.dm_os_spinlock_stats;

SELECT TOP 1 @previous_snap_time = snap_time
FROM #_spin_waits
WHERE snap_time < (
    SELECT max(snap_time)
    FROM #_spin_waits
)
ORDER BY snap_time DESC;

--get delta in the spin locks stats
SELECT TOP 10 spins_current.lock_name,
    (spins_current.collisions - spins_previous.collisions) AS collisions,
    (spins_current.spins - spins_previous.spins) AS spins,
    (spins_current.sleep_time - spins_previous.sleep_time) AS sleep_time,
    (spins_current.backoffs - spins_previous.backoffs) AS backoffs,
    spins_previous.snap_time AS [start_time],
    spins_current.snap_time AS [end_time],
    DATEDIFF(ss, @previous_snap_time, @current_snap_time) AS [seconds_in_sample]
FROM #_spin_waits spins_current
INNER JOIN (
    SELECT *
    FROM #_spin_waits
    WHERE snap_time = @previous_snap_time
    ) spins_previous
    ON (spins_previous.lock_name = spins_current.lock_name)
WHERE spins_current.snap_time = @current_snap_time
    AND spins_previous.snap_time = @previous_snap_time
    AND spins_current.spins > 0
ORDER BY (spins_current.spins - spins_previous.spins) DESC;

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

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