Base de données tempdbTempDB Database

S’APPLIQUE À : ouiSQL Server ouiAzure SQL Database nonAzure Synapse Analytics (SQL DW) nonParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

La base de données système TempDB est une ressource globale à la disposition de tous les utilisateurs connectés à l’instance de SQL ServerSQL Server ou à SQL Database.The TempDB system database is a global resource that is available to all users connected to the instance of SQL ServerSQL Server or connected to SQL Database. La base de données tempdb peut stocker les éléments suivants :Tempdb is used to hold:

  • Les objets utilisateurs temporaires créés explicitement, tels que les tables et index temporaires locaux ou globaux, les procédures stockées temporaires, les variables de table, les tables retournées dans des fonctions table, ou les curseurs.Temporary user objects that are explicitly created, such as: global or local temporary tables and indexes, temporary stored procedures, table variables, Tables returned in table-valued functions, or cursors.

  • Les objets internes créés par le moteur de base de données.Internal objects that are created by the database engine. notamment :These include:

    • Les tables de travail afin de stocker les résultats intermédiaires pour les mises en spools, les curseurs, les tris et le stockage temporaire des des objets volumineux (LOB).Work tables to store intermediate results for spools, cursors, sorts, and temporary large object (LOB) storage.
    • les fichiers de travail correspondant aux opérations de jointures ou d'agrégations hachées ;Work files for hash join or hash aggregate operations.
    • les résultats de tris intermédiaires pour les opérations de création ou de reconstruction d'index (si SORT_IN_TEMPDB est spécifié) ou pour certaines requêtes GROUP BY, ORDER BY ou UNION.Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.

    Notes

    Chaque objet interne utilise un minimum de neuf pages, une page IAM et une étendue de huit pages.Each internal object uses a minimum of nine pages; an IAM page and an eight-page extent. Pour plus d’informations sur les pages et les extensions, consultez Pages et étendues.For more information about pages and extents, see Pages and Extents.

    Important

    Les pools élastiques et les bases de données uniques Azure SQL Database prennent en charge les tables temporaires globales et les procédures stockées temporaires globales qui sont stockées dans TempDB et dont l’étendue est limitée à la base de données.Azure SQL Database single databases and elastic pools support global temporary tables and global temporary stored procedures that are stored in TempDB and are scoped to the database level. Les tables temporaires globales et les procédures stockées temporaires globales sont partagées pour toutes les sessions utilisateur exécutées dans la même instance Azure SQL Database.Global temporary tables and global temporary stored procedures are shared for all users' sessions within the same Azure SQL database. Les sessions utilisateur d’autres instances Azure SQL Database n’ont pas accès aux tables temporaires globales.User sessions from other Azure SQL databases cannot access global temporary tables. Pour plus d’informations, consultez Database scoped global temporary tables (Azure SQL Database).For more information, see Database scoped global temporary tables (Azure SQL Database). Azure SQL Database Managed Instance prend en charge les mêmes objets temporaires que SQL Server.Azure SQL Database Managed Instance) supports the same temporary objects as does SQL Server. Pour les pools élastiques et les bases de données uniques Azure SQL Database, seules les bases de données MASTER et TempDB s’appliquent.For Azure SQL Database single databases and elastic pools, only master database and TempDB database apply. Pour plus d’informations, consultez Qu’est-ce qu’un serveur Azure SQL Database.For more information, see What is an Azure SQL Database server. Pour une présentation de TempDB dans le contexte des bases de données uniques et des pools élastiques Azure SQL Database, consultez Base de données TempDB dans les bases de données uniques et les pools élastiques Azure SQL Database.For a discussion of TempDB in the context of Azure SQL Database single databases and elastic pools, see TempDB Database in Azure SQL Database single databases and elastic pools. Pour SQL Database Managed Instance, toutes les bases de données système s’appliquent.For Azure SQL Database Managed Instance, all system databases apply.

  • Des banques de versions, qui sont un ensemble de pages de données contenant les lignes de données requises pour prendre en charge les fonctionnalités qui utilisent le contrôle de version de ligne.Version stores, which are a collection of data pages that hold the data rows that are required to support the features that use row versioning. Il existe deux banques de versions : une banque de versions commune et une banque de versions de construction d'index en ligne.There are two version stores: a common version store and an online-index-build version store. Les banques de versions contiennent les éléments suivants :The version stores contain:

    • Les versions de ligne générées par les transactions de modification de données dans une base de données qui utilise l'isolement basé sur le contrôle de version de ligne read committed ou les transactions d'isolement d'instantané.Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
    • Versions de ligne qui sont générées par les transactions de modification de données pour les fonctionnalités telles que : opérations d'index en ligne, MARS (Multiple Active Result Sets) et déclencheurs AFTER.Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Les opérations effectuées dans TempDB font l’objet d’un enregistrement minimal pour permettre la restauration des transactions.Operations within TempDB are minimally logged so that transactions can be rolled back. La base de données TempDB étant recréée chaque fois que SQL ServerSQL Server est démarré, le système démarre toujours avec une copie propre de la base de données.TempDB is re-created every time SQL ServerSQL Server is started so that the system always starts with a clean copy of the database. Les tables et les procédures stockées temporaires sont automatiquement supprimées à la déconnexion et aucune connexion n'est active lorsque le système est arrêté.Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Par conséquent, aucune donnée de la base de données TempDB ne doit être enregistrée d'une session de SQL ServerSQL Server à l'autre.Therefore, there is never anything in TempDB to be saved from one session of SQL ServerSQL Server to another. La sauvegarde et la restauration ne sont pas autorisées sur la base de données TempDB.Backup and restore operations are not allowed on TempDB.

Propriétés physiques de TempDB dans SQL ServerPhysical Properties of TempDB in SQL Server

Le tableau suivant répertorie les valeurs de configuration initiales des fichiers de données et journaux de TempDB dans SQL Server. Ces valeurs sont basées sur les valeurs par défaut pour la base de données Model.The following table lists the initial configuration values of the TempDB data and log files in SQL Server, which are based on the defaults for the Model database. La taille de ces fichiers peut varier légèrement en fonction des éditions de SQL ServerSQL Server.The sizes of these files may vary slightly for different editions of SQL ServerSQL Server.

FichierFile Nom logiqueLogical name Nom physiquePhysical name Taille initialeInitial size Croissance du fichierFile growth
Données primairesPrimary data tempdevtempdev tempdb.mdftempdb.mdf 8 mégaoctets8 megabytes Croissance automatique de 64 Mo jusqu’à saturation du disque.Autogrow by 64 MB until the disk is full
Fichiers de données secondaires*Secondary data files* temp#temp# tempdb_mssql_#.ndftempdb_mssql_#.ndf 8 mégaoctets8 megabytes Croissance automatique de 64 Mo jusqu’à saturation du disque.Autogrow by 64 MB until the disk is full
JournalLog templogtemplog templog.ldftemplog.ldf 8 mégaoctets8 megabytes Croissance automatique de 64 mégaoctets jusqu’à un maximum de 2 téraoctets.Autogrow by 64 megabytes to a maximum of 2 terabytes

* Le nombre de fichiers dépend du nombre de processeurs (logiques) sur l’ordinateur.* The number of files depends on the number of (logical) processors on the machine. En règle générale, si le nombre de processeurs logiques est inférieur ou égal à huit, utilisez le même nombre de fichiers de données que de processeurs logiques.As a general rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. Si le nombre de processeurs logiques est supérieur à huit, utilisez huit fichiers de données et, si le conflit persiste, augmentez le nombre de fichiers de données par multiples de quatre pour réduire le conflit à un niveau acceptable ou bien modifiez la charge de travail/le code.If the number of logical processors is greater than eight, use eight data files and then if contention continues, increase the number of data files by multiples of 4 until the contention is reduced to acceptable levels or make changes to the workload/code.

Notes

La valeur par défaut du nombre de fichiers de données est basée sur les directives générales de l’article KB 2154845.The default value for the number of data files is based on the general guidelines in KB 2154845.

Déplacement des fichiers de données et journaux de TempDB dans SQL ServerMoving the TempDB data and log files in SQL Server

Pour déplacer les données TempDB et les fichiers journaux, consultez Déplacer des bases de données système.To move the TempDB data and log files, see Move System Databases.

Options de la base de données pour TempDB dans SQL ServerDatabase Options for TempDB in SQL Server

Le tableau suivant répertorie les valeurs par défaut de chaque option de la base de données TempDB et précise si elles sont modifiables.The following table lists the default value for each database option in the TempDB database and whether the option can be modified. Pour afficher les valeurs actuelles de ces options, utilisez l'affichage catalogue sys.databases .To view the current settings for these options, use the sys.databases catalog view.

Option de base de donnéesDatabase option Valeur par défautDefault value Peut être modifiéeCan be modified
ALLOW_SNAPSHOT_ISOLATIONALLOW_SNAPSHOT_ISOLATION OFFOFF OuiYes
ANSI_NULL_DEFAULTANSI_NULL_DEFAULT OFFOFF OuiYes
ANSI_NULLSANSI_NULLS OFFOFF OuiYes
ANSI_PADDINGANSI_PADDING OFFOFF OuiYes
ANSI_WARNINGSANSI_WARNINGS OFFOFF OuiYes
ARITHABORTARITHABORT OFFOFF OuiYes
AUTO_CLOSEAUTO_CLOSE OFFOFF NonNo
AUTO_CREATE_STATISTICSAUTO_CREATE_STATISTICS ONON OuiYes
AUTO_SHRINKAUTO_SHRINK OFFOFF NonNo
AUTO_UPDATE_STATISTICSAUTO_UPDATE_STATISTICS ONON OuiYes
AUTO_UPDATE_STATISTICS_ASYNCAUTO_UPDATE_STATISTICS_ASYNC OFFOFF OuiYes
CHANGE_TRACKINGCHANGE_TRACKING OFFOFF NonNo
CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL OFFOFF OuiYes
CURSOR_CLOSE_ON_COMMITCURSOR_CLOSE_ON_COMMIT OFFOFF OuiYes
CURSOR_DEFAULTCURSOR_DEFAULT GLOBALGLOBAL OuiYes
Options de disponibilité de base de donnéesDatabase Availability Options ONLINEONLINE

MULTI_USERMULTI_USER

READ_WRITEREAD_WRITE
NonNo

NonNo

NonNo
DATE_CORRELATION_OPTIMIZATIONDATE_CORRELATION_OPTIMIZATION OFFOFF OuiYes
DB_CHAININGDB_CHAINING ONON NonNo
ENCRYPTIONENCRYPTION OFFOFF NonNo
MIXED_PAGE_ALLOCATIONMIXED_PAGE_ALLOCATION OFFOFF NonNo
NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT OFFOFF OuiYes
PAGE_VERIFYPAGE_VERIFY CHECKSUM pour les nouvelles installations de SQL ServerSQL Server.CHECKSUM for new installations of SQL ServerSQL Server.

NONE pour les mises à niveau de SQL ServerSQL Server.NONE for upgrades of SQL ServerSQL Server.
OuiYes
PARAMETERIZATIONPARAMETERIZATION SIMPLESIMPLE OuiYes
QUOTED_IDENTIFIERQUOTED_IDENTIFIER OFFOFF OuiYes
READ_COMMITTED_SNAPSHOTREAD_COMMITTED_SNAPSHOT OFFOFF NonNo
RECOVERYRECOVERY SIMPLESIMPLE NonNo
RECURSIVE_TRIGGERSRECURSIVE_TRIGGERS OFFOFF OuiYes
Options de Service BrokerService Broker Options ENABLE_BROKERENABLE_BROKER OuiYes
TRUSTWORTHYTRUSTWORTHY OFFOFF NonNo

Pour obtenir une description de ces options de base de données, consultez Options ALTER DATABASE SET (Transact-SQL).For a description of these database options, see ALTER DATABASE SET Options (Transact-SQL).

Base de données TempDB dans SQL DatabaseTempDB database in SQL Database

Tailles de TempDB pour les niveaux de service basés sur DTUTempDB sizes for DTU-based service tiers

SLOSLO Taille maximale de fichier de données TempDB (Go)Max TempDB Data File Size (GBs) Nombre de fichiers de données TempDB# of TempDB data files Taille maximale des données TempDB (Go)Max TempDB data size (GB)
SimpleBasic 1313 11 1313
S0S0 1313 11 1313
S1S1 1313 11 1313
S2S2 1313 11 1313
S3S3 3232 11 3232
S4S4 3232 22 6464
S6S6 3232 33 9696
S7S7 3232 66 192192
S9S9 3232 1212 384384
S12S12 3232 1212 384384
P1P1 1313 1212 156156
P2P2 1313 1212 156156
P4P4 1313 1212 156156
P6P6 1313 1212 156156
P11P11 1313 1212 156156
P15P15 1313 1212 156156
Pools élastiques Premium (toutes les configurations de DTU)Premium Elastic Pools (all DTU configurations) 1313 1212 156156
Pools élastiques Standard (S0-S2)Standard Elastic Pools (S0-S2) 1313 1212 156156
Pools élastiques Standard (S3 et plus)Standard Elastic Pools (S3 and above) 3232 1212 384384
Pools élastiques De base (toutes les configurations de DTU)Basic Elastic Pools (all DTU configurations) 1313 1212 156156

Tailles de TempDB pour les niveaux de service basés sur vCoreTempDB sizes for vCore-based service tiers

Consultez les limites des ressources basées sur vCore.See vCore-based resource limits

RestrictionsRestrictions

Les opérations suivantes ne peuvent pas être effectuées sur la base de données TempDB :The following operations cannot be performed on the TempDB database:

  • Ajout de groupes de fichiersAdding filegroups
  • Sauvegarde ou restauration de la base de donnéesBacking up or restoring the database
  • Modification du classement.Changing collation. Le classement par défaut est celui du serveurThe default collation is the server collation
  • Modification du propriétaire de la base de données.Changing the database owner. TempDB appartient à saTempDB is owned by sa
  • Création d'un instantané de base de donnéesCreating a database snapshot
  • Suppression de la base de donnéesDropping the database
  • Suppression de l'utilisateur Invité de la base de donnéesDropping the guest user from the database
  • Activation de la capture des changements de donnéesEnabling change data capture
  • Participation à la mise en miroir de bases de donnéesParticipating in database mirroring
  • Suppression du groupe de fichiers primaire, du fichier de données primaire ou du fichier journalRemoving the primary filegroup, primary data file, or log file
  • Changement du nom de la base de données ou du groupe de fichiers primaireRenaming the database or primary filegroup
  • Exécution de DBCC CHECKALLOCRunning DBCC CHECKALLOC
  • Exécution de DBCC CHECKCATALOGRunning DBCC CHECKCATALOG
  • Affectation de la valeur OFFLINE à la base de donnéesSetting the database to OFFLINE
  • Affectation de la valeur READ_ONLY à la base de données ou au groupe de fichiers primaireSetting the database or primary filegroup to READ_ONLY

AutorisationsPermissions

Tous les utilisateurs peuvent créer des objets temporaires dans TempDB.Any user can create temporary objects in TempDB. Les utilisateurs n'ont accès qu'aux objets qu'ils possèdent, sauf s'ils ont reçu des autorisations supplémentaires.Users can only access their own objects, unless they receive additional permissions. Il est possible de révoquer l’autorisation de connexion à TempDB pour empêcher un utilisateur d’utiliser TempDB, mais cela n’est pas recommandé, car certaines opérations courantes nécessitent l’utilisation de TempDB.It is possible to revoke the connect permission to TempDB to prevent a user from using TempDB, but is not recommended as some routine operations require the use of TempDB.

Optimisation des performances de TempDB dans SQL ServerOptimizing TempDB performance in SQL Server

La taille et l’emplacement physique de la base de données TempDB peuvent influer sur les performances d’un système.The size and physical placement of the TempDB database can affect the performance of a system. Par exemple, si la taille définie pour TempDB est trop petite, il se peut que, à chaque redémarrage de l'instance SQL ServerSQL Server, une partie de la charge de traitement du système soit absorbée par l'ajustement automatique de TempDB à la taille nécessaire à la gestion de la charge de travail.For example, if the size that is defined for TempDB is too small, part of the system-processing load may be taken up with auto growing TempDB to the size required to support the workload every time you restart the instance of SQL ServerSQL Server.

Si possible, utilisez l’initialisation instantanée de fichiers de base de données pour améliorer les performances des opérations de croissance de fichiers de données.If possible, use database instant file initialization to improve the performance of data file grow operations.

Pré-allouez l'espace de tous les fichiers de TempDB en définissant leur taille avec une valeur suffisamment élevée pour assumer la charge de travail habituelle de l'environnement.Preallocate space for all TempDB files by setting the file size to a value large enough to accommodate the typical workload in the environment. La préallocation permet de limiter le rythme de croissance de TempDB pour ne pas impacter les performances.Preallocation prevents TempDB from expanding too frequently, which affects performance. La base de données TempDB doit être définie de façon à autoriser la croissance automatique, mais celle-ci doit être utilisée pour augmenter l'espace disque en cas d'exceptions non prévues.The TempDB database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions.

Les fichiers de données doivent être de taille égale dans chaque groupe de fichiers, car SQL ServerSQL Server utilise un algorithme de remplissage proportionnel qui privilégie les allocations dans les fichiers ayant davantage d’espace libre.Data files should be of equal size within each filegroup, as SQL ServerSQL Server uses a proportional-fill algorithm that favors allocations in files with more free space. Le fait de diviser TempDB en plusieurs fichiers de données de taille égale procure un niveau élevé d’efficacité parallèle dans les opérations qui utilisent TempDB.Dividing TempDB into multiple data files of equal size provides a high degree of parallel efficiency in operations that use TempDB.

Définissez l'incrément de croissance de la taille du fichier avec une taille suffisante afin d'éviter que la valeur de croissance des fichiers de la base de données TempDB ne soit trop faible.Set the file growth increment to a reasonable size to avoid the TempDB database files from growing by too small a value. Si la croissance des fichiers est insuffisante par rapport à la quantité de données à écrire dans la base de données TempDB, TempDB risque de devoir se développer en permanence, ce qui impacte les performances.If the file growth is too small, compared to the amount of data that is being written to TempDB, TempDB may have to constantly expand and affect performance.

Pour vérifier les paramètres actuels de croissance et de taille de TempDB, utilisez la requête suivante :To check current TempDB size and growth parameters, use the following query:

 SELECT name AS FileName,
    size*1.0/128 AS FileSizeInMB,
    CASE max_size
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file grows to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' =
        CASE
            WHEN growth = 0 THEN 'Size is fixed.'
            WHEN growth > 0 AND is_percent_growth = 0
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;
GO

Placez la base de données TempDB sur un sous-système d'E/S rapide.Put the TempDB database on a fast I/O subsystem. Si plusieurs disques sont directement attachés, utilisez l'agrégation de disques.Use disk striping if there are many directly attached disks. Il n’est pas obligatoire que les fichiers ou les groupes de fichiers de données TempDB soient sur des disques ou des piles de disques différents, sauf si vous observez également des goulots d’étranglement d’E/S.Individual or groups of TempDB data files do not necessarily need to be on different disks or spindles unless you are also encountering I/O bottlenecks.

Placez la base de données TempDB sur des disques différents de ceux employés par les bases de données utilisateur.Put the TempDB database on disks that differ from those that are used by user databases.

Amélioration des performances dans TempDB pour SQL ServerPerformance improvements in TempDB for SQL Server

À compter de SQL Server 2016 (13.x)SQL Server 2016 (13.x), les performances de TempDB sont optimisées comme suit :Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), TempDB performance is further optimized in the following ways:

  • Les tables temporaires et les variables de table sont mises en cache.Temporary tables and table variables are cached. La mise en cache permet aux opérations de création et de suppression des objets temporaires de s'exécuter très rapidement et réduit la contention d'allocation des pages.Caching allows operations that drop and create the temporary objects to execute very quickly and reduces page allocation contention.
  • Le protocole de verrouillage des pages d’allocation a été amélioré pour réduire le nombre de verrous UP (update) utilisés.Allocation page latching protocol is improved to reduce the number of UP (update) latches that are used.
  • La surcharge d’enregistrement pour TempDB a été réduite pour consommer moins de bande passante d’E/S disque sur le fichier journal TempDB.Logging overhead for TempDB is reduced to reduce disk I/O bandwidth consumption on the TempDB log file.
  • Le programme d’installation ajoute plusieurs fichiers de données TempDB lors de l’installation d’une nouvelle instance.Setup adds multiple TempDB data files during a new instance installation. Cette tâche peut être réalisée par le biais du nouveau contrôle d’entrée de l’interface utilisateur dans la section Configuration du moteur de base de données et d’un paramètre de ligne de commande /SQLTEMPDBFILECOUNT.This task can be accomplished with the new UI input control on the Database Engine Configuration section and a command-line parameter /SQLTEMPDBFILECOUNT. Par défaut, le programme d’installation ajoute huit fichiers de données TempDB ou autant de fichiers de données TempDB que de processeurs logiques, la valeur la plus petite étant retenue.By default, setup adds as many TempDB data files as the logical processor count or eight, whichever is lower.
  • S’il y a plusieurs fichiers de données TempDB, tous les fichiers continuent de croître automatiquement de la même manière et en même temps, sur la base des paramètres de croissance définis.When there are multiple TempDB data files, all files autogrow at same time and by the same amount depending on growth settings. L’indicateur de trace 1117 n’est plus nécessaire.Trace flag 1117 is no longer required.
  • Toutes les allocations dans TempDB utilisent des extensions uniformes.All allocations in TempDB use uniform extents. L’indicateur de trace 1118 n’est plus nécessaire.Trace flag 1118 is no longer required.
  • Pour le groupe de fichiers primaire, la propriété AUTOGROW_ALL_FILES est activée et la propriété ne peut pas être modifiée.For the primary filegroup, the AUTOGROW_ALL_FILES property is turned on and the property cannot be modified.

Pour plus d’informations sur les améliorations des performances dans TempDB, consultez l’article de blog suivant :For more information on performance improvements in TempDB, see the following blog article:

TEMPDB - Files and Trace Flags and Updates, Oh My!TEMPDB - Files and Trace Flags and Updates, Oh My!

Métadonnées tempdb à mémoire optimiséeMemory-Optimized TempDB Metadata

La contention de métadonnées TempDB a toujours été un goulot d’étranglement pour la scalabilité de nombreuses charges de travail s’exécutant sur SQL ServerSQL Server.TempDB metadata contention has historically been a bottleneck to scalability for many workloads running on SQL ServerSQL Server. SQL Server 2019 (15.x)SQL Server 2019 (15.x) introduit dans la famille de fonctionnalités Base de données en mémoire une nouvelle fonctionnalité, les métadonnées TempDB à mémoire optimisée, qui supprime efficacement ce goulot d’étranglement et déverrouille un nouveau niveau de scalabilité pour les charges de travail de base de données TempDB lourdes.introduces a new feature that is part of the In-Memory Database feature family, memory-optimized TempDB metadata, which effectively removes this bottleneck and unlocks a new level of scalability for TempDB-heavy workloads. Dans SQL Server 2019 (15.x)SQL Server 2019 (15.x), les tables système impliquées dans la gestion des métadonnées de table temporaire peuvent être déplacées dans des tables à mémoire optimisée non durables dépourvues de verrous.In SQL Server 2019 (15.x)SQL Server 2019 (15.x), the system tables involved in managing temporary table metadata can be moved into latch-free non-durable memory-optimized tables. Pour pouvoir bénéficier de cette nouvelle fonctionnalité, utilisez le script suivant :In order to opt-in to this new feature, use the following script:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON 

Cette modification de la configuration nécessite un redémarrage du service.This configuration change requires a restart of the service to take effect.

Certaines limitations de cette implémentation méritent votre attention :There are some limitations with this implementation that are important to note:

  1. L’activation et la désactivation de la fonctionnalité ne sont pas dynamiques.Toggling the feature on and off is not dynamic. En raison des modifications intrinsèques qui doivent être apportées à la structure de TempDB, un redémarrage est nécessaire pour activer ou désactiver la fonctionnalité.Because of the intrinsic changes that need to be made to the structure of TempDB, a restart is required to either enable or disable the feature.

  2. Une transaction ne peut pas accéder aux tables à mémoire optimisée dans plus d’une base de données.A single transaction may not access memory-optimized tables in more than one database. Cela signifie que toute transaction qui implique une table à mémoire optimisée dans une base de données utilisateur ne peut pas parallèlement accéder à des vues système tempdb.This means that any transactions that involve a memory-optimized table in a user database will not be able to access TempDB system views in the same transaction. Si vous tentez d’accéder à des vues système tempdb dans la même transaction qu’une table à mémoire optimisée dans une base de données utilisateur, vous recevez l’erreur suivante :If you attempt to access TempDB system views in the same transaction as a memory-optimized table in a user database, you will receive the following error:

    A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.
    

    Exemple :Example:

    BEGIN TRAN
    SELECT *
    FROM tempdb.sys.tables  -----> Creates a user In-Memory OLTP Transaction on Tempdb
    INSERT INTO <user database>.<schema>.<mem-optimized table>
    VALUES (1)  ----> Attempts to create user In-Memory OLTP transaction but will fail
    COMMIT TRAN
    
  3. Les requêtes sur les tables à mémoire optimisée ne prennent pas en charge les indicateurs de verrouillage et d’isolation ; les requêtes sur les vues de catalogue tempdb à mémoire optimisée ne respectent donc pas les indicateurs de verrouillage et d’isolation.Queries against memory-optimized tables do not support locking and isolation hints, so queries against memory-optimized TempDB catalog views will not honor locking and isolation hints. Comme avec les autres vues de catalogue système dans SQL ServerSQL Server, toutes les transactions sur des vues système sont effectuées au niveau de l’isolation READ COMMITTED (ou dans ce cas READ COMMITTED SNAPSHOT).As with other system catalog views in SQL ServerSQL Server, all transactions against system views will be in READ COMMITTED (or in this case READ COMMITTED SNAPSHOT) isolation.

  4. Les index columnstore ne peuvent pas être créés sur les tables temporaires quand les métadonnées tempdb à mémoire optimisée sont activées.Columnstore indexes cannot be created on temporary tables when Memory-Optimized TempDB Metadata is enabled.

  5. En raison de la limitation sur les index columnstore, l’utilisation de la procédure stockée système sp_estimate_data_compression_savings avec le paramètre de compression de données COLUMNSTORE ou COLUMNSTORE_ARCHIVE n’est pas prise en charge lorsque les métadonnées tempdb à mémoire optimisée sont activées.Due to the limitation on columnstore indexes, use of the sp_estimate_data_compression_savings system stored procedure with the COLUMNSTORE or COLUMNSTORE_ARCHIVE data compression parameter is not supported when Memory-Optimized TempDB Metadata is enabled.

Notes

Ces limitations s’appliquent uniquement quand vous référencez des vues système TempDB ; si vous le souhaitez, vous pouvez créer une table temporaire dans la même transaction quand vous accédez à une table à mémoire optimisée dans une base de données utilisateur.These limitations only apply when referencing TempDB system views, you will be able to create a temp table in the same transaction as you access a memory-optimized table in a user database if desired.

Vous pouvez vérifier si TempDB est à mémoire optimisée à l’aide de la commande T-SQL suivante :You can verify whether or not TempDB is memory-optimized by using the following T-SQL command:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized')

Si le serveur ne parvient pas à démarrer pour une raison quelconque après l’activation des métadonnées tempDB à mémoire optimisée, vous pouvez ignorer la fonctionnalité en démarrant SQL Server avec la configuration minimale en utilisant l’option de démarrage -f.If the server fails to start for any reason after enabling Memory-Optimized TempDB Metadata, you can bypass the feature by starting the SQL Server with minimal configuration using the -f startup option. Ceci vous permet de désactiver la fonctionnalité, puis de redémarrer SQL Server en mode normal.This will enable you to disable the feature and then restart SQL Server in normal mode.

Planification de la capacité de TempDB dans SQL ServerCapacity Planning for TempDB in SQL Server

La détermination de la taille appropriée pour TempDB dans un environnement de production SQL ServerSQL Server dépend de nombreux facteurs.Determining the appropriate size for TempDB in a SQL ServerSQL Server production environment depends on many factors. Comme décrit plus haut dans cet article, ces facteurs incluent la charge de travail existante et les fonctionnalités SQL ServerSQL Server utilisées.As described previously in this article, these factors include the existing workload and the SQL ServerSQL Server features that are used. Nous vous recommandons d’analyser la charge de travail existante en effectuant les tâches suivantes dans un environnement de test SQL Server :We recommend that you analyze the existing workload by performing the following tasks in a SQL Server test environment:

  • Activez la croissance automatique de TempDB.Set autogrow on for TempDB.
  • Exécutez les requêtes individuelles ou les fichiers de trace de la charge de travail, et surveillez l’utilisation de l’espace dans TempDB.Execute individual queries or workload trace files and monitor TempDB space use.
  • Exécutez les opérations de maintenance des index, comme leur reconstruction et la surveillance de l’espace dans TempDB.Execute index maintenance operations, such as rebuilding indexes and monitor TempDB space.
  • Retenez les valeurs d’utilisation de l’espace des étapes précédentes pour prédire l’utilisation de la charge de travail totale, ajustez cette valeur en fonction de l’activité simultanée prévue, et définissez la taille de TempDB en conséquence.Use the space-use values from the previous steps to predict your total workload usage; adjust this value for projected concurrent activity, and then set the size of TempDB accordingly.

Mode de surveillance de l’utilisation de TempDBHow to Monitor TempDB use

Un espace disque insuffisant dans TempDB peut générer des perturbations significatives dans l’environnement de production SQL ServerSQL Server et empêcher les applications en cours d’exécution de terminer leurs opérations.Running out of disk space in TempDB can cause significant disruptions in the SQL ServerSQL Server production environment and can prevent applications that are running from completing operations. Vous pouvez utiliser la vue de gestion dynamique sys.dm_db_file_space_usage pour surveiller l’espace disque utilisé dans les fichiers TempDB :You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used in the TempDB files:

 -- Determining the Amount of Free Space in TempDB
SELECT SUM(unallocated_extent_page_count) AS [free pages],
  (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

-- Determining the Amount Space Used by the Version Store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
  (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;

-- Determining the Amount of Space Used by Internal Objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
  (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;

-- Determining the Amount of Space Used by User Objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
  (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;

En outre, pour surveiller l’activité d’allocation et de désallocation de pages dans TempDB au niveau de la session ou de la tâche, vous pouvez utiliser les vues de gestion dynamique sys.dm_db_session_space_usage et sys.dm_db_task_space_usage.Additionally, to monitor the page allocation or deallocation activity in TempDB at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. Ces vues permettent d’identifier les requêtes, les tables temporaires et les variables de table qui utilisent un espace disque volumineux dans TempDB.These views can be used to identify large queries, temporary tables, or table variables that are using lots of TempDB disk space. Il existe également plusieurs compteurs permettant de surveiller l’espace libre disponible dans TempDB, ainsi que les ressources qui utilisent TempDB.There are also several counters that can be used to monitor the free space that is available in TempDB and also the resources that are using TempDB. Pour plus d'informations, consultez la section suivante.For more information, see the next section.

-- Obtaining the space consumed by internal objects in all currently running tasks in each session
SELECT session_id,
  SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
  SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;

-- Obtaining the space consumed by internal objects in the current session for both running and completed tasks
SELECT R2.session_id,
  R1.internal_objects_alloc_page_count
  + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count
  + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id
GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count;;

Option SORT_IN_TEMPDB pour les index SORT_IN_TEMPDB Option For Indexes
Bases de données système System Databases
sys.databases sys.databases
sys.master_files sys.master_files
Déplacer des fichiers de bases de donnéesMove Database Files