SQL Server Update Query does not use Index

Question

I have a update query that runs slow (see first query below). I have an index created on the table PhoneStatus and column PhoneID that is named IX_PhoneStatus_PhoneID. The Table PhoneStatus contains 20 million records. When I run the following query, the index is not used and a Clustered Index Scan is used and in-turn the update runs slow.

UPDATE PhoneStatus    
SET RecordEndDate = GETDATE()    
WHERE PhoneID = 126  

If I execute the following query, which includes the new FROM, I still have the same problem with the index not used.

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

But if I add the HINT to force the use of the index on the FROM it works correctly, and an Index Seek is used.

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

Does anyone know why the first query would not use the Index?

Update

In the table of 20 million records, each phoneID could show up 10 times at the most

BarDev

Solution 1

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.

Solution 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

Solution 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.

Solution 4

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.

View more solutions