Missing Indexes in SQL Server 2005

Question

Missing Indexes in SQL Server 2005

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 More actions · Answer 2

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: 57190 More actions · Answer 3

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 More actions · Answer 4

Charles Kincaid

SSChampion

Points: 13593

September 17, 2008 at 1:27 pm

#873233

Thanks Rob.

ATBCharles Kincaid

einman33 SSCrazy Points: 2813 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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: 2813 More actions · Answer 8

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 More actions · Answer 9

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: 148809 More actions · Answer 10

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 More actions · Answer 11

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: 1003867 More actions · Answer 12

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.

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

Wayne West SSC-Insane Points: 22586 More actions · Answer 13

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 More actions · Answer 14

Narendra Kumar-227102

Newbie

Points: 3

December 24, 2008 at 6:47 am

#917358

Ranga,

Great info!

Narendra | SQL ADC | ML

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 15

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.

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