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

SQL Index Help.. Expand / Collapse
Author
Message
Posted Monday, January 21, 2013 5:12 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, July 26, 2014 5:26 PM
Points: 205, Visits: 744
Hi guys,

I have question for you guys,

I have one table ABC (For Example) and Table ABC has few Indexes

First Index on Table ABC
COLUMN_NAME = ABC.COLUMN1
COLUMN_NAME = ABC.COLUMN2

Second Index
Column_Name = ABC.COlumn1

My question is Would it be same. I can understand if i have Query with both column COlumn1 & Column2
then First Index is good. What you think should i delete Second Index or Keep, if i keep second
index its not gonna slow down the performance?

Please guide me.

Thank You
Post #1409753
Posted Monday, January 21, 2013 5:14 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:35 PM
Points: 21,340, Visits: 15,015
Since indexes are Left Based, the second index is a duplicate.

The only reason to not remove it is if somebody is using an index hint with the name of this second index.

Removing it should have no negative impact. But really, you should test in your dev/qa environments first.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1409754
Posted Tuesday, January 22, 2013 5:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:51 AM
Points: 138, Visits: 661
Unless your users have specific interest in using that index as an hint, thereby forcing the optimizer to think that you're smart enough to trick it. Apart from this I don't find any use of that index, as it's just a duplicate index lying there, and occupying unnecessary space. I would usually let the optimizer do that work for me rather than forcing it to choose something else which might no be the best alternative, and could impact performance.

Regards,
Faisal
Post #1409960
Posted Thursday, January 24, 2013 9:33 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:31 PM
Points: 1,977, Visits: 2,926
IF and only if ABC.COLUMN2 is a (very) large column, and ABC.COLUMN1 is used by itself a lot, it might be worthwhile to keep both indexes.

You should also review sys.dm_db_index_usage_stats to see how often, and in what ways, each index is currently being used.



SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1411213
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse