3 ms·
MSSQL plan cache is something you need to be aware of, it will work against you if statistics change but the plan remains the same. this is especially true if y
by baq 5y ago
MSSQL plan cache is something you need to be aware of, it will work against you if statistics change but the plan remains the same. this is especially true if you start with a small amount of data, get a plan cached and then bulk load lots after testing. foot = shot
- SigmundA 5y agoNormally AUTO_UPDATE_STATISTICS will take care of recompiling plans if statistics changing significantly. I usually have the opposite problem with a good plan changing to a bad one due to statistics changing at some random time in production, which seems to be a common problem in PG as well and why everyone want hints. I prefer hinting for critical problematic queries that tend to have unstable plans due to statistics changes, but you can also lock a plan in SQL server to force a specific plan, another useful feature to prevent production db instability.
- baq 5y agoAgree completely on the plan changing from good to bad after some kind of analyze going awry. Had this happen multiple times on mssql and postgres. Our workaround was to perform analyze periodically multiple times a day since we could afford it.