I got my hands on SQL Server 2016 CTP 2 this morning and ran through the installation process. Some of the small print in the data sheets mentioned automatically creating multiple tempdb files. As its been a long time best practice to do this to alleviate GAM/SGAM contention in Tempdb, I was curious how SQL Server would handle this. Would it automatically create multiple files according to best practices?
It turns out, it's simply another option in the installation process. Normally, you'd want to separate your data, log and tempdb files, but as my laptop only has one drive, I was unable to set these up optimally.
 |
| Number of Temp DB Files |
SQL Server defaults to 8 data files or the number of cores, whichever is less. This is the general starting point when creating multiple tempdb data files. My laptop has less than 8 cores, it has defaulted to the lesser number. It should be noted here, this is actually the number of threads, not cores available. Whether or not hyperthreading should be enabled for SQL Server is a whole other topic. Just be aware that this appears to read your threads as opposed to cores. As this is a Community Technology Preview, this can easily change in future releases.
 |
| sys.database_files |
Looking at the files, it created the additional data files and sized them identically with the same growth pattern. This is all in line with generic best practices, however, you will likely want to presize the files and growth values to match what you see in your environment, while still honoring identical file sizes and growth patterns.
There you have it, Microsoft still gives you control over the number of files in create, but starts you off with a best practice suggestion. Additionally, the files sizes and growth patterns are are set to generic best practices, however, you should not to rely on autogrowth and pre-size these files accordingly.
Edit 10-10-2015:
Microsoft has put out SQL Server 2016 CTP 2.4. They've given tempdb configuration its own tab in the installation procedure. With this, you have the option to set the number of files, file locations and auto growth settings.
Labels: SQL Server, SQL Server 2016, SQL Server Best Practices, Temp Database, TempDB
