Set Number of Processor Cores for Number of Temp DB Files

VMWare Player enables users to set the number of processor cores on a virtual machine configuration which limits the maximum number of tempdb files. SQL Server database administrators and developers can define the number of temp db file during SQL Server 2016 setup. Multiple Temp DB file is a new enhancement for database professionals introduced with SQL Server 2016.

Let's see in this tutorial how administrators can define the number of processor cores for a virtual machine which is set to 1 by default on VMWare Player.

Number of TempDB Files for SQL Server 2016

With the release of Microsoft SQL Server 2016, database administrators can use multiple temp DB files for their SQL Server instances. Multiple Temp DB files is a new feature introduced with SQL Server 2016 when compared to previous SQL Server releases.

The easiest way to define multiple temp db files is to set it during SQL Server 2016 setup. While installing SQL Server 2016, at Database Engine Configuration step, dbadmin can provide the desired number of Temp DB files as seen in below screenshot.

number of temp db files at SQL Server 2016 setup

Of course there is a limitation on the temp db files number.
The default value is 8 or the number of cores, whichever is lower. This value can be increased up to the number of cores.

This means for the first release of SQL Server 2016, the maximum number of Temp DB files is 8 and the default value is set to the number of processor cores on your server.

You can not set a number which is over the number of processor cores on your server. I tried to enter 2 for the number of temp db files for SQL Server 2016 instance I was installing on my Windows 10 virtual PC running on VMWare Player.

I got the error message: The value 2 for the number of Temp DB files exceeds the allowed limit.

The value 2 for the number of Temp DB files exceeds the allowed limit

Set Number of Processor Cores for Virtual Machine Guest Operating System

When I checked the number of processor cores for my virtual machine running Windows 10 operating system using VMWare Player, I see that it was set to 1 by default. On the other hand, my host machine is running on a PC with 8 cores.

multi-core processor configuration for virtual machine

So I have a multiple core processor as the physical computing device which is running the host of my Windows 10 virtual PC with SQL Server 2016 database instance. If I can define the processor cores for the virtual PC, I can use multiple Temp DB files on my SQL Server 2016 instance.

Here is how to set the processor cores on VMWare Player.

First of all, Shut down guest operating system. Otherwise the related configuration settings will be disabled for editing.

Then launch VMWare Player.
Select the virtual PC you want to configure. Do not start it.
From menu options: Player > Manage > Virtual Machine Settings

On Hardware tab, you will see Processors under Device window. Select Processors. On detail screen, you will be able to choose number of processor cores using the enabled dropdown list which includes numbers upto 16 (for my case).

number of processor cores in virtual machine settings

After you make the changes, you can start your virtual PC. Now it will be running with multiple processor cores. This will enable database administrators or programmers to define multiple Temp DB files for the SQL Server 2016 instance they are installing.

To summarize, if you want to test SQL Server 2016 on a Windows 10 operating system running guest on VMWare Player, first configure the virtual PC settings for a multiple processor core. This will give database administrators the option to define multiple Temp DB files for the SQL Server 2016 instance they are installing.