Geek Answers Handbook

SQL Server Update Query does not use index

I have an update request that is slow (see first request below). I have an index created on the PhoneStatus table and in the PhoneID column called IX_PhoneStatus_PhoneID. The PhoneStatus table contains 20 million entries. When I run the following query, the index is not used and clustered index scanning is used, and in turn, the update is slow.

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

If I run the following query that includes the new FROM, I still have the same index problem that is not used.

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

But if I add HINT to force the use of the index in FROM, it works correctly and index search 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 will not use Index?

Update

In a table of 20 million entries, each phone ID can be displayed a maximum of 10 times

Bardev

+4
source share
5 answers

How many different PhoneIDs are in table 20M? If the condition where PhoneID=126 is not selective enough, you can fall into the tipping point index. If this request and access condition are very frequent, PhoneID is a good candidate for the clustered leftmost index key.

+4
source

Take a look at Is index search always better or faster than index scanning? Sometimes the search and crawl will be exactly the same.

The index may be ignored because the statistics may be out of date or the selectivity index of the index is so low that SQL Server believes that scanning will be better

Turn on statistics and see if there are differences between a query with and without a search

 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 read messages that are returned

+2
source

Pablo is correct, SQL Server will only use the index if it believes that this will lead to efficient query execution. But with 20 million rows, he would have to use an index. I would suggest that you just need to update the statistics on the database.

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

+2
source

SQLServer (or any other SQL Server product, for that matter) if it is not forced to use any index at all. He will use it if he thinks it will help to fulfill the request more efficiently.

So, in your case, SQLServer thinks that it does not need to use IX_PhoneStatus_PhoneID , and with the help of a clustered index it can get better results. It may have been wrong that the tips for the index are: so that the Server knows that it will do a better job using a different index.

If your table was recently created and populated, it may happen that the statistics are somewhat outdated. This way you can force a statistical update .

+1
source

Repeat:

  • Do you have a PhoneStatus table
  • With clustered index
  • And the non-clustered index on the PhoneStatus and PhoneId columns, in that order
  • You are posting an update using "... WHERE PhoneId = 126"
  • The table has 20 million rows (i.e. large, and then some)

SQL will take your query and try to figure out how to do the work without processing the entire table. For your nonclustered index, the data may look like this:

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

The thing is, SQL will first check the first column before it checks the value of the second column. Since the first column is not specified in the update, SQL cannot “shorten” the index search tree to the corresponding records, and therefore it is necessary to scan the entire table. (No, SQL is not smart enough to say “ah, I just check the second column first,” and yes, they are right to do it this way.)

Since a nonclustered index will not make the query faster, the scan table is used by default — and since there is a clustered index, this means that instead a clustered index scan occurs. (If the clustered index is on PhoneId, then you will have optimal performance for your request, but I assume that is not the case here).

When you use a tooltip, it forces you to use a non-clustered index, and it will be faster than a full table scan if the table contains a lot more columns than the index (which essentially consists of only two), because there would be much less data to sift.

0
source

More articles:


All Articles