Применение 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 Программное средство 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.х. Проведём эксперимент:
По завершении анализа Tuning Advisor в области Tuning Progress сообщил, что 18% обработанных операторов содержат ошибки (см. рисунок ниже). А в области Tuning Log видно, что все конструкции, в которых фигурируют временные таблицы, вызвали ошибку: "Invalid object name {Название временной таблицы}". Т.е. Tuning Advisor по каким-то причинам не смог распознать ни одной временной таблицы, несмотря на то, что в трассе имеются команды создания каждой из них (проверено). Почему? Работа с "select … into…" select TOP 0 xtype [c1] into #tt1 from sysobjects (nolock) Проанализируем этот скрипт с помощью Tuning Advisor. В результате увидим, что так же, как в случае с трассировкой 1С, конструкция с временной таблицей вызвала ошибку.
Работа с "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...".
Обобщим результаты. Создание временной таблицы возможно двумя способами: явно ("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С создаёт временную таблицу Первая выборка формирует временную таблицу, а вторая делает соединение этой временной таблицы со справочником Номенклатура. На уровне SQL-сервера этим выборкам соответствуют команды, которые приведены на скриншоте MS Profiler:
Если трассу с этими командами проанализировать с помощью 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 является хорошим инструментом, имеющим массу достоинств, к основным из которых можно отнести:
Однако следует заметить, что хотя сведения об ожидаемом улучшении производительности весьма интересны, их информативность несколько неоднозначна, и имеется ряд существенных замечаний. Во-первых, возникает естественный вопрос, в каких единицах высчитывается вклад выражений в общую нагрузку? Понятно, что, скорее всего, это некая величина, агрегированная из показателей reads, cpu, writes (возможно ещё каких-то). Но, как именно она получается, не известно, алгоритм её вычисления закрыт. Во-вторых, как показывает практика, процент улучшения зачастую даётся с большой погрешностью, и сильно отличается от эффекта, фактически полученного после применения рекомендаций.
Компания "СофтПоинт" разработала собственный инструментарий для помощи в оптимизации тяжелых запросов на MS SQL Server 2005/2008,. Помощник используется для оптимизации запросов, как в 1С 7.7, 8.1, так и в произвольных системах работающих на MS SQL Server 2005/2008. При этом инструментарий позволяет не только определять узкие места в запросах с детализацией до «узла» плана выполнения и предикатов, но и рекомендует какой индекс следует добавить, а также дает рекомендации по оптимальному синтаксису и оптимальной структуре запросов. Помощник умеет анализировать временные таблицы, которые в спектре запросов 1С 8.1 могут составлять более 50%. В ближайшее время выйдет статья с реальными примерами оптимизации, следите за новостями на нашем сайте.
|
Статья: Применение Database Engine Tuning Advisor в оптимизации производительности запросов 1С 8.1, 1С 8.2 |
Перейти на главную страницу компании "Софтпоинт" |