Question & Answer
Question
Performing the RUNSTATS command while a table is being updated is possible but the statistics could be inconsistent depending on the level of update activity. You may receive the following warning:
SQL2314W Some statistics are in an inconsistent state. The newly collected "
Cause
A sub-optimal plan might result when the statistics are inconsistent. SQL2314W is a warning of that possibility.
Answer
You should try to issue the RUNSTATS command when the activity level is as low activity as possible (or with no activity if possible). In particular, you should try to run RUNSTATS when there is little update activity.
In addition, you could issue RUNSTATS with the optional "ALLOW READ ACCESS" clause, which prevents others from modifying the table while RUNSTATS is running. This clause will affect concurrency since any applications trying to modify the table will be required to wait. To reduce the time period that the table is locked during RUNSTATS with ALLOW READ ACCESS, consider using the TABLESAMPLE clause: this results in RUNSTATS gathering statistics on a sample of the data in the table rather than all of it. A judicious choice of sample size can speed up RUNSTATS while still ensuring statistical integrity.
If none of the suggestions above can be used to prevent the SQL2314W warning and a review of query access plans shows some questionable choices when accessing the affected table, you could consider marking the table "volatile" to encourage index accesses.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21255437