Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    Unanswered: Hypothetical Indexes: What I know and don't.

    So I am sitting on a sizeable datapile at the moment and my team's particular pile is the performance stinker in the organization. So as my days usually start, there is an urgent email from my bosses boss directed to me and while trolling around this particular issue I find a number of hypothetical indexes. While trying to code and juggling a few things I was able learn from the interwebs...

    1. that someone probably launched that abomination that M$ calls the database tuning advisor against the production database (I know this is bad) and that the DTA probably crashed and that these got stuck in the database, because the DTA is supposed to remove them once it is done.

    2. I have seen the KB article regarding hypothetical clustered indexes causing performance issues even though this is not my issue. I just have hypothetical nonclusters.

    3. The reason for the performance issue mentioned in #2 is that there are excessive recompiles caused by the out of date statistics in the index because apparently hypothetical statistics are stagnant. Or something to that effect.

    What I do not know...(and I would hope someone here might)

    1. Is there a similar perf issue with the hypothetical nonclusters? I mean I still have indexes with stagnant statistics, right?

    2. Are my execution plans using them? I tried to run a couple of queries, but I did not get a seek, much less a scan, but like I said, today was a little hectic.

    3. And I was getting around to figuring this one out when it was time to leave today, but do they consume disk space?

    Don't go out of your way. If you know, let me know. If not, don't sweat it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    oh and if anyone knows why when I plug my headphones into my new android phone at home, they work, and when I am at the office they do not and the music comes out of the phones speaker, that would be keen. 2 days in a row. That would be my other riddle of the day.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I never use the DTA, either.

    I would say you could script out the hypothetical indexes, delete them, and see what happens. If something was benefiting from them, you can add those indexes back as real indexes.

    As always, have a look at profiler for some of the top users of reads. Generate a query plan, and look for any of the hypothetical indexes. In SQL 2005 and up, you can dump the query plan to an XML file, and do a text search for a part of the name of the index.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yeah. limited access to production. one of my partners already kicked it over the fence to the DBAs.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hypothetical clustered indexes can be really bad, because they physically reorganize your data. As per hypo-NC's - their presence by itself may or may not harm the performance. What does impact it is the accompanying set of hypothetical stats. The problem is not in the space they take, but in the hints that QO gets by looking at them, and consequently creating an execution plan that may be detremental to performance. Main thing is to drop both hypo's, indexes and stats.

    As per your headphones...I think you're on your own there
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Thanks. The DBAs are dropping them tonight.

    Headphones suddenly started working.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    HELP

    What the hell are hypothetical Indexes?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SQL Server 2005: Hypothetical index - a what?!?!

    OK

    M$ doing NONSENSE Stuff again they shouldn't be doing for developers who HAVEN'T GOT A CLUE

    READ A BOOK

    Damn Wizards and GUI's
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I did not run the DTA. Someone had against prod before I got here. Not my shame.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Did dropping them help at all?

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It usually does, but just in case run dbcc flushprocindb(<db_id>). It'll remove all existing execution plans from the procedure cache of the specified database.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •