Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Missing Indexes in SQL Server 2005


Missing Indexes in SQL Server 2005

Author
Message
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1049 Visits: 2383
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. Smile Case in point: IE7 Pro. 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
timothyawiseman
SSC Eights!
SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)

Group: General Forum Members
Points: 806 Visits: 920
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
einman33
Mr or Mrs. 500
Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)

Group: General Forum Members
Points: 575 Visits: 512
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
Wayne West
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1544 Visits: 3694
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.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7520 Visits: 15175
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
Charles Kincaid
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1049 Visits: 2383
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 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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45450 Visits: 39946
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. Wink

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Wayne West
Wayne West
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1544 Visits: 3694
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. Wink

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....

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Narendra Kumar-227102
Narendra Kumar-227102
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 9
Ranga,

Great info!

Narendra | SQL ADC | ML
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45450 Visits: 39946
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. Wink

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. Wink

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search