Недокументированные возможности Microsoft SQL Server: STATISTICS_ONLY, DBCC AUTOPILOT и SET AUTOPILOT

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

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

Собственно, вопрос заключается в том как создать «гипотетический» индекс? Просто для того, чтобы проверить действительно ли такой индекс будет полезен при выполнении запроса.

WITH STATISTICS_ONLY

Для создания гипотетического индекса, мы можем использовать недокументированную возможность команды CREATE INDEX. Например:

USE AdventureWorksDW GO CREATE INDEX ix_FirstName ON DimCustomer(FirstName) WITH STATISTICS_ONLY = -1 GO 

В результате будет создана статистика по этому индексу (построена гистограмма и рассчитана плотность) и появится запись в sys.indexes. Вы можете проверить это с помощью sp_helpindex и DBCC SHOWSTATISTICS:

sp_HelpIndex DimCustomer 

DBCC SHOW_STATISTICS(DimCustomer, ix_FirstName) 

Бенджамин Неварес описывает эти гипотетические индексы здесь.

P.S. Если вы создадите индекс используя WITH STATISTICS_ONLY = 0, SQL Server не будет создавать статистику. Только гипотетический индекс.

DBCC AUTOPILOT и SET AUTOPILOT

Теперь у нас есть гипотетический индекс, как нам его использовать?

Можно попробовать указать его явно, с помощью хинта:

SELECT * FROM DimCustomer WITH(index=ix_FirstName) WHERE FirstName = N'Eugene' 

и получить ошибку:
Msg 308, Level 16, State 1, Line 1 Index 'ix_FirstName' on table 'DimCustomer' (specified in the FROM clause) does not exist.

А если указать Index ID?

SELECT * FROM DimCustomer WITH(index=5) WHERE FirstName = N'Eugene' 

Тоже самое:
Msg 307, Level 16, State 1, Line 1 Index ID 5 on table 'DimCustomer' (specified in the FROM clause) does not exist.

Так каким же образом мы можем создать план запроса, учитывающий этот индекс?

Вот здесь-то и начинается вся забава.

DBCC AUTOPILOT используется для того, чтобы сказать оптимизатору, что при составлении плана нужно учитывать существование определённого индекса. Эта DBCC, совместно с флагом SET AUTOPILOT ON позволяют нам использовать этот индекс.

Посмотрим на синтаксис этой команды:

SET AUTOPILOT ON|OFF /*     DBCC TRACEON (2588)     DBCC HELP('AUTOPILOT') */ DBCC AUTOPILOT (typeid [, dbid [, {maxQueryCost | tabid [, indid [, pages [, flag [, rowcounts]]]]} ]]) 

Тестируем

Итак, посмотрим как это всё работает.

-- Current Cost = 0,762133 -- Clustered Index Scan on pk SELECT * FROM DimCustomer WHERE FirstName = N'Eugene' GO 

-- creating the index  -- DROP INDEX ix_FirstName ON DimCustomer CREATE INDEX ix_FirstName ON DimCustomer(FirstName) WITH STATISTICS_ONLY = -1 GO -- Looking at the info necessary in the DBCC AUTOPILOT comand SELECT name, id, Indid, Dpages, rowcnt  FROM sysindexes WHERE id = object_id('DimCustomer') GO 

DBCC AUTOPILOT (5, 9, 0, 0, 0, 0, 0) -- Starting with the TypeID 5 DBCC AUTOPILOT (6, 9, 37575172, 1, 0, 0, 0) -- Clustered Index with TypeID 6 DBCC AUTOPILOT (0, 9, 37575172, 2, 0, 0, 0) -- All other index with TypeID 0 DBCC AUTOPILOT (0, 9, 37575172, 3, 0, 0, 0) -- All other index with TypeID 0 DBCC AUTOPILOT (0, 9, 37575172, 5, 0, 0, 0) -- All other index with TypeID 0 GO SET AUTOPILOT ON GO -- Query to create the estimated execution plan with the cost = 0,0750712 SELECT * FROM dbo.DimCustomer WHERE FirstName = N'Eugene' OPTION (RECOMPILE) GO SET AUTOPILOT OFF GO 

прим. переводчика: Что из себя представляет параметр TypeID, естественно, неизвестно, в другой своей статье, ссылка на которую есть ниже, тот же самый автор, пишет, что для использования определённого индекса в режиме «автопилота», нужно указывать 0


Так же вы можете обмануть оптимизатор, передавая произвольные значения в DBCC AUTOPILOT, в качестве параметров Pages и RowCount. Если же передавать их равными нулю, будут использованы значения такие же, как для кластерного индекса.

Выводы

В описании этих возможностей остаётся достаточно «белых пятен», но я уверен, что что этот пост будет хорошей отправной точкой для ваших собственных тестов.

Я до сих пор играюсь с этой штукой, так что вы можете спокойно задавать мне вопросы, или делиться своими открытиями.

И вам ведь не нужно говорить, что вы не должны использовать всё это на рабочих серверах? Это недокументированная возможность, так что никто не может вам точно сказать что и как она делает, до тех пор пока парни из Microsoft не сделают её официально публичной и документированной.

От переводчика:
Присоединяясь к автору, напоминаю о том, что использование недокументированного функционала в рабочем окружении может привести к нежелательным последствиям.
Так же, хочу добавить ещё одну ссылку на того же автора: Hypothetical Indexes on SQL Server. Это более новая, расширенная версия того же поста. Почему я перевёл не её? Потому что суть у них одинаковая, главное отличие — это то, что в новой версии, он предлагает сборку CLR, для более просто использования DBCC AUTOPILOT, что, на мой взгляд, не существенно.
Весь код и все скриншоты взяты мной у автора, на моём SQL Server 2005 SP4 он потребовал минимального допиливания (например использования WITH STATISTICS_ONLY вместо WITH STATISTICS_ONLY = -1), так что всё должно работать на SQL Server 2005 и старше (именно, начиная с 2005-го сервера, используется Database Tuning Advisor, который, предположительно, и использует этот функционал).
Как обычно, любые пожелания и исправления по переводу и стилистике приветствуются.