New indexes, hypothetically


November 2, 2016 by Kenneth Fisher


I saw something like this the other day. My first thought was “Hu, never seen that before.” My second thought was “Wow, that’s really cool. I wonder what a hypothetical index is?” A quick search later and I discovered that the DTA (database tuning adviser) uses them to test out what indexes will work best. A pretend (one might almost say hypothetical) index is created, with statistics, but without the actual index structure. Then a query plan is created allowing for that index.

This is pretty cool since creating a real index can take quite a bit of time, particularly on a really large table. It would be nice to be able to tell SQL that an index exists and try it out before actually spending the time creating it. I’d learned about a DB2 method of doing this a while back but wasn’t aware of one for SQL Server. In part that’s because it’s undocumented. Because the commands I’m going to use here are undocumented standard warnings apply.

So first here is a query that could use an index.

SELECT Title AS Salutation, FirstName, MiddleName, LastName, Suffix, AddressLine1, AddressLine2, 
	City, StateProvince.StateProvinceCode AS State, Address.PostalCode, Address.ModifiedDate, Address.ModifiedDate
FROM Person.Person
JOIN Person.BusinessEntityAddress
	ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID
JOIN Person.Address
	ON BusinessEntityAddress.AddressID= Address.AddressID
JOIN Person.StateProvince
	ON Address.StateProvinceID = StateProvince.StateProvinceId
WHERE Address.PostalCode = '98168'

Next let’s try creating a couple of possible indexes. (In case you happened to be curious if the optimizer would pick an index with more indexed columns or an index with included columns.)

ON [Person].[Address] ([PostalCode], AddressLine1, 
			AddressLine2, City, StateProvinceId, ModifiedDate)
CREATE INDEX IX_Address_PostalCode_Includes 
ON Person.Address(PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, 
	StateProvinceId, ModifiedDate)

Now that the indexes are created we need to tell SQL to use them. To do this we use the undocumented DBCC command AUTOPILOT. Even though it is undocumented we can still get information on it using DBCC HELP.


-- dbcc AUTOPILOT (typeid [, dbid [, {maxQueryCost | tabid [, 
--	indid [, pages [, flag [, rowcounts]]]]} ]])

I have no idea what a lot of these parameters do, but a few of them are pretty obvious.

After some reading and a bit of experimentation a typeid of 0 means the index can be tested and a 1 means that it can’t. So in order to turn both of these indexes on:

-- Collect the database, object and index IDs.
SELECT db_id() AS db_id, object_id, index_id FROM sys.indexes 
WHERE object_id = object_id('Person.Address')
  AND is_hypothetical = 1;
-- "turn on" the indexes
DBCC AUTOPILOT (0,7,373576369,6);
DBCC AUTOPILOT (0,7,373576369,7);

Next we turn AUTOPILOT on.


Note: This also runs SET SHOWPLAN XML on and has to be run in it’s own batch, so put a GO after it.

Now we run our query and we get the following results:


So the prefered index is the one with the includes! (Not overly surprising, since it is narrower and so faster.) Let’s turn everything off, drop the hypothetical indexes and create the correct one. I wouldn’t leave hypothetical indexes just laying around. Nothing is going to use one but when you look at indexes you’re liable to confuse yourself (well I do anyway).

DBCC AUTOPILOT (1,7,373576369,7);
DBCC AUTOPILOT (1,7,373576369,6);
DROP INDEX Person.Address.ix_Address_PostalCode;
DROP INDEX Person.Address.ix_Address_PostalCode_Includes;
CREATE INDEX IX_Address_PostalCode_Includes 
ON Person.Address(PostalCode) 
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceId, ModifiedDate);

One thought on “New indexes, hypothetically

  1. […] New indexes, hypothetically: Видел с базе гипотетические индексы, которые оставались после запуска DTA, но оказывается их можно создавать самому. Вау! Это открывает новые возможности. Представьте, вы создаёте гипотетический индекс на очень большой таблице очень быстро и проверяете будет ли оптимизатор его использовать, до того как потратить кучу времени на его создание. Интересный кейс. […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: