Исправления медленных запросов в 1С

Почему план запроса может быть неоптимальным

Основными причинами медленно работающего запроса являются:

  • > Неактуальная статистика. Если у СУБД неактуальная статистика, то оптимизатор может ошибиться при выборе оптимальной операции для получения данных, в результате чего запрос может сильно замедлиться. Например, из-за неактуальной статистики СУБД посчитала, что вернется 100 строк, и выбрала операцию сканирования, а на самом деле вернулся 1 млн строк, и в результате вместо 0,5 с запрос выполнялся 10 с, что существенно снизило производительность. Всегда важно, чтобы статистика была актуальной.
  • > Плохо написанный запрос. В самом тексте запроса, приходящем в оптимизатор СУБД, уже могут быть допущены ошибки, приводящие к его медленной работе. Например, не указаны условия отбора, в результате чего выборка происходила сканированием.
  • > Отсутствие подходящих индексов. Даже отлично написанный запрос не сможет увеличить скорость работы запроса так, как это может сделать наличие подходящих индексов. Если запрос вначале быстро выполняется, то из-за проблем с неиспользованием индексов со временем производительность его будет уменьшаться, т.к. будет расти количество обрабатываемых запросом данных.

Ваша 1С медленно формирует отчеты, тормозит и зависает, мы поможем решить эту проблему, обращайтесь [email protected]

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

Рассмотрим основные признаки неоптимального плана за­проса. Какие основные операции, присутствующие в плане запроса, указывают на его возможную неоптимальность.

  • >   Nested Loops — означает операцию соединения вло­женными циклами. Принцип работы вложенных ци­клов: это самый простейший способ соединения двух таблиц — когда перебираются записи первой таблицы и для каждой ее записи делается перебор записей вто­рой таблицы на выполнение условия соединения. Nested Loops как способ соединения двух таблиц хорош, если ведущая таблица содержит мало данных (1-3 строки), в других случаях предпочтительны другие способы со­единения.
  • >   Операции сканирования (Table Scan Index Scan Clustered Index Scan) — означают полный перебор дан­ных таблицы или индекса. Сканирование хорошо, ког­да сканируемая таблица содержит мало записей либо сканируется большая таблица, но из нее возвращает­ся значительное количество данных. Если сканируется большая таблица, а возвращается небольшое количест­во данных, то это считается неоптимальностью — здесь лучше бы было искать данные по индексу.
  • >   Index Seek …Where — означает сканирование не всего индекса, а его части. То есть сначала идет выборка дан­ных по индексу, а оставшаяся часть сканируется по опре­деленному условию. В принципе наличие этой операции всегда указывает на неоптимальность, а именно на от­сутствие подходящего индекса, будет только отличаться степень этой неоптимальности (количество сканируемых данных).

Как получить планы запросов в SQL Profiler

Чтобы получить планы запросов в SQL Profiler, необходимо выполнить следующие шаги:

Шаг 1. Запуск SQL Profiler

«Пуск — Все программы — Microsoft SQL Server — Средства обеспечения производительности — SQL Server Profiler*.

Шаг 2. Создание новой трассировки

Чтобы начать работу с SQL Profiler, необходимо выбрать меню «Файл — Создать трассировку». Прежде чем исполь­зовать SQL Profiler для решения задач, мы должны войти в SQL Server, чтобы убедиться, что у нас есть соответству­ющие права.

Шаг 3. Установка свойства трассировки

Нажав на кнопку «Соединить», открываем окно «Свойства трассировки». Первое, что необходимо сделать для нас­тройки трассировки, — это задать «Имя трассировки» на за­кладке «Общие». Желательно, чтобы оно отражало суть тех данных, которые мы хотим собрать с помощью трасси­ровки.

На закладке «Выбор событий» для получения пла­на запроса нас будут интересовать следующие события (см. рис. 1):

  • >   Performance\Showplan Statistics Profile. Получение тек­стового представления плана запроса
  • >   Performance\Showplan XML. Получение графического представления плана запроса
  • >   Stored Procedures\RPC:Completed. Получение запроса, выполняемого хранимой процедурой
  • >   TSQL\SQL:BatchCompleted. Получение запроса, выпол­няемого как обычный запрос

profiler

Шаг 4. Установка фильтров

Часто необходимо собрать информацию о конкретном событии, но только при наступлении определенных ус­ловий. Например, события определенного пользователя или для определенной базы данных. Фильтры позволяют отобрать те события, которые нас интересуют.

При нажатии на кнопку «Фильтры столбцов» на закладке «Выбор событий» окна «Свойства файла трассировки» от­кроется окно «Изменение фильтра», в котором мы обяза­тельно должны установить следующие отборы:

  • >  Отбор на столбец DatabaseName. Здесь мы ставим фильтр на имя базы данных, в которой мы хотим найти медленные запросы. Если мы не установим отбор на базу данных, то к нам в трассировку будут попадать все запросы по всем базам данных, находящимся на данном SQL Server (см. рис. 2).

profiler-1

 

  • > Отбор на столбец Duration. Здесь мы ставим фильтр, чтобы получать только те запросы, которые выполнялись более 1 мс (см. рис. 3).

profiler-2

Шаг 5. Запуск, сбор данных и остановка трассировки

После того как свойства файла трассировки установлены, нажимаем кнопку «Запустить» в нижнем правом углу интерфейса.
Сейчас рассмотрим основные приемы работы с трассировкой (запуск, пауза, остановка и т.д.).

  • > Старт трассировки. После нажатия нами кнопки «Запустить» уже начали собираться данные в трассировку (см. рис. 4). Экран сбора трассировки разделен на две части: в верхней части содержится список событий, а нижняя часть показывает содержимое поля TextData. В нижней левой части окна выдается сообщение

profiler-3

«Трассировка выполняется», которая показывает текущее состояние трассировки. Другие сообщения, которые могут выводиться: «Трассировка приостановлена», «Трассировка остановлена». В нижней правой части окна показываются номер текущей строки и номер текущего столбца, на котором остановлен курсор, а также общее количество строк.
Приостановка трассировки. Мы можем приостановить сбор трассировки для того, чтобы в дальнейшем продолжить ее сбор. Для этого на панели необходимо нажать кнопку «Пауза».
Остановка трассировки. Чтобы остановить сбор данных трассировкой, необходимо нажать на кнопку «Стоп» на панели.
Очистка трассировки. Если мы сохранили данные трассировки и собранные данные нам уже не понадобятся, то мы можем очистить оперативную память от собранных данных. Для этого необходимо на панели нажать кнопку «Очистить трассировку»

Шаг 6. Сохранение файла трассировки

Сохранение данных трассировки в файл может понадобиться в очень многих случаях. Например, у вас нет времени именно сейчас расследовать причину либо собранные данные необходимо отправить другому человеку для дальнейшего анализа.
Файл трассировки можно сохранить в формате .trc. Для этого необходимо просто остановить трассировку и выбрать пункт меню «Файл — Сохранить как — Файл трассировки».
Поиск плана запроса для медленных запросов
После завершения сбора данных трассировки необходимо ее проанализировать. Если трассировка небольшая по размеру, то можно ее посмотреть вручную. Для этого необходимо смотреть строки с большим временем выполнения (Duration) и большим количеством чтений (Reads). План запроса для выделенной строки будет находиться над соответствующей строкой с текстом запроса (см. рис. 5).

profiler-4

Анализ полученного плана запроса

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

Проверка плана запроса на признаки неоптимальности
В данном плане запроса присутствуют вложенные циклы, что является подозрением на причину медленной работы. Но если присмотреться к этой операции плана запроса (см. рис. 6), то можно увидеть, что она занимает только 10% от всего времени выполнения запроса. И если навести курсор на входящую стрелочку, то видно, что ведущая таблица содержит мало данных (предполагаемое количество равно 1, а фактическое равно 0), поэтому вложенные циклы в данном плане запроса не являются проблемой.

profiler-5

Следующим подозрительным элементом плана запроса является операция поиска по индексу Clustered Index Seek (см. рис. 6). Эта операция означает, что происходил поиск по кластерному индексу. Поиск по индексу является «хорошей» операцией, но т.к. вес времени выполнения всего запроса очень высок (76%), то нужно проверить ее на дополнительное сканирование (Index Seek .. .Where).

Для того чтобы проверить операцию на дополнительное сканирование, мы можем навести курсор на данную операцию и посмотреть во всплывающей подсказке, есть ли блок «Предикат», — это и будет условием дополнительного сканирования (см. рис. 7).

profiler-6

Нахождение неоптимального запроса в коде конфигурации 1С

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

Запрос.Текст = »
|ВЫБРАТЬ
| ТоварыНаСкладахОстатки.Номенклатура ↵
КАК Номенклатура,
| СУММА(НеобходимыеТовары.Количество) КАК Необходимо,
| СУММА(ТоварыНаСкладахОстатки.КоличествоОстаток) ↵
КАК Остаток
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки(
| ,
| (Склад, Номенклатура) В
| (ВЫБРАТЬ РАЗЛИЧНЫЕ
| Документ.Реализация.Товары.Склад,
| Документ.Реализация.Товары.Номенклатура
| ИЗ
| Документ.Реализация.Товары
| ГДЕ
| Документ.Реализация.Товары.Ссылка = ↵
&Документ)) ↵
КАК ТоварыНаСкладахОстатки
|
| ЛЕВОЕ СОЕДИНЕНИЕ Документ.Реализация.Товары ↵
КАК НеобходимыеТовары
| ПО НеобходимыеТовары.Ссылка = &Документ
| И НеобходимыеТовары.Номенклатура = ↵
ТоварыНаСкладахОстатки.Номенклатура
|
|СГРУППИРОВАТЬ ПО
| ТоварыНаСкладахОстатки.Номенклатура»;

Исправление плохого запроса

Исходя из текста запроса, структуры метаданных и плана запроса можно сделать вывод, что в запросе используется одновременно условие по полям «Склад» и «Номенклатура», а в индексе данной таблицы поля идут следующим образом: «Склад», «Характеристика», «Номенклатура», т.е. из-за разрыва полей в условии СУБД пришлось прибегнуть к дополнительному сканированию по полю «Номенклатура».

profiler-7
Чтобы исправить работы этого запроса, мы изменим порядок полей индекса данной таблицы. Для этого в конфигураторе 1С поменяем местами порядок следования измерений «Номенклатура» и «Харакеристика» в регистре накопления «ТоварыНаСкладах» (см. рис. 9).

profiler-8
Важно помнить, что, исправив что-то в одном месте, можно ухудшить ситуацию с производительностью в другом месте.

Проверка полученных результатов

После исправления и запуска трассировки заново можно увидеть, что время выполнения запроса уменьшилось в пять раз (с 10 до 2 мс) и дополнительное сканирование по полю [AccumRgT11].[_Fld8RRef] тоже исчезло.
Анализ большой трассировки. Сохранение трассировки в таблицу
Когда собранных данных в трассировке много, то искать медленные запросы вручную тяжело. Рассмотрим последовательность действий для нахождения медленных запросов.
Выгрузить полученную трассировку в таблицу
Чтобы сохранить трассировку в таблицу SQL Server надо:

  • > Выбрать «Файл — Сохранить как — Таблица трассировки».
  • > В открывшемся меню выбрать базу данных и название таблицы, в которую будет сохранена трассировка. Если работаете на тестовом оборудовании и работа носит разовый характер, то можно пользоваться базой данных master, но лучше создать отдельную базу данных для этих целей. В данном случае мы создадим таблицу PF2016 в базе данных master.

Получить запросы с максимальным временем
Для этого мы должны открыть Microsoft SQL Server Management Studio и создать новый запрос (см. листинг 2).
Листинг 2. Получение списка самых медленных запросов

SELECT [RowNumber]
,[Duration]/1000 AS Duration ,[Reads] ,[TextData] FROM [master].[dbo].[PF2016] Order by [Duration] desc

В результате мы получим отсортированный по убыванию список самых медленных запросов (см. рис. 10).

profiler-9

Чтобы найти соответствующий запросу план, нам необходимо найти этот же запрос в файле трассировки. Зная значение номера строки (RowNumber) из полученного запроса, в окне трассировки выберем «Правка — Перейти к» (<Ctrl> + <G>), перейдем к соответствующему номеру строки, а план запроса будет располагаться выше этой позиции.

Список основных причин медленной работы запросов 1С

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

  • > Соединения подзапросами или виртуальными таблицами. Приводит к использованию Nested Loops.

Решение: переписать запрос с использованием временных таблиц.

  • > Несоответствие индексов и условий запроса. Приводит к операциям сканирования.

Решение: переписать текст запроса или создать подходящий индекс.

  • > Неиспользование параметров виртуальных таблиц 1С. Приводит к неиспользованию индекса и, как следствие, к сканированию.

Решение: использовать параметры виртуальных таблиц.

  • > Получение данных через точку от полей составного типа. Приводит к усложнению текста запроса, т.к. СУБД приходится делать соединение со всеми таблицами, входящими в это составное поле.

Решение: использование «Выразить» для ограничения количества таблиц или не использовать получение данных через точку.

  • > Использование логического «ИЛИ» в условии. Использование «ИЛИ» в условии может привести к неиспользованию индекса.

Решение: заменить текст запроса на два отдельных с объединением результатов.
Использование SQL Profiler может серьезно помочь в поиске, анализе и исправлении медленно работающих запросов 1С. Важно научиться грамотно работать с этим инструментом оптимизации.

Источник: Журнал системный администратор №6 2016

0 ответы

Ответить

Хотите присоединиться к дискуссии?
Приглашаем поучаствовать!

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Можно использовать следующие HTML-теги и атрибуты: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>