Forum Discussion
Optimizer Day of Week Consideration
I have stored proc that runs on a daily SSIS job which references a non-temporary staging table that is truncated daily and populated with data from the current month to date. One of the statements in the proc has started selecting a very poor query plan specifically on Sundays. This has been verified through Query Store, it is the same statement every week, consistently a different plan that can run for an hour or more, no other day of the week picks this plan, every other day of the week runs in under 5 minutes, and even on Sundays when the stored proc is terminated and run manually, it does not choose this poor performing plan.
The likely cause of this, as weird as it sounds, was an index health weekly job that reindexed the staging table in question. There really aren't any other factors common to Sundays that could have been the culprit.
However, we took this table out of the reindexing job on Friday and moved that job itself to Saturday evening (the reindex was overlapping the SP in question as well). Sunday morning, the statement still picked the bad query plan.
My question is, when looking at saved plans, would the optimizer take the day of week of the saved plan into consideration when choosing it? Could this be just that its preferred Sunday plan has become this poor performer? We know we can force the better plan, but this stored proc gets updated for logical issues decently often (like once a month) and that has just reset the plan picked on Sundays to this bad one in the past. I'm thinking, though, if we force it now that we've removed the table from the reindex job, maybe it doesn't go back to it. But it just seems odd to me that a statement which makes no use of day of week in the logic and there's no indexes involved which care about day of week would fixate on a bad query plan on a specific day of the week.