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
Ranga N
Ranga N
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

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



Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44930 Visits: 39857
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.
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 usually 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
marshall.jones
marshall.jones
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47159 Visits: 44346
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1473 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
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2751 Visits: 2606
GilaMonster (9/16/2008)

It doesn't take into account existing indexes


Gail, can you explain what you mean ??



Kev
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: 1037 Visits: 2383
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
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9436 Visits: 9517
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
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 3740
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
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 3740
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