TempDB configuration SQL Server 2016

Standard

As you may have read by now, in SQL Server 2016 you’ll be able to set the TempDB configuration during the installation. Whoehoe!

We’ve waited long for that option and I’m very glad that Microsoft implemented it. It’s certainly a time saver because now I don’t have to go into SQL Server afterwards and setup the TempDB, but….the settings could be better.

I created a virtual machine with 1 CPU and 4 cores, 2 GB (not that much but for testing it’s enough) and some disks for data (30GB), log (20GB) and tempdb (25GB).

The screen for the TempDB configuration look like this:

tempdb configuration screen

Number of files

The number of files is based on the amount of cores that’s available up to 8. In my case I have 4 cores and I would create 4 files in total, one for each core.

Initial size

The initial size is set to 8MB. I would never create TempDB data files with a size of 8 MB which in my opinion is really small for a data file even for small systems.

In most cases I would look at the size of the TempDB disk (because you create a desperate disk for TempDB right…) and fill up the entire disk with data files. In this case I would create 4 x 5 GB files to leave some space for the log file.

To properly set the initial size of the TempDB you still need to go through some checks how much your system might need.

Autogrowth

The next field “Autogrowth” is set to 64 MB and I don’t know why, maybe it’s the initial size multiplied by 8. The disk is 25 GB in size and growing the files of the TempDB with 64 MB every time would be inefficient. I did not create a separate disk for the TempDB log because this is just a test machine.

I like the fact that in SQL Server 2016 you no longer need to enable traceflag 1117 to enable to grow all data files in a file group when one data file grows. You can also see that when you change the values in the autogrowth settings. When you change the value it will multiply is with the number of files you put in.

Data directories

The next field is where the files are going to be located. It’s possible to use multiple locations. The SQL Server installation will split your files up accordingly. As a test I put in two directories on the same drive to see what would happen. The configuration looks like this:

tempdb config multiple data directories

What happens is that the installation will place each individual file by going round robin through the directories. The result will look like this:

tempdb config result multiple data dir

So that’s really nice because you don’t have to think about where to place those files and the installation will do that for you.

TempDB log

The same settings for the data files are applied to the log file settings with the exception of the data directories because you only need one log file.

Again I think the initial size is pretty small but that’s up to your needs for your system.

Conclusion

I like this new setting in the installation but you still have to think about your configuration. There are some default settings which in my opinion in larger systems wouldn’t work.

Maybe in some small environments these settings will work perfect but in the environments I managed these settings would not be a good choice.

Maybe this is why we still need professionals who can interpret the needs for a database or application and make configure the instance to achieve the best results 😉

 

Leave a Reply