If the SQL Server is restarted, the PDF size is reduced

tempdb database

Scope of application:SQL Server (all supported versions) Azure SQL database

The system database is a global resource and is available to all users connected to an instance of SQL Server or Azure SQL Database. contains the following:

  • Temporary User objectsthat are created explicitly. These include global or local temporary tables and indexes, temporary stored procedures, table variables, tables returned in table-valued functions, and cursors.

  • Internal objectscreated by the database engine. This includes:

    • Work tables that store direct results for spools, cursors, sorts, and temporary large object storage (LOB).
    • Work files for hash join or hash aggregate operations.
    • Intermediate results of sortings for activities such as For example, creating or rebuilding indexes (if specified) or for specific -, -, or queries.

    Each internal object uses at least nine pages: an Index Allocation Map (IAM) page and an eight-page extension. For more information on pages and extensions, see Pages and Blocks.

  • Version store. These are collections of data pages that store the rows of data to support row versioning features. There are two types of storage: a general version store and a version store for online indexing. The version stores contain the following:

    • Row versions generated by data modification transactions in a database used by row versioning isolation or by snapshot isolation transactions.
    • Row versions generated by data change transactions for features such as: For example, the following are generated: online index operations, multiple active result sets (MARS) and triggers.

Operations in are minimally logged so that transactions can be rolled back. is rebuilt every time SQL Server is started, so the system always starts with a clean copy of the database. Temporary tables and stored procedures are automatically deleted when the connection is disconnected; no connections are active when the system is shut down.

So nothing is ever saved in between individual SQL Server sessions. Backup and restore operations are not allowed for.

Physical properties of tempdb in SQL Server

The following table lists the initial configuration values ​​of the data and log files in SQL Server. These values ​​are based on the standard values ​​for the database. The size of these files may differ slightly between editions of SQL Server.

fileLogical namePhysical nameOriginal sizeFile growth
Primary datatempdevtempdb.mdf8 megabytesAutomatically grows by 64 MB until the disk space is exhausted
Secondary data filestemp #tempdb_mssql _ #. ndf8 megabytesAutomatically grows by 64 MB until the disk space is exhausted
logtemplogtemplog.ldf8 megabytesAutomatically grows by 64MB until it reaches the maximum of 2TB

The number of secondary data files depends on the number of (logical) processors on the computer. As a general rule, if the number of logical processors is eight or less, use the number of data files equal to the number of logical processors. If there are more than eight logical processors, use eight data files. If the conflict persists, increase the number of data files by multiples of four until the conflict is reduced to an acceptable level. Alternatively, you can change the workload or the code.

Note

The default value for the number of data files is based on the general guidelines in KB 2154845.

Note

Query the view to check the current size and magnification parameters of the.

Moving the tempdb data and log files to SQL Server

For information about moving the data and log files from, see Moving System Databases.

Database options for tempdb in SQL Server

The following table lists the default values ​​for each of the database database options and whether the option can be changed. To view the current settings for these options, use the sys.databases catalog view.

Database optiondefault valueCan be changed.
ALLOW_SNAPSHOT_ISOLATIONOFFYes
ANSI_NULL_DEFAULTOFFYes
ANSI_NULLSOFFYes
ANSI_PADDINGOFFYes
ANSI_WARNINGSOFFYes
ARITHABORTOFFYes
AUTO_CLOSEOFFNo
AUTO_CREATE_STATISTICSAYes
AUTO_SHRINKOFFNo
AUTO_UPDATE_STATISTICSAYes
AUTO_UPDATE_STATISTICS_ASYNCOFFYes
CHANGE_TRACKINGOFFNo
CONCAT_NULL_YIELDS_NULLOFFYes
CURSOR_CLOSE_ON_COMMITOFFYes
CURSOR_DEFAULTGLOBALYes
Database availability optionsON-LINE

MULTI_USER

READ_WRITE
No

No

No
DATE_CORRELATION_OPTIMIZATIONOFFYes
DB_CHAININGANo
ENCRYPTIONOFFNo
MIXED_PAGE_ALLOCATIONOFFNo
NUMERIC_ROUNDABORTOFFYes
PAGE_VERIFYCHECKSUM for new installations of SQL Server

NONE for upgrading SQL Server
Yes
PARAMETERIZATIONSIMPLEYes
QUOTED_IDENTIFIEROFFYes
READ_COMMITTED_SNAPSHOTOFFNo
RECOVERYSIMPLENo
RECURSIVE_TRIGGERSOFFYes
Service broker optionsENABLE_BROKERYes
TRUSTWORTHYOFFNo

For a description of these database options, see ALTER DATABASE SET Options (Transact-SQL).

tempdb database in SQL database

Tempdb sizes for DTU-based service plans

Service level targetMaximum file size (GB) in Number of data files in Maximum data size (GB) in
basic13,9113,9
S013,9113,9
S113,9113,9
S213,9113,9
S332132
S432264
S632396
S7326192
S93212384
S123212384
P113,912166,7
P213,912166,7
P413,912166,7
P613,912166,7
P1113,912166,7
P1513,912166,7
Elastic basic pool (all DTU configurations)13,912166,7
Elastic standard pool (50 eDTU)13,912166,7
Elastic standard pool (100 eDTU)32132
Elastic standard pool (200 eDTU)32264
Elastic standard pool (300 eDTU)32396
Elastic standard pool (400 eDTU)32396
Elastic standard pool (800 eDTU)326192
Elastic standard pool (1,200 eDTU)3210320
Elastic standard pool (1,600-3,000 eDTU)3212384
Elastic premium pool (all DTU configurations)13,912166,7

Tempdb sizes for virtual core based service tariffs

For information, see Resource Limits in the Virtual Core Purchase Model.

Restrictions

The following operations cannot be performed on the database:

  • Adding filegroups.
  • Back up and restore the database.
  • Change the sort order. The standard sorting corresponds to the server sorting.
  • Change database owner is owned by sa.
  • Create a database snapshot.
  • Delete the database.
  • Delete the guest -User from the database.
  • Enable change data capture.
  • Participate in database mirroring.
  • Remove the primary filegroup, primary data file, or log file.
  • Rename the database or the primary filegroup.
  • Running.
  • Running.
  • Set the database to.
  • Set the database or primary filegroup on.

Permissions

Any user can create temporary objects in. Users only have access to their own objects unless they have been assigned additional permissions. It is possible to revoke permission to connect to to prevent a user from using. However, this is not recommended because some routine operations require the use of.

Optimizing the performance of tempdb in SQL Server

The size and physical placement of the database can affect the performance of a system. For example, if the size is defined to be too small, each time the SQL Server instance is restarted, part of the system processing load may have to be used to automatically grow the database to the size necessary to support the resulting workload.

Whenever possible, use fast file initialization to improve the performance of data file expansion operations.

Allocate space for all files in advance by setting the file size to a value large enough to accommodate the typical workload in the environment. Preassignment prevents you from zooming in too often and affecting performance. The database should be set to autogrowth to increase the storage space for unscheduled exceptions.

Data files should be the same size in each filegroup because SQL Server uses a proportional padding algorithm that prioritizes allocations in files with more free space. Splitting into multiple data files of the same size provides a high degree of parallel efficiency in operations in which is used.

Set the file size increment to a reasonable size so that the database files do not grow too small. If the magnification is too small compared to the amount of data written to, it may need to be increased continuously. This affects the performance.

Use the following query to check the current size and magnification parameters of:

Place the database on a fast I / O subsystem. Use disk striping when many disks are directly attached. Individual or groups of data files do not necessarily need to be stored on different volumes or spindles unless you also encounter I / O bottlenecks.

Do not place the database on the same media used by user databases.

Performance improvements in tempdb for SQL Server

Starting with SQL Server 2016 (13.x), the performance of is further optimized in the following ways:

  • Temporary tables and table variables are cached. Caching enables the operations of deleting and creating the temporary objects to be carried out very quickly. It also reduces page mapping and metadata conflicts.
  • The latch log for allocation pages has been improved to reduce the number of latches (update latches) used.
  • The logging overhead for has been reduced to reduce the I / O bandwidth of the disk for the log file.
  • Setup adds several data files during the installation of a new instance. For this task, you can use the new user interface input controls in the Database engine configuration and use the command line parameter. By default, setup adds the number of data files equal to the number of logical processors, up to a maximum of eight.
  • If there are multiple data files, all files will automatically grow at the same time and by the same amount, depending on the growth settings. Trace flag 1117 is no longer required.
  • All assignments in use uniform extensions. Trace flag 1118 is no longer required.
  • The property is activated for the primary filegroup. This cannot be changed.

For more information on performance improvements in, see the blog post TEMPDB - Files and Trace Flags and Updates, Oh My! (TEMPDB files and trace flags and updates, oh my!).

Memory optimized tempdb metadata

Historically, metadata conflicts have created a scalability bottleneck for many of the workloads running on SQL Server. SQL Server 2019 (15.x) introduces a new feature belonging to the In-Memory Database family of features: memory-optimized tempdb metadata.

This feature removes this bottleneck and enables a new level of scalability for tempdb-intensive workloads. In SQL Server 2019 (15.x), the system tables involved in managing temporary table metadata can be moved to non-persistent memory-optimized tables without latches.

This seven-minute video provides an overview of when and how to use storage-optimized tempdb metadata:

Configure and use memory-optimized tempdb metadata

The following script can be used to enable this new feature:

The service must be restarted for this configuration change to take effect.

You can use the following T-SQL command to check whether it is memory-optimized:

If, after enabling memory-optimized metadata, the server does not start for any reason, you can bypass the feature by running the SQL Server instance using the startup option -f start in the minimal configuration. Then you can disable the feature and restart SQL Server in normal mode.

To protect the server from potential out of memory conditions, you can bind to a resource pool. This is done by command instead of the steps you would normally follow to bind a resource pool to a database.

This change also requires a reboot to take effect, even if the tempdb memory-optimized metadata is already enabled.

Limitations of memory-optimized tempdb metadata

  • Switching this function on and off is not dynamic. Due to the intrinsic changes that must be made to the structure of, a restart is required to enable or disable the feature.

  • A single transaction cannot access memory-optimized tables in multiple databases. In transactions involving a memory-optimized table in a user database, system views cannot be accessed within the same transaction. When you try to access system views in the same transaction, you receive the following error message:

    Example:

  • Queries against memory-optimized tables do not support locking and isolation hints, so these hints are not taken into account when queries against memory-optimized catalog views. As with other system catalog views in SQL Server, all transactions for system views are in isolation (or in this case in isolation).

  • Columnstore indexes cannot be created on temporary tables when memory-optimized metadata is enabled.

  • Due to the limitation on columnstore indexes, use of the system stored procedure with the data compression parameter or when memory-optimized metadata is enabled is not supported.

Note

These restrictions only apply when referring to system views. If necessary, you can create a temporary table in the same transaction when accessing a memory-optimized table in a user database.

Capacity planning for tempdb in SQL Server

Determining the appropriate size for a SQL Server production environment depends on many factors. As explained earlier, these factors include the existing workload and the SQL Server features being used. It is recommended that you analyze your existing workload by performing the following tasks in a SQL Server test environment:

  • Set the auto-magnification for.
  • Run individual queries or trace files against the workload and monitor disk space usage.
  • Perform index maintenance operations - such as: B. Rebuilding indexes and monitoring disk space.
  • Use the space usage values ​​from the previous steps to forecast the total workload. Adjust this value to reflect the anticipated concurrent activity, and then size accordingly.

Monitor tempdb usage

Insufficient disk space can cause significant disruptions in the SQL Server production environment. This problem can also prevent applications from being able to complete operations. With the dynamic administration view sys.dm_db_file_space_usage you can monitor the space used in the files:

In addition, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic administrative views to monitor page allocation and detachment at the session or task level. You can use these views to identify large queries, temporary tables, or table variables that are consuming a lot of space.You can also use various performance counters to monitor the free space available in as well as the resources that are in use.

Related content