Edit

Share via

Configure tempdb settings for Azure SQL Managed Instance

Applies to: Azure SQL Managed Instance

This article teaches you to configure your tempdb settings for Azure SQL Managed Instance.

Azure SQL Managed Instance allows you to configure the following:

  • Number of tempdb files
  • The growth increment of tempdb files
  • Maximum tempdb size

tempdb settings persist after your instance is restarted, updated, or if there's a failover.

Overview

tempdb is one of the default system databases that comes with Azure SQL Managed Instance. The structure of tempdb is the same as any other user database structure. The difference is that since tempdb is used for nondurable storage, transactions are minimally logged.

tempdb can't be dropped, detached, taken offline, renamed, or restored. Attempting any of these operations returns an error. tempdb is regenerated upon every start of the instance. Any objects that might have been created in tempdb during a previous session don't persist when the service restarts, after an instance update management operation or a failover.

The workload in tempdb differs from workloads in other user databases; objects and data are frequently created and destroyed and there's extremely high concurrency. There's only one tempdb for each SQL managed instance. Even if you have multiple databases and applications connecting to the instance, they all use the same tempdb database. Services might experience contention when they try to allocate pages in a heavily used tempdb. Depending on the degree of contention, queries and requests that involve tempdb could become unresponsive. This is why tempdb is critical to the performance of the service.

Number of tempdb files

Increasing the number of tempdb data files creates one or more GAM and SGAM pages for each data file, which helps improve tempdb concurrency and reduces PFS page contention. However, increasing the number of tempdb data files could have other performance implications, so test thoroughly before implementing in production.

By default, Azure SQL Managed Instance creates 12 tempdb data files and one tempdb log file, but it's possible to modify this configuration.

Modifying the number of tempdb files has the following limitations:

  • The logical name of the new file is case insensitive, with a maximum of 16 characters and no spaces.
  • The maximum number of tempdb files is 128.

Note

You don't have to restart the instance after adding new files. However, the emptier files will be filled with higher priority and the round-robin algorithm for allocating pages is lost until the system is rebalanced.

You can use both SQL Server Management Studio (SSMS) and Transact-SQL (T-SQL) to change the number of files for tempdb in Azure SQL Managed Instance.

You can use SQL Server Management Studio (SSMS) to modify the number of tempdb files. To do so, follow these steps:

  1. Connect to your SQL managed instance in SSMS.

  2. Expand Databases in Object Explorer, and then expand System databases.

  3. Right-click tempdb, and choose Properties.

  4. Select Files under Select a page to view the existing number of tempdb files.

  5. To add a file, choose Add and then provide information about the new data file in the row.

    Screenshot of Database Properties in SSMS, with new database file name highlighted.

  6. To remove a tempdb file, choose the file you want to remove from the list of database files, and then select Remove.

Growth increment

tempdb file growth can have a performance impact to queries using tempdb. As such, tempdb data file growth increments that are too small can cause extent fragmentation, while increments that are too large can result in slow growth or growth failure if there isn't sufficient space for the growth to happen. The optimal value for tempdb file growth increments depends on your workload.

The default growth increments for SQL Managed Instance are 254 MB for tempdb data files and 64 MB for tempdb log files, but you can configure growth increments to adapt to your workload and tune your performance.

Consider the following:

  • The file growth parameter supports the following units for int_growth_increment: KB, MB, GB, TB, and %.
  • Growth increments should be the same for all tempdb data files. Otherwise, the round robin algorithm that allocates pages could be affected.

You can use both SQL Server Management Studio (SSMS) and Transact-SQL (T-SQL) to change the growth increment for your tempdb files.

You can use SQL Server Management Studio (SSMS) to modify the growth increment of tempdb files. To do so, follow these steps:

  1. Connect to your SQL managed instance in SSMS.

  2. Expand Databases in Object Explorer, and then expand System databases.

  3. Right-click tempdb, and choose Properties.

  4. Select Files under Select a page to view the existing number of tempdb files.

  5. Choose the ellipses (...) next to a data file to open the Change Autogrowth properties dialog window.

  6. Check the box next to Enable Autogrowth, and then modify your autogrowth settings by specifying the file growth values, in either percent or megabytes.

    Screenshot of Change Autogrowth for tempdev in SSMS, with new database file name highlighted.

  7. Select OK to save your settings.

Maximum size

tempdb size is the sum size of all tempdb files. tempdb file size is an allocated (zeroed) space for that tempdb file. The initial file size for all tempdb files is 16 MB, which is the size of all tempdb files when the instance restarts or fails over. Once a tempdb data file's used space reaches the file size, all tempdb data files automatically grow by their configured growth increments.

tempdb used space is the sum of the used space of all tempdb files. tempdb file used space is equal to the part of that tempdb file size that's occupied with nonzero information. The sum of tempdb used space and tempdb free space is equal to the tempdb size.

You can use T-SQL to determine the current used and free space for your tempdb files.

To get used space, free space, and size of your tempdb data files, run this command:

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

The following screenshot shows an example output:

Screenshot of the query result in SSMS showing used and free space in the tempdb data file.

To get the used space, free space, and size of your tempdb log files, run this command:

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

The following screenshot shows an example output:

Screenshot of the query result in SSMS showing used and free space in the tempdb log file.

tempdb max size is the limit after which your tempdb can't grow further.

tempdb max size in SQL Managed Instance has the following limitations:

  • In the General Purpose service tier, the maximum size for tempdb is limited to 24 GB/vCore (96-1920 GB). The max size of the tempdb log file is 120 GB.
  • In the Business Critical service tier, tempdb competes with other databases for resources, so the reserved storage is shared between tempdb and other databases up to the maximum storage size allocated to the instance. The maximum size of the tempdb log file is two TB.

tempdb files grow until they reach either the maximum limit allowed by the service tier, or by the manually configured max tempdb file size.

You can use both SQL Server Management Studio (SSMS) and Transact-SQL (T-SQL) to change the maximum size for your tempdb files.

To determine your current tempdb max size in SSMS, follow these steps:

  1. Connect to your SQL managed instance in SSMS.
  2. Expand Databases in Object Explorer, and then expand System databases.
  3. Right-click tempdb, and choose Properties.
  4. On the General page, check the Size value under Database to determine your max tempdb size. A value of -1 indicates tempdb max size is unlimited.

Screenshot of tempdb database properties showing the max size for tempdb in SSMS.

To change your current tempdb max size in SSMS, follow these steps:

  1. Connect to your SQL managed instance in SSMS.
  2. Expand Databases in Object Explorer, and then expand System databases.
  3. Right-click tempdb, and choose Properties.
  4. Select Files under Select a page to view the existing number of tempdb files.
  5. Choose the ellipses (...) next to a data file to open the Change Autogrowth properties dialog window.
  6. Modify your tempdb max size settings by changing the values under Maximum file size.
  7. Select OK to save your settings.

Screenshot of the change autogrowth dialog box in SSMS, with maximum file size highlighted.

tempdb limits

The following table defines limits for various tempdb configuration settings:

Configuration setting Values
Logical names of tempdb files 16 characters maximum
Number of tempdb files 128 files maximum
Default number of tempdb files 13 (1 log file + 12 data files)
Initial size of tempdb data files 16 MB
Default growth increment of tempdb data files 256 MB
Initial size of tempdb log files 16 MB
Default growth increment of tempdb log files 64 MB
Initial max tempdbsize -1 (unlimited)
Max size of tempdb data files - In the General Purpose service tier: 24 GB/vCore up to 1920 GB total
In the Business Critical service tier: Up to the storage size
Max size of tempdb log files - In the General Purpose service tier: 120 GB
In the Business Critical service tier: 2 TB

Related content