| Author |
Topic |
|
rmg1
Constraint Violating Yak Guru
256 Posts |
 Posted - 2012-12-13 : 07:55:19
|
| Hi allNot sure if this is a dumb question or not, so apologies it if is.I've just inherited a reference table that has 2 indexes (indicies?) on it (amongst others).That wouldn't normall be a problem but the 2 indexes relate to exactly the same fields in exactly the same manner.They are both set to non-unique, non-clustered.I'm not sure if this will cause any performance issues and would like some advice on if I need to get rid of one of them? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-13 : 08:05:49
|
| yes if it is exactly same you should remove it. |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-13 : 08:09:26
|
| If they are indeed identical (i.e., same columns in the same order, same status on filtered vs non filtered, both non-clustered) then there is no benefit.You can script both indexes and compare the code to be sure that they are indeed identical.If they are identical, at the very least, it affects performance in updates and inserts, and consumes unnecessary storage. If you choose to delete one of those, test in a dev environment. You can also look at sys.dm_db_index_usage_stats to see the usage (when it was last used etc.) |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-12-13 : 08:11:41
|
| As well as suggestions above - run sampelk queries in execution plan ? Which indexe are you using?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-12-13 : 08:22:52
|
| Thanks folks, I'll drop one of them (does it matter which?) and see if that improves performance. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-13 : 08:26:38
|
| Have you checked that they don't have included columns?Removing an index should only affect update performance not read.Actually a thought - you could have two identical indexes and update stats on them at different times. Pretty sure that's not what these are for though otherwise you would know about it.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-12-13 : 08:36:48
|
| The indexes were absolutely identical in every way.I've removed one (I can put it back if necessary) and I'm going to see how thngs work now. |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-12-13 : 08:40:31
|
This is the create statement from one of the indexes:-USE [Reference]GO/****** Object: Index [idx_origin_CallType] Script Date: 12/13/2012 13:16:54 ******/CREATE NONCLUSTERED INDEX [idx_origin_CallType] ON [dbo].[ref_CASOriginsToServices] ( [Origin] ASC, [CallType] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO The other one was exactly identical except for the name of the index. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-13 : 10:15:43
|
| Just delete it. |
|
|
|
|
Multiple indexes on a table