SQL Server Update Query does not use Index

Answer 1

SQLServer (or any other SQL Server product for that matter) if not forced to use any index at all. It will use it, if it thinks will help running the query more efficiently.

So, in your case, SQLServer is thinking that it doesn't need using IX_PhoneStatus_PhoneID and by using its clustered index might get better results. It might be wrong though, that's what index hints are for: letting the Server know it would do a better job by using other index.

If your table was recently created and populated, it might be the case that statistics are somewhat outdated. So you might want to force a statistic update.

Answer 2

Take a look at Is an index seek always better or faster than an index scan? Sometimes a seek and a scan will be the exact same.

An index might be disregarded because your stats could be stale or the selectivity of the index is so low that SQL Server thinks a scan will be better

Turn on stats and see if there are any differences between the query with and without a seek

SET STATISTICS io ON
UPDATE PhoneStatus
SET RecordEndDate = GETDATE()
WHERE PhoneID = 126


UPDATE PhoneStatus
SET RecordEndDate = GETDATE()
FROM Cust_Profile.PhoneStatus WITH(INDEX(IX_PhoneStatus_PhoneID))
WHERE PhoneID = 126

Now look at the reads that came back

Answer 3

Pablo is correct, SQL Server will use an index only if it thinks this will run the query more efficiently. But with 20 million rows it should have known to use the index. I would imagine that you simply need to update statistics on the database.

Form more information, see http://msdn.microsoft.com/en-us/library/aa260645(SQL.80).aspx.

Answer 4

How many distinct PhoneIDs are in the 20M table? If the condition where PhoneID=126 is not selective enough, you may be hitting the index tipping point. If this query and access condition is very frequent, PhoneID is a good candidate for a clustered index leftmost key.

Answer 5

To restate:

• You have table PhoneStatus
• With a clustered index
• And a non-clustered index on columns PhoneStatus and PhoneId, in that order
• You are issuing an update with "...WHERE PhoneId = 126"
• There are 20 million rows in the table (i.e. it's big and then some)

SQL will take your query and try to figure out how to do the work without working over the whole table. For your non-clustered index, the data might look like:

PhoneStatus  PhoneID
  A            124
  A            125
  A            126
  B            127
  C            128
  C            129
  C            130
 etc.

The thing is, SQL will check the first column first, before it checks the value of the second column. As the first column is not specified in the update, SQL cannot "shortcut" through the index search tree to the relevant entries, and so will have to scan the entire table. (No, SQL is not clever enough to say "eh, I'll just check the second column first", and yes, they're right to have done it that way.)

Since the non-clustered index won't make the query faster, it defaults to a table scan -- and since there is a clustered index, that means it instead becomse a clustered index scan. (If the clustered index is on PhoneId, then you'd have optimal performance on your query, but I'm guessing that's not the case here.)

When you use the hint, it forces the use the non-clustered index, and that will be faster than the full table scan if the table has a lot more columns than the index (which essentially has only the two), because there'd be that much less data to sift through.