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

← На главную

Рассмотрим весьма распространенную и неприятную ситуацию при работе с базами данных 1С на базе СУБД MS SQL Server. Вы настроили систему прав доступа на уровне записей (RLS). Однако в процессе эксплуатации выясняется, что у одних пользователей списки и документы открываются мгновенно, а у других тот же самый список вызывает катастрофические зависания базы. При этом права могут быть настроены практически идентично, различается лишь набор доступных элементов (групп, подразделений, организаций).

Если мы преобразуем шаблон RLS в обычный запрос для тестирования, то увидим примерно следующую картину:


ВЫБРАТЬ ПЕРВЫЕ 50
    ТекущаяТаблица.Ссылка,
    ТекущаяТаблица.Реквизит1
ИЗ
    Документ.ТаблицаККоторойНеобходимоПолучитьДоступ КАК ТекущаяТаблица
        ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ РАЗЛИЧНЫЕ
            СоставГруппы.Ссылка КАК ГруппаПользователей
        ИЗ
            Справочник.ГруппыПользователей.ПользователиГруппы КАК СоставГруппы
        ГДЕ
            СоставГруппы.Пользователь = &ТекущийПользователь) КАК ГруппыПользователей
        ПО (ИСТИНА)
ГДЕ
    НЕ ГруппыПользователей.ГруппаПользователей ЕСТЬ NULL

Проанализируем профилировщик SQL Server (Profiler) для обеих ситуаций. Часто можно заметить, что план выполнения запроса визуально абсолютно одинаковый. Разница кроется лишь в атрибутах: у «быстрого» пользователя ожидаемое количество строк (Estimated number of rows) примерно совпадает с фактическим (Actual number of rows). А вот у «медленного» пользователя фактическое количество строк может исчисляться сотнями тысяч или миллионами, вызывая многократные циклические операции. Разберем по шагам, почему так происходит и как с этим бороться.

Причина проблемы: Parameter Sniffing и Data Skew

Выясним причину такого поведения оптимизатора запросов. Мы сталкиваемся с классической проблемой реляционных баз данных, которая называется Parameter Sniffing (прослушивание параметров) и Data skew (перекос данных).

Когда запрос выполняется сервером в первый раз, оптимизатор SQL Server оценивает входные параметры (в нашем случае это конкретный &ТекущийПользователь и его группы доступа). На основе этих параметров и статистики таблиц СУБД строит оптимальный план выполнения запроса. Если запрос впервые выполнил пользователь с малым объемом доступных данных, оптимизатор решает, что выборка будет небольшой, и применяет алгоритм Nested Loops (вложенные циклы). Этот план кэшируется в оперативной памяти сервера.

Затем в базу заходит пользователь с обширными правами, для которого выборка исчисляется десятками тысяч строк. Сервер берет уже готовый закэшированный план (с Nested Loops) и начинает выполнять его. Для огромных массивов данных вложенные циклы крайне неэффективны — сервер начинает перемножать итерации на миллионы строк таблицы, что и приводит к падению скорости в сотни раз.

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

Решение 1: Обновление статистики и очистка процедурного кэша

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

Проведем процедуру обновления статистики. Можно использовать стандартную команду, которая обновляет статистику по всем таблицам и сбрасывает закэшированные планы:

EXEC sp_updatestats;

Или выполним очистку кэша вручную:


DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

Важное замечание: Это решение носит лишь временный характер. Как только процедурный кэш очистится, план сформируется заново. Но если первым запрос снова выполнит «быстрый» пользователь с малым набором прав, неоптимальный план снова зафиксируется в кэше, и проблема вернется.

Решение 2: Использование флагов трассировки SQL Server

Если нет возможности оперативно изменить конфигурацию 1С, попробуем повлиять на поведение самого SQL Server. Отключим жесткую привязку планов к параметрам первого вызова (Parameter Sniffing) с помощью флагов трассировки.

В частности, существует Trace Flag 4136, который заставляет оптимизатор при построении плана использовать не значения конкретных переданных параметров, а усредненную плотность статистики по таблице. Это приводит к построению более консервативного, но безопасного плана (например, с использованием Hash Match вместо Nested Loops), который будет стабильно (пусть и не сверхбыстро) работать для любых пользователей.

Решение 3: Архитектурный рефакторинг — переход на «Производительный RLS»

Для современных систем (например, на базе Библиотеки Стандартных Подсистем) рекомендуется применять «Производительный RLS». Стандартный (типовой) RLS из старых конфигураций в момент запроса каждый раз приклеивает огромные конструкции левых соединений с регистрами настроек прав, что заставляет SQL Server каждый раз собирать права "на лету".

В производительном RLS логика иная: вычисление прав доступа происходит не в момент чтения, а в момент записи объекта (документа, справочника). Платформа формирует специальный числовой или строковый «ключ доступа» для объекта и сохраняет его в служебный регистр сведений. Таким образом, сложный шаблон RLS превращается в примитивное внутреннее соединение с узкой таблицей регистра по готовому ключу. SQL-сервер переваривает такие соединения моментально.

Решение 4: Оптимизация RLS через параметры сеанса (Рекомендуемое решение для старых конфигураций)

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

Создадим в дереве конфигурации новые объекты ПараметрыСеанса. Зададим им тип ФиксированныйМассив. Назовем их, например, ДоступныеОрганизации, ДоступныеПодразделения и т.д.
В модуле сеанса, при старте системы (событие УстановкаПараметровСеанса), выполним тяжелый запрос, который вычисляет все разрешенные для текущего пользователя элементы. Сформируем из них массивы и запишем в созданные параметры сеанса. Для обладателей полных прав можно передавать пустое значение или специальный флаг.
Теперь перепишем громоздкие шаблоны RLS в ролях. Заменим многоэтажные подзапросы с соединениями на элементарное условие вхождения в массив.

Новый шаблон RLS станет выглядеть предельно просто:


ГДЕ ТекущаяТаблица.Организация В (&ДоступныеОрганизации)
    И ТекущаяТаблица.Подразделение В (&ДоступныеПодразделения)

Такой подход радикально снижает нагрузку на SQL Server. Оптимизатору больше не нужно соединять таблицы динамически, он просто сверяет поле с переданным списком значений, что полностью исключает проблему Data skew в соединениях.

Решение 5: Ревизия и укрупнение групп доступа (Уменьшение Data Skew)

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

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