Применение Database Engine Tuning Advisor в оптимизации производительности запросов 1С 8.1, 1С 8.2

В данной статье рассматривается, насколько применим инструмент Database Engine Tuning Advisor, входящий в MS SQL Server 2005, при решении проблемы производительности запросов, генерируемых платформой 1С 8.1 и 1С 8.2.

При этом не ставилось целью провести детальный анализ всех возможностей данного инструмента, или сравнить его с какими-то альтернативными средствами. Это слишком большие вопросы, достойные отдельного исследования. В статье рассмотрен более узкий вопрос: принципиальная возможность использования Tuning Advisor с учетом специфики команд, используемых платформой 1С 8.1 и 1С 8.2.

Содержание статьи

Дана общая характеристика области применения и основных функциональных возможностей Tuning Advisor.
Описаны эксперименты по его использованию при анализе профиля нагрузки 1С 8.х., а также эксперименты, демонстрирующие работу Tuning Advisor с командами, использующими временные таблицы.
Описаны некоторые особенности работы менеджера временных таблиц 1С 8.х.
Показано, что эти особенности сильно ограничивают применимость Tuning Advisor для оптимизации запросов 1С 8.1 и 1С 8.2.
Приведён подробный пример того, каким образом платформа 1С 8.1 и 1С 8.2 создаёт временную таблицу при явном её задании в пользовательской выборке данных.
Сделаны обобщающие выводы.

Краткое описание Tuning Advisor

Программное средство Database Engine Tuning Advisor, входящее в MS SQL Server 2005, пришло на смену мастеру Index Tuning Wizard, который входил в MS SQL Server 2000. Этот инструмент предназначен для того, чтобы облегчить работу по оптимизации индексов и других структур в базе данных. В качестве исходной информации для анализа он может принимать файл или таблицу трассировки, созданную при помощи профилировщика (Profiler), а также любой текстовый файл с командами T-SQL. Обычно в таком файле или таблице собирается последовательность команд, выполнявшихся в процессе работы пользователей на SQL-сервере (профиль нагрузки) за какой-то промежуток времени (например, за рабочий день). Исходная информация анализируется в соответствии с заданными параметрами (рассчитываются различные варианты внесения изменений в индексы и статистики), и по результатам анализа генерирует отчёт и рекомендации. Анализ происходит в автоматическом режиме, рекомендации можно сохранить в файл и применить в любое время. Есть возможность предварительной оценки сценариев до их применения в базе данных (что-то вроде "А что будет, если этот индекс добавить, а этот — удалить?").

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

Проверим, насколько эффективен Tuning Advisor при анализе типичного профиля нагрузки, создаваемого при работе пользователей в 1С 8.1 и 1С 8.2.

Временные таблицы в 1С

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

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

Очевидно, что данная особенность наложит определённые ограничения на использование Tuning Advisor. Если запустить трассировку спустя какое-то время после начала работы пользователей 1С, то в трассу не попадут команды создания временных таблиц, уже выполненные до запуска. Это значит, что Tuning Advisor не сможет проанализировать конструкции Transact-SQL, в которых встречаются эти временные таблицы. И таких конструкций может оказаться значительная часть.

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

Анализ трассы 1С

Попробуем проанализировать в Tuning Advisor произвольную небольшую трассу, собранную с одной сессии 1С:Предприятие 8.х. Проведём эксперимент:

Закрываем все сессии 1С:Предприятие 8.1 (или 1С 8.2) на тестовом сервере приложений
Запускаем одну сессию 1С:Предприятие 8.1 (или 1С 8.2).
Включаем трассировку MS Profiler, включив события
Перепроводим произвольный документ «Поступление НМА»
Сохраняем собранную трассу в файл
Проверяем: действительно, все команды создания временных таблиц в трассу попали
Анализируем полученный файл трассировки в Tuning Advisor

По завершении анализа Tuning Advisor в области Tuning Progress сообщил, что 18% обработанных операторов содержат ошибки (см. рисунок ниже).

А в области Tuning Log видно, что все конструкции, в которых фигурируют временные таблицы, вызвали ошибку: "Invalid object name {Название временной таблицы}". Т.е. Tuning Advisor по каким-то причинам не смог распознать ни одной временной таблицы, несмотря на то, что в трассе имеются команды создания каждой из них (проверено). Почему?

Работа с "select … into…"

Известно, что для создания временных таблиц 1С 8.1 или 1С 8.2 использует команду "select TOP 0 {столбцы} into #tt{N} from {Таблица}" (см. Tuning Log выше). Проверим, каким образом работает с такими конструкциями Tuning Advisor.

Для эксперимента возьмём простейший скрипт, в котором сначала создаётся и заполняется временная таблица, а затем выполняется её соединение с реальной таблицей:

select TOP 0 xtype [c1] into #tt1 from sysobjects (nolock)
go
insert #tt1 (c1) select top 1 xtype from sysobjects (nolock)
go
create unique clustered index idx1 on #tt1 (c1)
go
select a.*
from rs_subqueue_arch a (nolock)
inner join #tt1 b
ON a.oper = b.c1
Go

Проанализируем этот скрипт с помощью Tuning Advisor. В результате увидим, что так же, как в случае с трассировкой 1С, конструкция с временной таблицей вызвала ошибку.

Работа с "create table…"

Теперь заменим в скрипте команду "select... into..." на "create table...":

create table #tt1 (c1 varchar(5))
go
insert #tt1 (c1) select top 1 xtype from sysobjects (nolock)
go
create unique clustered index idx1 on #tt1 (c1)
go
select a.*
from rs_subqueue_arch a (nolock)
inner join #tt1 b
ON a.oper = b.c1
Go

Модифицированный таким образом скрипт Tuning Advisor обработал без ошибок и выдал две рекомендации: создать по таблице [rs_subqueue_arch] индекс по полю [oper] и статистику по полям ([id],[oper]).

Следует отметить, что перед расчётами Tuning Advisor преобразует многие выражения, приводя их к некой нормальной форме. В данном случае в области Tuning Log видно, что выражение "select a.* from ... join #tt1..." было преобразовано к виду "create table #tt1 (c1 int) select a.* from ... join #tt1...".

Обобщение по "select … into…" и "create table…"

Обобщим результаты. Создание временной таблицы возможно двумя способами: явно ("create table") и неявно ("select...into"). Когда Tuning Advisor встречает в анализируемой трассе (скрипте) команду явного создания временной таблицы "create table #tt1...", он запоминает её, и далее использует при анализе других команд, в которых эта временная таблица встречается. При таком способе наличие временных таблиц не вызывает проблем, главное, чтобы в трассе присутствовали операторы их создания.

Иначе обстоит дело, когда временная таблица создаётся "неявно" - с помощью T-SQL конструкция "select TOP 0... into #tt1...from..." (именно такие конструкции использует платформа 1С 8.1 и 1С 8.2). В отличие от "create table #tt1...", команду "select TOP 0... into #tt1..." Tuning Advisor игнорирует и никак далее не учитывает. А когда в анализируемой трассе (скрипте) встречается временная таблица, созданная такой командой, Tuning Advisor не может разобрать соответствующую конструкцию, и сообщает о неопознанном объекте - "Invalid object name {Название временной таблицы}".

Таким образом, поскольку временные таблицы используются платформой 1С 8.1 и 1С 8.2 довольно часто, значительная часть команд 1С не может быть проанализирована в Tuning Advisor.

Пример, как платформа 1С создаёт временную таблицу

Для иллюстрации того, каким способом платформа 1С 8.1 и 1С 8.2 создаёт временные таблицы, приведём простой пример.

Возьмём типичный участок кода 1С, где в выборке данных используется временная таблица. Например, из формы подбора номенклатуры:

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

На уровне SQL-сервера этим выборкам соответствуют команды, которые приведены на скриншоте MS Profiler:

"select TOP 0 ... into #tt1 ..." - создаётся пустая временная таблица
"exec sp_executesql N'INSERT INTO #tt1..." - временная таблица заполняется данными
"exec sp_executesql N'SELECT...FROM #tt1..." - итоговая выборка, выполняющая соединение временной таблицы со справочником.

Если трассу с этими командами проанализировать с помощью Tuning Advisor, то получится такой же результат, как в приводимых выше примерах: первый оператор "SELECT TOP 0..." пропускается, а два оператора, использующие временную таблицу, вызывают ошибку "Invalid object name #tt1".

В приведённом примере временная таблица была явно задана в выборке с помощью команды "ПОМЕСТИТЬ". Однако следует заметить, что использование временных таблиц далеко не ограничивается выборками данных, явно прописанными в коде конфигурации. Они также активно используются на уровне платформы 1С 8.1 и 1С 8.2. при выполнении самых различных операций. И во всех случаях для создания временной таблицы используется оператор "select TOP 0 ... into".

Удельное количество команд, использующих временные таблицы

Проанализируем, каково удельное количество команд, использующих временные таблицы. Поскольку для оптимизации интересны в первую очередь наиболее ресурсоёмкие команды, будем выбирать только из команд, наиболее тяжелых по времени выполнения (Duration).

В качестве примера возьмём статистику, собранную программой мониторинга производительности "PerfExpert" по тяжёлым запросам (Duration>5сек) в течение рабочего дня в базе данных ЦО одного из клиентов.

Всего операторов 1С с Duration>5сек за выбранный период:

Отберём из общего числа операторы 1С, в которых фигурируют временные таблицы:

Количество команд с временными таблицами составило более 62% от общего числа, при этом их удельный вес по CPU составил почти 70%. Это означает, что большая половина тяжёлых команд, создающих большую часть нагрузки на процессор, и интересных с точки зрения оптимизации выполнения, не могут быть обработаны в Tuning Advisor.

Общий вывод по Tuning Advisor

Tuning Advisor является хорошим инструментом, имеющим массу достоинств, к основным из которых можно отнести:

анализ исходных данных производится в автоматическом режиме в соответствии с заданными настройками
от специалиста не требуется глубоких знаний в области оптимизации T-SQL-запросов
предоставляется информация о том, на сколько в процентах будет улучшение производительности конструкций при реализации предложенных рекомендаций
имеется возможность оценки сценариев, предлагаемых пользователем, например: "А что будет, если этот индекс добавить, а этот — удалить?".

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

Во-первых, возникает естественный вопрос, в каких единицах высчитывается вклад выражений в общую нагрузку? Понятно, что, скорее всего, это некая величина, агрегированная из показателей reads, cpu, writes (возможно ещё каких-то). Но, как именно она получается, не известно, алгоритм её вычисления закрыт.

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

И в-третьих, объединив информацию из нескольких отчётов, выданных в результате анализа, можно понять, какой индекс на каком выражении какой выигрыш даст. Но при этом Tuning Advisor информацию по одному и тому же параметризованному запросу, выполненному с разными наборами параметров, в отчётах приводит раздельно, не агрегируя сведения по аналогичным запросам.

Кроме того, при всех плюсах, применимость Tuning Advisor для анализа и оптимизации индексов в базах данных 1С 8.х сильно ограничена из-за специфики работы платформы 1С 8.х с временными таблицами. И в первую очередь из-за того, что для создания временных таблиц платформа 1С использует Transact-SQL конструкцию «SELECT … INTO…», которую Tuning Advisor не умеет корректно анализировать.

Использование этой конструкции для создания таблиц приводит к тому, что из поля зрения Tuning Advisor полностью выпадают все операторы, в которых фигурируют временные таблицы. Статистика, собранная на реальных производственных системах, показывает, что количество таких операторов достигает 60% и более от общего числа наиболее тяжёлых запросов.

Компания "СофтПоинт" разработала собственный инструментарий для помощи в оптимизации тяжелых запросов на MS SQL Server 2005/2008,. Помощник используется для оптимизации запросов, как в 1С 7.7, 8.1, так и в произвольных системах работающих на MS SQL Server 2005/2008. При этом инструментарий позволяет не только определять узкие места в запросах с детализацией до «узла» плана выполнения и предикатов, но и рекомендует какой индекс следует добавить, а также дает рекомендации по оптимальному синтаксису и оптимальной структуре запросов. Помощник умеет анализировать временные таблицы, которые в спектре запросов 1С 8.1 могут составлять более 50%. В ближайшее время выйдет статья с реальными примерами оптимизации, следите за новостями на нашем сайте.