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: Monday, August 18, 2014 2:48 AM
Points: 335, Visits: 500
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: Monday, November 17, 2014 12:23 PM
Points: 1,360, Visits: 1,761
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


SSC Eights!

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

Group: General Forum Members
Last Login: Yesterday @ 11:03 PM
Points: 976, Visits: 3,045
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: Yesterday @ 8:31 AM
Points: 40,456, Visits: 36,912
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: Monday, August 18, 2014 2:48 AM
Points: 335, Visits: 500
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 2,268, Visits: 3,429
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
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: Tuesday, November 25, 2014 7:03 AM
Points: 590, Visits: 1,327
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
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