CODEWITHSUNDEEP
sql-serverperformanceindexing
Mike Barlow - BarDev
Mike Barlow - BarDev

Reputation: 11267

SQL Server Update Query does not use Index

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

Upvotes: 5

Views: 15048

Answers (5)

Philip Kelley
Philip Kelley

Reputation: 40319

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.

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294307

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.

Upvotes: 5

SQLMenace
SQLMenace

Reputation: 135021

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

Upvotes: 2

Russ
Russ

Reputation: 4163

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.

Upvotes: 2

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181290

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.

Upvotes: 1

Related Questions