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
over using percent based growth (the previous default used to be 10% which resulted in non-linear growth and performance issues).
when a user database is created, if size is
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.
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