Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Ranga Narasimhan » Missing Indexes in SQL Server 2005
Add to briefcase
47 posts, Page 2 of 5
««12345»»»

Missing Indexes in SQL Server 2005 Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
GilaMonster
Posted Tuesday, September 16, 2008 10:48 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 37,744, Visits: 30,023
kevriley (9/16/2008)
GilaMonster (9/16/2008)

It doesn't take into account existing indexes


Gail, can you explain what you mean ??

Kev


If there's an existing index on a table on (for example) columns A, B, C and then the optimiser optimises a query and decides that an index on A, B INCLUDE D would be very useful, it will suggest that as a new index even though just adding D as an include to the existing index would work just as well.

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 #570444
bob.taylor
Posted Tuesday, September 16, 2008 6:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 21, 2010 12:21 PM
Points: 1, Visits: 12
And in SQL Server 2008, it gets even easier. When you display an actual execution plan in SQL Server Management Studio query windows, you will receive a message in green indicating the missing index information!

boB Taylor, MCA: Database
Post #570704
Rob Reid-246754
Posted Wednesday, September 17, 2008 3:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 21, 2013 7:23 AM
Points: 205, Visits: 363
In answer to Charles request to see a script that puts all this info into use with a script that runs on a timed basis that creates a list of index recommendations to create and drop.

http://blogs.msdn.com/queryoptteam/archive/2006/06/01/613516.aspx

I use it on my system and its a good tool for advice but as Gila said you will often find that you get
very similar recommendations from it.
Post #570869
Manie Verster
Posted Wednesday, September 17, 2008 3:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 3:00 AM
Points: 1,151, Visits: 879
Very good article! I immediately copied the code and ran it on my database but I have a couple of questions if anyone can help me please. I do not quite understand inequality_columns, equality_columns and included_columns. Which of these columns should be used to test your indexes? 'Scuse me if I sound a bit dumb but I am still learning about these things.

Manie Verster
Developer
Johannesburg
South Africa

Life is about choices.... I choose to be happy today
Post #570870
Manie Verster
Posted Wednesday, September 17, 2008 4:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 3:00 AM
Points: 1,151, Visits: 879
Hi all,
Just after I posted my reply on this web I came upon another website (MSDN) where someone posted a blog on this exact same article. Visit it and let me know what you think.
http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

Manie Verster
Developer
Johannesburg
South Africa

Life is about choices.... I choose to be happy today
Post #570873
GilaMonster
Posted Wednesday, September 17, 2008 6:35 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 37,744, Visits: 30,023
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 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 #570947
charles.gary
Posted Wednesday, September 17, 2008 9:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2008 9:53 AM
Points: 2, Visits: 9
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.


Post #571115
Marios Philippopoulos
Posted Wednesday, September 17, 2008 9:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:04 AM
Points: 1,825, Visits: 3,478
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.

__________________________________________________________________________________

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 #571118
Charles Kincaid
Posted Wednesday, September 17, 2008 1:27 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:18 AM
Points: 772, Visits: 1,825
Thanks Rob.

ATB

Charles Kincaid
Post #571281
einman33
Posted Wednesday, September 17, 2008 3:08 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 8:14 AM
Points: 567, Visits: 512
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?
Post #571338
« Prev Topic | Next Topic »

Add to briefcase
47 posts, Page 2 of 5
««12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.