Что вы делаете, чтобы убедиться, что новый индекс не замедляет запросы?

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

запустите Profiler,
запустите SQL script, который содержит много запросов, которые я не хочу замедлять.
загрузить трассировку из файла в таблицу,
анализировать CPU, читать и записывать из трассировки на результаты предыдущих запусков, прежде чем добавить (или удалить) индекс.

Это своего рода автоматизированное и вроде того, что я хочу. Однако я не уверен, есть ли лучший способ сделать это. Есть ли какой-нибудь инструмент, который делает то, что я хочу?

Изменить 1 Человек, который голосовал, чтобы закрыть мой вопрос, не могли бы вы объяснить свои причины?

Изменить 2 Я искал googled, но не нашел ничего, что объясняло бы, как добавление индекса может замедлить. Однако это хорошо известный факт, поэтому должно быть что-то где-то. Если ничего не появится, я могу написать несколько примеров позже.

Изменить 3 Один из таких примеров: два столбца сильно коррелированы, например, высота и вес. У нас есть индекс по высоте, который не является достаточно избирательным для нашего запроса. Мы добавляем индекс по весу и выполняем запрос с двумя условиями: диапазон по высоте и диапазон по весу. потому что оптимизатор не знает о корреляции, он сильно недооценивает мощность нашего запроса.

Другим примером является добавление индекса по возрастанию столбца, такого как OrderDate, может серьезно замедлить запрос с условием вроде OrderDate > SomeDateAfterCreatingTheIndex.

Ответ 1

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

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

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

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

Ответ 2

Как насчет следующего подхода:

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

Ответ 3

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

Улучшить индексы

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

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

Другие ресурсы:

http://databases.about.com/od/sqlserver/a/indextuning.htm

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

http://searchsqlserver.techtarget.com/tip/Stored-procedure-to-find-fragmented-indexes-in-SQL-Server

Фрагментированные индексы и таблицы в SQL Server могут замедлить производительность приложений. Здесь хранится процедура, которая находит фрагментированные индексы в SQL-серверах и базах данных.

Ответ 4

Хорошо. Во-первых, индекс замедляет две вещи (по крайней мере)

- > insert/update/delete: index rebuild

- > планирование запросов: "использовать этот индекс или нет?"

Кто-то сказал, что планировщик запросов может использовать менее эффективный маршрут - это не должно произойти.

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

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

То, что вы делаете в настоящее время, выглядит довольно здорово, вы должны просто убедиться, что данные, которые вы смотрите, релевантны, то есть запросы реального использования в правильной пропорции (это может сделать мир различий).

Чтобы сделать это, я всегда советую написать бенчмаркинг script на основе реального использования - путем ведения журнала production-env. запросов, немного как я сказал здесь:

Завершить преобразование схемы db - как протестировать перезаписанные запросы?