В процессе администрирования крупных баз данных на платформе 1С:Предприятие 8 под управлением СУБД MS SQL Server системные администраторы и разработчики часто сталкиваются с ситуацией, когда сервер СУБД начинает испытывать предельную нагрузку на дисковую подсистему или процессор. При анализе активности через SQL Profiler или Extended Events обнаруживаются крайне неоптимальные запросы, выполняющие миллионы логических чтений (Reads > 1 000 000). Характерный признак таких запросов — наличие конструкции SELECT TOP 25 в сочетании с многочисленными условиями LIKE '%значение%'.
В этой статье мы подробно разберем природу подобных запросов, научимся безошибочно определять их контекст в коде 1С с помощью Технологического журнала и СУБД, а также рассмотрим практические методы оптимизации динамических списков для устранения тормозов.
Давайте проанализируем структуру запроса, представленного в исходном обращении. В нем фигурирует выборка первых 25 записей (SELECT TOP 25) из таблицы документов _Document404 (в конфигурации это документ "Счет на оплату покупателю"), к которой через левое соединение (LEFT OUTER JOIN) привязывается таблица регистра сведений _InfoRg22139 ("СтатусыДокументов") и несколько таблиц справочников (_Reference35, _Reference63, _Reference90, _Reference116), отвечающих за связанные реквизиты (Контрагенты, Договоры, Организации, Валюты).
В секции фильтрации WHERE мы видим конструкцию следующего вида:
WHERE ((T1._Fld872 = @P8))
AND ((((((((((T3._Description LIKE @P9 ESCAPE '/'')
OR (T4._Description LIKE @P10 ESCAPE '/''))
OR (T1._Fld11864 LIKE @P11 ESCAPE '/''))
OR (T5._Description LIKE @P12 ESCAPE '/''))
OR (T1._Number LIKE @P13 ESCAPE '/''))
OR (T6._Description LIKE @P14 ESCAPE '/''))
OR ((T1._Fld11869 >= @P15) AND (T1._Fld11869 < @P16))))
Этот шаблон указывает на то, что перед нами типичный запрос, генерируемый платформой 1С для Динамического списка (ДС). Такое происходит, когда пользователь открывает форму списка документов (например, журнал счетов на оплату) и вводит поисковое значение в строку быстрого поиска (или использует сочетание клавиш Ctrl+F).
Платформа пытается выполнить полнотекстовый поиск "по всему списку" на уровне СУБД. Она транслирует введенное пользователем значение (в нашем примере это число/строка "2657") во все строковые реквизиты документа, представления связанных справочников, а также преобразует его в числовой диапазон для числовых реквизитов (>= 2657 AND < 2658).
Главная причина деградации производительности СУБД при выполнении таких запросов заключается в невозможности использования индексов для поиска по подстроке. Когда платформа формирует условие вида LIKE '%2657%', символ процента в начале значения блокирует операцию Index Seek (поиск по индексу). СУБД вынуждена выполнять операцию Index Scan или Table Scan (сканирование всей таблицы или индекса целиком) для основной таблицы документов и всех связанных справочников.
Если в базе данных содержатся сотни тысяч или миллионы документов, то один такой поиск приводит к колоссальному объему логического ввода-вывода (чтения с диска и из оперативной памяти). Ситуация усугубляется наличием сортировки ORDER BY (T1._Number) DESC, (T1._IDRRef) DESC. Чтобы вернуть первые 25 записей, соответствующих условию, СУБД сначала должна отсканировать всю таблицу, отфильтровать записи по условиям LIKE, а затем отсортировать результат. Это приводит к созданию временных таблиц в базе данных tempdb и резкому росту нагрузки на процессоры сервера.
---
Чтобы точно определить, какой именно пользователь запустил этот запрос, из какого компьютера и в какой форме списка это произошло, нам необходимо правильно настроить Технологический журнал (ТЖ) 1С. Автор темы на форуме пытался настроить сбор логов, но файлы ТЖ оставались пустыми. Рассмотрим ошибки в его конфигурационном файле logcfg.xml.
В его файле конфигурации была допущена критическая ошибка в условии фильтрации событий:
<eq property="Name" value=""/>
Такой фильтр заставлял систему искать события с пустым именем, которых в платформе 1С не существует. Кроме того, имя события СУБД в технологическом журнале чувствительно к регистру и должно записываться заглавными буквами: DBMSSQL.
Создадим правильный файл конфигурации logcfg.xml, который позволит перехватить данный тяжелый запрос, определить пользователя (Usr) и получить стек вызова (Context), указывающий на конкретную форму в конфигураторе.
Разместим в каталоге настроек платформы (обычно это C:\Program Files\1cv8\conf\ на сервере 1С) файл logcfg.xml со следующим содержимым:
<?xml version="1.0" encoding="UTF-8"?>
<config xmlns="http://v8.1c.ru/v8/tech-log">
<log location="C:\1C\logs\slow_queries" history="24">
<event>
<eq property="Name" value="DBMSSQL"/>
<!-- Отбираем запросы, которые выполнялись дольше 3 секунд (время в микросекундах) -->
<ge property="Duration" value="3000000"/>
<!-- Фильтруем логи по имени нашей проблемной таблицы документов -->
<like property="Sql" value="%_Document404%"/>
</event>
<!-- Свойство all необходимо для записи детального контекста вызова (стека 1С) -->
<property name="all"/>
</log>
</config>
После активации этой настройки платформа начнет записывать в указанный каталог файлы логов. В логе мы увидим событие, содержащее строку следующего вида:
35:12.124000-12,DBMSSQL,4,Usr=ИвановИИ,ClientComputerName=WORKSTATION01,
Sql="SELECT TOP 25 ... FROM dbo._Document404 ...",
Context="ФормаСписка : Документ.СчетНаОплатуПокупателю.Форма.ФормаСписка"
Благодаря параметрам Usr, ClientComputerName и Context мы мгновенно поймем, кто именно из пользователей запустил поиск и в какой форме списка находится этот динамический список.
---
Если тяжелый запрос выполняется прямо сейчас и сервер "завис", мы можем определить виновника оперативно в режиме реального времени без настройки Технологического журнала. Разберем этот алгоритм по шагам:
sp_who2). Также можно выполнить запрос к системным представлениям:
SELECT r.session_id, r.status, r.cpu_time, r.logical_reads, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE t.text LIKE '%_Document404%';
session_id (это так называемый SPID — идентификатор сессии в SQL Server). Допустим, наш проблемный SPID равен 74.74, укажет нам имя пользователя, имя его компьютера, время начала сеанса и объем потребляемых ресурсов на сервере приложений 1С.---
В тексте запроса из SQL Profiler мы видим только физические имена таблиц и полей в СУБД (например, _Document404, _InfoRg22139, _Fld11865RRef). Для точного сопоставления этих имен с объектами метаданных в конфигураторе 1С выполним специальный программный код.
Создадим внешнюю обработку или воспользуемся обработкой "Групповая обработка справочников и документов" (или консолью кода) и выполним следующий скрипт:
ТаблицаСтруктуры = ПолучитьСтруктуруХраненияБазыДанных();
// Поиск имени таблицы документов
Для Каждого СтрокаТаблицы Из ТаблицаСтруктуры Цикл
Если СтрокаТаблицы.ИмяТаблицыХранения = "_Document404" Тогда
Сообщить("Таблица _Document404 — это объект: " + СтрокаТаблицы.ИмяМетаданных);
// Выведем информацию по полям таблицы
Для Каждого Поле Из СтрокаТаблицы.Поля Цикл
Если Поле.ИмяПоляХранения = "_Fld11864" Или Поле.ИмяПоляХранения = "_Fld11869" Тогда
Сообщить(" Поле " + Поле.ИмяПоляХранения + " — это реквизит: " + Поле.ИмяМетаданных);
КонецЕсли;
КонецЦикла;
КонецЕсли;
// Поиск имени таблицы регистра статусов документов
Если СтрокаТаблицы.ИмяТаблицыХранения = "_InfoRg22139" Тогда
Сообщить("Таблица _InfoRg22139 — это регистр: " + СтрокаТаблицы.ИмяМетаданных);
КонецЕсли;
КонецЦикла;
Этот метод позволяет точно сопоставить физические имена СУБД с метаданными вашей конфигурации 1С, что существенно упрощает поиск проблемных реквизитов, по которым строится соединение или выполняется полнотекстовый поиск.
---
Выяснив причину тормозов и обнаружив форму списка, в которой находится неоптимальный динамический список, перейдем к методам устранения проблемы. Рассмотрим три основных подхода.
По умолчанию платформа 1С пытается искать введенную пользователем строку по всем текстовым колонкам динамического списка, включая связанные справочники. Мы можем принудительно ограничить состав полей, по которым разрешено осуществлять поиск.
Номер и Контрагент).После этого платформа перестанет генерировать громоздкие конструкции LEFT OUTER JOIN ко всем связанным справочникам только ради выполнения условий LIKE при поиске, что кардинально снизит количество логических чтений на сервере СУБД.
Если пользователям жизненно необходим поиск по множеству полей (включая содержание услуг, комментарии и т.д.), стандартный поиск SQL через LIKE использовать нельзя. Вместо этого настроим и задействуем Полнотекстовый поиск (ППД) 1С.
Когда в системе включен и регулярно обновляется индекс ППД, платформа при вводе поискового запроса в динамическом списке не отправляет тяжелые LIKE '%2657%' в SQL Server. Вместо этого платформа:
WHERE T1._IDRRef IN (@P1, @P2, ... @P25). Этот запрос выполняется СУБД мгновенно с использованием первичного кластерного индекса (Clustered Index Seek).Для реализации этого метода убедимся, что в базе данных настроено регламентное задание «Обновление индекса ППД» (выполняется раз в несколько минут) и задание «Слияние индекса ППД» (выполняется раз в сутки).
Обратим внимание на секцию сортировки в исходном запросе: ORDER BY (T1._Number) DESC, (T1._IDRRef) DESC. Если поле "Номер" документа не проиндексировано на уровне СУБД, то даже при быстром поиске СУБД тратит много ресурсов на сортировку данных перед выдачей первых 25 строк.
Чтобы помочь оптимизатору СУБД быстро возвращать первые строки, выполним следующие действия в конфигураторе:
Номер установлено свойство «Индексировать» или «Индексировать с дополнительным упорядочиванием».Создание правильного индекса позволит серверу MS SQL Server осуществлять эффективную выборку первых 25 записей непосредственно из индекса, полностью исключая тяжелые сортировки во временных таблицах.