Каталог решений - Поиск причин блокировок СУБД

Вступление

Количество пользователей нашей основной базы данных активно увеличивается за счет слияния второстепенных баз. Динамика примерно такая: 2020 – 150, 2021 – 300, 2022 – 500 (план). Поэтому оптимизация и быстродействие для нас важны: при увеличении числа пользователей проблемы растут нелинейно. Одно время даже хотели заключить договор ЦКТП, но пока справляемся своими силами.

Был один случай..

.. участились ошибки блокировки СУБД.

Анализ типов блокировок на сервере СУБД (за пол-дня). Ожидания LCK_M_X, LCK_M_U занимают доминирующие позиции.

Что делать ?

Нашел видео по ключевым словам «блокировки, эксперт», автор — Виктор Богачев. Составил краткий конспект

0-3 Вступление, рабочие инструменты

3-4 Поиск блокировок по dmv

4-5 Причина блокировок — отсутствует подходящий индекс

5-8 План запроса, поиск запроса в 1С, используя logcfg

8-10 Ключ dbmslocks

10-12 Заключение, контакты

Решил посмотреть в СУБД. В видео использован запрос

  SELECT
  db.name,
  a.session_id,
  a.blocking_session_id,
  a.transaction_id,
  a.wait_time,
  a.wait_type,
  st.text,
  qp.query_plan
  FROM sys.dm_exec_requests AS a
  OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) AS st
  OUTER APPLY sys.dm_exec_query_plan(a.sql_handle) AS qp
  LEFT JOIN sys.sysprocesses AS p
  OUTER APPLY sys.dm_exec_sql_text(p.sql_handle) AS stblock
  ON a.blocking_session_id > 0 AND a.blocking_session_id = p.spid
  LEFT JOIN sys.databases db
  ON a.database_id = db.database_id
  WHERE NOT a.status IN ('background', 'sleeping')
  ORDER BY a.cpu_time DESC

представление sys.dm_exec_requests показывает выполняемые запросы в реальном времени, оно содержит поле blocking_session_id которое указывает на блокирующую сессию. (Доступна документация)

Как я уже говорил, у нас большая компания с развитой инфраструктурой. Физически невозможно иметь доступ ко всем серверам компании. Получение информации с сервера целевой СУБД — через системного администратора. Поэтому просматривать состояние базы данных в реальном времени с помощью динамических представлений было неудобно. Мы не знали — в какой момент происходит блокировка. Договорились, что системный администратор соберет extended events по описанию (Спасибо, Юрий Пермитин) но напрямую задачу решить не удалось — события блокировок в СУБД происходят настолько часто, что производительность сервера упала. Составил более подробное описание, с фильтрами по длительности.

Количество логируемых событий сократилось до приемлемого уровня. Оказалось неожиданностью: событие lock_timeout тоже нуждается в ограничении по длительности. Оказывается, при соединении внутренних процессов с tempdb, команда SET LOCK_TIMEOUT timeout_period не выполняется, и работа с временными таблицами #tt сопровождается lock_timeout, длительность=0. Файл записался при остановке сессии сбора данных. В результате я получил файл и открыл его Management Studio на тестовом сервере.

События lock_timeout, фрагменты sql_text

1. DELETE FROM T1 FROM dbo._InfoRg18447 T1 WHERE (T1._Fld18454RRef = @P1) AND (T1._Fld1420 = @P2)

С помощью функции ПолучитьСтруктуруБазыДанных() нашел имя регистра и имя поля. Как вы догадываетесь, индекс поля отсутствует. Очень похоже на ситуацию из видео на минуте 7. Для поля поставил значение «индексировать», флаг «ведущее» использовать не стал: могут быть пустые значения. Кстати, до добавления индекса события lock_deadlock на таблице dbo._InfoRg18447 тоже происходили. Причина блокировок СУБД подробно описана в видео: отсутствует индекс, происходит сканирование (с захватом) всей таблицы в режиме исключительной блокировки. При этом управляемые блокировки не учитывают план запроса, блокируют по значениям конкретных полей, конфликта между ними не происходит.

2. UPDATE T1 SET _Description = @P1, …. FROM dbo._ScheduledJobs31390 T1 WHERE T1._ID = @P15 AND T1._Version = @P16

Может показаться, что не хватает индекса по полю Version. Я даже полез на сайте смотреть возможные индексы https://its.1c.ru/db/metod8dev/content/1590/hdoc, но потом сообразил, что это регламентное задание. Такие таблицы содержат только одну строку, индекс не нужен. Регламентное задание запускалось в устарелой версии УТ при открытии программы, для целей rls. В новой версии его можно отключить.

3. Было несколько ошибок блокировок, связанных с шиной обмена Datareon. При зависании службы шины, отслеживаемые события в 1С не записывались, создавали lock_timeout. Эта ситуация была исправлена без моего участия.

События lock_escalation

Иногда происходит эскалация при работе с ценами по регистру «Цены номенклатуры» и табличной части документа «Установка цен». Документы больше 5000 строк табличной части, решение этой проблемы лежит в области организации процессов.

Итоги работы

Стало заметно лучше. Ниже анализ ожиданий на сервере СУБД за неделю, суммарная длительность ожиданий LCK_M_X, LCK_M_U составляет 18 минут, до начала работ суммарная длительность ожиданий могла быть до 2 часов в день.

P.S. Чтобы счастье было полным

настроил технологический журнал для dbmslocks (важно, что события собираются без отборов).

  <?xml version="1.0"?>
  <config xmlns="http://v8.1c.ru/v8/tech-log">
  <log location="C:\Log\Logs\UT" history="24">
  <event>
  <eq property="name" value="DBMSSQL"/>
  <eq property="p:processName" value="My_Database_Name"/>
  </event>
  <property name="all"/>
  <property name="lkaid"/>
  <property name="lka"/>
  <property name="lkp"/>
  <property name="lkpid"/>
  <property name="lksrc"/>
  </log>
  <dbmslocks />
  </config>

Свойства описаны на сайте ИТС.