Configure your TempDB max size in Azure SQL Managed Instance

[Guest NevenaNikolic]

In September 2022, we announced the option to configure the number of TempDB files and their growth increments. For more details, read Improve your SQL Managed Instance performance with new TempDB configurations.

 

Today we are pleased to announce a new option to configure your TempDB max size. As expected, your new TempDB configuration will persist upon a server restart, an instance update management operation or a failover.

 

 

 

What are TempDB size, used space and free space?

 

 

TempDB size is the sum of the file sizes of all TempDB files. A TempDB file size is an allocated (zeroed) space for that TempDB file. Initial file size for all TempDB files is 16 MB. That is the size all TempDB files are set to upon a restart, a failover, or an instance update. Every time a TempDB data file’s used space reaches the file size, all TempDB data files auto-grow by their configured (or default) growth increments. Similarly, every time the TempDB log file’s used space reaches the log file size, the log file auto-grows by its configured (or default) growth increment.

 

TempDB used space is the sum of the used spaces of all TempDB files. A TempDB file used space is equal to the part of that TempDB file size that is occupied with non-zero information.

 

The sum of TempDB used space and TempDB free space is equal to the TempDB size.

 

 

 

Find your TempDB size, used space and free space

 

 

Using T-SQL:

 

Run the following script to get your used TempDB data files’ space, free TempDB data files’ space and TempDB data files’ size:

 

 

 

USE tempdb

SELECT SUM((allocated_extent_page_count)*1.0/128) AS TempDB_used_data_space_inMB,

SUM((unallocated_extent_page_count)*1.0/128) AS TempDB_free_data_space_inMB,

SUM(total_page_count*1.0/128) AS TempDB_data_size_inMB

FROM sys.dm_db_file_space_usage

 

 

 

 

 

Run the following script to get your used TempDB log file’s space, free TempDB log file’s space and TempDB log file’s size:

 

 

 

USE tempdb

SELECT used_log_space_in_bytes*1.0/1024/1024 AS TempDB_used_log_space_inMB,

(total_log_size_in_bytes- used_log_space_in_bytes)*1.0/1024/1024 AS TempDB_free_log_space_inMB,

total_log_size_in_bytes*1.0/1024/1024 AS TempDB_log_size_inMB

FROM sys.dm_db_log_space_usage

 

 

 

 

 

TempDB size is equal to the sum of TempDB data files’ size (TempDB_data_size_inMB) and TempDB log file’s size (TempDB_log_size_inMB). In our example TempDB size is 208 MB (192 MB + 16 MB).

 

To find the TempDB size in a quicker way, run:

 

 

 

USE tempdb

SELECT (SUM(size)*1.0/128) AS TempDB_size_InMB FROM sys.database_files

 

 

 

 

 

Using 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 see TempDB Size.

 

 

 

 

What is TempDB max size?

 

 

TempDB max size is the limit after which TempDB cannot further grow. TempDB max size has its

 

• Technical limitations: In General Purpose service tier, the TempDB max size is technically limited to 24 GB/vCore (96 - 1,920 GB) and TempDB log file is technically limited to 120 GB. In Business Critical service tier, TempDB competes with other databases for the resources, meaning that the reserved storage is shared between the TempDB and other databases. The max size of the TempDB log file in Business Critical service tier is technically limited to 2TB.
  
• Manually imposed limitations [NEW!]: you can now configure the maximum size of TempDB, you can do it in the same manner as on SQL Server on premises; by changing the max sizes of TempDB files.
  

 

NOTE! The TempDB files will grow until they hit the more restricted of the two limitations: the technical and the imposed limitation per file.

 

The default max size for all TempDB data files on the new SQL Managed Instances is -1 which stands for unlimited. The default max size for TempDB log file is 120 GB on the General Purpose managed instance and 2 TB on the Business Critical managed instances.

 

 

 

Find your TempDB max size

 

 

Using T-SQL:

 

Run the following script to get the max sizes of the TempDB files:

 

 

 

USE tempdb

SELECT name, max_size FROM sys.database_files

 

 

 

 

 

Using 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 see TempDB files’ Max sizes by selecting a page Files.

 

 

 

 

You can now configure the maximum size of TempDB

 

 

You can now configure the maximum size of TempDB in accordance with your workload, in the same manner as on SQL Server on premises; by changing the max sizes of TempDB files.

 

RECOMMENDATION! We strongly suggest setting the maximum size for all the Temp DB data files to be the same, because the round robin algorithm favors allocations in files with more free space and the system may take a long time to rebalance. Dividing TempDB into data files of equal size provides a high degree of parallel efficiency in operations that use TempDB .

 

 

 

Set the max size for a new TempDB data file

 

 

Using T-SQL:

 

 

 

ALTER DATABASE tempdb ADD FILE (NAME = 'file_name', MAXSIZE = int_maxsize[KB|MB|GB|TB])

 

 

 

 

 

Change the max size for an existing TempDB file

 

 

Using T-SQL:

 

 

 

ALTER DATABASE tempdb MODIFY FILE (NAME = file_name, MAXSIZE = int_maxsize[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 the Maximum file size for the TempDB file.After changing the TempDB max sizes for every TempDB file, this is our new TempDB files layout:

 

 

 

 

Summary table of all TempDB configurations

 

 

 

 

 

TempDB configurations are persisted after a restart, an instance update management operation 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 the TempDB number of files, their growth increments and their max file sizes stay the same after a restart, an instance update, or a failover.

 

 

 

Summary

 

 

In this article, we highlighted the differences between TempDB max size, TempDB size and TempDB used space in SQL Managed Instance, and we introduced the new TempDB configuration, a change of the TempDB max size. Thank you for reading and enjoy better performance of your SQL Managed Instance with the customized TempDB.

 

Continue reading...