SQL changing what index it uses

Question

SQL changing what index it uses

Kris-155042

Hall of Fame

Points: 3460
More actions
October 29, 2020 at 5:30 am

#3802328

Hi, I have a database that has many inserts and was using a specific index and was humming along quite nicely. Then all of a sudden it stopped using that index and everything grinded to a holt. No changes were made on the database and it just suddenly happened at 4:21am. How or why would SQL just stop using the most effective index for that query. I have changed the stored proc to now force it to use that index and it's all good again. But why did it happen in the first place.
Thanks,
Kris

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

BrainDonor SSCoach Points: 19254 More actions · Answer 1

Index usage can change within a database due to several factors, but part of it can be due to the data changing and the statistics no longer reflecting the true data spread. The query optimizer may now have a different, incorrect, impression of the data layout within your tables.

Have a look at this article for some pointer on index maintenance.

Steve Hall
Linkedin
Blog Site

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

The statistics went out of date and need to be updated. As was already stated, the data changed, the statistics changed, a new threshold was reached, and when the plan recompiled, a new, less effective, plan was formed. The plan recompiled due to data changes, but the parameter value used to compile it at 4:21AM was an outlier causing a bad parameter sniffing issue. Any, or all of these in combination, could have lead to the change.

Index hints as a solution might work, but also could prevent the optimizer from possibly making better choices in the future. I'm very cautious about implementing those.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

Kris-155042 wrote:

But why did it happen in the first place.

Heh... It's not a "Fault"... It's a "Feature" that helps keep DBAs and accidental DBAs employed. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)