SQL Server 2016 – TempDB

With the release of SQL Server 2016 which is due out somewhere around March through May potentially 2016 (exact release dates still to be finalised) some changes have been made around the installation process. The SQL Server Installation Center is still the same with needing to navigate to the Installation tab before selecting “New SQL Server stand-alone installation or add features to an existing installation” to begin the installation process via the GUI.

As SQL Server 2016 has not been released yet, this post is based off of Community Technical Preview (CTP) 2.3. I mention this because things in the final product can change between now and when the product is released next year.

Just like previous versions, the process is still the same, until you reach the Database Engine Configuration window. Once you have chosen the authentication type the instance will utilise and added the appropriate members of the SQL Server Administrators group, we navigate to the Data Directories tab. On this tab we need to take special notice of the new section which specifically relates to TempDB.

Now for quite a number of years, the recommendations about increasing the number of TempDB files per instance of SQL Server is very relevant to assist with the performance of SQL Server to reduce contention in TempDB. The recommendations have been:

• If you have <= 8 Cores available then create 1 TempDB data file per core.
• If you have > 8 Cores available then create 8 TempDB data files and monitor TempDB. If the monitoring of TempDB indicates in-memory contention, that is you are seeing PAGELATCH_XX waits, then add 4 TempDB data files at a time, continually monitoring until you find the optimal spot for your SQL Server environment.

If you wish to see what is happening inside of your TempDB database causing contention, then have a look at Robert L. Davis (Blog | Twitter) script.

With these recommendations having been around for a while now, this has been included into the installation process for SQL Server 2016. So straight out of the box, the installation for SQL Server 2016 will create TempDB with the number of TempDB data files based on what it finds on the server you are installing onto. In my virtual lab my Hyper-V VM has been allocated 4 vCPU’s and as such you can see the installation media automatically set this to 4 TempDB data files. This figure can be overridden, however I would not recommend changing the default value unless you have undertaken the appropriate investigation into the requirements of your application databases in your new SQL Server 2016 environment.

Remember using the default entries is not always the best recommendation, but in this case it is highly recommended to not change this default without good reason.

If after your installation has been completed or you are experiencing contention, you can still add additional data files to TempDB the old fashioned way.