Database choosing using inefficient index/plan suddenly

[bgreiman]

We are using v12.01.3942.

Today we encountered a subqquery on a table with 15+ million rows that has been running very well for many years but is very suddenly running very slowly. I fixed by forcing index usage by adding "WITH INDEX(index_name)" clause.

My question is why all of the sudden would this change - I think it happened mid-day and is there any preventative measures to keep thinks working reliably. Dropping/re-creating statistics or any other ideas.

Thanks, Brian

[bgreiman]

Just wanted to add another fact that I just remembered. I updated about 1 million of these rows this weekend to add some data to a column that was previously null. This is a blob column with average image loaded of about 45k. Could this have caused table fragmentation that would have altered the statistics enough to start using different plans?

[jeff_albion]

Could this have caused table fragmentation

Yes - if rows were shuffled across page boundaries as a result of the additional row data, page fragmentation may have occurred.

that would have altered the statistics enough to start using different plans?

Not by the addition of the data itself, no - unless your queries also started using the new data as part of their sargable predicates.

---

If you drop the 'WITH INDEX' clause, can you collect detailed plans with statistics from the two situations? Have you tried running 'CREATE STATISTICS' on the tables involved to see if this changes the behaviour?

If the query without a forced index is using a table scan, yes, it is quite possible the statistics may be off and the statistics governor hasn't had time to catch up - the detailed plans with statistics would tell us more about why this situation is occurring.

[Former Member]

Try to analyse query plan, and you find, why server choose another strategy. May be will be better, if after serious data updates, you will run UPDATE STATISTICS instead of index hinting.