Microsoft SQL Server Auto Update and Auto Create Statistics Options

Document ID : KB000031331
Last Modified Date : 14/02/2018
Show Technical Document Details

Question: 

Microsoft SQL Server Database Auto Update and Auto Create Statistics Options is automatically set to false, Is it possible to change it to true?

Database Niku.Options.Automatic.Auto Create Statistics = False
Database Niku.Options.Automatic.Auto Create Statistics = False

Environment:  

All Supported Environment

Answer: 

We wouldn't force or stipulate either option (True or False) as its really about how the DBA’s wish to do their job. Depending if it is ok to let the server take care of this setting automatically or if to enforce some manual control and scheduling over when this happens.

Our own, in-house support DBs have both values set to true for every database, as per the following demonstration:

select name, is_auto_create_stats_on, is_auto_update_stats_on from sys.databases

name                         is_auto_create_stats_on     is_auto_update_stats_on   

 ---------------------------  --------------------------  --------------------------

master                       true                        true                

tempdb                       true                        true                

model                        true                        true                

msdb                         true                        true                  

Clarity132MSSQL2012          true                        true                

niku                         true                        true                

clarity                      true                        true                          

clarity_prod                 true                        true                

08 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 1/ms]

Executed: 01/29/16 14:12:14 CDT ] [Execution: 110/ms]

Setting this value to true would be perfectly normal.