Background:
I have a large table whose values are significantly rewritten with a switch ~once a week, but the table's primary key values remain relatively constant. After the switch, statistics are guaranteed to be out of date on the table.
A consequence then is that user queries against this table after the switch are pretty much guaranteed to need to update statistics. This has lead them to timing out. (User queries have a 30 second time limit; these same queries running with up-to-date statistics complete in <1 second.)
To address this, I'm looking to manage statistics manually by (1) calling UPDATE STATISTICS after the switch, and (2) turning on STATISTICS_NORECOMPUTE. User queries the rest of the week will then potentially use outdated statistics, but that should be fine.
Question:
Let's say I turn STATISTICS_NORECOMPUTE on for this table, have a constant stream of new queries against this table, and I call UPDATE STATISTICS. When will the new statistics be used after the UPDATE STATISTICS call is made? Will they be used by all queries that execute after the statistics transaction is made?
Put another way, I'm considering a SQL query in two phases: when it is submitted, and when it is executed. The query optimizer is responsible for telling a query which statistics to use. Does the optimizer create the query plan when the query is submitted, or when the query finally executes?