STATISTICS_NORECOMPUTE = { ON | OFF }
Specifies whether statistics are recomputed. The default is OFF.
ON
Out-of-date statistics are not automatically recomputed.
OFF
Automatic statistics updating are enabled.
The general recommendation to leave auto-update stats turned on (STATISTICS_NORECOMPUTE = OFF, which is the default) is for safety reasons, because if this is turned off and nothing is manually updating the stats, the result could be really horrendous execution plans that never change after they’re first created (and don’t get invalidated for other reasons later on).
One good case would be using STATISTICS_NORECOMPUTE=ON and FILLFACTOR=100 for read-only lookup tables that are only changed by DBA’s using a script that does an INDEX REBUILD with FULLSCAN after the changes; then the table’s in optimal shape with optimal statistics, and with no other changes, there’s no reason to even consider recomputing statistics, or leaving space to reduce page splits on future changes.
Sources:
https://dba.stackexchange.com/questions/51787/advisability-of-using-statistics-norecompute
Comments