Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Missing Indexes in SQL Server 2005 Expand / Collapse
Author
Message
Posted Monday, September 15, 2008 9:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 18, 2013 11:20 AM
Points: 67, Visits: 135
Comments posted to this topic are about the item Missing Indexes in SQL Server 2005


Post #569915
Posted Monday, September 15, 2008 11:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #569936
Posted Tuesday, September 16, 2008 12:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 27, 2010 10:31 PM
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).
Post #569961
Posted Tuesday, September 16, 2008 3:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #570022
Posted Tuesday, September 16, 2008 4:59 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, 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!
Post #570081
Posted Tuesday, September 16, 2008 6:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 8:26 AM
Points: 2,716, Visits: 2,484
GilaMonster (9/16/2008)

It doesn't take into account existing indexes


Gail, can you explain what you mean ??



Kev
Post #570146
Posted Tuesday, September 16, 2008 8:23 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 11:42 AM
Points: 821, Visits: 2,028
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.


ATB

Charles Kincaid

Post #570270
Posted Tuesday, September 16, 2008 8:24 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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."
Post #570271
Posted Tuesday, September 16, 2008 8:27 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:24 AM
Points: 1,865, Visits: 3,620
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.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #570275
Posted Tuesday, September 16, 2008 9:05 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:24 AM
Points: 1,865, Visits: 3,620
See this great link on the same topic:

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


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #570331
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse