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
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: Wednesday, November 19, 2014 2:52 PM
Points: 238, Visits: 955
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

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



Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 17,948, Visits: 15,944
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...


Posting Performance Based Questions - Gail Shaw
Post #1409754
Posted Tuesday, January 22, 2013 5:11 AM


Group: General Forum Members
Last Login: Monday, November 17, 2014 3:13 AM
Points: 194, Visits: 752
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.

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


Group: General Forum Members
Last Login: 2 days ago @ 3:44 PM
Points: 2,266, Visits: 3,419
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