5

I am in the process of optimizing a long running T-SQL query. Somehow, I remember there is a feature in SQL Server that allows checking how a plan would be if an index would exist. Something like CREATE VIRTUAL INDEX and then checking the plan. But I do not find how to achieve this.

Is there a feature like this in SQL Server 2008R2?

FrankPl
  • 13,205
  • 2
  • 14
  • 40
  • Never heard of `VIRTUAL INDEX` I know of `INDEXED VIEWS`. Usually if I want to test how something would execute with specific index. I actually create it, run my query and drop it after the fact if I don't need it. –  Jan 21 '14 at 16:05
  • I think you probably need something like Sql Server Tuning Advisor which looks at your query and Advises you what Indexes can help to improve performance of a query. It doesn’t create any virtual indexes but it only gives you suggestion about what indexes can possibly improve the performance of a query and by how much. have a look here http://technet.microsoft.com/en-us/library/ms173494(v=sql.105).aspx – M.Ali Jan 21 '14 at 16:21
  • After you have seen the suggestions by tuning Advisor then you have to make the decision of whether to create that index or not depending on your personal requirements. – M.Ali Jan 21 '14 at 16:24
  • When you review your `ACTUAL EXECUTION PLAN` does it suggest to add index? You can not depend on it, but SQL Server should suggest new indexes and how good it will improve your query. –  Jan 21 '14 at 16:26
  • 3
    You are probably thinking of [hypothetical indexes](http://blogs.solidq.com/fabianosqlserver/post.aspx?id=39). There is not a documented way to use them yourself. – Martin Smith Jan 21 '14 at 17:27
  • @M.Ali, the Database Tuning advisor does create hypothetical indexes, this is how it determines the benefits of adding the indexes it suggests, when it crashes it leaves them all behind requiring manual cleanup, something that I've had to do a fair bit as a DBA. – steoleary Jan 21 '14 at 17:33

1 Answers1

8

Yes you can, they are called hypothetical indexes, not virtual indexes. They are normally created by the database tuning advisor and are ignored by the query optimiser unless you run the query in autopilot mode.

So, to do this you need to do the following:

Create your hypothetical index on your table:

CREATE NONCLUSTERED INDEX IX_Hypothetical ON dbo.tableName (columnName) 
WITH STATISTICS_ONLY = -1

This creates an entry in the sys.indexes table with the is_hypothetical flag set to 1 (i.e. the index doesn't actually exist, just the statistics)

You then need to find out some information about the index to give to the autopilot, you need the database id, the object id of the table and the id number of the index, which you can get with the following query:

SELECT dbid = DB_ID(),
       objectid = 
object_id,
       indid = index_id
  FROM sys.indexes
 WHERE 
object_id = 
OBJECT_ID('dbo.tableName')
   AND is_hypothetical = 1

In my case, dbid = 7, objectid = 1237579447 and indid = 4

You can then run your query in autopilot mode to get an execution plan that could be generated if the hypothetical index you created actually existed on your table:

DBCC AUTOPILOT(0, 7, 1237579447, 4)
GO
SET AUTOPILOT ON
GO
SELECT * FROM dbo.tableName
WHERE columnName = 8
GO
SET AUTOPILOT OFF

The 0 is the typeid of the object, 0 is nonclustered index and I believe 6 is clustered index, you can run DBCC AUTOPILOT multiple times with different indexes if you have created more than one before running SET AUTOPILOT ON so that you can get the optimiser to evaluate all of them.

Also bear in mind that this is completely undocumented by Microsoft so not recommended for use outside of a dev machine and could change between versions with no warning, so don't rely too heavily on it working like this.

steoleary
  • 8,968
  • 2
  • 33
  • 47