В SQL Server, начиная с 2005-го, присутствует такой компонент как Database Tuning Advisor. Он может проанализировать рабочую нагрузку и предоставить свои рекомендации по созданию/удалению индексов для повышения производительности.
Не сказать, чтобы мне прям было очень интересно как он работает. Теоретически, глядя на условия соединения таблиц, выбираемые поля, сортировки и прочее - можно предоставить рекомендации по создаваемым индексам. Мой диплом, например, назывался "Инструментарий для оптимизации запросов в среде 1с 8.1", который этим и занимался. Смотрел какие индексы есть, какие поля остаются неиндексированными и, основываясь на этих данных, давал свои рекомендации. Надо признать, что спустя 4 года я понимаю какую ересь он предлагал, но сам подход, в принципе, можно, наверное считать приемлемым.
Ну так вот, возвращаясь к DTA. Я думал, что примерно такую же работу он и проводит. Но, оказалось, что это не совсем так. Точнее, возможно, именно этим он и занимается, но не в том виде, как я себе это представлял.
Итак, у SQL Server есть несколько интересных недокументированных возможностей.
1) При создании индекса есть возможность указать WITH STATISTICS_ONLY.
Это заставляет SQL Server создать гипотетический (hypothetical) индекс. Т.е. фактически создаётся такой индекс, который нельзя использовать в обычных запросах (и который никак не учитывается оптимизатором), но который, как бы существует. Ещё есть возможность указания WITH STATISTICS_ONLY = -1 - тогда индекс будет гипотетическим, но, вот статистика, созданная по этому индексу, будет вполне себе реальной.
2) DBCC AUTOPILOT и SET AUTOPILOT.
Как я уже говорил - гипотетический индекс не может использоваться при выполнении запросов. Но, с помощью DBCC AUTOPILOT мы можем заставить оптимизатор создать такой план запроса, какой он создал бы при наличии этого индекса в "обычном", не гипотетическом, виде.
Итак, чтобы использовать DBCC AUTOPILOT, выполним:
DBCC TRACEON (2588)
DBCC HELP('AUTOPILOT')
и увидим описание:
DBCC AUTOPILOT (typeid [, dbid [, {maxQueryCost | tabid [, indid [, pages [, flag [, rowcounts]]]]} ]])
Итак, чтобы выполнить запрос с учётом гипотетического индекса, в режиме "автопилота", нужно сделать следующее:
1. Создать индекс WITH STATISTICS_ONLY.
2. Узнать идентификатор БД: select db_id()
3. Узнать id таблицы и id индекса, например из sysindexes
4. Вместо остальных значений можно использовать нули, а можно попробовать поиграться и проанализировать результаты.
5. Выполнить SET AUTOPILOT ON
6. Выполнить DBCC AUTOPILOT.
7. Выполнить запрос и посмотреть его план выполнения. Если гипотетический индекс будет являться оптимальным для этого запроса, он будет использоваться.
8. Выполнить SET AUTOPILOT OFF
В общем, штука крутая, но с ней надо быть осторожным. Недокументированные возможности всегда используются на свой страх и риск (и я никому не советовал бы использовать их на продакшене).
О DBCC AUTOPILOT я узнал отсюда (мой перевод на хабре). Примерно тоже самое, от того же автора, но с более удобным вариантом использования есть здесь. О гипотетических индексах можно почитать здесь.
Не сказать, чтобы мне прям было очень интересно как он работает. Теоретически, глядя на условия соединения таблиц, выбираемые поля, сортировки и прочее - можно предоставить рекомендации по создаваемым индексам. Мой диплом, например, назывался "Инструментарий для оптимизации запросов в среде 1с 8.1", который этим и занимался. Смотрел какие индексы есть, какие поля остаются неиндексированными и, основываясь на этих данных, давал свои рекомендации. Надо признать, что спустя 4 года я понимаю какую ересь он предлагал, но сам подход, в принципе, можно, наверное считать приемлемым.
Ну так вот, возвращаясь к DTA. Я думал, что примерно такую же работу он и проводит. Но, оказалось, что это не совсем так. Точнее, возможно, именно этим он и занимается, но не в том виде, как я себе это представлял.
Итак, у SQL Server есть несколько интересных недокументированных возможностей.
1) При создании индекса есть возможность указать WITH STATISTICS_ONLY.
Это заставляет SQL Server создать гипотетический (hypothetical) индекс. Т.е. фактически создаётся такой индекс, который нельзя использовать в обычных запросах (и который никак не учитывается оптимизатором), но который, как бы существует. Ещё есть возможность указания WITH STATISTICS_ONLY = -1 - тогда индекс будет гипотетическим, но, вот статистика, созданная по этому индексу, будет вполне себе реальной.
2) DBCC AUTOPILOT и SET AUTOPILOT.
Как я уже говорил - гипотетический индекс не может использоваться при выполнении запросов. Но, с помощью DBCC AUTOPILOT мы можем заставить оптимизатор создать такой план запроса, какой он создал бы при наличии этого индекса в "обычном", не гипотетическом, виде.
Итак, чтобы использовать DBCC AUTOPILOT, выполним:
DBCC TRACEON (2588)
DBCC HELP('AUTOPILOT')
и увидим описание:
DBCC AUTOPILOT (typeid [, dbid [, {maxQueryCost | tabid [, indid [, pages [, flag [, rowcounts]]]]} ]])
Итак, чтобы выполнить запрос с учётом гипотетического индекса, в режиме "автопилота", нужно сделать следующее:
1. Создать индекс WITH STATISTICS_ONLY.
2. Узнать идентификатор БД: select db_id()
3. Узнать id таблицы и id индекса, например из sysindexes
4. Вместо остальных значений можно использовать нули, а можно попробовать поиграться и проанализировать результаты.
5. Выполнить SET AUTOPILOT ON
6. Выполнить DBCC AUTOPILOT.
7. Выполнить запрос и посмотреть его план выполнения. Если гипотетический индекс будет являться оптимальным для этого запроса, он будет использоваться.
8. Выполнить SET AUTOPILOT OFF
В общем, штука крутая, но с ней надо быть осторожным. Недокументированные возможности всегда используются на свой страх и риск (и я никому не советовал бы использовать их на продакшене).
О DBCC AUTOPILOT я узнал отсюда (мой перевод на хабре). Примерно тоже самое, от того же автора, но с более удобным вариантом использования есть здесь. О гипотетических индексах можно почитать здесь.
