Hypothetical Indexes

Kenneth Fisher discusses hypothetical indexes:

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.

That’s completely new to me.

Related Posts

Generating Index Drop And Create Statements

Drew Furgiuele says “Game over, man, game over!” to indexes: The premise is simple: it will generate a series of DROP and then CREATE INDEX commands for every index. The process is a little more complex in practice, but at a high level it: Creates a special schema to house a temporary object, Creates a […]

Read More

Index That Column Or Include It?

Jeanne Combrinck lays out her recommendations on whether to make a particular column part of an index or have it be an included column: The original question we wanted to answer was whether we would see a performance difference when a query used the index with all columns in the key, versus the index with […]

Read More

Categories

November 2016
MTWTFSS
« Oct Dec »
 123456
78910111213
14151617181920
21222324252627
282930