SQL Server – Update Index Query

index-tuningsql server

So, here is a question from a totally non DBA guy, but I've done a lot of research and lots of tests if that matters at all. SQL Server 2016 as the underlying database.

A simple table like this :

 User 
     - id (clustered index)
     - income
     - status_id (some foreign key to some status table)

 a nonclustered index on (income, status_id) with include (id)

Now, if I perform a select like this:

 select id from user where income in (1,2,3,4,5) and status_id = 32;

I see a index seek – well pretty much expected, this is a covering index after all.

Now the second query, an update:

 update user set status_id = 3 where status_id = 32 and income = 5;

I still see the index seek and that makes me happy. (this follows the rule of covering index right?)

The third one :

  update user set status_id = 5 where income in (32,33,34) and status_id = 6;

but that is clustered index scan. I don't get it here really. Shouldn't this still "fall" under the covering index?

Doing the same last update query with a hint forceseek or even index hint makes it a index seek.

Best Answer

Depending on what you're trying to do and how many rows might be affected, you can have a covering index which SQL Server 'may' happily ignore.

The SQL Server optimizer is 'cost-based'. It tries to find a 'good-enough' plan that, in it's estimation (using statistics), is the least costly in terms of CPU and I/O. If using a covering index has a lower cost than NOT using the covering index, then it will generate a plan to use the covering index. Depending on how many rows satisfy income in (32,33,34), the optimizer may decide it's too expensive to use the covering index to satisfy the query.

And just because you 'forced' SQL Server to use the index (hints), does not necessarily mean your statement is more efficient than the clustered index scan (now or in the future) - it just means SQL Server used your covering index to satisfy the query.

Take a look at actual execution plans both with and without your hints and also SET STATISTICS TIME ON and SET STATISTICS IO ON. Make sure you have update-to-date statistics.

I guess I'm saying "we don't have enough information" to fully answer your question - (or maybe it's SQL Server that doesn't have enough information).

I wouldn't want to speculate on the 'future' usage (or non-usage) of an index. A lot of this boils to the amount of data, the granularity (think histogram) of the columns used in a predicate and index fragmentation. My advice would be to make sure you have proper index maintenance scheduled and continue to monitor your index usage via Index Usage Stats.

See also The Tipping Point Query Answers by Kimberly L. Tripp.

Related Question