Can SPM cause a temp regression for the SQL in case below --
Scenario :
1.Weekend Changes (suppose) like huge data load and the weekend gather stats job causes stats to change and thus this change necessitates the new execution plan to be used to perform optimal. 2.Now lets say the application job next executes Monday morning, that is before the SPM maintenance window. So by now the new EP in SPM is not validated and accepted (by Monday morning) and so SPM will force the previous plan to be used. 3.Now this previous (which is currently accepted plan in SPM) will cause regression since the weekend huge data load changed the actual stats. (until the new plan is verified and accepted by the next evolve window).
In this case SPM can not be 100% reliant and such case will need to work out with the manual plan evolution ? or is there any other approach ?
Regards, Sneha
It could happen but in most cases it won’t. Normally things like data / key distributions don’t change significantly in regular operations. When a fresh application is starting to grow the chance is a bit bigger because more transactions are coming in and will be marked completed. In the beginning most will be new and after a while most will be completed.
If this happens frequently in a longer running application it might be worth investigating the data model land application logic.