title: How can an index slow down a select statement?
body: <p>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.</p>
  
  <p>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 :</p>
  
  <pre><code>DBCC FREEPROCCACHE
  DBCC DROPCLEANBUFFERS
  GO
  
  select top 20 * from  MyView
  </code></pre>
  
  <p>It takes 20 seconds with the index and 9 without.</p>
  
  <pre><code>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]
  </code></pre>