Search code examples
sqlsql-serverquery-optimization

SQL Server non-clustered index and the query optimizer

In one of the projects I am working on, there is a table which has about one million records. For better performance I created a non-clustered index and defined sid field as index key column. When I execute this query

SELECT [id]
      ,[sid]
      ,[idm]
      ,[origin]
      ,[status]
      ,[pid]
  FROM [EpollText_Db].[dbo].[PhoneNumbers] where sid = 9

enter image description here

The execution plan is like the above picture. My question is, why does SQL server ignore the sid index and scan the whole one million records instead, to find the query result. Your help is greatly appreciated

Solution

  • I believe that the problem is in the size of your result. You are selecting ten thousand records from your database which is quite a lot if you consider the necessary query plan that would include index seek operation. The plan includes index seek would be something like this

    enter image description here

    Therefore, ten thousand key lookups would be included and a significant number of random logical accesses. Due to this, if your table row is small, he could decide to use clustered index scan. If you are really concerned about the performance of this query create a covering index:

     CREATE INDEX idx_PhoneNumbers_sid 
    ON [EpollText_Db].[dbo].[PhoneNumbers](sid)
    INCLUDE ([id],[idm],[origin],[status],[pid])

    However, this may slow down inserts, deletes, and updates, and it may also double the size of your table.