I have 200 GB production database. Normally daily prepared query plan count 8K. Most of these plans are non necessary because of the non parametric queries. Software developers are working on this.
When a lot of non parameterized queries are sent, some query's plan will be deleted. New plans are sometimes incorrect and cause the query to timeout. We clear the cache of query plan to resolve problem.
Daily data changes count between 600k-1m (INSERT-UPDATE-DELETE). For this reason, the statistics on some tables are modified. I think it creates incorrect plan when statistics updated.
I changed some settings:
SET AUTO_UPDATE_STATISTICS_ASYNC OFF SET AUTO_UPDATE_STATISTICS OFF
I update all stats every night with ola.hallengren script and creates good working plans.
But sometimes the problem repeats. All queries need to be parameterized for the solution. This takes a long time. What should I do for a solution during this time? Is it an error to turn off automatic statistics updates?