Оптимизация базы данных MS SQL при помощи Database Engine Tuning Advisor для конечных пользователей

Введение

Часто такая задача, как оптимизация быстродействия БД, оказывается за скобками разработки. Проект сдан, тесты выполняются и быстродействие на конкретных данных – уже проблема не разработчика, а клиента.

К тому же, проповедование Agile всё больше склоняет команды к использованию ORM. А это ещё бОльшая абстракция от СУБД и такие вещи, как: индексы, кластерные индексы, статистика данных — разработчикам или не известны или забыты.

Подобную методику лично я успешно использовал на MS SQL Server 2005, 2008, 2008R2. Все эти версии уже имеют встроенные инструменты для профилирования и тюнинга.

Как это можно использовать

Разработчики смогут использовать методику для дополнительного “разгона” своего творения перед релизом. А конечные пользователи (например, владельцы магазинов) для поддержания жизни в уже введённой в эксплуатацию системе без дополнительных капиталовложений.

Делай раз

Для начала нам придётся собрать статистику использования нашей БД. Для этого мы запускаем профайлер SQL Server Profiler. Открываем новую сессию и включаем фильтрацию запросов по нашей БД.

Далее запускаем сбор статистики и ждём, пока наберётся достаточно запросов. Обычно, пары-тройки минут достаточно. В это время можно любоваться, как в БД летят всякие as Extent1 и SingleRowTable1.
Заканчиваем сбор статистики кнопочкой Stop Selected Trace. Выделяем все записи и жмём File – Export – Extract SQL Server Events – Extract Transact-SQL events. Эта команда позволяет нам сохранить в файл именно SQL запросы, обрезав ненужные нам данные. Допустим, файл мы назвали “log1.sql”.

Делай два

Теперь нам нужно запустить Database Engine Tuning Advisor. Это тоже стандартная утилита от Microsoft, доступная после установки MS SQL 2005, 2008[R2], 2012(?). У неё удобный wizard-подобный интерфейс.

Для начала во вкладке General мы устанавливаем файл для имитации нагрузки “log1.sql” и указываем нашу БД “testBD” в качестве пациента для диагностики в 2х местах на форме. Таб Tuning Options мы не трогаем, его настройки по умолчанию нам подходят. По умолчанию там отключены опции анализа, которые потребуют не самых простых решений, например, нам не предложат сделать партиционирование.
Всё, жмём заветную Start Analysis и смотрим, как Tuning Advisor имитирует нагрузку на БД.

Профит

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

От нас требуется лишь выделить те пункты, которые мы считаем нужными реализовать, и через меню Actions – Apply Recommendations применить их.

Просто, быстро, эффективно.