Post reply

hypothecated index

  • January 11, 2008 at 7:33 am

    #120200

    Hi all,

    Can anyone please explain what an hypothecated index is and wat r ther impact of those indexes in tables.

    Thanks in Advance

  • January 11, 2008 at 7:47 am

    #767114

    select * from sysindexes where name like '_Wa_Sys%' in sql 2000 or 2005.

    SQL server collects statistics on every query you run....those statistics can be used to determine whether a new additional index might be warranted or not.

    when you run a query, and the query does not use an index, SQL Server might build a hidden, or hypothetical index based on the query and those statistics in order to see if there is a performance boost.

    Those indexes are named as you see in the query above, and are used from there on to increase performance.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 11, 2008 at 7:50 am

    #767119

    As per BOL..

    When Database Engine Tuning Advisor creates recommendations, it automatically creates names for the objects that it recommends. These objects are indexes, indexed views, statistics, partition functions, or partition schemes. Microsoft strongly recommends that you change these application-generated names before you implement a tuning recommendation. Otherwise, it is difficult to distinguish between objects that existed before tuning and those that are added by implementing a Database Engine Tuning Advisor recommendation.

    You can read more about it in BOL

    -Roy

  • January 11, 2008 at 8:09 am

    #767137

    A hypothetical index is created by the database tuning advisor as it's attempting to find the 'correct' indexes. It's dropped once the run is complete

    From what I understand, they're indexes that don't have a tree structure, just meta data and stats

    SQL Server 2005, check the is_hypothetical column in the sys.indexes.

    The _WA_Sys objects are statistics (not indexes) created by the query processor. They're there to help the query optimiser work out the number of rows affected. They can suggest that an index might be useful, but not always.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply