Tips for using tempdb database in SQL Server 2016
Use TempDB section of SQL Server Installation Wizard.
SQL Server 2016 introduces the new UI input control on the Database Engine Configuration –
TempDB section of SQL Server Installation Wizard. Now during setup, you can configure the
number of tempdb database files, initial size, autogrowth and directory placement.
You can specify multiple volumes for tempdb database files.
If multiple directories are specified, tempdb data files will be spread across the
directories in a round-robin fashion.
Set the reasonable size for the tempdb database and transaction log.
First of all you should estimate how large your tempdb database will be. To estimate the
reasonable database size, you should estimate the size of each table individually, add
some additional space (10-20%) and then add the values obtained. The general rule of thumb
for setting the transaction log size is to set it to 20-25 percent of the database size.
The smaller the size of your database, the greater the size of the transaction log should be,
and vice versa. For example, if the estimation tempdb database size is equal to 100Mb, you
can set the size of the transaction log to 40-50Mb.
Permit the tempdb database to automatically grow.
Autogrow feature is set by default. Each time the SQL Server 2016 is started, tempdb database
is recreated and reset to its default size. Automatically growing results in some performance
degradation, therefore you should set a reasonable size for tempdb database and a reasonable
autogrow increment to avoid tempdb automatically growing too often.
Set the PAGE_VERIFY option for the tempdb database to CHECKSUM.
In SQL Server 2016, the default value for the tempdb database is CHECKSUM, but when you
upgrade an installation SQL Server, the default value remains NONE. Microsoft recommends
setting the PAGE_VERIFY option for the tempdb database to CHECKSUM.
Permit the tempdb database to automatically update statistics.
The “Auto Update Statistics” option specifies whether the database automatically updates
out-of-date optimization statistics. When this option is set to True, any out-of-date
statistics needed by a query for optimization are automatically built during optimization.
Setting this option to True usually increases the tempdb database performance.
If you create several tempdb data files, make each data file the same size.
In this case, SQL Server 2016 will load data in tempdb database in optimal
proportional-fill way.
Consider placing the tempdb log files on other physical disk arrays than those with
the data files.
Because logging is usually more write-intensive, it is important that the disk arrays
containing the tempdb log files have sufficient disk I/O performance.
Set a reasonable size of the autogrow increment.
Setting a database to automatically grow results in some performance degradation, therefore you
should set a reasonable size for the Autogrow increment to avoid automatically growing too often.
Try to set the initial size of the database, and the size of the Autogrow increment, so that
automatic growth will occur once per week or less. You can set the Autogrow increment in the
FILEGROWTH option in the CREATE DATABASE or ALTER DATABASE statement.
Use the sqlserver.latch_suspend_begin and sqlserver.latch_suspend_end events
to boost the tempdb database performance.
Extended Events were first introduced in SQL Server 2008. The sqlserver.latch_suspend_begin
and sqlserver.latch_suspend_end events fire when a latch wait occurs inside the database
engine. By using these events, you can check the duration, database_id, file_id and page_id
associated with the latch wait.
Consider placing the tempdb database on disks that are different from those used by
user databases.
By using so, you can increase the general performance of SQL Server 2016 because all
operations that use temporary objects will not compete with other operations that use
permanent tables.
Set the SIMPLE recovery model for the tempdb database.
SQL Server 2016 supports three recovery models: FULL, BULK_LOGGED and SIMPLE.
With the SIMPLE recovery model log space can be automatically reused when no longer
needed for server failure recovery. This model automatically reclaims log space to keep
space requirements small.
Avoid setting the SORT_IN_TEMPDB option to ON if you need to increase the tempdb
database performance.
When you set the SORT_IN_TEMPDB option to ON, you direct the SQL Server 2016 to use
tempdb database to store the intermediate sort results that are used to build the index.
Because this option increases the amount of temporary disk space that is used to create
an index, you should avoid setting the SORT_IN_TEMPDB to ON if you need to increase
the tempdb database performance.
Consider using the RAID 0 disk array for the tempdb database.
Because the tempdb database usually very exhausted used, it is important that the disk
arrays containing the tempdb database have sufficient disk I/O performance.
Use the Windows System Monitor to determine the appropriate number of the data
and log files of the tempdb database.
In Windows System Monitor, check the “PhysicalDisk: % Disk Time” counter. This counter monitors
the percentage of time that the disk is busy with read/write activity. If the “PhysicalDisk:
% Disk Time” counter is more than 90 percent check the “PhysicalDisk: Current Disk Queue Length”
counter to determine how many system requests are waiting for disk access. If the “PhysicalDisk:
Current Disk Queue Length” counter is more than 2, consider placing some data or log files to
an additional disk and continue monitoring.