How can an index slow down a select statement?

Question

I am having quite a difficult time at finding why adding an index on the foreign key of a table is slowing down the view of my colleague. This view is composed of several packed views with outer join and inner join. I tried to remove them one by one to figure out where the problem was, but I cannot say, it doesn't seem to come from a particular view but more from them all.

I knew indexes could slow down insert or that they were taking size on the hard drive, but I never read anywhere that they could be responsible for slowing down a view. The truth is when I do :

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

select top 20 * from  MyView

It takes 20 seconds with the index and 9 without.

CREATE NONCLUSTERED INDEX [IX_MyField] ON [dbo].MyTable
(
    [MyField] 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, FILLFACTOR = 90) ON [PRIMARY]

Answer 1

It's possible your OTHER indexes or stats are out of date. If they aren't current, it's possible the query analyzer is choosing a sub-optimal execution plan using your new index since it thinks that will be quicker.

Try running:

UPDATE STATISTICS WITH (FULLSCAN)

on your table.

Answer 2

Are you selecting any other columns from MyTable? If so, you're probably doing a Bookmark Lookup (or RID Lookup), which means you're going to back to your table for the additional data.

You should place any columns that you additionally select in the INCLUDE clause of the index.

Run both queries with execution plans enabled and compare the 2, identifying which portions of the query take longer.