SQL Clone
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
Ranga N
Ranga N
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 151
Comments posted to this topic are about the item Missing Indexes in SQL Server 2005



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (337K reputation)SSC Guru (337K reputation)SSC Guru (337K reputation)SSC Guru (337K reputation)SSC Guru (337K reputation)SSC Guru (337K reputation)SSC Guru (337K reputation)SSC Guru (337K reputation)

Group: General Forum Members
Points: 337948 Visits: 42601
Great info! I gotta try it. Thanks.

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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
marshall.jones
marshall.jones
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 69
this is super handy.

does anyone know if i can truncate these tables at any time safely (for example before running a test load for example).
GilaMonster
GilaMonster
SSC Guru
SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)SSC Guru (368K reputation)

Group: General Forum Members
Points: 368386 Visits: 46943
The missing indexes DMVs are reset when the server starts. They can't be modified by a user.

One thing I would like to add is that the missing indexes DMVs should (like the Database Tuning Advisor) be taken as a suggestion and tested carefully before been applied. The missing indexes are less accurate than the DTA, as only single queries are considered when entries are added to that (the query optimiser adds the entries as it's optimising queries).

It doesn't take into account existing indexes, it doesn't compare with other similar suggestions already in the DMV and it doesn't considered clustered/nonclustered indexes.

They are very, very useful for index tuning, they just shouldn't be applies without consideration.

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


SuperDBA-207096
SuperDBA-207096
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3733 Visits: 711
GilaMonster (9/16/2008)

They are very, very useful for index tuning, they just shouldn't be applies without consideration.



Good point! Remember every action has consequences, so be sure to test!
kevriley
kevriley
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5246 Visits: 2637
GilaMonster (9/16/2008)

It doesn't take into account existing indexes


Gail, can you explain what you mean ??



Kev
Charles Kincaid
Charles Kincaid
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4495 Visits: 2384
Good article. Great starting point. I one time saw a script that used this info to build the suggested indexes.

There is also some DMV about unused indexes. I'd love to see a set of scripts that would build the missing and deleted the unused.

ATBCharles Kincaid
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54954 Visits: 9518
Great article, I'm looking forward to using this.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Marios Philippopoulos
Marios Philippopoulos
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21650 Visits: 3773
Gail already mentioned some of the caveats of the missing-index DMVs.

Here are a couple more:

(1) Because the data in the DMVs are kept in memory, they are at risk of being flushed from the cache if there is memory pressure. Therefore, the DMVs need to be polled regularly and stored in a database for later analysis. This is something Gail has mentioned before in one of the posts here.

(2) The DMVs are no substitute for a strategy of regular index defragmentation and statistics updating. The optimizer may make the wrong choices when it constructs the 'best' execution plans if indexes are defragmented and/or the stats is not up-to-date and accurate. This can cause "wrong" data to be deposited to the DMVs and therefore erroneous information.

I absolutely love the DMVs. They have opened a much-needed window into the internals of the database engine and have made DBA work all the more interesting. I use them all the time.

We just need to keep these limitations in mind as well.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Marios Philippopoulos
Marios Philippopoulos
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21650 Visits: 3773
See this great link on the same topic:

Uncover Hidden Data to Optimize Application Performance:
http://msdn.microsoft.com/en-us/magazine/cc135978.aspx

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
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