Does anybody know what hypothetical indexes are used for in sql server 2000? I have a table with 15+ such indexes, but have no idea what they were created for. Can they slow down deletes/inserts?
+1
A:
Not sure about 2000, but in 2005 hypothetical indexes and database objects in general are objects created by DTA (Database Tuning Advisor)
You can check if an index is hypothetical by running this query:
SELECT *
FROM sys.indexes
WHERE is_hypothetical = 1
If you have given the tuning advisor good information on which to base it's indexing strategy, then I would say to generally trust its results, but if you should of course examine how it has allocated these before you trust it blindly. Every situation will be different.
Galwegian
2008-09-29 15:36:49
A:
Hypothetical indexes are those generated by the Database Tuning Advisor. Generally speaking, having too many indexes is not a great idea and you should examine your query plans to prune those which are not being used.
Ben Hoffstein
2008-09-29 15:37:02
+1
A:
Please, at least do a Google (or whatever) search before posting on SO. A search for "sql server hypothetical indexes" returned the following article as the first result. Quote:
Hypothetical indexes and database objects in general are simply objects created by DTA (Database Tuning Advisor)
Cd-MaN
2008-09-29 15:38:59
A google search did not tell me if leaving the hypothetical indexes on the table added any benefit to the queries I made, hence the question above.
2008-09-29 15:48:46
A:
hypothetical indexes are usually created when you run index tuning wizard, and are suggestions, under normal circumstances they will be removed if the wizard runs OK.
If some are left around they can cause some issues, see this link for ways to remove them.
curtisk
2008-09-29 15:40:22