This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

Today we are pleased to announce TempDB configurations. You can now configure the number of TempDB files and their growth increments to tune your instance performance even more. Your TempDB configurations will be persisted upon a server restart, an update SLO or a failover. Additionally, a snapshot isolation property for TempDB will now also be persisted upon a server restart, an update SLO or a failover.

What is TempDB?

SQL Server instance comes with four system databases by default, one of which is TempDB. The structure of TempDB is the same as any other user database structure; the difference is that it is used for non-durable storage and therefore the transactions are minimally logged. Even though it is rarely called explicitly, TempDB is a database that has so many functions within SQL Server, that it is probably the busiest database on most SQL Server instances.

TempDB cannot be dropped, detached, taken offline, renamed, or restored. Attempting any of these operations will return an error. TempDB is regenerated upon every start of the server instance and any objects that may have been created in TempDB during a previous session will not persist upon a service restart, an update SLO or a failover.

Why is TempDB critical to the performance of your instance?

The workload in TempDB is quite different than workloads in other user databases; objects and data are frequently being created and destroyed and there is extremely high concurrency. Moreover, there is only one TempDB on each server and even if you have multiple databases and applications connecting to that server, they all will be connecting to the same TempDB. When TempDB is heavily used, a service may experience contention when it tries to allocate pages. Depending on the degree of contention, this may cause queries and requests that involve TempDB to be unresponsive. Therefore, TempDB is critical to the performance of the service.

Why is the number of TempDB files important for the performance of your instance?

Increasing the number of data files in TempDB can help you to improve the concurrency of your instance’s TempDB and maximize disk bandwidth as it effectively creates one or more GAM and SGAM pages for each data file. The resource contention of the PFS page is reduced because eight pages at a time are marked as FULL because GAM is allocating the pages. 

On the other hand, increasing the number of files is not always the solution; having a lot of TempDB files may hurt certain workloads. Namely, in the latest SQL Server versions (2016+), when SQL Server auto-grows a TempDB data file, it auto-grows all the files at the same time and each growth event has a non-zero performance cost; this means that the more data files the TempDB has, the bigger effect it has on the performance. Additionally, every startup of the instance would last longer due to zeroing of more files each time.

All in all, there is no optimal number of TempDB files for every workload; it depends on the degree of contention seen in TempDB, which is unique for each workload.

Find the number of TempDB files

In SQL Server Management Studio (SSMS):

Go to Object Explorer; expand Databases; expand System Databases; right-click on tempdb database; click on the Properties. This will bring up the following screen where you can find the number of the TempDB files.

 Using  T-SQL:

- To count all TempDB files:

USE tempdb
SELECT COUNT(*) TempDBFiles FROM sys.database_files

- To count only TempDB data files:

USE tempdb
SELECT COUNT(*) TempDBFiles FROM sys.database_files where type = 0

You can now configure the number of TempDB files

Azure SQL Managed instance has been having a fixed number of TempDB files: 12 TempDB data files and 1 TempDB log file. This is still a default configuration when creating a new SQL MI. However, now if you want to change the number of TempDB data files, you can do it in the same manner as on SQL Server on premises; by adding a TempDB data file(s) or removing a TempDB data file(s).

You are now able to configure the number of TempDB data files in accordance with your workload.

Limitations: You can give a logical name to the new file respecting the following naming properties: case insensitivity, maximum 16 characters (this is specific limitation to SQL MI), no space char. The maximum number of TempDB files is 128.

NOTE! You do not have to restart the server after adding new files; however, the emptier files will be filled with higher priority and the round-robin algorithm for allocating pages will be shortly lost, until the system is rebalanced.

Add a new TempDB data file

In SSMS:

Go to Object Explorer; expand Databases; expand System Databases; right-click on tempdb database; click on the Properties. This will bring up the following screen where you can Add TempDB file(s).

Using T-SQL:

ALTER DATABASE tempdb ADD FILE (NAME = 'file_name')

Remove an existing TempDB data file

In SSMS:

Go to Object Explorer; expand Databases; expand System Databases; right-click on tempdb database; click on the Properties. This will bring up the following screen where you can Remove TempDB file(s).

Using T-SQL:

ALTER DATABASE tempdb REMOVE FILE (NAME = 'file_name')

Why are the growth increments of TempDB files important for the performance of your instance?

As already mentioned, each growth event has a non-zero performance cost; too small increment may cause extent fragmentation, and too large increment may make the growth slow due to zeroing or may cause the growth to fail if there is not enough space for the increment to happen.

Therefore, the optimal value for the file growth increment depends on the customer’s workload.

You can now configure the growth increments of TempDB files

Azure SQL Managed instance has been having a fixed growth increment of 254 MB for TempDB data files and a fixed growth increment of 64 MB for TempDB log file. You are now able to configure the growth increments for both TempDB data and for TempDB log file to adapt them to your workload and tune the performance even more.

NOTE! The file growth parameter can be in different forms taking either unit or percentage i.e., FILEGROWTH = int_growth_increment [KB|MB|GB |TB|%].

NOTE! We strongly suggest setting the growth increments the same across all TempDB data files. Otherwise, the round robin algorithm for allocating pages will be lost after each increase of any file, and the system may not be able to rebalance.

Set the growth increment for a new TempDB data file

Using T-SQL:

ALTER DATABASE tempdb ADD FILE (NAME = 'file_name', FILEGROWTH = int_growth_increment [KB|MB|GB|TB|%])

Change the growth increment for an existing TempDB data file

In SSMS:

Go to Object Explorer; expand Databases; expand System Databases; right-click on tempdb database; click on the Properties. Select Files page and click on the “…” to edit “Autogrowth / Maxsize”. This will bring up another screen where you can change Autogrowth for the TempDB file.

Using T-SQL:

ALTER DATABASE tempdb MODIFY FILE (NAME = 'file_name', FILEGROWTH = int_growth_increment[KB|MB|GB|TB|%])

Summary table of the new TempDB configurations

New TempDB configurations are persisted after a restart, an update SLO or a failover

TempDB is always re-created as an empty database when the instance restarts or fails over and any changes made in TempDB are not preserved in these situations. However, TempDB configuration settings are saved such that your TempDB layout (number of files and their growth increments) stays the same after a restart, an update, or a failover.

Wait! There is more: A persisted snapshot isolation property

Now, besides the TempDB layout, ALLOW_SNAPSHOT_ISOLATION property configuration is also persisted. This is important because updated row versions for each transaction must be maintained once the snapshot isolation is enabled.

Prior to SQL Server 2019, these versions were stored in TempDB. SQL Server 2019 introduces a new feature, Accelerated Database Recovery (ADR) which requires its own set of row versions. However, if ADR is not enabled, row versions are kept in TempDB as usual. In SQL Server, ADR is disabled by default, whereas in SQL MI and SQL DB, ADR is enabled by default and cannot be disabled.

Snapshot isolation must be enabled by setting ON the ALLOW_SNAPSHOT_ISOLATION database option before it is used in transactions. This way, ADR is overridden and the mechanism for storing row versions in the TempDB is activated. Customers often used this workaround such that they ran a SQL Agent Job to set the ALLOW_SNAPSHOT_ISOLATION property upon the service restart, but there was no guarantee that some other transactions would not be executed before this configuration applies.

Now, the ALLOW_SNAPSHOT_ISOLATION property is persisted after the TempDB restart, an update SLO or a failover, which resolves the prior issues.

Summary

In this article, we discussed the new TempDB configurations in SQL Managed Instance and how they can affect your instance performance. Thank you for reading and enjoy better performance of your SQL Managed Instance with the customized TempDB.