Saturday 7 April 2018

Hypothetical Indexes in SQL Server

Problem:


While attempting to rebuild all indexes on a table, I am getting the following error:

Cannot rebuild hypothetical index '_dta_index_table_name_10_992214785__K9' online.

What is this hypothetical index and I cannot see any such indexes in the object explorer against the specific table. How can I get over this error.


By: Anonymous


Solution:


You or someone who works on the database should have used Database Tuning Advisor (DTA) to analyze the performance aspect of the tables and indexes. DTA creates hypothetical Indexes when it checks queries & tables for columns to measure the performance gain that such indexes may offer and thus come up with a recommendation. Usually when the DTA completes its analysis, it deletes all the hypothetical indexes if it cleanly finishes but in some cases, these indexes may be left over.


For a query or a stored procedure execution, after an initial recompile is triggered, the optimizer uses some of the information from these hypothetical indexes, which is out of date, and hence incorrectly determines that a recompile is needed again. During the ensuing recompiles, the information from the hypothetical indexes is never refreshed, and so the optimizer remains in a recompile loop. Hence, dropping hypothetical indexes is a better choice.


Clustered hypothetical indexes can be dropped with DROP INDEX statement. Nonclustered hypothetical indexes can be dropped with DROP STATISTICS statement.

You may find out all hypothetical indexes using this query:


WITH [hypo] AS (

SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(i.[object_id])) AS [Table] , QUOTENAME([i].[name]) AS [Index_or_Statistics], 1 AS [Type]
FROM sys.[indexes] AS [i]
JOIN sys.[objects] AS [o] ON i.[object_id] = o.[object_id]
WHERE 1=1
AND INDEXPROPERTY(i.[object_id], i.[name], 'IsHypothetical') = 1
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1

UNION ALL

SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(o.[object_id])) AS [Table], QUOTENAME([s].[name]) AS [Index_or_Statistics], 2 AS [Type]
FROM sys.[stats] AS [s]
JOIN sys.[objects] AS [o] ON [o].[object_id] = [s].[object_id]
WHERE [s].[user_created] = 0
AND [o].[name] LIKE '[_]dta[_]%'
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1
)

SELECT [hypo].[Table],[hypo].[Index_or_Statistics], 
CASE [hypo].[Type]
WHEN 1 THEN 'DROP INDEX ' + [hypo].[Index_or_Statistics] + ' ON ' + [hypo].[Table] + ';'
WHEN 2 THEN 'DROP STATISTICS ' + [hypo].[Table] + '.' + [hypo].[Index_or_Statistics] + ';'
END AS [Drop Stmt]
FROM [hypo]


Hope this helps you.