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 Tuesday, September 16, 2008 10:48 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: Today @ 3:17 PM
Points: 42,450, Visits: 35,505
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
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
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: Tuesday, December 10, 2013 4:10 PM
Points: 205, Visits: 365
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
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: Today @ 5:21 AM
Points: 1,205, Visits: 923
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

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #570870
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: Today @ 5:21 AM
Points: 1,205, Visits: 923
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

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #570873
Posted Wednesday, September 17, 2008 6:35 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: Today @ 3:17 PM
Points: 42,450, Visits: 35,505
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
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
Posted Wednesday, September 17, 2008 9:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 4:13 AM
Points: 1,860, Visits: 3,597
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 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 #571118
Posted Wednesday, September 17, 2008 1:27 PM


SSC Eights!

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

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
Thanks Rob.

ATB

Charles Kincaid

Post #571281
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 ««12345»»»

Permissions Expand / Collapse