New indexes, hypothetically

Kenneth Fisher, 2016-11-02


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





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads