В данной статье рассматривается, насколько применим инструмент 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).
Проверяем: действительно, все команды создания временных таблиц в трассу попали
Анализируем полученный файл трассировки в 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 ..." - создаётся пустая временная таблица
"execsp_executesql N'INSERT INTO #tt1..." - временная таблица заполняется данными
"execsp_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%. В ближайшее время выйдет статья с реальными примерами оптимизации, следите за новостями на нашем сайте.
