Missing Indexes in SQL Server 2005

  • Gail Shaw

    SSC Guru

    Points: 1004454

    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
  • charles.gary

    Newbie

    Points: 8

    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.

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    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]

  • Charles Kincaid

    SSChampion

    Points: 13593

    Thanks Rob.

    ATBCharles Kincaid

  • einman33

    SSCrazy

    Points: 2753

    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?

  • Charles Kincaid

    SSChampion

    Points: 13593

    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

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    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/

  • einman33

    SSCrazy

    Points: 2753

    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.

  • Wayne West

    SSC-Insane

    Points: 22586

    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]

  • John Mitchell-245523

    SSC Guru

    Points: 148519

    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

  • Charles Kincaid

    SSChampion

    Points: 13593

    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

  • Jeff Moden

    SSC Guru

    Points: 995462

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

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

  • Wayne West

    SSC-Insane

    Points: 22586

    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]

  • Narendra Kumar-227102

    Newbie

    Points: 3

    Ranga,

    Great info!

    Narendra | SQL ADC | ML

  • Jeff Moden

    SSC Guru

    Points: 995462

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    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