SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple indexes on a table		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rmg1
Constraint Violating Yak Guru

253 Posts
Posted - 12/13/2012 :  07:55:19  Show Profile  Reply with Quote
Hi all

Not 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
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 12/13/2012 :  08:05:49  Show Profile  Reply with Quote
yes if it is exactly same you should remove it.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 12/13/2012 :  08:09:26  Show Profile  Reply with Quote
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.)
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1795 Posts
Posted - 12/13/2012 :  08:11:41  Show Profile  Visit jackv's Homepage  Reply with Quote
As well as suggestions above - run sampelk queries in execution plan ? Which indexe are you using?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

rmg1
Constraint Violating Yak Guru

253 Posts
Posted - 12/13/2012 :  08:22:52  Show Profile  Reply with Quote
Thanks folks, I'll drop one of them (does it matter which?) and see if that improves performance.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 12/13/2012 :  08:26:38  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Edited by - nigelrivett on 12/13/2012 08:28:09
Go to Top of Page

rmg1
Constraint Violating Yak Guru

253 Posts
Posted - 12/13/2012 :  08:36:48  Show Profile  Reply with Quote
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.
Go to Top of Page

rmg1
Constraint Violating Yak Guru

253 Posts
Posted - 12/13/2012 :  08:40:31  Show Profile  Reply with Quote
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.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 12/13/2012 :  10:15:43  Show Profile  Reply with Quote
Just delete it.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000