New defaults for model and user databases in SQL Server 2016

  • Article

In the previous blog post on Changes in default behavior for autogrow and allocations for tempdb and user databases, we discussed how some of the commonly used trace flags (1117, 1118) are no longer required for tempdb and user databases. In this blog post, we will discuss additional defaults for databases created in SQL Server 2016.

Initial size and autogrow

model database: New default data and log file size is 8MB and default auto-growth is 64MB. This ensures that any new database created without explicitly specifying the SIZE/FILEGROWTH parameter will have 8MB initial size for all data and log files and 64MB for auto-growth for both data and log files.

For data files, having a 64MB autogrow, aligns with 1 PFS interval (which covers a range of 8088 pages = 64MB). For log files, having a 64MB autogrow helps with sizing the initial VLFs correctly so that they can be garbage claimed (wrapped-around) without which the log can keep growing.

In both cases, having a fixed size MB based autogrow is recommended over using percent based growth (the previous default used to be 10% which resulted in non-linear growth and performance issues).

To summarize, when a user database is created, if size is not supplied for the primary file, the Database Engine uses the size of the primary file in the model database (default size is 8MB). When a secondary data file or log file is specified, but size is not specified for the file, the Database Engine makes the file 8 MB. The size specified for the primary file must be at least as large as the primary file of the model database. 

Similarly, if FILEGROWTH is not specified, the default value is 64 MB for data files and 64 MB for log files, and the minimum value is 64 KB.

Indirect Checkpoint

In addition to the size and autogrow defaults, indirect checkpoint is also enabled in model. Hence, any database created in SQL Server 2016 will have indirect checkpoint enabled by default. There are several benefits to this as described in It Just Runs Faster: Indirect Checkpoint Default.

 

Ajay Jagannathan ( @ajayMSFT )

Principal Program Manager