Влияние оптимизиатора запросов на производительность 1с

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

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

Для того чтобы разработчик имел возможность использовать запросы для реализации собственных алгоритмов, в 1С:Предприятии реализован язык запросов. Этот язык основан на SQL, но при этом содержит значительное количество расширений (виртуальные таблицы, обращение через точку и т.п.), ориентированных на отражение специфики финансово-экономических задач и на максимальное сокращение усилий по разработке прикладных решений.
Модель работы с базой данных, реализованная в 1С:Предприятии 8, позволяет разработчику сосредоточиться на создании бизнес-логики приложения и не заботиться о структурах таблиц, преобразованиях типов данных и пр.
Платформа 1С:Предприятия обеспечивает операции исполнения запросов, описания структур данных и манипулирования данными, транслируя их в соответствующие команды. В случае клиент-серверного варианта работы — это команды MS SQL Server  2005, IBM DB2 или PostgreSQL.

«Внутренняя кухня» на примере MS SQL Server

Возьмем пример запроса 1С и проследим за путем его исполнения.

Воспользуюсь обработкой «Консоль запросов» с диска ИТС и выполню простенький запрос:

Простой запрос 1С

Большинство запросов, это инструкция «ВЫБРАТЬ …», поэтому мне кажется будет правильным дальше опираться на эту инструкцию.

Для знакомых с языком SQL команда «ВЫБРАТЬ» — это перевод на русский язык команды SELECT (кстати, если писать в 1С на английском языке, то запросы 1С становятся еще больше похожими на запросы SQL).

Давайте проследим путь выполнения этого запроса.

Путь прохождения запроса

путь прохождения запроса 1с

Прежде всего не пугайтесь :). Расшифрую рисунок (он хоть и немного устарел, но суть сохранилась). Мы — это «пользователь» на рисунке. В консоли запросов я нажимаю кнопку «Выполнить».

Код обработки выполняется на клиенте (но в принципе может и на сервере 1С:Предприятие), он проходит по сети и попадает на рабочий сервер приложений, где транслируется в SQL запрос.

Отслеживание запросов технологическим журналом

С помощью обработки (тоже с диска ИТС) «Настройка технологического журнала» я создаю файл logcfg.xml, который будет логировать запросы к субд.

Содержимое файла у меня было такое:

<?xml version=»1.0″?>
<config xmlns=»http://v8.1c.ru/v8/tech-log»>
<dump create=»false» location=»" type=»0″/>
<log history=»1″ location=»E:\Common\logs»>
<event>
<eq property=»name» value=»dbmssql»/>
</event>
<property name=»all»/>
</log>
</config>

Текст logcfg.xml содержит фильтр dbmssql на сбор всех запросов к MS SQL Server.

технологический лог

затем открыл файл технологического лога

Для моего запроса в консоли запросов в логе появились такие строчки:
45:44.1727-1,DBMSSQL,3,process=rphost,p:processName=bi,t:clientID=15,t:applicationName=1CV8,t:computerName=TESTCENTER,t:connectID=10,Usr=Тест,Trans=0,dbpid=52,Sql=

‘SELECT
_InfoReg482_Q_000_T_001._Fld484 AS f_1
FROM
_InfoReg482 _InfoReg482_Q_000_T_001 WITH(NOLOCK)
WHERE
_InfoReg482_Q_000_T_001._Fld483RRef = ?
p_0: 0x9455005056C0000811DCE204C649E3E8
‘,Rows=1,RowsAffected=-1
45:44.2031-0,Context,1,process=rphost,p:processName=bi,t:clientID=15,t:applicationName=1CV8,t:computerName=TESTCENTER,t:connectID=10,Usr=Тест,Context=’
Обработка.КонсольЗапросов.Форма.Форма : 458 : мРезЗапроса = ОбъектЗапрос.Выполнить();’

 

У нас появился SQL запрос к «страшному и непонятному» имени таблицы базы данных «_InfoReg482 _InfoReg482_Q_000_T_001″, плюс «загадочные» поля таблицы «_Fld484″ и «_Fld483RRef».

Тут надо дать комментарий почему мы не увидели запроса 1С. У нас есть «контекст запроса» — 458 строка модуля Обработка.КонсольЗапросов.Форма — мРезЗапроса = ОбъектЗапрос.Выполнить();

Встаньте в эту строчку отладчиком конфигуратора и для ОбъектЗапрос.Текст содержимым будет запрос 1С.

 

Но это мы знаем исходный запрос, и поэтому можем догадаться, что за «_InfoReg482″ таблица на самом деле.

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

 

Возвращаемся к нашей схеме прохождения запроса.

Сервер приложений 1С:Предприятие является клиентом для СУБД MS SQL Server и передает исследованный нами SQL запрос на сервер СУБД.

Запрос выполняет сервер СУБД и затем по обратной цепочки возвращает ответ.

В этой статье сконцентрируемся на подробностях исполнения запроса сервером субд, так как язык SQL предназначен для формулирования запросов в терминах СУБД (таблицы и поля).

При выполнении запроса:

SELECT
_InfoReg482_Q_000_T_001._Fld484 AS f_1
FROM
_InfoReg482 _InfoReg482_Q_000_T_001 WITH(NOLOCK)
WHERE
_InfoReg482_Q_000_T_001._Fld483RRef = ?

мы говорим, что нужно получить «_InfoReg482_Q_000_T_001._Fld484″, но не говорим, «как это получить»!

 

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

-Части запроса можно обрабатывать в разном порядке

-Можно использовать разные индексы

Это связано с механизмом субд — оптимизатором запросов.

Оптимизатор запросов СУБД

Оптимизатор запросов осуществляет поиск наиболее оптимального плана выполнения запросов из всех возможных для заданного запроса. Один и тот же результат может быть получен СУБД различными способами (планами выполнения запросов), которые могут существенно отличаться как по затратам ресурсов, так и по времени выполнения. Задача оптимизации заключается в нахождении оптимального способа.

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

Планы выполнения запроса сравниваются исходя из следующих факторов:

  • потенциальное число строк, извлекаемое из каждой таблицы, получаемое из статистики;
  • наличие индексов ( подразумевается, что вы уже прочли мою статью об индексах);
  • возможность выполнения слияний (merge-join).

оптимизатор запросов

 

Что из всего этого следует:

Перед выполнением запроса формируется его ПЛАН выполнения.

  1. Полностью определяет, как будет выполняться запрос
  2. Формируется автоматически сервером СУБД
  3. Повлиять  на план средствами 1С:Предприятие можно только косвенно

Давайте «посмотрим» план выполнения нашего запроса.

План запроса

Страшно и непонятно, если не знать английского :)

Давайте разберемся в этой схеме плана запроса.

Для выполнения выбора (SELECT …) поля _InfoReg482_Q_000_T_001._Fld484 (он находится в составе возвращаемых данных Output List) оптимизатор запросов принял решение выполнить перебор всех записей (Clustered Index Scan в заголовке операции (единственной) плана) кластерного индекса таблицы _InfoReg482_ByPeriod_TRN (об этом подсказывает раздел object).

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

Обработка показывающая структуру таблиц и индексов базы данных ИБ

Привожу для справки работу оператора сканирования (перебора) кластерного индекса:

Clustered Index Scan – логический и физический оператор сканирует кластеризованный индекс, определенный в колонке Argument.

Если есть опциональный параметр WHERE:(), то возвращаются только те строки, которые соответствуют параметру. Если колонка Argument содержит параметр ORDERED, процессор запроса предложит вывод строк в том порядке, в котором они отсортированы в кластерном индексе. Если упорядочения нет, индекс будет отсканирован самым оптимальным образом (но не гарантируется, что вывод будет отсортирован).

В нашем примере плана опциональный параметр отсутствует.

 

Основная причина недостаточной производительности запросов — неоптимальный план выполнения запроса.

Для того, чтобы устранять проблемы производительности, необходимо понять причины.

Причины, по которым СУБД может выбирать неоптимальный план (наиболее известные)

Но прежде, чем перейдем к разбору софтверных нюансов, хочется ОБРАТИТЬ ВНИМАНИЕ, что

сильная загруженность ресурсов сервера на выбор плана влияет НЕПРЕДСКАЗУЕМО!

Это означает, что если у вас сервер «не тянет», то результат оптимизации кода конечно снизит нагрузку и улучшит производительность, но вот ВЫПОЛНИТЬ САМУ ОТЛАДКУ и оптимизировать код на таком сервере сложно и НЕТ ГАРАНТИЙ!

В качестве примера обычно рассказываю такую ситуацию. Допустим Вы нашли медленный запрос и знаете как его улучшить. Чтобы оценить на практике результаты, Вы должны сначала выполнить замер производительности на исходном коде, затем на оптимизированном. А как можно интерпретировать результаты, если в момент время замера исходного запроса сервер был загружен на 70%, а в момент второго на 99%. Можно ли доверять таким замерам? Нет.

 

Несоответствие индексов и условий запросов

 

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

Индексы наиболее подходят для задач следующего типа:

  • Запросы, которые указывают «узкие» критерии поиска. Такие запросы должны считывать лишь небольшое число строк, отвечающих определенным критериям.
  • Запросы, которые указывают диапазон значений. Эти запросы также должны считывать небольшое количество строк.
  • Поиск, который используется в операциях связывания. Колонки, которые часто используются как ключи связывания, прекрасно подходят для индексов.
  • Поиск, при котором данные считываются в определенном порядке. Если результирующий набор данных должен быть отсортирован в порядке кластеризованного индекса, то сортировка не нужна, поскольку результирующий набор данных уже заранее отсортирован. Например, если кластеризованный индекс создан по колонкам lastname (фамилия), firstname (имя), а для приложения требуется сортировка по фамилии и затем по имени, то здесь нет необходимости добавлять инструкцию ORDER BY.

Правда при всей полезности индексов, есть одно очень важное НО – индекс должен быть «эффективно используемым» и должен позволять  находить данные с использованием меньшего числа операций ввода-вывода и объема системных ресурсов.

В запросах кода конфигурации использование индексов зависит условий, на которые есть смысл обратить внимание:

  • ВЫБРАТЬ … ИЗ … ГДЕ <условие>
  • СОЕДИНЕНИЕ … ПО <условие>
  • ВЫБРАТЬ … ИЗ <ВиртуальнаяТаблица>(, <условие>)
  • ИМЕЮЩИЕ <условие>

Если в системе нет индекса, который мог бы эффективно использоваться для данного запроса, то необходимо рассмотреть одну из следующих возможностей:

  • Установить свойство «Индексировать»
  • Изменить порядок следования измерений в регистре

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

  1. Знание используемого количества строк в таблице (чаще всего записей в справочнике, регистре сведений, последовательности и т.д.)
  2. Знание запроса, использующего таблицы (записи объектов метаданных)

Почему так происходит?

Предлагаю выполнить несколько примеров. Сначала займемся практикой, а потом сделаем выводы из наблюдений и познакомимся с теорией.

Пример 1

Создайте справочник с реквизитом. Пусть это будет «Склады» с реквизитом «МОЛ». Заполните справочник, сгенерировав например обработкой несколько тысяч записей, с разными значениями реквизита «МОЛ».

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

 

Выполните (например в консоли запросов) такой запрос, использующий отбор «ГДЕ»:

Пример запроса с отбором ГДЕ

Для того, чтобы получить неискаженную информацию, выполните запрос Transact-SQL (здесь приложен файл) к базе данных с помощью SQL Server Management Studio:

USE MyDataBase1C
GO
exec sp_msforeachtable N’UPDATE STATISTICS ? WITH FULLSCAN’
GO
DBCC UPDATEUSAGE (MyDataBase1C)
GO
DBCC FREEPROCCACHE

,где MyDataBase1C — имя базы данных информационной базы 1С:Предприятие.

Далее используя SQL Server Profiler и приложенный здесь шаблон включите «трассировку» для вашей базы данных.

После выполнения запроса кода конфигурации в MS SQL Server будет «транслирован» запрос Transact-SQL.

Выделите событие Showplan XML Statistics Profile, и Вы уведете картинку плана запроса, похожу на мою.

Графическое отображение плана запроса

Подробней приемы работы с планом запроса можно посмотреть здесь http://technet.microsoft.com/ru-ru/magazine/cc137757.aspx

Предлагаю обратить внимание на название операции физического исполнения логической команды SELECT (ВЫБРАТЬ) — Clustered Index Scan.

Теперь изменим у реквизита свойство Индексировать.

Включить свойство Индексировать

И после перезапуска конфигуратора (у нас будет создан индекс) повторить наблюдение за планом запроса.

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

Что произошло? Тот же самый запрос, выполняясь в MS SQL Server  в этот раз выполняется другими физическим оператором — Clustered Index Seek. А если еще точнее, двумя операциями Clustered Index Seek и результаты объединяются физической операцией Nested Loops.

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

 

Пример 2

В нашем примере будем выполнять один и тот же запрос к регистру накопления.

Пример запроса

Сначала выполним размещения измерения номенклатура последним по порядку, и выключим индексирование этого реквизита, выполним запрос. Затем включим индексирование и повторим запрос.

Для анализа используем профайлер с такими настройками:

настройки профайлера

Запрос к неиндексированной «Номенклатуре» регистра.

неоптимальный запрос

Длительность Duration в моем замере составила 60 мс.

Теперь запрос с индексированным вариантом:

оптимизированный запрос

 

Интересно, что в 1С:Предприятии код запроса мы не трогали, более того, и в MS SQL Server логический текст запроса тоже остался тем же.

А вот физические операторы изменились.

Почему же оптимизатор запросов ведет себя по-разному?

Для этого надо понимать различия между физическими операторами, применяющимися для логических запросов. Ниже немного теории.

Сканирование таблицы или индекса

На сканирование указывает наличие в плане запроса одной из следующих операций:

Операция сканирования указывает на неоптимальную работу запроса, но не всегда, а только при выполнении следующих условий:

  • Таблица содержит большое количество записей
  • Запрос возвращает маленькое количество записей

Операция сканирования всегда указывает на то, что для оптимального выполнения данного запроса нет подходящего индекса.

операция table scan

Вид операции Table Scan. Значок нам как бы подсказывает, что происходит перебор всех записей таблицы.

 

операция Clustered Index Scan

Вид операции Clustered Index Scan

 

Отдельно выделим операцию поиска по кластеризованному индексы с условием.

 

Поиск в индексе по неполному условию

На выполнение поиска по неполному условию указывает наличие в плане запроса операции:

Данная операция аналогична сканированию таблицы или индекса, но при ее выполнении сканируется не вся таблица, а только ее часть.

Операция поиска по неполному условию указывает на неоптимальную работу запроса не всегда, а только при выполнении следующих условий:

  • Сканируемая область (ограниченная условием SEEK) содержит большое количество записей
  • Запрос возвращает маленькое количество записей

Операция поиска по неполному условию так же указывает на отсутствие подходящего индекса.

операция Index Seek

 

Вид операции Index Seek

Избегать конструкции ПОДОБНО (LIKE)

При работе с отборами удобно выполнять поиск контрагента не по наименованию, а путем установки отбора по условию «Содержит» по полю «Наименование». Однако это отрицательно сказывается на производительности из-за невозможности использования индексов и так приводит к запросам:

ВЫБРАТЬ .. ИЗ Справочник.Контрагенты
ГДЕ Наименование ПОДОБНО «%СтрокаПоиска%»

В таком запросе будет выполнен полный перебор всех записей таблицы. Это отрицательно сказывается на производительности.

Примечание. В последних версиях MS SQL Server LIKE стал уметь в некоторых случаях использовать поиск, но это все равно не лучший оператор.

 

Большая вложенность подзапросов и соединения с вложенными подзапросами

Сервер СУБД с трудом оптимизирует такие конструкции как

Если в тексте встречаются конструкции вида:

ВЫБРАТЬ ИЗ <запрос1>
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ ИЗ <подзапрос1>
) ПО …

особенно, если <запрос1> или <подзапрос1> содержат не физические таблицы, а также вложенные запросы, потому что:

  1. Неизвестно, сколько записей вернет подзапрос
  2. Неизвестно, какой способ соединения лучше использовать
  3. Выбирается тот способ, который сам по себе проще (NESTED LOOPS)
  4. Это приводит к сильному падению производительности

Рассмотрим более подробно суть проблемы.

Соединение в цикле

На выполнение соединения в цикле указывает наличие в плане запроса операции:

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

В этом случае следует попытаться упростить запрос:

  • Использовать временные таблицы вместо подзапросов
  • Уменьшить вложенность подзапросов
  • По возможности исключить соединения с подзапросами

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

 

Большая вложенность запросов

Другой момент – это возможные ошибки в сложных вложенных запросах (на момент написания характерны для ЗУП). Если запрос выполняет неоправданно долго, упростите его с помощью временных таблиц.

МенеджерВТ = Новый МенеджерВременныхТаблиц;
Подзапрос = Новый Запрос;
Подзапрос.МенеджерВременныхТаблиц = МенеджерВТ;
Подзапрос.Текст = »
| ВЫБРАТЬ …
| ПОМЕСТИТЬ МояВременнаяТаблица
| ИЗ <подзапрос1>
| ГДЕ…
| ИНДЕКСИРОВАТЬ ПО…
«;

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

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

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

 

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

  1. Заполняем временную таблицу результатом вложения
  2. Выполняем основной запрос

Резюме. При решении вопросов производительности в MS SQL Server обратите внимание на физические операторы плана запроса:

  • Сканирование таблицы или индекса
  • Поиск в индексе по неполному условию
  • Соединение в цикле

Оптимизатор СУБД выбирает и анализирует лишь некоторое количество вариантов выполнения запроса и выбирает “лучший из них”. При количестве таблиц больше 8 скорее всего все варианты точно не будут проверены — даже математически слишком много комбинаций за отведенное ограниченное время.

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

  1. По возможности условие ИЛИ заменять  операцией ОБЪЕДИНЕНИТЬ ВСЕ.Чтобы убедиться в том, что нужна замена, можно сделать так: выполнить запрос с условием на равенство по одиночному значению поля, а потом запрос с условиемИЛИ по этому полю на большой таблице. Если второй запрос выполняется намного дольше, чем первый, то данный запрос можно переписать.
  2. Условие “не равно” (<>) тоже может отключать использование индекса. Например если необходимо исключить большую часть таблицы, а поле отбора проиндексировано, – есть смысл переписать запрос на вариант ОБЪЕДИНЕНИТЬ ВСЕ
  3. В условиях соединений, стараться обходиться без использования вложенных запросов и виртуальных таблиц, а так же лишних разыменований (выражений через несколько точек). Вложенные запросы и виртуальные таблицы рекомендую заменить на временные таблицы.
  4. Стараться избегать в условии “В ИЕРАРХИИ” содержания пустой ссылки. Возможно ситуация когда оптимизатор СУБД начнет проверять каждый элемент справочника на принадлежность корню (т.е. самому справочнику), теряя на этом время.
  5. Следует использовать ОБЪЕДИНИТЬ ВСЕ вместо ОБЪЕДИНИТЬ, если наличие одинаковых записей не критично. Оператор ОБЪЕДИНИТЬ использует дополнительные операции, которые могут занять много времени.
  6. Условие “ПОДОБНО” подавляет использование индекса.
  7. Если запрос содержит несколько условий, то они должны располагаться в порядке уменьшения эффекта от выбора. То есть первым надо делать то условие, которое максимально уменьшит результирующую таблицу.
  8. Максимально использовать индексы таблиц. Необходимо стараться чтобы для всех условий, использованных в запросе, имелись подходящие индексы. Подходящий индекс – это индекс, который содержит все поля перечисленные в условии, эти поля находятся в самом начале индекса и они расположены вместе, т.е между ними нет других полей.
  9. Не рекомендуется фильтровать виртуальные таблицы при помощи условий в секции ГДЕ и т.п. Надо использовать только параметры.
  10. Стараться для ссылочных полей, по которым будет вывод, получать представление  сразу в запросе, т.е. использовать конструкцию “ПРЕДСТАВЛЕНИЕ(НашаСсылка)“
  11. Если в запросе реализовано соединение двух и более таблиц, то эти таблицы должны стоять в запросе в порядке уменьшения количества записей в них, а в части условий (ГДЕ) первым должно стоять условие на первую таблицу.
  12. Если запрос содержит условие для проиндексированного (относится к некластерному индексу) поля маленькой таблицы, которая может быть считана за одно обращение к памяти, то лучше убрать индекс с этого поля в конфигурации.
  13. Условия вхождения значений полей в результаты вложенных запросов лучше заменять на внутренние соединение по равенству этого поля для ситуаций, когда есть вероятность получения больших размеров таблиц результатов вложенных запросов. Т.е. конструкцию “Поле1 В (Выбрать Поле1 из Таблица2)” заменить на “ВНУТРЕННЕЕ СОЕДИНЕНИЕ Таблица2 По Таблица1.Поле1 = Таблица2.Поле1“
  14. Для составных типов смотрите http://infostart.ru/public/184361/
  15. Пример оптимизации тут

Влияние данных на выбор плана

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

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

Время проведения документа с 50, 500, 5000 и 50000 строчками вряд ли будет иметь линейную зависимость замедления. В какой-то момент «где-то» система упрется в узкое место и скорость проведения резко упадет. В качестве рекомендаций можно посоветовать нагрузочное тестирование именно с тем количеством строк, которое реально используется на предприятии.

Влияние статистики на выбор плана

С объемом данных и его разнородности в рамках таблицы сильно связано «знание» оптимизатора с помощью статистик распределения. Как правило, СУБД не знает и не может знать точное число строк в таблице (даже для выполнения запроса SELECT COUNT(*) FROM TABLE выполняется сканирование первичного индекса), поскольку в базе могут храниться одновременно несколько образов одной и той же таблицы с различным числом строк. Сбор статистики для построения гистограмм осуществляется либо специальными командами СУБД, либо фоновыми процессами СУБД.

Из практических можно отметить уже данные рекомендации моей статьи по обслуживанию MS SQL Server  выполнять принудительно или регламентной процедурой команду SQL:

exec sp_msforeachtable N’UPDATE STATISTICS ? WITH FULLSCAN’

Влияние механизмов кэширования запросов (их компиляции перед исполением).

Кеш запросов используется для уменьшения времени ответа СУБД для часто используемых запросов.
План выполнения запроса помещается в кеш и ассоциируется с синтаксическим деревом или текстом запроса. Впоследствии, если семантика входящего запроса соответствует семантике некоторого запроса, помещенного в кеш, то СУБД использует сохраненный план выполнения, а не генерирует его.

При очень высокой загруженности СУБД может использовать скомпилированной запрос с устаревшей статистикой или другими неактуальными данными. Инструкция

DBCC FREEPROCCACHE

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

Влияние RLS

1. При использовании у пользователя большого количества групп нужно создавать новую группу, которая бы содержала в себе эквивалент это множества групп. Т.е. проверять одну группу значительно лучше чем десяток.

2. Разбивайте запрос на несколько частей. Старайтесь как можно большее количество участков запроса выносить в привилегированный модуль. Под «RLS» должна остаться только та таблица и кусок запроса, без которой потеряется смысл проверки доступа к записи.

Способы поиска проблемных запросов

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

Поиск длительных запросов опросом пользователей и отладчиком

Метод имеет кучу недостатков и ограничений. Пожалуй проще сказать, когда его можно использовать. Если пользователь жалуется на долгое проведение документа или построение отчета, и при этом нет блокировок, т.е. работает одинаково долго как в многопользовательском, так и в монопольном режиме. Кроме того, отладчиком по-умолчанию можно отлаживать только код клиентской части. Для включения отладки на сервере Вам придется перезапускать сервер приложений и лезть в реестр. А именно

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\1C:Enterprise 8.1 Server Agent]
«ImagePath«=

по умолчанию »C:\Program Files\1cv81\bin\ragent.exe» -srvc -agent -regport 1541 -port 1540 -range 1560:1591 -d «C:\Program Files\1cv81\server»

надо »C:\Program Files\1cv81\bin\ragent.exe» -srvc -agent -regport 1541 -port 1540 -range 1560:1591 -debug -d «C:\Program Files\1cv81\server»

Таким образом, это самый дешевый и простой способ исследования медленных запросов. Он наиболее известен 1С специалистам.

Поиск длительных запросов технологическим журналом

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

Недостаток — нужно иметь навык работы с технологическим журналом или потратить время на его освоения. Облегчает работу обработка с диска ИТС «Настройка технологического журнала».

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

Поиск длительных запросов с помощью бесплатного сервиса Анализа долгих запросов

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

 

Поиск длительных запросов SQL Server Profiler-ом

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

Вариант настройки SQL Server Profiler

 

  • Events:
    • Stored Procedures \ RPC:Comleted
    • TSQL \ SQL:BatchCompleted
    • Performance \ Showplan Statistics Profile
    • Performance \ Showplan XML Statistics Profile
  • Колонки данных:
    • Все
  • Column Filters:
    • Все условия фильтрации, включенные по умолчанию
    • DatabaseName
      • Like <имя базы данных>
    • Duration
      • Greater then or equal <длительность запроса в миллисекундах>

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

 

Имеется возможность автоматизировать ранжирование групп запросов. Для этого следует сохранить трассировку в таблицу SQL и выполнить, например, следующий запрос:

 

select substring(TextData, 1, 100), SUM(Duration)

from MySQLTrace

group by substring(TextData, 1, 100)

order by SUM(Duration) desc

 

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

 

Ну, вообще Вы поняли, что будет не скучно :).

 

Поиск отсутствующих индексов средствами MS SQL Server

Так получилось, что задача поиска отсутствующих индексов характерна не только 1С-приложениям, но и вообще любым клиентам MS SQL Server. Задача вообще-то не очень простая, как вы уже могли заметить. Поэтому для «закоренелых sql-щиков» не могу не осветить еще несколько моментов.

Помощник по настройке ядра СУБД (Database Engine Tuning Advisor)

Помощник по настройке ядра СУБД (часто обозначают DTA)— это инструмент для анализа влияния рабочей нагрузки на производительность в одной или нескольких базах данных. Рабочая нагрузка представляет собой набор инструкций Transact-SQL, которые выполняются в отношении баз данных, нуждающихся в настройке. Другими словами данный инструмент позволяет человеку непонимая, как используются оптимизатором ресурсы, использовать советы самого сервера, дающего рекомендации «мол такой индекс надо сделать, а этот лишний».

Поскольку мы работаем с 1С:Предприятие 8, то SQL-щики могут соблазниться на подобную кажущуюся легкость решения проблемы.

Подводными камнями является «мягкая» привязка структуры информационной базы к именам таблиц в базе данных MS SQL Server. Именя таблиц генерируются платформой автоматически и нет гарантий в постоянном соответствии метаданных к именам таблиц.

Вообщем такой подход решения проблемы методически неверен.

Однако я бы не стал вообще о нем упоминать, если бы не одно но. Рекомендованные индексы помощником DTA можно расмотреть для создания подобных индексов уже средствами 1С:Предприятие. Более подробно об управлении индексов смотрите в моей статьте.

Как пользоваться DTA.

1. Вам необходимо профайлером собрать «рабочую нагрузку». Это означает, что вы не просто включаете на подробную запись запросов профайлер и из под своей сессии делаете одно-два проведений документов или построений отчетов. Вам нужна запись реальных действий пользователей за весь рабочий день. Разумеется слово рабочий день подразумевает не время с 10.00 до 18.00, а реальная работа пользователей в это время.

2. В составе трассировки обязательно должны быть и выполненные запросы, и планы запросов.

3. Сохранить трассировку в файл.

Выбор файла с рабочей нагрузкой

4. Выполнить анализ файла с помощью DTA, указав сохранять структуру существующих таблиц и индексов.

Найстрока параметров

После старта анализа мастер будет некоторое время оценивать данные нагрузки и выдаст рекомендации.

Рекомендованные индексы

Сохраняем скрипт в файл и с помощью уже выше озвученой обработку смотрим на содержимое колонок индексов.

Содержимое скрипта

Очень желательно создавать индексы не средствами СУБД, но если вы решите все таки использовать скрипт, советую продумывать отслеживание эффективности подобных индексов.

Вы можете также прочить в январьском номере 2008 года статью Яна Стерка «Открытие скрытых данных для оптимизации производительности приложений».

Часть приемов экспериментально реализована в моей обработке.

Мегаэкстремальный способ препарирования запросов

Есть такой прием — подсказка USE PLAN в запросе.

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

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

Влияние оптимизатора запроса от редакции СУБД

См. здесь