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, a resize management operation or a failover. Additionally, a snapshot isolation property for TempDB will now also be persisted upon a server restart, an instance update management operation or a failover.
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 instance update management operation or a failover.
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.
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.
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
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.
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')
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 file_name
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.
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.
Using T-SQL:
ALTER DATABASE tempdb ADD FILE (NAME = 'file_name', FILEGROWTH = int_growth_increment [KB|MB|GB|TB|%])
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|%])
|
Before |
Now |
Logical names of the TempDB files |
Preconfigured - Fixed |
Configurable Maximum 16 characters. |
Number of TempDB files |
13 (1 log file + 12 data files) - Fixed |
Configurable The maximum is 128. |
Default number of TempDB files |
13 (1 log file + 12 data files) |
13 (1 log file + 12 data files) |
Initial size of TempDB data files |
16 MB |
16 MB |
Growth increment of TempDB data files |
256 MB - Fixed |
Configurable |
Default growth increment of TempDB data files |
256 MB |
256 MB |
Initial size of TempDB log file |
16 MB |
16 MB |
Growth increment of TempDB log file |
64 MB - Fixed |
Configurable |
Default growth increment of TempDB log file |
64 MB |
64 MB |
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 instance update operation, or a failover.
Now, besides the TempDB layout, ALLOW_SNAPSHOT_ISOLATION property configuration on TempDB is also persisted. This is important because updated row versions for each transaction must be maintained once the snapshot isolation is enabled.
Snapshot isolation must be enabled by setting ON the ALLOW_SNAPSHOT_ISOLATION database option before it is used in transactions, which is tricky for TempDB. Customers often used a workaround such that they ran a SQL Agent Job to set the ALLOW_SNAPSHOT_ISOLATION property on TempDB 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 configuration is persisted after the TempDB restart, an instance update or a failover, which resolves the prior issues.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.