I have always felt that to have a perfectly tuned tempdb, we are supposed to have a number of traceflags and tuning. For many of my DBA friends, this conversation is no brainer. They create multiple files once they get into tempdb contention. I always felt this could have been automated as part of the SQL Server® best practices.
Along with many new features in SQL Server 2016, we are seeing that the Microsoft® SQL product team is also enhancing existing features. If you are a seasoned DBA, it would be easy to recall the importance of trace flag 1118 and multiple tempdb files. Read this knowledge base article for more details.
Here are the SQL server 2016 product enhancements for tempdb that are worth noting:
No need for trace flags
As soon as SQL Server 2016 is installed, the trace flags 1117 and 1118 are ON by default. This means that there is no need to add them in startup parameters. The functionality of the trace flag 1118 is to have uniform extent for every allocation in tempdb. This is the default behavior even without trace flag.
Specify tempdb files during installation
In SQL Server 2016, we have the option to specify tempdb file specifications during the setup. Most companies have a post-deployment script, and one of the steps is to optimize tempdb files. It’s a tab called tempdb under Database Engine Configuration.
If you are a doing the installation by script, there are also parameters in the command line as shown below:
| Parameter | Meaning |
| SQLTEMPDBFILECOUNT | The number of Database Engine tempdb data files. |
| SQLTEMPDBFILESIZE | Initial size of a Database Engine tempdb data files in MB. |
| SQLTEMPDBFILEGROWTH | Automatic growth increment of each Database Engine tempdb data file in MB. |
| SQLTEMPDBLOGFILESIZE | Initial size of the Database Engine tempdb log file in MB. |
| SQLTEMPDBLOGFILEGROWTH | Auto-growth increment of the Database Engine tempdb log file (MB). |
| SQLTEMPDBDIR | Directories for Database Engine tempdb files. |
| SQLTEMPDBLOGDIR | Directory for the Database Engine tempdb log files. |
Default size change for tempdb
In earlier versions, the default size of tempdb was taken from the model database. Now, because this is set during installation, the default initial size of tempdb would be 8MB, and the default auto-growth is 64MB.Default size change for tempdb
Default file numbers change for tempdb
The SQL Server setup is smart enough to identify the number of CPUs on the server, and adds as many tempdb files as the CPU count, or 8, whichever is lower. In earlier versions, there was just one data file and one log file or tempdb database.
Leave a Reply