When you have installed SQL Server 2016 you will have noticed that the configuration for tempdb has changed.
Microsoft made a proper tempdb configuration easy to do via the GUI during installation.
Today I want to go through the settings offered and will measure performance of different setups.
What are the options?
During installation you can set the following values:
For TempDB datafiles:
|Number of files||4|
|Initial size (MB)||8|
|Data directories||Default data directory, in my case C:\MSSQL\Instance root\MSSQL13.MSSQLSERVER\MSSQL\Data|
For TempDB logfile:
|Initial size (MB)||8|
|Log directory||Default data directory, in my case C:\MSSQL\Instance root\MSSQL13.MSSQLSERVER\MSSQL\Data|
Configuration screen during SQL Server 2016 installation (I changed the directories already).
So what is the best setup?
This subject is not new for SQL Server 2016, but the installation procedure now just gives a better opportunity to set it up right the first time.
There are roughly five differentiators in the setup:
- Initial size (MB)
- Autogrowth (MB)
- Number of data files
- Location of data files
- Read/write performance of the different storage media
What are the considerations when setting these values?
Initial size (MB)
A larger initial size will accomplish two things:
- It will take longer before the file will have the need to grow.
- The file can be stored less fragmented on disk.
If you have enough disk space, the larger the better, there is no real downside on making the initial size quite large. More about this in my conclusion.
With autogrowth the choice is a bit more delicated, as a small or large value will both have pros and cons, as pointed out in the schedule below.
|Small||More frequent smaller growth actions will cause less noticable hick ups||TempDB datafiles will become more fragmented.|
|Large||Tempdb datafiles will become less fragmented.||Less frequent larger growth actions might cause noticable hick ups|
Number of data files
In general, the recommendation is one data file per (logical) processor. You can configure more files than the number of (logical) processors, but do this only if you notice that this gives a better performance.
Location of data files
Locating the data files on different drives can have two effects:
- Queue length per disk drive in Windows (all scenarios, also with virtual disks residing on the same physical drive).
- Physical writes to the diskdrive can go in parallel (only when different physical drives).
Creating combinations of all differentiators would just create *a lot* of testscenarios.
So I will be a bit pragmatic.
The testcases are chosen, so that they can be compared with at least one other testcase, of which one differentiator is different.
|Test #||# Data files||Init size (MB)||Auto growth (MB)||# Fol- ders||Scenario|
|0||4||8 (x4)||64||1||Default installation (data files on OS disk)|
|1||4||8 (x4)||64||1||Data files on dedicated drive|
|2||4||8 (x4)||64||4||Data files on more drives|
|3||4||2048 (x4)||64||4||Data files on more drives + Larger initial size|
|4||4||8 (x4)||256||4||Data files on more drives + Larger autogrowth|
|5||4||2048 (x4)||64||1||Data files on dedicated drive + Larger initial size|
|6||4||2048 (x4)||64||1||Data files on dedicated drive + Larger initial size + TempDB on different SSD|
|7||8||2048 (x8)||64||1||Data files on dedicated drive + Larger initial size + More data files|
|8||4||8 (x4)||64||1||Default installation (data files on OS disk) + Larger initial size|
I will present you both two graphs as well as the raw data. I think especially the first graph gives some useful insights, as it compares all testcases with testcase 0 (default installation), by using an index 100 for testcase 0. If a different testcase performs better, the index will be less than 100. Also, in this way, the average result of both testsets (A and B) can be shown.
I left testcase 6 out of the graph, later I will tell why I did this.
Index-based comparison of testresults
Execution times of testcases for testset A and B
Test results figures
|Test #||Scenario||Testresult testset A (ms)||Testresult testset B (ms)|
|1||Data files on dedicated drive||28344||261467|
|2||Data files on more drives||22514||248484|
|3||Data files on more drives + Larger initial size||28040||210223|
|4||Data files on more drives + Larger autogrowth||24173||200632|
|5||Data files on dedicated drive + Larger initial size||19608||192587|
|6||Data files on dedicated drive + Larger initial size + TempDB on different SSD||123289||387646|
|7||Data files on dedicated drive + Larger initial size + More data files||19692||211501|
|8||Default installation + Larger initial size||20149||361844|
Data files on dedicated drive + Larger initial size (test 5) gives the best performance for both testsets. Putting tempdb files on multiple disks can not win from this, however the differences are relatively small.
Surprisingly performance dramaticly decreases when the virtual disk on which tempdb is stored is moved to a different (faster) SSD (this is test 6)! I have no explanation for this. Therefore I will leave test 6 out of the analysis, by doing this physical storage is the same for all testcases and changes in performance are only caused by the other differentiators.
My most important conclusion is that the default values offered during installation are quite okay, except for two things:
The Initial size (MB)
You should really set the initial size to a big number (gigabytes if possible). Note that if you leave the number of files 4 (which I recommend), the space used is four times the size that you use for Initial size.
If you have a dedicated disk for tempdb (which is recommended), you might want to set the initial size of the datafile(s) to fill up the disk. You should then disable Autogrowth.
Also on a production server, check the size of tempdb files after a week of uptime (also the SQL instance may not have been restarted). Say the tempdb size of all files together (can also be one file) is 22GB. You could then set your initial size to 28GB in total, so 7GB per file if you configured 4 files. This would mean that during normal operation, the tempdb never has to grow.
It is a common best practive to put tempdb on a seperate disk so that the Windows Disk Queue does not have to compete with other read/write actions to that disk.
Other settings are somewhat less important but … think for yourself (my disclaimer)
In my opinion both putting tempdb files on multiple different disks and setting Autogrowth to a different value do not offer much performance benefit.
The Autogrowth value is even irrelevant if the initial size is so large that temdb never has to grow during normal operation.
But every server is different! Test and think for yourself.
My testresults are presented “AS IS” in the hope they will be useful as a starting point for further own analysis. I am not responsible for any disadvantage you might have by using my test results for your tempdb setup.
What if SQL Server is already installed and/or it is an older version?
No worries, you still can change the tempdb configuration.
You can move and add files using SQL scripts.
Initial size and Autogrowth you can also set with Management Studio.
Go to properties of tempdb
Here you can change Initial size and Autogrowth
By the way, I noticed that SQL Server does not delete moved tempdb files. So if you move tempdb files, you should delete the files on the old location, so that they do not use disk space and do not cause confusion for administrators.
- All tests are done on a Hyper V Virtual Machine running Windows Server 2012 R2 and SQL Server 2016 Developer Edition.
- Change of physical storage type is done by moving vhdx files to different physical disks, and then change the file location in the settings of the Hyper V Virtual Machine (while it is not running, of course).
- Each test is done on an initial copy of the Virtual Machine, therewith the startsituation is equal for every test (e.g. no tempdb growth by previous tests).
- Tests where done with two sets of scripts (set A and B) that inserted, updated and deleted rows in temporary tables in tempdb. Set A consists of three sql scripts, and set B of five.
- All scripts were developed, so that all tempdb inserts started at the same time (maybe milliseconds difference in starttime only). This to ensure that every testrun was executed in the same way.
- All scripts were executed in SQL Server Management Studio.
- The first file of tempdb is stored on its own (virtual) disk except for testcase 0, where tempdb files are on the C-drive where Windows is installed.
- When spreading files over multiple virtual disks, the disks created for database files, log files, and backup files are used. This was done for pragmatic reasons during the test. This was defendable, because no other activity on databases and log files was going on during the test. It is however not recommended to spread tempdb like that in production environment, unless every disk is dedicated to be used for tempdb only.
- The host machine had the same applications open during every test, being a Windows Explorer and Microsoft Excel. No other applications that used CPU and could influence performance of the virtual machines were running.
- The tempdb log file was placed on a seperate disk (the same one) for each test. Therewith it was no subject of performance investigation. This would have at least doubled the number of testcases.
Microsoft did a good job in improving the setup options for tempdb in the installation procedure.
It is a bit of a pity that they did not go the last mile, giving the tempdb data files a more realistic Initial size.
Luckily this is easy to change during installation, and it is a quick win for anyone installing SQL Server 2016.
Download all the used scripts here.
(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.