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

Missing Index Expand / Collapse
Author
Message
Posted Wednesday, October 10, 2012 8:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 14, 2012 6:42 AM
Points: 333, Visits: 483
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
Post #1370939
Posted Wednesday, October 10, 2012 8:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 5:39 PM
Points: 1,318, Visits: 1,692
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
Post #1370948
Posted Wednesday, October 10, 2012 8:51 AM


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: Monday, April 14, 2014 11:49 AM
Points: 739, Visits: 2,470
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
Post #1370983
Posted Wednesday, October 10, 2012 9:09 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 @ 11:52 AM
Points: 41,530, Visits: 34,446
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 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 #1370995
Posted Wednesday, October 10, 2012 9:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 14, 2012 6:42 AM
Points: 333, Visits: 483
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

Post #1371020
Posted Wednesday, October 10, 2012 11:07 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:53 PM
Points: 1,746, Visits: 2,553
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)
I'm not fat, I'm gravity challenged.
Post #1371045
Posted Monday, October 29, 2012 11:32 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: Saturday, December 21, 2013 10:30 AM
Points: 549, Visits: 1,177
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 & Regards
NAGA.ROHITKUMAR
Post #1378569
Posted Tuesday, October 30, 2012 3:01 AM


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: Friday, April 11, 2014 6:50 AM
Points: 517, Visits: 346
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)

Post #1378620
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse