Недокументированные возможности 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 не сделают её официально публичной и документированной.

Ссылки по теме

• Купить программу Microsoft SQL Server 2012
• Каталог программ Microsoft
• Задать вопрос ONLine по покупке программ в интернет магазине ITShop
• 20% на переход с Windows Server Standard на редакцию Windows Server Datacenter
• Авторизованные курсы обучения по продуктам Microsoft