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.
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?