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'
GO

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.)

CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON [Person].[Address] ([PostalCode], AddressLine1, 
AddressLine2, City, StateProvinceId, ModifiedDate)
WITH STATISTICS_ONLY = -1;
GO
CREATE INDEX IX_Address_PostalCode_Includes 
ON Person.Address(PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, 
StateProvinceId, ModifiedDate)
WITH STATISTICS_ONLY = -1;
GO

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 TRACEON(2588) WITH NO_INFOMSGS;
DBCC HELP ('AUTOPILOT') WITH NO_INFOMSGS;
-- 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;
GO
-- "turn on" the indexes
DBCC AUTOPILOT (0,7,373576369,6);
DBCC AUTOPILOT (0,7,373576369,7);
GO

Next we turn AUTOPILOT on.

SET 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);
GO
DROP INDEX Person.Address.ix_Address_PostalCode;
DROP INDEX Person.Address.ix_Address_PostalCode_Includes;
GO
CREATE INDEX IX_Address_PostalCode_Includes 
ON Person.Address(PostalCode) 
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceId, ModifiedDate);
GO

Filed under: Index, Microsoft SQL Server, Performance, Query Plans, SQLServerPedia Syndication, T-SQL Tagged: indexes, microsoft sql server, Performance, query plans