Blog

Improve your SQL Managed Instance performance with new TempDB configurations

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

 

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

 

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.

 

No Comments

Leave a Reply

Norges beste datingside norsk homse porno & escort girls denmark hot or notNakenbilder av linni meister helene rask kjæreste – hvordan få orga navn påxstillinger

Contact Us

We are always here to help. Please reach out to us and we'll get back to you as soon as possible.

Address:

Maruti Court, Ground Floor
Mvuli Road, Nairobi, Kenya

Contacts:

+254716973110

office@digitalexperts.co.ke

Hours:

Mon-Fri 8am – 5pm
Sat 9am – 1am
Sun & Holidays Closed