Database tempdbTempDB Database

SI APPLICA A: sìSQL Server sìDatabase SQL di Azure noAzure SQL Data Warehouse noParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Il database di sistema TempDB è una risorsa globale disponibile per tutti gli utenti connessi all'istanza di SQL ServerSQL Server o al database SQL.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. Tempdb viene usato per contenere:Tempdb is used to hold:

  • Oggetti utente temporanei creati in modo esplicito, ad esempio tabelle e indici temporanei globali o locali, stored procedure temporanee, variabili di tabella, tabelle restituite in funzioni con valori di tabella o cursori.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.

  • Oggetti interni creati dal motore di database.Internal objects that are created by the database engine. tra cui:These include:

    • Tabelle di lavoro in cui archiviare i risultati intermedi di operazioni di spooling e di ordinamento e cursori, nonché in cui archiviare LOB (Large Object) temporanei.Work tables to store intermediate results for spools, cursors, sorts, and temporary large object (LOB) storage.
    • File di lavoro per operazioni hash join o hash aggregate.Work files for hash join or hash aggregate operations.
    • Risultati intermedi dell'ordinamento per operazioni quali la creazione o la ricompilazione di indici (se SORT_IN_TEMPDB è specificato) o per alcune query GROUP BY, ORDER BY o 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.

    Nota

    Ogni oggetto interno usa un minimo di nove pagine: una pagina IAM e un extent di otto pagine.Each internal object uses a minimum of nine pages; an IAM page and an eight-page extent. Per altre informazioni sulle pagine e sugli extent, vedere Pagine ed extent.For more information about pages and extents, see Pages and Extents.

    Importante

    I database singoli e i pool elastici di database SQL di Azure supportano tabelle temporanee globali e stored procedure temporanee globali archiviate in TempDB e con ambito a livello di database.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. Le tabelle temporanee globali e le stored procedure temporanee globali vengono condivise per le sessioni di tutti gli utenti all'interno dello stesso database SQL di Azure.Global temporary tables and global temporary stored procedures are shared for all users' sessions within the same Azure SQL database. Le sessioni utente di altri database SQL di Azure non possono accedere alle tabelle temporanee globali.User sessions from other Azure SQL databases cannot access global temporary tables. Per altre informazioni, vedere Tabelle temporanee globali con ambito database (database SQL di Azure).For more information, see Database scoped global temporary tables (Azure SQL Database). Istanza gestita di Azure SQL Database) supporta gli stessi oggetti temporanei di SQL Server.Azure SQL Database Managed Instance) supports the same temporary objects as does SQL Server. Per i database singoli e i pool elastici di database SQL di Azure si applicano solo il database master e il database TempDB.For Azure SQL Database single databases and elastic pools, only master database and TempDB database apply. Per altre informazioni, vedere Informazioni sul server di database SQL di Azure.For more information, see What is an Azure SQL Database server. Per una descrizione di TempDB nel contesto di database singoli e pool elastici di database SQL di Azure, vedere Database TempDB nel database SQL.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. Per Istanza gestita di database SQL di Azure si applicano tutti i database di sistema.For Azure SQL Database Managed Instance, all system databases apply.

  • Archivi delle versioni, raccolte di pagine di dati che contengono le righe di dati usate dalle caratteristiche che supportano il controllo delle versioni delle righe.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. Vengono utilizzati due archivi delle versioni: uno comune e uno per la compilazione di indici online.There are two version stores: a common version store and an online-index-build version store. Gli archivi delle versioni contengono:The version stores contain:

    • Versioni di riga generate dalle transazioni di modifica dei dati in un database in cui viene usato il Read committed tramite isolamento del controllo delle versioni delle righe o transazioni di isolamento dello snapshot.Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
    • Versioni di riga generate dalle transazioni di modifica dei dati per le caratteristiche, ad esempio le operazioni sugli indici online, la caratteristica MARS (Multiple Active Result Set) e i trigger 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.

Le operazioni all'interno di TempDB sono a registrazione minima in modo che sia possibile eseguire il rollback delle transazioni.Operations within TempDB are minimally logged so that transactions can be rolled back. TempDB viene ricreato ogni volta che SQL ServerSQL Server viene avviato in modo che il sistema inizi sempre con una copia pulita del database.TempDB is re-created every time SQL ServerSQL Server is started so that the system always starts with a clean copy of the database. Poiché le tabelle e le stored procedure temporanee vengono eliminate automaticamente al momento della disconnessione e poiché al momento della chiusura del sistema non vi sono connessioni attive,Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. nessuna parte del database TempDB viene salvata per le sessioni successive di SQL ServerSQL Server.Therefore, there is never anything in TempDB to be saved from one session of SQL ServerSQL Server to another. Le operazioni di backup e ripristino non sono consentite nel database TempDB.Backup and restore operations are not allowed on TempDB.

Commenti e suggerimenti: se si trovano informazioni obsolete o non corrette in questo articolo, ad esempio in un passaggio di una procedura o in un esempio di codice, è possibile comunicarlo a Microsoft.We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. È possibile fare clic sul pulsante Questa pagina nella sezione Commenti nella parte inferiore della pagina.You can click the This page button in the Feedback section at the bottom of this page. Ogni commento o suggerimento su SQL viene letto, in genere il giorno successivo.We read every item of feedback about SQL, typically the next day. Grazie.Thanks.

Proprietà fisiche di TempDB in SQL ServerPhysical Properties of TempDB in SQL Server

La tabella seguente elenca i valori di configurazione iniziali di file di dati e di log di TempDB in SQL Server, basati sulle impostazioni predefinite per il database modello.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. Le dimensioni di questi file possono variare leggermente a seconda dell'edizione di SQL ServerSQL Server.The sizes of these files may vary slightly for different editions of SQL ServerSQL Server.

FileFile Nome logicoLogical name Nome fisicoPhysical name Dimensioni inizialiInitial size Aumento di dimensioni del fileFile growth
Dati primariPrimary data tempdevtempdev tempdb.mdftempdb.mdf 8 megabyte8 megabytes Aumento automatico di 64 MB fino a quando il disco risulta pienoAutogrow by 64 MB until the disk is full
File di dati secondari*Secondary data files* temp#temp# tempdb_mssql_#.ndftempdb_mssql_#.ndf 8 megabyte8 megabytes Aumento automatico di 64 MB fino a quando il disco risulta pienoAutogrow by 64 MB until the disk is full
File di logLog templogtemplog templog.ldftemplog.ldf 8 megabyte8 megabytes Aumento automatico di 64 megabyte fino a un massimo di 2 terabyteAutogrow by 64 megabytes to a maximum of 2 terabytes

* Il numero di file dipende dal numero di processori (logici) del computer.* The number of files depends on the number of (logical) processors on the machine. In generale, se il numero di processori logici è minore o uguale a otto, usare un numero di file di dati pari al numero dei processori logici.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. Se il numero di processori logici è maggiore di otto, usare otto file di dati e, se la contesa persiste, aumentare il numero di file di dati per multipli di 4 fino a quando la contesa si riduce a livelli accettabili o modificare il carico di lavoro o il codice.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.

Nota

Il valore predefinito per il numero di file di dati si basa sulle linee guida generali in KB 2154845.The default value for the number of data files is based on the general guidelines in KB 2154845.

Spostamento dei file di dati e di log di TempDB in SQL ServerMoving the TempDB data and log files in SQL Server

Per spostare i file di dati e di log di TempDB, vedere Spostare i database di sistema.To move the TempDB data and log files, see Move System Databases.

Opzioni di database per TempDB in SQL ServerDatabase Options for TempDB in SQL Server

Nella tabella seguente sono elencati i valori predefiniti delle singole opzioni di database in TempDB e viene indicato se l'opzione è modificabile.The following table lists the default value for each database option in the TempDB database and whether the option can be modified. Per visualizzare le impostazioni correnti di queste opzioni, usare la vista del catalogo sys.databases .To view the current settings for these options, use the sys.databases catalog view.

Opzione di databaseDatabase option Valore predefinitoDefault value ModificabileCan be modified
ALLOW_SNAPSHOT_ISOLATIONALLOW_SNAPSHOT_ISOLATION OFFOFF Yes
ANSI_NULL_DEFAULTANSI_NULL_DEFAULT OFFOFF Yes
ANSI_NULLSANSI_NULLS OFFOFF Yes
ANSI_PADDINGANSI_PADDING OFFOFF Yes
ANSI_WARNINGSANSI_WARNINGS OFFOFF Yes
ARITHABORTARITHABORT OFFOFF Yes
AUTO_CLOSEAUTO_CLOSE OFFOFF noNo
AUTO_CREATE_STATISTICSAUTO_CREATE_STATISTICS ONON Yes
AUTO_SHRINKAUTO_SHRINK OFFOFF noNo
AUTO_UPDATE_STATISTICSAUTO_UPDATE_STATISTICS ONON Yes
AUTO_UPDATE_STATISTICS_ASYNCAUTO_UPDATE_STATISTICS_ASYNC OFFOFF Yes
CHANGE_TRACKINGCHANGE_TRACKING OFFOFF noNo
CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL OFFOFF Yes
CURSOR_CLOSE_ON_COMMITCURSOR_CLOSE_ON_COMMIT OFFOFF Yes
CURSOR_DEFAULTCURSOR_DEFAULT GLOBALGLOBAL Yes
Opzioni relative alla disponibilità del databaseDatabase Availability Options ONLINEONLINE

MULTI_USERMULTI_USER

READ_WRITEREAD_WRITE
noNo

noNo

noNo
DATE_CORRELATION_OPTIMIZATIONDATE_CORRELATION_OPTIMIZATION OFFOFF Yes
DB_CHAININGDB_CHAINING ONON noNo
ENCRYPTIONENCRYPTION OFFOFF noNo
MIXED_PAGE_ALLOCATIONMIXED_PAGE_ALLOCATION OFFOFF noNo
NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT OFFOFF Yes
PAGE_VERIFYPAGE_VERIFY CHECKSUM per nuove installazioni di SQL ServerSQL Server.CHECKSUM for new installations of SQL ServerSQL Server.

NONE per aggiornamenti di SQL ServerSQL Server.NONE for upgrades of SQL ServerSQL Server.
Yes
PARAMETERIZATIONPARAMETERIZATION SIMPLESIMPLE Yes
QUOTED_IDENTIFIERQUOTED_IDENTIFIER OFFOFF Yes
READ_COMMITTED_SNAPSHOTREAD_COMMITTED_SNAPSHOT OFFOFF noNo
RECOVERYRECOVERY SIMPLESIMPLE noNo
RECURSIVE_TRIGGERSRECURSIVE_TRIGGERS OFFOFF Yes
Opzioni relative a Service BrokerService Broker Options ENABLE_BROKERENABLE_BROKER Yes
TRUSTWORTHYTRUSTWORTHY OFFOFF noNo

Per una descrizione di queste opzioni di database, vedere Opzioni ALTER DATABASE SET (Transact-SQL).For a description of these database options, see ALTER DATABASE SET Options (Transact-SQL).

Database TempDB nel database SQLTempDB database in SQL Database

Dimensioni di TempDB per i livelli di servizio basati su DTUTempDB sizes for DTU-based service tiers

SLOSLO Dimensioni massime file di dati TempDB (GB)Max TempDB Data File Size (GBs) N. di file di dati TempDB# of TempDB data files Dimensioni massime dei dati TempDB (GB)Max TempDB data size (GB)
StandardBasic 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
Pool elastici Premium (tutte le configurazioni DTU)Premium Elastic Pools (all DTU configurations) 1313 1212 156156
Pool elastici standard (S0-S2)Standard Elastic Pools (S0-S2) 1313 1212 156156
Pool elastici standard (S3 e versioni successive)Standard Elastic Pools (S3 and above) 3232 1212 384384
Pool elastici Basic (tutte le configurazioni DTU)Basic Elastic Pools (all DTU configurations) 1313 1212 156156

Dimensioni di TempDB per i livelli di servizio basati su vCoreTempDB sizes for vCore-based service tiers

Vedere Limiti delle risorse basati su vCoreSee vCore-based resource limits

RestrictionsRestrictions

Di seguito sono riportate le operazioni che non è possibile eseguire nel database TempDB:The following operations cannot be performed on the TempDB database:

  • Aggiunta di filegroupAdding filegroups
  • Backup o ripristino del databaseBacking up or restoring the database
  • Modifica delle regole di confronto.Changing collation. Le regole di confronto predefinite corrispondono a quelle del serverThe default collation is the server collation
  • Modifica del proprietario del database.Changing the database owner. TempDB è di proprietà di saTempDB is owned by sa
  • Creazione di uno snapshot del databaseCreating a database snapshot
  • Eliminazione del databaseDropping the database
  • Eliminazione dell'utente guest dal databaseDropping the guest user from the database
  • Abilitazione dell'acquisizione dei dati delle modificheEnabling change data capture
  • Partecipazione al mirroring del databaseParticipating in database mirroring
  • Rimozione del filegroup primario, del file di dati primario o del file di logRemoving the primary filegroup, primary data file, or log file
  • Ridenominazione del filegroup primario o del databaseRenaming the database or primary filegroup
  • Esecuzione di DBCC CHECKALLOCRunning DBCC CHECKALLOC
  • Esecuzione di DBCC CHECKCATALOGRunning DBCC CHECKCATALOG
  • Impostazione del database su OFFLINESetting the database to OFFLINE
  • Impostazione del database o del filegroup primario su READ_ONLYSetting the database or primary filegroup to READ_ONLY

AutorizzazioniPermissions

Qualsiasi utente può creare oggetti temporanei in TempDB.Any user can create temporary objects in TempDB. Gli utenti possono accedere solo ai propri oggetti, a meno che non ottengano ulteriori autorizzazioni.Users can only access their own objects, unless they receive additional permissions. È possibile revocare l'autorizzazione per la connessione a TempDB per impedire a un utente di usarlo, tuttavia questa scelta non è consigliata poiché alcune operazioni di routine richiedono l'uso di 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.

Ottimizzazione delle prestazioni di TempDB in SQL ServerOptimizing TempDB performance in SQL Server

Le dimensioni e la posizione fisica del database TempDB possono influire sulle prestazioni di un sistema.The size and physical placement of the TempDB database can affect the performance of a system. Se, ad esempio, le dimensioni definite per TempDB sono eccessivamente ridotte, il carico di elaborazione del sistema può essere in parte gestito aumentando automaticamente le dimensioni di TempDB fino a raggiungere quelle necessarie per supportare il carico di lavoro a ogni riavvio dell'istanza di SQL ServerSQL Server.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.

Se possibile, usare l'inizializzazione immediata dei file di database per migliorare le prestazioni delle operazioni di aumento delle dimensioni.If possible, use database instant file initialization to improve the performance of data file grow operations.

Preallocare lo spazio per tutti i file di TempDB impostando le relative dimensioni su un valore adeguato per il carico di lavoro tipico dell'ambiente.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 preallocazione evita che il database TempDB si espanda con una frequenza eccessiva deteriorando le prestazioni.Preallocation prevents TempDB from expanding too frequently, which affects performance. È opportuno impostare il database TempDB per l'aumento automatico delle dimensioni. Questa funzionalità deve tuttavia essere usata per aumentare lo spazio su disco per le eccezioni non pianificate.The TempDB database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions.

All'interno di ogni filegroup i file di dati devono avere le stesse dimensioni, perché SQL ServerSQL Server usa un algoritmo di riempimento proporzionale che favorisce le allocazioni all'interno di file con maggiore spazio disponibile.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. La suddivisione di TempDB in più file di dati di dimensioni uguali garantisce un livello elevato di efficienza parallela nelle operazioni che usano TempDB.Dividing TempDB into multiple data files of equal size provides a high degree of parallel efficiency in operations that use TempDB.

Impostare un valore di aumento delle dimensioni del file tale da evitare aumenti troppo ridotti delle dimensioni dei file del database TempDB.Set the file growth increment to a reasonable size to avoid the TempDB database files from growing by too small a value. Se l'aumento delle dimensioni dei file è troppo ridotto in confronto alla quantità di dati scritti nel database TempDB, quest'ultimo potrebbe espandersi costantemente e influire sulle prestazioni.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.

Per controllare i parametri di dimensione e crescita correnti di TempDB, usare la query seguente: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

Posizionare il database TempDB in un sottosistema I/O veloce.Put the TempDB database on a fast I/O subsystem. In presenza di molti dischi collegati direttamente, utilizzare lo striping del disco.Use disk striping if there are many directly attached disks. File singoli o gruppi di file di dati di TempDB non devono necessariamente trovarsi in dischi o spindle diversi, a meno che non si verifichino anche colli di bottiglia di I/O.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.

Posizionare il database TempDB in dischi diversi da quelli usati dai database utente.Put the TempDB database on disks that differ from those that are used by user databases.

Miglioramenti delle prestazioni in TempDB per SQL ServerPerformance improvements in TempDB for SQL Server

A partire da SQL Server 2016 (13.x)SQL Server 2016 (13.x), le prestazioni di TempDB vengono ulteriormente ottimizzate nei modi seguenti:Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), TempDB performance is further optimized in the following ways:

  • Le tabelle temporanee e le variabili di tabella vengono memorizzate nella cache.Temporary tables and table variables are cached. La memorizzazione nella cache consente di eseguire molto rapidamente le operazioni di eliminazione e creazione degli oggetti temporanei e di ridurre i problemi di contesa nell'allocazione delle pagine.Caching allows operations that drop and create the temporary objects to execute very quickly and reduces page allocation contention.
  • Il protocollo di latch delle pagine di allocazione è stato migliorato per ridurre il numero di latch di aggiornamento (UP) usati.Allocation page latching protocol is improved to reduce the number of UP (update) latches that are used.
  • L'overhead di registrazione per TempDB è stato ridotto per diminuire l'uso della larghezza di banda per le operazioni di I/O del disco nel file di log di TempDB.Logging overhead for TempDB is reduced to reduce disk I/O bandwidth consumption on the TempDB log file.
  • Durante l'installazione di una nuova istanza vengono aggiunti più file di dati di TempDB.Setup adds multiple TempDB data files during a new instance installation. Questa attività può essere eseguita con il nuovo controllo input dell'interfaccia utente nella sezione Configurazione del motore di database e con un parametro della riga di comando /SQLTEMPDBFILECOUNT.This task can be accomplished with the new UI input control on the Database Engine Configuration section and a command-line parameter /SQLTEMPDBFILECOUNT. Per impostazione predefinita, il programma di installazione aggiunge un numero di file di dati TempDB pari al numero di processori logici oppure a otto, a seconda di quale sia il valore inferiore.By default, setup adds as many TempDB data files as the logical processor count or eight, whichever is lower.
  • Se ci sono più file di dati TempDB, le dimensioni di tutti i file aumentano contemporaneamente e della stessa quantità in base alle impostazioni di espansione specificate.When there are multiple TempDB data files, all files autogrow at same time and by the same amount depending on growth settings. Il flag di traccia 1117 non è più necessario.Trace flag 1117 is no longer required.
  • Tutte le allocazioni in TempDB usano extent uniformi.All allocations in TempDB use uniform extents. Il flag di traccia 1118 non è più necessario.Trace flag 1118 is no longer required.
  • Per il filegroup primario, la proprietà AUTOGROW_ALL_FILES è attivata e non può essere modificata.For the primary filegroup, the AUTOGROW_ALL_FILES property is turned on and the property cannot be modified.

Per altre informazioni sui miglioramenti delle prestazioni in TempDB, vedere l'articolo di blog seguente:For more information on performance improvements in TempDB, see the following blog article:

TEMPDB - Files and Trace Flags and Updates, Oh My! (TEMPDB - File, flag di traccia e aggiornamenti)TEMPDB - Files and Trace Flags and Updates, Oh My!

Metadati tempdb ottimizzati per la memoriaMemory-Optimized TempDB Metadata

La contesa tra metadati TempDB è tipicamente un collo di bottiglia per la scalabilità per molti carichi di lavoro in esecuzione su 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) introduce una nuova funzionalità della famiglia di funzionalità Database in memoria, i metadati TempDB ottimizzati per la memoria, che rimuove questo collo di bottiglia e sblocca un nuovo livello di scalabilità per i carichi di lavoro TempDB eccessivi.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. In SQL Server 2019 (15.x)SQL Server 2019 (15.x) le tabelle di sistema coinvolte nella gestione dei metadati delle tabelle temporanee possono essere spostate in tabelle ottimizzate per la memoria non durevoli senza latch.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. Per accettare questa nuova funzionalità, usare lo script seguente:In order to opt-in to this new feature, use the following script:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON 

Per rendere effettiva questa modifica della configurazione è necessario riavviare il servizio.This configuration change requires a restart of the service to take effect.

Questa implementazione presenta alcune limitazioni che è importante prendere in considerazione:There are some limitations with this implementation that are important to note:

  1. L'attivazione o la disattivazione della funzionalità non avviene in modo dinamico.Toggling the feature on and off is not dynamic. A causa delle modifiche intrinseche che devono essere apportate alla struttura del database TempDB, è necessario un riavvio per abilitare o disabilitare la funzionalità.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. È possibile che una singola transazione non possa accedere alle tabelle ottimizzate per la memoria in più di un database.A single transaction may not access memory-optimized tables in more than one database. Ciò significa che tutte le transazioni che interessano una tabella ottimizzata per la memoria in un database utente non saranno in grado di accedere alle viste di sistema tempdb nella stessa transazione.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. Se si tenta di accedere alle viste di sistema tempdb nella stessa transazione della tabella ottimizzata per la memoria in un database utente, verrà visualizzato l'errore seguente: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.
    

    Esempio: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. Poiché le query eseguite nelle tabelle ottimizzate per la memoria non supportano gli hint di blocco e isolamento, le query eseguite nelle viste di catalogo tempdb ottimizzate per la memoria non rispetteranno gli hint di blocco e isolamento.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. Come nelle altre viste del catalogo di sistema in SQL ServerSQL Server, tutte le transazioni eseguite nelle viste di sistema saranno nell'isolamento READ COMMITTED (o in questo caso 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. Non è possibile creare indici columnstore nelle tabelle temporanee quando sono abilitati i metadati tempdb ottimizzati per la memoria.Columnstore indexes cannot be created on temporary tables when Memory-Optimized TempDB Metadata is enabled.

  5. A causa della limitazione per gli indici columnstore, l'uso della stored procedure di sistema sp_estimate_data_compression_savings con il parametro di compressione dei dati COLUMNSTORE o COLUMNSTORE_ARCHIVE non è supportato quando i metadati tempdb ottimizzati per la memoria sono abilitati.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.

Nota

Queste limitazioni si applicano solo quando si fa riferimento a viste di sistema tempdb. Se si desidera, sarà possibile creare una tabella temporanea nella stessa transazione non appena si accede a una tabella con ottimizzazione per la memoria in un database utente.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.

È possibile verificare se tempdb è ottimizzato per la memoria usando il comando T-SQL seguente:You can verify whether or not TempDB is memory-optimized by using the following T-SQL command:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized')

Se il server non viene avviato per qualsiasi motivo dopo aver abilitato i metadati TempDB ottimizzati per la memoria, è possibile ignorare la funzionalità avviando SQL Server con la configurazione minima usando l'opzione di avvio -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. In questo modo sarà possibile disabilitare la funzionalità e quindi riavviare SQL Server in modalità normale.This will enable you to disable the feature and then restart SQL Server in normal mode.

Pianificazione delle capacità per TempDB in SQL ServerCapacity Planning for TempDB in SQL Server

La determinazione delle dimensioni appropriate per TempDB in un ambiente di produzione SQL ServerSQL Server dipende da molti fattori.Determining the appropriate size for TempDB in a SQL ServerSQL Server production environment depends on many factors. Come precedentemente descritto in questo articolo, questi fattori includono il carico di lavoro esistente e le funzionalità di SQL ServerSQL Server usate.As described previously in this article, these factors include the existing workload and the SQL ServerSQL Server features that are used. È consigliabile analizzare il carico di lavoro esistente eseguendo le attività seguenti in un ambiente di test di SQL Server:We recommend that you analyze the existing workload by performing the following tasks in a SQL Server test environment:

  • Attivare l'aumento automatico delle dimensioni di TempDB.Set autogrow on for TempDB.
  • Eseguire singole query o file di traccia del carico di lavoro e monitorare l'uso dello spazio da parte di TempDB.Execute individual queries or workload trace files and monitor TempDB space use.
  • Eseguire operazioni di manutenzione degli indici, ad esempio la ricompilazione degli indici stessi, e monitorare lo spazio occupato da TempDB.Execute index maintenance operations, such as rebuilding indexes and monitor TempDB space.
  • Impiegare i valori di uso dello spazio ottenuti con i passaggi precedenti per prevedere l'utilizzo totale da parte del carico di lavoro. Regolare poi tali valori in funzione delle attività simultanee previste e quindi impostare di conseguenza le dimensioni di TempDB.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.

Come monitorare l'uso di TempDBHow to Monitor TempDB use

L'esaurimento dello spazio disponibile in TempDB può provocare interruzioni significative nell'ambiente di produzione di SQL ServerSQL Server e può impedire alle applicazioni in esecuzione di completare le operazioni in corso.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. Per eseguire il monitoraggio dello spazio su disco usato dai file di TempDB, è possibile usare la vista a gestione dinamica (DMV, Dynamic Management View) sys.dm_db_file_space_usage: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;

Per eseguire il monitoraggio dell'allocazione delle pagine o dell'attività di deallocazione in TempDB a livello di sessione o di attività, poi, è possibile usare le DMV sys.dm_db_session_space_usage e 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. Queste viste consentono di identificare le query, le tabelle temporanee o le variabili di tabella che usano una grande quantità di spazio su disco in TempDB.These views can be used to identify large queries, temporary tables, or table variables that are using lots of TempDB disk space. Sono anche disponibili diversi contatori che consentono di monitorare lo spazio libero disponibile in TempDB e le risorse che stanno usando 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. Per ulteriori informazioni, vedere la sezione successiva.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;;

Opzione SORT_IN_TEMPDB per gli indici SORT_IN_TEMPDB Option For Indexes
Database di sistema System Databases
sys.databases sys.databases
sys.master_files sys.master_files
Spostare file del databaseMove Database Files