January 21, 2013 at 5:12 pm
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
January 21, 2013 at 5:14 pm
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[/url]
Learn Extended Events
January 22, 2013 at 5:11 am
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
January 24, 2013 at 9:33 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply