If I had to rank my performance killers, TempDB would have to rank in the top 3, and possibly be my first pick.
Microsoft never really addressed the issue until SQL Server 2016. The main problem is that both the “Initial Size” and “Autogrowth” properties have inadequate default values.
- Initial Size: Considering all the things that TempDB must handle, 8 MB and 1 MB are kind of ridiculous defaults – the files are practically guaranteed to grow immediately after startup.
- Autogrowth: 10% for both data and log files is not a good default at all – this means that each successive autogrowth event will be 10% larger than the last. Also, SQL Server needs to "zero-out" out the new file and that takes time!
One of my pet peeve with SQL Server was the fact that we needed to partition TempDB on mutli-processor environments. To simplify the TempDB configuration, SQL Server 2016 setup has been extended to configure various properties for TempDB.
A new tab has been added dedicated to TempDB.
Here are the different configuration options:
TempDB Data Files section:
- Number of files: this will default to the lower value of 8 or number of logical cores as detected by the setup program. The value can be increased as required for a specific workload. The file names for secondary data files will follow the tempdb_mssql_#.ndf naming convention where # represents the file number for each additional file.
- Initial Size: is specified in MB and applies to each TempDB data file. This makes it easier to configure all files of same size. Total initial size is the cumulative TempDB data file size (Number of files * Initial Size) that will be created.
- Autogrowth: is specified in MB and applies to each file. The default value of 64 MB was chosen to cover one PFS interval. Since Trace Flag 1117 is enabled by default for TempDB in SQL server 2016, all files will grow at the same time by the specified size.
- Note: It’s a good idea to set your file sizes to avoid having to auto grow the files since the Instant File Initialization is not enabled by default for SQL Server service startup account
- Data Directories: specify multiple folders/drives to spread the data files across several volumes. Each file will be placed in a round-robin manner. For example: if you have specified 8 data files and 3 volumes – files 1,4,7 will go to vol 1; files 2,5,8 will go to vol 2; files 3,6 will go to vol 3.
TempDB Log File section:
- Initial Size: is specified in MB. After the SQL Server service is started a new log file with that size is created.
- Autogrowth: is specified in MB. A default value of 64 MB is provided to so that the number of Virtual Log Files (VLFs) during initial creation is a small and manageable number and with appropriate size so that the unused log space can be reclaimed easily.