SQL Server. DBCC WRITEPAGE. Ещё одна недокументированная функция

Закончив с учёбой и появивишись на работе, добрался до гугл ридера. Там меня ждал вот такой вот пост Пола Рэндала, посвящённый недокументированной DBCC WRITEPAGE (какой-то месяц недокументированных команд SQL Server прям).
Название, в принципе, вполне себе говорящее. Она позволяет "на лету" подправить содержимое ЛЮБОЙ страницы в БД.
Пол говорит, что эта штука может использоваться коммандой SQL Team для починки БД клиента. Плюс с её помощью можно испортить себе БД для тестов, либо наоборот на лету починить повреждённую БД (в том случае если есть полная уверенность в своих действиях).
Синтатксис у неё простой:
dbcc WRITEPAGE ({'dbname' | dbid}, fileid, pageid, offset, length, data [, directORbufferpool])
(
проверить, что синтаксис не отличается от приведённого выше, можно очень просто:
DBCC TRACEON (2588);
GO
DBCC HELP ('WRITEPAGE');
GO
)
, где offset - смещение в байтах относительно начала файла (начало файла = 0), length - длина изменения в байтах (от 1 до 8), data - новые данные для вставки (в 16-м виде, '0xAABBCC' - пример строки длиной три байта), directORbufferpool - должен ли быть задействован buffer pool (да - 0, нет - 1).
Последний параметр очень важен. Если вы укажете, что buffer pool задействовать не нужно, то запись будет произведена непосредственно на диск! Это значит, что SQL Server не пересчитает контрольную сумму, и при попытке чтения изменённой страницы, появится сообщение о повреждении страницы.
Пример такого повреждения можно посмотреть непосредственно в блоге Пола Рэндала, ссылка есть в начале моего поста.

Конечно, такой функционал не должен применяться на рабочем сервере, только на тестовом и в тестовых целях. DBCC WRITEPAGE может стать ещё одним способом выстрелить себе в ногу, при попытке использовать его на рабочей базе, не до конца осознавая собственные действия. 

Как работает DTA в SQL Server

В 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 я узнал отсюда (мой перевод на хабре). Примерно тоже самое, от того же автора, но с более удобным вариантом использования есть здесь. О гипотетических индексах можно почитать здесь.