Missing Indexes in SQL Server 2005

  • The equality/inequality refers to how the columns are used in the query that resulted in the missing index entry.

    Generally, if you are going to use the missing index stats DMV to create indexes, you want to create the index with first the equality columns, then the inequality columns and put the columns listed as include columns in the INCLUDE list.

    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
  • Isnt this an incorrect filter?

    where d.database_id = d.database_id and d.object_id = d.object_id

    Also you can get more info on this from

    http://msdn.microsoft.com/en-us/library/ms345405(SQL.90).aspx

    This article will show you how to interpret the meaning of the columns and how to build indexes off of the results.

  • charles.gary (9/17/2008)


    Isnt this an incorrect filter?

    where d.database_id = d.database_id and d.object_id = d.object_id

    Also you can get more info on this from

    http://msdn.microsoft.com/en-us/library/ms345405(SQL.90).aspx

    This article will show you how to interpret the meaning of the columns and how to build indexes off of the results.

    ...and there is this link as well, right below the link you mention on the limitations of the missing-index methodology:

    http://msdn.microsoft.com/en-us/library/ms345485(SQL.90).aspx

    Thank you, I hadn't seen these links til now.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Thanks Rob.

    ATBCharles Kincaid

  • Great info.

    I see a lot of these in all my environments:

    CREATE INDEX [missing_index_537_536_MSdistribution_history] ON [distribution].[dbo].[MSdistribution_history] ([agent_id],[time]) INCLUDE ([runstatus], [start_time], [timestamp])

    Looks like replication needs some help, but fear would not let me add this!!! Anyone else seeing their distribution DB showing up also?

  • Eric Inman (9/17/2008)


    Great info.

    I see a lot of these in all my environments:

    CREATE INDEX [missing_index_537_536_MSdistribution_history] ON [distribution].[dbo].[MSdistribution_history] ([agent_id],[time]) INCLUDE ([runstatus], [start_time], [timestamp])

    Looks like replication needs some help, but fear would not let me add this!!! Anyone else seeing their distribution DB showing up also?

    Looks like MS is keeping to the 80 / 20 rule. They get a product 80% there and leave 20% for us third party folk. 🙂 Case in point: IE7 Pro[/url]. SQL Server Central has a great spell checker but some of the other forums don't

    Seriously, I would only mess with replication in a test environment that you would have no trouble recreating if you jack it up. Not too surprised that they might have missed an index. This could also be a case where "Your mileage has varried".

    ATBCharles Kincaid

  • Excellent article. Thank you.

    Also, remember you can get similar information in SQL Server 2005 for a specific query by looking at the xml plan and its missing index section. It can be enabled by running

    set showplan_xml on

    before executing the query.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • My guess is it involves maybe the replication monitor. This may explain why it takes so much time to load the repl monitor for us. If I find anything of value i will post to a new thread in the replication section.

  • Thanks for the article, Ranga, very useful. I look forward to getting our ERP system into 2005 or 2008 as I know they're not doing a good job indexing, but I'm a little leery of altering or adding indexes without some solid backup proof.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • Bear in mind also that the DMVs are cleared down not only when SQL Server restarts, but also when databases undergo certain changes of state, such as to or from READ_WRITE.

    John

  • John Mitchell (11/25/2008)


    Bear in mind also that the DMVs are cleared down not only when SQL Server restarts, but also when databases undergo certain changes of state, such as to or from READ_WRITE.

    John

    Cool 😎 That means that I could programatically define a workload by altering the DB status and not have to restart the whole instance.

    ATBCharles Kincaid

  • Wayne West (10/22/2008)


    Thanks for the article, Ranga, very useful. I look forward to getting our ERP system into 2005 or 2008 as I know they're not doing a good job indexing, but I'm a little leery of altering or adding indexes without some solid backup proof.

    Oh... be careful... the worst part about an ERP system is that it enables folks who have no clue about how databases work or how to get performance out of the system, including index usage, to write 62 table joins with a built in cross join like the one I've recently seen. The old saying of "If you make something idiot proof, only idiots will use it" has a lot of truth to it when it comes to using the wonderful features of ERP's. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (11/25/2008)


    Wayne West (10/22/2008)


    Thanks for the article, Ranga, very useful. I look forward to getting our ERP system into 2005 or 2008 as I know they're not doing a good job indexing, but I'm a little leery of altering or adding indexes without some solid backup proof.

    Oh... be careful... the worst part about an ERP system is that it enables folks who have no clue about how databases work or how to get performance out of the system, including index usage, to write 62 table joins with a built in cross join like the one I've recently seen. The old saying of "If you make something idiot proof, only idiots will use it" has a lot of truth to it when it comes to using the wonderful features of ERP's. 😉

    Haven't yet found a 62 table join, the most I've seen in the canned views that they supplied for reporting is probably 7 or 8 as there's pretty much zero T-SQL code in the system. They're doing everything through a 4GL "application server" and wonder why their performance isn't acceptable to us! We just found an error message indicating that they're using cursors in their code, it's going to make for an interesting phone conference this AM....

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • Ranga,

    Great info!

    Narendra | SQL ADC | ML

  • Wayne West (12/4/2008)


    Jeff Moden (11/25/2008)


    Wayne West (10/22/2008)


    Thanks for the article, Ranga, very useful. I look forward to getting our ERP system into 2005 or 2008 as I know they're not doing a good job indexing, but I'm a little leery of altering or adding indexes without some solid backup proof.

    Oh... be careful... the worst part about an ERP system is that it enables folks who have no clue about how databases work or how to get performance out of the system, including index usage, to write 62 table joins with a built in cross join like the one I've recently seen. The old saying of "If you make something idiot proof, only idiots will use it" has a lot of truth to it when it comes to using the wonderful features of ERP's. 😉

    Haven't yet found a 62 table join, the most I've seen in the canned views that they supplied for reporting is probably 7 or 8 as there's pretty much zero T-SQL code in the system. They're doing everything through a 4GL "application server" and wonder why their performance isn't acceptable to us! We just found an error message indicating that they're using cursors in their code, it's going to make for an interesting phone conference this AM....

    Director of Technology for my old company was evaluating some "real time replication" software that used triggers to do the replication. I asked if I could take a look... not only were they RBAR, but they couldn't handle batch inserts. No matter how many rows you inserted in a single insert, it would only replicate the "first" row it came across. Than was an "interesting phone conference" in the AM, as well. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 16 through 30 (of 47 total)

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