SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Missing Index


Missing Index

Author
Message
Sarah 38992
Sarah 38992
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 506
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
sasken
sasken
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3576 Visits: 1948
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
Sean Pearce
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4098 Visits: 3436
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

@SeanPearceSQL

About Me
GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227023 Visits: 46335
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
Sarah 38992
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 506
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
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19813 Visits: 7412
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
naga.rohitkumar
naga.rohitkumar
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1572 Visits: 1378
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
Vegard Hagen
SSC Eights!
SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)

Group: General Forum Members
Points: 921 Visits: 387
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.



Vegard Hagen
Norwegian DBA, blogger and generally a nice guy who believes the world is big enough for all of us.

@vegard_hagen on Twitter
Blog: Vegard's corner (No actual SQL stuff here - haven't found my niche yet. Maybe some day...)

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


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