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: Friday, October 17, 2014 3:37 PM
Points: 227, Visits: 886
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:31 AM
Points: 17,808, Visits: 15,729
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
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: Wednesday, October 15, 2014 8:04 AM
Points: 193, Visits: 745
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:54 AM
Points: 2,193, Visits: 3,300
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1411213
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse