Missing Index

Question

Missing Index

Sarah 38992

SSC Eights!

Points: 825
More actions
October 10, 2012 at 8:06 am

#268404

Hi
I'm currently trying to dig around to tweak the performance of our database. One of the tasks being removing unused indexes and creating missing indexes.
Using the sy missing index tables I've identified a missing index that confuses me as an almost identical one already exists and is regularly used. The only difference between the two indexes is the order in which the seek columns appear in the index creation script. Can the order of the columns in the creation script really make a difference as to whether the index is used or not?
Existing index creation script on DB
CREATE INDEX IDX_Claim_ColumnA_ColumB ON [Schema].[Table] ([ColumnA],[ColumnB]) INCLUDE ([Id]) ON [INDEXES]
Apparent Missing Index
CREATE INDEX IDX_Claim_ColumnB_ColumA ON [Schema].[Table] ([ColumnB],[ColumnA]) INCLUDE ([Id]) ON [INDEXES]
Thanks for any light you can shed on this.
Sarah

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Pyman SSChampion Points: 13830 More actions · Answer 1

Per your example the order does not matter. What is the size of the index and the table? What is the number of reads over writes for that index? Before dropping any index please verify if it is being used by any report (that runs once/twice monthly, quarterly or over a longer time frame).

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Sean Pearce SSCoach Points: 15999 More actions · Answer 2

Of course the order matters.

Picture a phone book. The index column order is Last Name, First Name.

Now if I asked you to find everyone who's last name is 'Smith' you could easily find them. However, to find everyone who's first name is 'Michael' would involve reading every single page i.e. an index scan.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Absolutely, the order that index columns are specified matters hugely.

http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

That said, you need to have a look at the queries that use that table and see if both indexes really are necessary. The missing index feature looks for the absolute best index for each and every individual query, it doesn't take more than one query into account when producing those suggestions. You need to do some deeper analysis yourself, you cannot just take those suggestions and implement them blindly.

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

Sarah 38992 SSC Eights! Points: 825 More actions · Answer 4

Hi

Thanks for your replies. Every day is a school day!

I have been using the missing index functionality as a starting point and then doing some more digging to see if it is really necessary. On that I have discovered that we are only reorganising indexes nightly not rebuilding them when fragmentation gets above 30%. Eureka! I'm going to sort that one out straight away which may improve things drastically before I start looking at which indexes are actually missing.

Thanks again

Sarah

ScottPletcher SSC Guru Points: 100946 More actions · Answer 5

It's also possible you need the other index instead, rather than in addition to, the one you have know.

You always need to look at the usage stats too when looking at the missing index stats.

And, as noted, take SQL's recommendations with extreme caution. SQL always wants an index, even if it's not really justified when you consider all information about the table.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

naga.rohitkumar SSCertifiable Points: 5557 More actions · Answer 6

hi , check this if u feel it 's use full to You

SELECT

TOP 10

[Total Cost]

= ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)

, avg_user_impact

, TableName = statement

, [EqualityUsage] = equality_columns

, [InequalityUsage] = inequality_columns

, [Include Cloumns] = included_columns

FROM

sys.dm_db_missing_index_groups g INNER

JOIN sys.dm_db_missing_index_group_stats s

ON s.group_handle = g.index_group_handle INNER

JOIN sys.dm_db_missing_index_details d

ON d.index_handle = g.index_handle

ORDER

BY [Total Cost] DESC;

Thanks
Naga.Rohitkumar

Vegard Hagen Hall of Fame Points: 3078 More actions · Answer 7

I know it's already been covered, but just to put in cleartext:

The reason why you need to be careful about which indexes to add is that adding indexes can actually harm performance if it's not the indexes you really need and are going to use. The indexes themselves need to be updated and maintained, so if you're not really using them then you're just wasting time and resources doing work that's not good for anything.

Kind regards,

Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

It is better to light a candle than to curse the darkness. (Chinese proverb)