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: Yesterday @ 1:44 PM
Points: 219, Visits: 829
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: Yesterday @ 4:51 PM
Points: 21,733, Visits: 15,424
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


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


Group: General Forum Members
Last Login: 2 days ago @ 11:58 PM
Points: 173, Visits: 723
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: Yesterday @ 4:05 PM
Points: 2,070, Visits: 3,113
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)
"And in the evening, After the fire and the light /
One thing is certain: Nothing can hold back the night /
Time is relentless, And as the past disappears /
We're on the verge of all things new, We are two thousand years" : the inimitable Mr. Billy Joel
Post #1411213
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse