Geek Answers Handbook

What are the hypothetical indicators?

Does anyone know what hypothetical indexes are used for SQL Server 2000? I have a table with 15 + such indexes, but I have no idea what they were created for. Can they slow down removal / insertion?

+6
source share
5 answers

hypothetical indexes, as a rule, are created when the index settings wizard is launched and are sentences; under normal circumstances, they will be deleted if the wizard executes OK.

If some of them are left, they may cause some problems, see this link for ways to remove them .

+3
source

2000, 2005 , DTA ( )

, , :

SELECT  *
FROM    sys.indexes
WHERE   is_hypothetical = 1

, , , , , , , , , , . .

+1

Google " SQL Server" . :

, DTA (Database Tuning Advisor)

+1

Hypothetical indexes are those generated by the database configuration advisor. Generally speaking, too many indexes are not a great idea, and you should study your query plans to trim those that are not used.

0
source

From sys.indexes :

is_hypothetical     bit     

1 = Index is hypothetical and cannot be used directly as a data access path. 
    Hypothetical indexes hold column-level statistics.

0 = Index is not hypothetical.

They can also be created manually with undocumented WITH STATISTICS_ONLY:

CREATE TABLE tab(id INT PRIMARY KEY, i INT);

CREATE INDEX MyHypIndex ON tab(i) WITH STATISTICS_ONLY = 0;
/* 0 - withoud statistics -1 - generate statistics */

SELECT name, is_hypothetical
FROM sys.indexes
WHERE object_id = OBJECT_ID('tab');

DB <> Fiddle Demo

0
source

More articles:


All Articles