среда, 3 декабря 2014 г.

MSSQL – простые индикаторы нехватки индексов

Как обычно в нашей профессии, есть несколько способов решить задачу :)

Расскажу о трёх наиболее простых подходах (и одном бонусном), не углубляясь в детали. Если интересны как раз глубокие детали, рекомендую почитать документацию (начать стоит с Designing Indexes, а прочитав о проектировании продолжить Performance (Database Engine)).

Кстати, если тема плана выполнения для вас нова – неплохим стартом будет мой давний пост «SQL – execution plan».

DISCLAIMER: в этой статье я даю простые советы, которые чаще всего работают, однако в MSSQL практически невозможно дать рекомендации на все случаи жизни – данные бывают разные, запросы бывают разные…

1. Мысленный эксперимент

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

Что касается простейших советов, они следующие:

  1. Если данных не мизерное количество – вам нужны индексы.
  2. Если есть внешние ключи – настоящие или по смыслу (некоторые их принципиально не любят делать) – они хорошие кандидаты для индексирования.
  3. Чаще всего стоит сделать кластерный индекс и сделать его не слишком большим (потому что другие индексы будут ссылаться на строки по кластерному индексу). Кстати, кластерный индекс отлично подходит для выборки по диапазону (например, по диапазону дат).
  4. Помните, что индексы тоже хранятся на диске. Простейший вывод – большое количество индексов снизит скорость вставки (иногда и обновления) данных. Обычно до создания 5-7 индексов проблем не возникает.
  5. Статистика очень важна для правильного использования индексов. Хорошая новость – чаще всего MSSQL сам достаточно неплохо за ней следит, плохая новость – это не всегда срабатывает.
  6. Для наиболее критичных запросов, возвращающих небольшое количество столбцов, имеет смысл использовать покрытие индексом (это уже чуть более продвинутая техника, но ничего сложного в ней нет).

Поскольку я обещал не углубляться в детали – идём дальше.

Database Engine Tuning Advisor

Это сравнительно простой в использовании, но, тем не менее, мощный и результативный инструмент, который даёт рекомендации по улучшению производительности запросов. Правда, надо отметить, что он недоступен в Express-версии.

Если вкратце, на вход он принимает набор запросов (можно подготовить с помощью SQL Server Profiler) и, после их анализа (иногда довольно длительного), даёт рекомендации.

Надо понимать, что рекомендации будут относиться к набору запросов, а не к общему случаю использования вашей БД. Для более подробного ознакомления рекомендую почитать документацию.

Missing Index Feature

Вероятно, вы уже обращали внимание, что при просмотре плана выполнения иногда встречаются рекомендации вида "Missing index...". Это как раз работает Missing Index Feature.

На это можно посмотреть, запустив на базе AdventureWorks (в моём случае была база для 2012 версии) такой запрос:

select s.ProductID from Sales.SalesOrderDetail s where s.UnitPrice = 2

Если включен просмотр плана выполнения (Ctrl+M), мы увидим вполне логичную рекомендацию для улучшения производительности этого запроса. Да, повторюсь – не стоит улучшать один, особенно не критичный запрос – мы можем замедлить другие, более важные запросы. С этой точки зрения предыдущие варианты более правильные.

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

Если у вас есть замечания, пожелания или новые темы – пишите в комментариях или на olegaxenow.reformal.ru. Постараюсь учесть.

1 комментарий:

  1. Важное замечание (подразумевал по умолчанию): предлагаемые к созданию индексы в последних двух способах полезно оценивать критически - могут пропустить что-то полезное или посоветовать лишнее (например почти всю таблицу в INCLUDE для индекса).

    ОтветитьУдалить