Каталог решений - Смотрим запросы 1С через Microsoft SQL Profiler по следам ошибок разработчиков, приводящих к проблемам производительности

Это продолжение статьи про распространенные ошибки разработчиков, в данном случае посмотрим, как выглядит  обработка  рассмотренных в этой статье "запросов" сервером MS SQL запросов из 1С. Также рассмотрим некоторые другие интересные ситуации.

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

Мы хотим показать, как выглядит оптимизация запросов из статьи о распространенных ошибках разработчиков с точки зрения SQL сервера.

Дополнительно мы надеемся, что по результатам прочтения статьи вы сможете:

1. Настроить и подключить профайлер
2. Получить/увидеть схему плана запроса, технологические данные — длительность (duration), нагрузку на CPU и др.
3. Сделать некоторую "грубую" оценку — оценить: стало лучше, не изменилось или хуже (в большинстве случаев этого достаточно). 

Мы ориентируемся на специалистов начального и среднего уровня. Возможно, даже господа сеньоры смогут почерпнуть для себя  что-то новое, по опыту общения с коллегами до публикации такие ситуации имели место.

I) Инструменты и подготовка.

SQL Server Profiler вам потребуется, чтобы понять, почему может тормозить запрос, проверить результаты оптимизации, когда анализа кода на языке 1С недостаточно. В большинстве случаев результирующий запрос выглядит монстром (почти 95% из-за RLS), и понять там что-либо можно с трудом. В этом случае вам может помочь SQL Sentry Plan Explorer, который поможет выделить самые жирные цепочки. Статей в интернете и на текущем ресурсе по этой тематике много или если вы уже знаете, как с ним обращаться, листайте дальше. Но для тех, кто не знает, это будет хорошим кратким описанием.

1. Начинаем и запускаем SQL Server Profiler.

1.1 Открываем Microsoft SQL Server Management Studio

1.2 Запускаем SQL Server Profiler и подключаемся к базе данных (вводим адрес, логин и пароль).

2. Настраиваем трассировку (события).

В открывшемся приложении сразу будет открыта форма настройки подключения. Если это не так, то нажмите "New trace …" (Новая трассировка) или "Ctrl+N".

2.1 На вкладке главное (General) смотрим настройки и сохраняем как шаблон, если нужно.

2.2 Переходим на вкладку события (Events Selection) и выполняем настройку захвата интересующих нас событий. Из выбранных по умолчанию оставляем RPC.Complated, остальные отключаем.

2.3 Добавляем еще следующие события из блока Performance: 

• Show plan XML Statistic Profile- схема плана запросов с данными статистики
• ShowPlan Text (Unencoded) — текстовое представление запроса (не обязательно)

3 Ставим отборы в профайлере

3.1 Ставим отбор по базе данных. Устанавливаем фильтр на имя базы данных DatabaseName. Если не видно, то нажмите флаг "Show all columns" и выберите требуемую колонку.

3.2. Ставим отбор по длительности Duration. Этот параметр вы можете поставить, чтобы искать длительные запросы или отсеять всякие мелкие служебные. При анализе нужно руководствоваться соображениями — чем меньше этот показатель, тем лучше. Фактически — это время выполнения запроса.

3.3 Ставим отбор по тексту. Устанавливаем по слову в тексте TextData. Будем искать по вхождению некоторого ключа. Обязательно поместите его в "%" (т.е. вхождение по подобно). Подставлять ключевое слово в запрос 1С и искать это ключевое слово в запросе в профайлере — это довольно популярный и удобный подход, и мы будем его использовать.

3.4. Дополнительные информационные поля. Добавляем еще поля

• RowCount – число записей, которые возвращает сервер.
• Reads — количество чтений, сколько данных было прочитано.
• Writes — количество записей, что было записано в нашем случае во временные таблицы.

3.5. Жмем кнопку "Run" (Запуск). Приложение можно сказать разделено на две части. В первой части таблица с основной информацией по запросу: Событие, Нагрузка CPU, Время выполнения (Duration) и т.д. По ним мы будем понимать/получать оценку в значениях/цифрах. Во второй части — будет приводиться информация по тексту запроса или плану запроса в графическом виде. Все картинки запросов по тексту ниже взяты из нее.

4. Запускаем SQL Sentry Plan Explorer

Как было сказано выше, этот инструмент будем использовать, когда план запроса сложный, чтобы выделить в нем самые жирные цепочки. Для выделения этих цепочек пользуйтесь фильтром "Filter" и изменяйте значение %. С увеличением этого параметра будут пропадать цепочки с "малым" вкладом в стоимость плана. Т.е. останутся только те, которые вносят самое большое влияние. 

Давайте рассмотрим, как в него загрузить данные чуть более подробно:

• кликнем левой кнопкой мышки на строке с событием "Show Plan XML" приложения MS SQL Profiler и в появившемся окне выберем опцию "Сохранить данные события… (Extract Event Data…)". Данные сохранятся в формате плана запроса "SQLPlan".

• Далее в приложении Plan Explorer открываем этот файл через "Открыть (Open)". В результате в данные сохраненного ранее плана загрузятся, и у вас появится возможность просмотра в более удобном варианте.

5. Запускаем тестируемого клиента

5.1. Открываем базу данных на платформе 1С. В нашем случае — это конфигурация ЕРП, некоторая версия с набором данных за хороший период. Запускаемся под администратором или под пользователем с ограничениями RLS (не забудьте для этого пользователя разрешить интерактивное открытие отчетов и обработок). Как вариант — можете добавить обработку в расширение и подключить ее.

5.2. Открываем консоль запросов (любую консоль запросов). И переходим к примерам. Сами примеры это текстовое представление динамического списка на языке запросов 1С с включением некоторого ключа (параметр "КлючЗапроса"), по которому далее будем искать в профайлере, т.ч. не забудьте его добавить.

II) Краткая информация про планы запросов

Прежде чем мы начнем смотреть, давайте введем несколько тезисов и рассмотрим краткую справку по операторам плана запросов. Мне понравилась статья на текущем ресурсе — "Зачем запросу план и кто его выполняет?"

Сначала давайте ответим на вопрос: Что же такое план запроса и зачем он нужен?

"План выполнения запроса — последовательность операций, необходимых для получения результата SQL-запроса в реляционной СУБД" — из Википедии

Т.е. на плане запроса мы видим, как SQL сервер получает и обрабатывает данные. И эта графическая (может, в формате структурированного текста) информация помогает нам выполнить оптимизацию. Однако, в рамках 1С нам доступен ограниченный набор операций по оптимизации, т.к. мы напрямую общаемся только с 1С.

Операторы:

• Sort (Сортировка) — сортирует данные
• Filter (Фильтр/Отбор) — фильтрует/отбирает данные согласно условию
• Compute Scalar (Вычислить) — вычисляет выражение
• Concatenation (Конкатенация) — объединяет все входные данные в один поток, в котором содержатся все данные (работает как оператор "ОБЪЕДИНИТЬ ВСЕ").
• Merge Interval (Объединение различные интервалов) — объединяет интервалы, выбирает только не пересекающиеся данные, различные.
• Select (Выбрать) — выбирает данные
• Insert (Вставить) — создает временную таблицу
• Key LookUp (Поиск ключа) — ищет ключ, если не хватает.
• Table Scan (Сканирует таблицу) — ходит по всей таблице, плохо для большого количества данных.
• Index Scan (Сканирует индекс) — считывает весь индекс.
• Index Seek (Поиск по индексу) — считывает только данные удовлетворяющие условию.
• Nested Loops (Соединение вложенным циклом) — пробегает по всем данным в цикле, работает всегда. Хорошо когда первая таблица маленькая, а вторая большая. При таком условии лучше Hash Match и Merge Join.
• Hash Match (Соединение хешированием) — для соединения использует хеш, при поиске по набору ключей, работает при операторе равно "=". Может потреблять много оперативной памяти и если ее не хватит, то полезет на диск в tempdb.
• Merge Join (Соединение слиянием) — самое быстрое слияние, требует сортировку в двух таблицах, а также наличие оператора равно "=". Хорошо на больших наборах данных.
• Stream Aggregate (Агрегирование) — вычисляет агрегирующую функцию MAX, MIN, SUM, AVG, COUNT. по агрегируемой колонке обычно в группировках. Требует обязательную сортировку данных.

Тезисы:

• Обычно чем проще и меньше структура плана запроса, тем лучше. 
• Чем меньше данных обрабатывает SQL Server тем лучше, т.е. чем меньше чтений и записей тем система быстрее работает. 
• Общая мудрость гласит, что поиск (seek) — это хорошо для производительности, поскольку он представляет собой прямой доступ SQL Server к требуемым строкам данных, в то время как сканирование (scan) — это плохо, поскольку он предполагает последовательное чтение индекса для извлечения большого числа строк, приводя к более медленной обработке.
• Соединения в плане запросов. Merge Join оптимально. Nested loops нормально для небольших данных. Hash match — присмотреться (возможно надо упорядочить сначала набор или индекс иметь на поля упорядочивания, по которым идет связь).
• Чем более высокая нагрузка, тем сложнее серверу выбрать наиболее оптимальный план запросов.

План запроса читается справа налево и сверху вниз. В плане для источников данных приводятся имена таблиц, которые находятся в таблицах SQL сервера. Чтобы получить обратное сопоставление (как они называются в 1С дереве конфигуратора), нужно воспользоваться специальной обработкой или функцией "ПолучитьСтруктуруХраненияБазыДанных" — возвращает таблицу значений с описаниями структуры таблиц, индексов и полей базы данных в терминах модели базы данных 1С:Предприятия или используемой СУБД.

III) Ситуации
 

1. Некоторые базовые простые ситуации

В данных примерах мы посмотрим только на структуру без цифровых показателей значений выполнения запроса (CPU, длительность, количество чтений и записей и др.), т.к. примеры супер простые. Проверку и просмотр проводим на ненагруженном мощном сервере. Платформа 1С 8.3.16 и MS SQL сервер 13.

В качестве начального запроса возьмем таблицу регистра накопления "Товары Организаций", в ней около 10 миллионов записей, что вполне достаточно для наших задач. Регистр накопления "Расчеты с клиентами", где 3 миллиона записей и документ "Заказ клиента", записей порядка 700 тысяч.

Для примеров ниже в профайлере необходимо отключить фильтр длительности (Duration), т.к. запросы выполняются быстро и вы их пропустите. Остальные два фильтра по имени базы и вхождению ключа в текст оставляем.