SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

New indexes, hypothetically


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

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


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...