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

20.04.22

База данных - HighLoad оптимизация

Расскажем про инструменты, рассмотрим планы запросов, увидим, как отслеживать и бороться с проблемами производительности на боевой базе.

Это продолжение статьи про распространенные ошибки разработчиков, в данном случае посмотрим, как выглядит  обработка  рассмотренных в этой статье "запросов" сервером 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С с включением некоторого ключа (параметр "КлючЗапроса"), по которому далее будет накладываться фильтр в профайлере TextData, т.ч. не забудьте его добавить. Еще раз - это значение будет ловиться фильтром TextData, поэтому убедитесь что значения фильтра и параметра "КлючЗапроса" совпадают. 

 

 

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), т.к. запросы выполняются быстро и вы их пропустите. Остальные два фильтра по имени базы и вхождению ключа в текст оставляем.

 
Простой базовый запрос
 
Добавим условие на равно
 
 Добавим условие на не равно
 
 Используем оператор "ИЛИ", "В"

 

2. Ситуации из статьи "Типовые ошибки разработчиков приводящие к проблемам".

 

Теперь давайте с вами рассмотрим планы запросов для ситуаций рассмотренных в предыдущей статье и проверим, как происходит и происходит ли оптимизация. В заголовке спойлера может быть приведен номер пункта из предыдущей статьи. Проверить все варианты и "поиграться" с данными вы можете самостоятельно.

 
Получаем данные через точку (пункт 10)
 
Еще два варианта оптимизации через точку, когда не нужны данные всех типов (доп. к пункту 10)
 
Посмотрим как ведет себя система при получении через точку, когда реквизит обычный (не составного типа) (доп. к пункту 10)
 
 Задание на перевозку и реквизит из табличной части (пункт 9)
 
 Сложный запрос в динамическом списке (пункт 11)
 
 Сложный запрос в динамическом списке с учетом RLS (доп. к пункту 11)
 
 Сложный запрос и отсутствие индекса (доп. к пункту 11)

 

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

 

В предыдущих примерах мы анализировали известные ситуации. Теперь давайте перейдем к вопросу: Как найти или увидеть проблемные ситуации в существующей рабочей базе? Что для этого необходимо сделать?

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

 

1) Настройка и загрузка технологического журнала

 

Процесс настройки и подключения конфигурации Мониторинг производительности отлично расписан в статье "5 простых шагов и 15 минут на разворачивание инструмента мониторинга проблем производительности базы 1С".

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

После некоторого времени наработки у нас с вами появится первая информация. И мы можем приступить к следующему шагу.

 

2) Настройка и описание инструмента

 

В предыдущем пункте вы должны были без особых проблем выполнить настройку технологического журнала и загрузку данных в базу. Теперь давайте рассмотрим как удобно настроить интерфейс и на что смотреть и что нажимать.

Первым делом давайте откроем замер и добавим необходимые информационные поля (колонки) в динамический список "События замера" как на рисунке ниже:

 

 

Это поля:

  • Usr - пользователь, тот пользователь, под которым проявилась ситуация;
  • SessionID - номер сеанса, вы можете отследить его действия в журнале регистрации в рамках интервала, чтобы к примеру, увидеть какой документ проводился и т.п.;
  • p:processName - имя базы данных, в какой базе проявилась ситуации;
  • Context - информация по стеку кода, где ситуация произошла. Позиция кода где смотреть возникшую проблему;
  • Sql - текст запроса который выполнялся указанное время;
  • Rows - количество строк полученных по запросу. Для динамических списков обычно не большое (25-50) и с ключевым словом в запросе TOP. Полезно чтобы оценить адекватность передаваемых пользователю данных, к примеру, если передается пользователю миллионы строк данных, то значит что-то не так;
  • RowsAffected - количество строк добавленных во временную таблицу;
  • SQL:Param - параметры запроса, могут входить в Sql. Удобно чтобы посмотреть, что передавалось.

 Теперь давайте посмотрим, как выглядит у нас список:

 

 

В поле замер выбираем необходимый замер. Для нас "долгие запросы (не отчеты)".

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

В поле фильтра "длительность" устанавливаем ограничение в 60 секунд (или другое большее число - зависит от ситуации) и начинаем смотреть, что у нас набралось.

 

3) Смотрим ситуации и анализируем

 

А) Пример "поиск подобно по всем колонкам".

Открываем понравившуюся нам позицию и начинам анализировать. Ставим курсор на выбранную строку:

 

 

Из контекста видно, что проблемная ситуация находится в динамическом списке "Сделки с клиентами". Давайте посмотрим почему у нас появился этот запрос в топе, т.к. сам запрос на языке запросов 1С выглядит достаточно просто и в нем нет особых проблем.

 
 Пример запроса динамического списка сделки с клиентами

 Далее посмотрим свойства параметров и обратим внимание на наличие "%" и вхождения в запрос оператора языка запросов "ПОДОБНО (LIKE)". Двойной клик на полях соответствующих колонок "Sql" и "SQL:Param".

 

 

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

 

 

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

 

Б) Выбор необоснованного количества данных.

 

Давайте посмотрим следующий пример, который на рисунке ниже.

 

 

Как мы видим, то происходит создание временной таблицы с количеством строк "RowsAffected" с более чем 620 тысяч строк. Это действительно большое количество для данных. Давайте посмотрим где это произошло по колонке "Context".

 

 

Это выполняется внешняя обработка, а позиция находится в модуле объекта и именно выполнение запроса.

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

 

Б) Сортировки по неиндексированным полям в динамическом списке

 

Давайте посмотрим, что у нас с самым популярным списком в базе ЕРП. Как вы догадались, то это список называется "Заказы клиентов". Самое обидное что в этом примере пользователь получил пустой список и ждал более 2х минут.

 

 

Контекст запроса: "ДинамическийСписок.ПолучитьДанные : Документ.ЗаказКлиента.Форма.ФормаСпискаДокументов.Реквизит.Список".

Первым делом поглядим на наличие подобно, его нет. Давайте посмотрим на еще один интересный оператор запросов "УПОРЯДОЧИТЬ ПО (ORDER BY)". Для этого возьмем запрос из колонки "Sql" посмотрим на него внимательней.

 

 

Как мы видим с вами, то сортировка присутствует и в него добавлено поле "Партнер". Дополнительно на быстродействие влияет наличие сложной RLS по этому пользователю. Также мы видим наличие некоторого количества отборов, в которые входит и партнер (не приведено на снимках экрана). 

Пользователь при запросе от сотрудника сопровождения (поддержка) объяснил, что сортировку поставил случайно (кликнул по этому полю). 

Резюме: Нам требуется убрать сортировку из динамического списка по неподходящему полю или выполнить его индексирование. Также можно взять консоль запросов и под этим пользователем посмотреть как меняется поведение (это можете сделать самостоятельно).

 

Г) Сложный запрос и дальнейший анализ по схеме выше

 

Давайте возьмем следующую позицию для запроса:

 

 

Открываем контекст и смотрим:

 

 

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

 

 

Мы видим, что это "ЖурналДокументов.Взаимодействия". В запросе нет поиска по подобно и сортировки по "плохим" полям. Давайте откроем эту форму списка и посмотрим на запрос:

 
 Запрос формы списка взаимодействия

Резюме: В списке нам не видно каких-то особых проблем, он достаточно простой. Дальнейшее расследование причин необходимо осуществлять с применением профайлера.

 

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

 

V) РЕЗЮМЕ

 

  1. Оптимизатор плана запроса в MS SLQ сервер очень умный (если так можно сказать про программу) и он хорошо выполняет свою работу. Для того чтобы он правильно работал необходимо следить за актуальностью статистики, использовать другие процедуры обслуживания сервиса. Некоторые рекомендации от вендора смотрите тут: Регламентные операции на уровне СУБД для MS SQL Server
  2. Следите внимательно и не допускайте использования реквизитов в запросах через точку для составных типов.
  3. Всегда где это возможно используйте отборы - по дате, по периоду, по организации, контрагентам, и т.п. 
  4. Избегайте где возможно использования в динамических списках общего поля поиска, отключайте, удаляйте, заклеивайте его.
  5. Обращайте внимание на наличие в схеме плана запроса оператора KeyLookUP - это говорит об отсутствии необходимых индексов. Однако, в типовых конфигурациях из-за наличия общих разделителей необходимо будет просматривать и анализировать каждый такой оператор, чтобы их отсеять. Добавление этих разделителей значительно ухудшило информативность схемы плана запросов и снизило возможности оптимизации, т.к. работа с этими реквизитами осуществляется платформой.
  6. Для очень сложных планов запросов используйте Plan Explorer и ищите "жирные" потоки данных, для того чтобы понять откуда идут проблемы. Если жирных потоков нет, то скорее всего требуется пересмотреть архитектуру решения. 
  7. Проблема для динамических списков может иметь простое объяснение - прежде чем открывать профайлер посмотрите на наличие в тексте запроса "паразитных" вхождений: ПОДОБНО (LIKE), УПОРЯДОЧИТЬ ПО (ORDER BY), СГРУППИРОВАТЬ ПО (GROUP BY) - это могут быть признаки неоптимальной работы с элементом интерфейса.
  8. Обращайте внимание на "огромные" количества получаемых данных или помещаемых во временные таблицы - это может говорить о том что не хватает фильтров или про некорректные алгоритмы процедур.

мониторинг производительности MSSQL Profiler

См. также

Анализируем SQL сервер глазами 1С-ника

HighLoad оптимизация Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих зааросов на sql, ожиданий, конвертация запроса в 1с и рекомендации где может тормозить

1 стартмани

15.02.2024    7664    159    ZAOSTG    68    

96

Удаление строк из таблицы значений различными способами с замером производительности

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    6000    doom2good    48    

63

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    8886    ivanov660    6    

76

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

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

15.11.2023    5116    a.doroshkevich    20    

72

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    16198    skovpin_sa    14    

98

«Монитор» – простой анализ производительности

Администрирование СУБД Технологический журнал Бесплатно (free)

Для расследования проблем производительности недостаточно просто проанализировать технологический журнал. Нужен парсинг контекста событий, его сопоставление с информацией из Extended Events и логов, агрегация огромного количества информации.

21.09.2023    5732    Andreynikus    14    

80

MS SQL Server: изучаем планы запросов

Запросы HighLoad оптимизация Запросы Бесплатно (free)

Многие знают, что для ускорения работы запроса нужно «изучить план». При этом сам план обычно обескураживает: куча разноцветных иконок и стрелочек; ничего не понятно, но очень интересно! Аналитик производительности Александр Денисов на конференции Infostart Event 2021 Moscow Premiere рассказал, как выполняется план запроса и что нужно сделать, чтобы с его помощью находить проблемы производительности.

20.06.2023    16039    Филин    37    

113
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Dmitryiv 161 07.09.21 11:42 Сейчас в теме
Годный контент!
FilipN; METAL; IvanGorbunov; bulpi; akR00b; sinichenko_alex; user790708; dvissarov5; ivanov660; +9 Ответить
7. sinichenko_alex 178 07.09.21 16:01 Сейчас в теме
(1) Очень годный! Именно такими статьями должен пополняться Инфостарт!
2. lunjio 66 07.09.21 13:08 Сейчас в теме
Поставлю плюс, советы дельные, хоть их не так уж и много, особенно первый. Планы обслуживания по статистике, реиндексация (перестроение и пересоздание), первое что надо настраивать на сервере баз данных.
3. ivanov660 4332 07.09.21 13:16 Сейчас в теме
(2)Предложите еще варианты. Мне будет интересно прочитать.
4. cdiamond 233 07.09.21 13:54 Сейчас в теме
Опять плохому учите - Profiler давно устарел, для трассировки принято использовать Extended Events прямо в MS SQL Management Studio.
rozer; ProgrammistC; orfos; +3 Ответить
5. ivanov660 4332 07.09.21 14:24 Сейчас в теме
(4)Можете привести примеры сравнения, чем лучше новый инструмент (в чем его преимущество) в рамках поставленной задачи? Спасибо.
sinichenko_alex; +1 Ответить
15. cdiamond 233 07.09.21 20:11 Сейчас в теме
(5) этот устаревший инструмент попросту уберут в будущих выпусках MS SQL Server. Лучше сразу на Extended Events, тем более что он в базе своей очень похож на профайлер и вам стоило всего лишь спуститься по меню SQL Management Studio вниз, найти вкладку инструмента и снять почти такие же скриншоты, без потери логики статьи, которая сама по себе прекрасна, только инструмент ошибочно выбран. Советую вам отредактировать.
16. ivanov660 4332 07.09.21 21:31 Сейчас в теме
(15)Спасибо, за замечание. Дополню статью.
18. evvakra 302 08.09.21 15:03 Сейчас в теме
(16) Самое главное почему следует использовать механизм расширенных событий, так это потому что этот механизм меньше нагружает СУБД по сравнению с профайлером.
Цитата с сайта Майкрософт: "Расширенные события — это упрощенная система мониторинга производительности, в которой применяется минимальный объем ресурсов."

НО, этот механизм появился только в 2014 MS SQL. Соответственно для древних систем только профайлер, только хардкор)
METAL; akR00b; ivanov660; +3 Ответить
6. John_d 5284 07.09.21 14:36 Сейчас в теме
Давно писал статью "Отлавливаем запрос 1С в profiler на MS SQL". Она попроще, но может кому-то пригодится.
https://infostart.ru/1c/articles/965250/
8. tormozit 7138 07.09.21 16:38 Сейчас в теме
Ловить выполненный в СУБД запрос вместе с XML планом при выполнении запроса 1С можно без вникания во все дебри профайлера или расширенных событий.
YPermitin сделал классный инструмент для этого https://infostart.ru/public/1175954
Я добавил аналогичную фичу в ИР https://www.hostedredmine.com/issues/926150
JohnyDeath; fancy; rozer; +3 Ответить
9. ivanov660 4332 07.09.21 17:05 Сейчас в теме
(8)
1. Вы все молодцы.
2. Но я считаю (и не только я), что должны быть обязательно основы и база. И хотя бы чуть чуть вникать нужно, а особо глубоко бессмысленно с точки зрения 1С.
3. Я не использую ИР.
4. Обработка YPermitin подойдет в качестве альтернативы консоли из примера.
5. Не всегда возможно идти от 1С, иногда мы делаем анализ от SQL в 1С. Пока для второго случая адекватного я решения не нашел.
METAL; Nefilimus; akR00b; +3 Ответить
10. tormozit 7138 07.09.21 17:46 Сейчас в теме
5. В ИР есть решение - из конвертора текста БД можно сгенерировать фильтр техножурнала для отлова контекста вызова такого запроса https://www.hostedredmine.com/issues/924526 . Но те, кто не используют ИР, конечно не смогут его найти =)
11. ivanov660 4332 07.09.21 17:52 Сейчас в теме
(10)Немного не то, о чем я спрашивал, но спасибо посмотрю идею реализации. Встречаются ситуации, когда мы с SQL получаем проблемный запрос без контекста и тут появляется вопрос, а откуда он пришел.
У каждого разработчика свой набор инструментов разработки)
12. tormozit 7138 07.09.21 18:05 Сейчас в теме
(11) Тогда уточни свой вопрос.
13. ivanov660 4332 07.09.21 18:40 Сейчас в теме
(12)Опишу ситуацию:
1. Мы получили из Активного Монитора Студии (Active Monitor из SQL Server Management Studio) текст SQL запроса, который попал в топ.
2. Теперь мы хотим узнать где такой расположен в конфигурации ЕРП: список, общий модуль, обработка и т.п., чтобы найти точку входа и провести дальнейшие разборы полетов.
3. Для этого я "на коленке" набросал обработку, которая заменяет таблицы и поля SQL в представление 1С. Но так как получается довольно приблизительно, поэтому начинаем гадать где могут встречаться такие таблицы, комбинации полей. И не всегда быстро доберешься до этой позиции.

Вот как еще можно решить данную обратную ситуацию?
14. tormozit 7138 07.09.21 18:54 Сейчас в теме
(13) Теперь понял. Но способ все же остается тем же, который я упомянул - нужно взять первый кусок текста SQL, заменить в нем вероятно динамические части на управляющий символ "любая строка" и на основе него создать фильтр для техножурнала. Затем нужно повторить или дождаться исполнения этого запроса и тогда увидишь все контексты встроенного языка, его выполняющие. Других достаточно надежных вариантов в текущей платформе просто нет.

Ну а перевод текста БД в термины метаданных конечно может помочь найти такой контекст, но это при высоком везении (например встретилась редкая комбинация таблиц) и уровне знакомства с кодом исследуемой базы.
17. dsdred 3279 08.09.21 06:46 Сейчас в теме
Конвертор запросов очень напомнил Монитор от Андрея Бурмистрова
19. ildary 21 10.09.21 08:56 Сейчас в теме
Большое спасибо за Ваши статьи!
ivanov660; +1 Ответить
20. user729951 20.09.21 05:32 Сейчас в теме
Добрый день.
Спасибо за статью.
Добавлю немного. Не соглашусь с резюме в п. 3.А., поиск в динамических списках не нужно отключать, его нужно уметь готовить.
Используйте полнотекстовый поиск данных. Благодаря ППД удалось ускорить поиск при настроенном RLS в 120 раз (с 360 секунд до 3-х).
Также замечено что чем меньше вводим слов в строке поиска тем быстрее работает.

Статьи в помощь:
1. Полнотекстовый поиск в 1С. №1 Грабли в динамических списках https://infostart.ru/1c/articles/1056842/
2. Полнотекстовый поиск в 1С. №2 Самое основное для разработчика https://infostart.ru/1c/articles/1278933/
21. ivanov660 4332 20.09.21 09:51 Сейчас в теме
(20)Крупные игроки говорят о необходимости отключения полнотекстового поиска, особенно для высоконагруженных систем. И у нас действительно было замечены проблемы с падением хостов из-за работы этого механизма, одним из факторов тут выступает большой объем данных.
Действительно при использовании полнотекстового поиска быстрее, т.к. сначала поиск производится по "текстовой базе 1С", а уже потом применяется к спискам, т.е. фактически исчезают обращения подобно.
22. user729951 20.09.21 10:51 Сейчас в теме
(21) Если не секрет, крупные это сколько пользователей и размер базы?
Падение rphost на какой версии платформы было (баги исправляются) ?
23. redfred 26.10.21 13:45 Сейчас в теме
Общая мудрость гласит, что поиск (seek) - это хорошо для производительности, поскольку он представляет собой прямой доступ SQL Server к требуемым строкам данных, в то время как сканирование (scan) - это плохо, поскольку он предполагает последовательное чтение индекса для извлечения большого числа строк, приводя к более медленной обработке


Слишком уж категоричное заявление. Seek запросто может вычитать всю таблицу (индекс) полностью, а scan - всего несколько нужных строк.
24. Manticor 66 20.04.22 19:13 Сейчас в теме
Друзья, всем привет.
Подскажите пожалуйста что нужно сделать, чтобы отсеять лишний хлам в профайлере???
Мне нужно отловить элементарный запрос.
Я пробовал играться с отбором duration. Но ничего не помогает. Очень много времени отнимает тыкать по всем строчкам(((.
Как сделать вывод самого нужного - текста самого запроса на стороне SQL, графическая схема, текстовая схема запроса. Без вывода служебных запросов.
Привожу в пример скрин. Это профайлер так поймал следующий элементарный запрос:

ВЫБРАТЬ
    Товары.Ссылка
ИЗ
    Справочник.Товары КАК Товары
ГДЕ
    Товары.Ссылка В (&СписокТоваров)
Прикрепленные файлы:
25. ivanov660 4332 20.04.22 21:34 Сейчас в теме
(24)Посмотрите еще раз про использование фильтра по полю TextData и добавления в запрос отдельного поля КлючЗапроса. Эти значения должны совпадать.
Пример:

ВЫБРАТЬ
    Товары.Ссылка,
   "test_key_12345" Как КлючЗапроса
ИЗ
    Справочник.Товары КАК Товары
ГДЕ
    Товары.Ссылка В (&СписокТоваров)


По значению "test_key_12345" надо будет установить фильтр TextData .
26. Manticor 66 21.04.22 19:32 Сейчас в теме
(25) Спасибо большое. Очень помогли).
Оставьте свое сообщение