banco de dados tempdb

Artigo
03/05/2025

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada SQL do Azure banco de dados SQL no Microsoft Fabric

Este artigo descreve o banco de dados do sistema tempdb, um recurso global disponível para todos os usuários conectados a uma instância do Mecanismo de Banco de Dados no SQL Server, Banco de Dados SQL do Azure ou Instância Gerenciada do SQL do Azure.

Visão geral

O banco de dados do sistema tempdb é um recurso global que contém:

Objetos de usuário que são criados explicitamente. Entre eles contam-se:
- Tabelas e índices temporários globais ou locais nessas tabelas
- Procedimentos armazenados temporários
- Variáveis da tabela
- Tabelas retornadas em funções com valor de tabela
- Cursores
Os objetos de usuário que podem ser criados em um banco de dados de usuário também podem ser criados em tempdb, no entanto, eles são criados sem uma garantia de durabilidade e são descartados quando a instância do Mecanismo de Banco de Dados é reiniciada.
Objetos internos que o mecanismo de banco de dados cria. Entre eles contam-se:
- Tabelas de trabalho para armazenar resultados intermediários para spools, cursores, classificações e armazenamento temporário de objetos grandes (LOB).
- Arquivos de trabalho para operações de junção de hash ou agregação de hash.
- Resultados de classificação intermediários para operações como a criação ou reconstrução de índices (se SORT_IN_TEMPDB for especificado) ou determinadas consultas GROUP BY, ORDER BYou UNION.
Cada objeto interno usa um mínimo de nove páginas: uma página do IAM e uma extensão de oito páginas. Para obter mais informações sobre páginas e extensões, consulte Páginas e extensões.
Version armazena, que são coleções de páginas de dados que contêm as linhas de dados que suportam versionamento de linha. Existem dois tipos: um repositório de versão comum e um repositório de versão de compilação de índice online. Os repositórios de versões contêm:
- Versões de linha geradas por transações de modificação de dados num banco de dados que utiliza transações de isolamento baseadas em controle de versão de linha como READ COMMITTED ou SNAPSHOT.
- Versões de linha geradas por transações de modificação de dados para recursos, como operações de índice online, MARS (Conjuntos de Resultados Ativos Múltiplos) e gatilhos AFTER.

As operações dentro tempdb são minimamente registradas. tempdb é recriado sempre que o Mecanismo de Banco de Dados é iniciado para que o sistema sempre comece com um banco de dados tempdb vazio. Os procedimentos armazenados temporários e as tabelas temporárias locais são descartados automaticamente quando a sessão que os criou se desconecta.

tempdb nunca tem nada para ser guardado de um período de atividade do Mecanismo de Banco de Dados para outro. As operações de backup e restauração não são permitidas no tempdb.

Propriedades físicas do tempdb no SQL Server

A tabela a seguir lista os valores de configuração inicial dos tempdb dados e arquivos de log no SQL Server. Os valores são baseados nos padrões para o banco de dados model. Os tamanhos desses arquivos podem variar ligeiramente para diferentes edições do SQL Server.

Ficheiro	Nome lógico	Nome físico	Tamanho inicial	Crescimento de ficheiros
Dados primários	`tempdev`	`tempdb.mdf`	8 megabytes	Crescimento automático de 64 MB até que o disco esteja cheio
Arquivos de dados secundários	`temp#`	`tempdb_mssql_#.ndf`	8 megabytes	Crescimento automático de 64 MB até que o disco esteja cheio
Registo	`templog`	`templog.ldf`	8 megabytes	Aumentar automaticamente em 64 megabytes até um máximo de 2 terabytes

Todos os tempdb arquivos de dados devem ter sempre o mesmo tamanho inicial e parâmetros de crescimento.

Número de arquivos de dados tempdb

Dependendo da versão do Mecanismo de Banco de Dados, sua configuração e a carga de trabalho, tempdb pode exigir vários arquivos de dados para mitigar a contenção de alocação.

O número total recomendado de arquivos de dados depende do número de processadores lógicos na máquina. Como orientação geral:

Se o número de processadores lógicos for menor ou igual a oito, use o mesmo número de arquivos de dados.
Se o número de processadores lógicos for maior que oito, use oito arquivos de dados.
Caso ainda seja observada a contenção de alocação de tempdb, aumente o número de arquivos de dados em múltiplos de quatro unidades até que a contenção diminua para níveis aceitáveis, ou faça alterações na carga de trabalho.

Para obter mais informações, consulte Recomendações para reduzir a contenção de alocação na base de dados tempdb do Microsoft SQL Server.

Para verificar o tamanho atual e os parâmetros de crescimento para tempdb, use o sys.database_files exibição de catálogo no tempdb.

Mover os dados tempdb e os arquivos de log no SQL Server

Para mover os dados tempdb e os ficheiros de log, consulte Mover bancos de dados do sistema.

Opções de banco de dados para tempdb no SQL Server

A tabela a seguir lista o valor padrão para cada opção de banco de dados no banco de dados tempdb e se a opção pode ser modificada. Para exibir as configurações atuais dessas opções, use a sys.databases vista de catálogo.

Opção de banco de dados	Valor padrão	Pode ser modificado
`ACCELERATED_DATABASE_RECOVERY`	`OFF`	Não
`ALLOW_SNAPSHOT_ISOLATION`	`OFF`	Sim
`ANSI_NULL_DEFAULT`	`OFF`	Sim
`ANSI_NULLS`	`OFF`	Sim
`ANSI_PADDING`	`OFF`	Sim
`ANSI_WARNINGS`	`OFF`	Sim
`ARITHABORT`	`OFF`	Sim
`AUTO_CLOSE`	`OFF`	Não
`AUTO_CREATE_STATISTICS`	`ON`	Sim
`AUTO_SHRINK`	`OFF`	Não
`AUTO_UPDATE_STATISTICS`	`ON`	Sim
`AUTO_UPDATE_STATISTICS_ASYNC`	`OFF`	Sim
`AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN)`	`OFF`	Não
`CHANGE_TRACKING`	`OFF`	Não
`COMPATIBILITY_LEVEL`	Depende da versão do Mecanismo de Banco de Dados. Para obter mais informações, consulte ALTER DATABASE (Transact-SQL) nível de compatibilidade.	Sim
`CONCAT_NULL_YIELDS_NULL`	`OFF`	Sim
`CONTAINMENT`	`NONE`	Não
`CURSOR_CLOSE_ON_COMMIT`	`OFF`	Sim
`CURSOR_DEFAULT`	`GLOBAL`	Sim
Estado do banco de dados	`ONLINE`	Não
Atualização da base de dados	`READ_WRITE`	Não
Acesso do usuário ao banco de dados	`MULTI_USER`	Não
`DATE_CORRELATION_OPTIMIZATION`	`OFF`	Sim
`DB_CHAINING`	`ON`	Não
`DELAYED_DURABILITY`	`DISABLED` Independentemente desta opção, a durabilidade atrasada está sempre ativada no `tempdb`.	Sim
`ENCRYPTION`	`OFF`	Não
`MIXED_PAGE_ALLOCATION`	`OFF`	Não
`NUMERIC_ROUNDABORT`	`OFF`	Sim
`PAGE_VERIFY`	`CHECKSUM` para novas instalações do SQL Server O valor de `PAGE_VERIFY` existente pode ser mantido quando uma instância do SQL Server é atualizada no local.	Sim
`PARAMETERIZATION`	`SIMPLE`	Sim
`QUOTED_IDENTIFIER`	`OFF`	Sim
`READ_COMMITTED_SNAPSHOT`	`OFF`	Não
`RECOVERY`	`SIMPLE`	Não
`RECURSIVE_TRIGGERS`	`OFF`	Sim
Corretor de Serviços	`ENABLE_BROKER`	Sim
`TARGET_RECOVERY_TIME`	60	Sim
`TEMPORAL_HISTORY_RETENTION`	`ON`	Sim
`TRUSTWORTHY`	`OFF`	Não

Para obter uma descrição dessas opções de banco de dados, consulte ALTER DATABASE SET Options (Transact-SQL).

tempdb no Banco de Dados SQL do Azure

No Banco de Dados SQL do Azure, alguns aspetos do comportamento e da configuração do tempdb são diferentes do SQL Server.

Para bancos de dados únicos, cada banco de dados em um servidor lógico tem seu próprio tempdb. Em um pool elástico, tempdb é um recurso compartilhado para todos os bancos de dados no mesmo pool, mas os objetos temporários criados por um banco de dados não são visíveis para outros bancos de dados no mesmo pool elástico.

Os objetos em tempdb, incluindo exibições de catálogo e exibições de gerenciamento dinâmico (DMVs), são acessíveis por meio de uma referência entre bancos de dados para o banco de dados tempdb. Por exemplo, pode consultar a vista sys.database_files.

SELECT file_id,
       type_desc,
       name,
       size,
       max_size,
       growth
FROM tempdb.sys.database_files;

As tabelas temporárias globais em do Banco de Dados SQL do Azure têm escopo de banco de dados. Para obter mais informações, consulte Tabelas temporárias globais com escopo de banco de dados no Banco de Dados SQL do Azure.

Para saber mais sobre tamanhos de tempdb no Banco de Dados SQL do Azure, consulte:

Modelo de compra vCore: bancos de dados únicos bancos de dados agrupados
Modelo de compra de DTU: bancos de dados únicos bancos de dados agrupados

Recuperação Acelerada de Banco de Dados está sempre habilitada para o tempdb no Azure SQL Database. Para obter mais informações, consulte Melhorando a confiabilidade do Banco de Dados SQL do Azure com recuperação acelerada de banco de dados em tempdb.

tempdb na instância gerenciada do SQL

Na Instância Gerenciada do SQL do Azure, alguns aspetos do comportamento tempdb e da configuração padrão são diferentes do SQL Server.

Você pode configurar o número de arquivos tempdb, seus incrementos de crescimento e seu tamanho máximo. Para obter mais informações sobre como definir configurações de tempdb na Instância Gerenciada SQL do Azure, consulte Definir configurações tempdb para a Instância Gerenciada SQL do Azure.

A Instância Gerenciada do SQL do Azure dá suporte a objetos temporários da mesma forma que o SQL Server, onde todas as tabelas temporárias globais e procedimentos armazenados temporários globais são acessíveis por todas as sessões de usuário dentro da mesma instância gerenciada pelo SQL.

Para saber mais sobre tamanhos de tempdb na Instância de SQL Gerida do Azure, consulte os limites de recursos.

tempdb no banco de dados SQL no contexto do Fabric

Para saber mais sobre tamanhos de tempdb no banco de dados SQL no Microsoft Fabric, reveja a secção sobre os limites de recursos em Comparação de Recursos: Banco de Dados SQL do Azure e banco de dados SQL no Microsoft Fabric.

Da mesma forma que Banco de Dados SQL do Azure, as tabelas temporárias globais no banco de dados SQL no Microsoft Fabric têm escopo de banco de dados. Para obter mais informações, consulte Tabelas temporárias globais com escopo de banco de dados no Banco de Dados SQL do Azure.

Restrições

As seguintes operações não podem ser executadas no banco de dados tempdb:

Adicionando grupos de arquivos.
Fazendo backup ou restaurando o banco de dados.
Alteração do agrupamento. O agrupamento padrão é o agrupamento do servidor.
Alterando o proprietário do banco de dados. tempdb é propriedade da sa.
Criando um instantâneo de banco de dados.
Descartando o banco de dados.
Eliminando o convidado , usuário, da base de dados.
Habilitar captura de dados de alteração.
Participação no espelhamento de banco de dados.
Remoção do grupo de arquivos primário, arquivo de dados primário ou arquivo de log.
Renomeando o banco de dados ou o grupo de arquivos primário.
Executando DBCC CHECKALLOC.
Executando DBCC CHECKCATALOG.
Definindo o banco de dados como OFFLINE.
Definindo o banco de dados ou grupo de arquivos primário como READ_ONLY.

Permissões

Qualquer usuário pode criar objetos temporários no tempdb.

Os usuários podem acessar apenas seus próprios objetos não temporários no tempdb, a menos que recebam permissões adicionais.

É possível revogar a permissão CONNECT em tempdb para impedir que um usuário ou função de banco de dados use tempdb. Isso não é recomendado porque muitas operações exigem o uso de tempdb.

Otimizar o desempenho do tempdb no SQL Server

O tamanho e o posicionamento físico de tempdb arquivos podem afetar o desempenho. Por exemplo, se o tamanho inicial de tempdb for muito pequeno, o tempo e os recursos podem ser consumidos para que tempdb cresça automaticamente até o tamanho necessário para suportar a carga de trabalho sempre que a instância do Mecanismo de Banco de Dados for reiniciada.

Se possível, use inicialização instantânea de ficheiros para melhorar o desempenho das operações de crescimento de ficheiros de dados.
- A partir do SQL Server 2022 (16.x), eventos de crescimento de arquivos de log de transações de até 64 MB também podem se beneficiar da inicialização instantânea de arquivos. Para obter mais informações, consulte Inicialização Instantânea de Arquivos e o Log de Transações.
Pré-aloque espaço para todos os arquivos tempdb definindo o tamanho do arquivo para um valor grande o suficiente para acomodar a carga de trabalho típica no ambiente. A pré-alocação impede que tempdb cresça automaticamente com muita frequência, o que pode afetar negativamente o desempenho.
Os arquivos no banco de dados tempdb devem ser configurados para crescimento automático para fornecer espaço durante eventos de crescimento não planejados.
Dividir tempdb em vários arquivos de dados de tamanho igual pode melhorar a eficiência das operações que usam tempdb.
- Para evitar o desequilíbrio de alocação de dados, os arquivos de dados devem ter o mesmo tamanho inicial e os mesmos parâmetros de crescimento, pois o Mecanismo de Banco de Dados usa um algoritmo de preenchimento proporcional que favorece as alocações em arquivos com mais espaço livre.
- Defina o incremento de crescimento do arquivo para um tamanho razoável, por exemplo, 64 MB, e torne o incremento de crescimento o mesmo para todos os arquivos de dados para evitar o desequilíbrio de crescimento.

Para verificar o tamanho atual e os parâmetros de crescimento para tempdb, use a seguinte consulta:

SELECT name AS file_name,
       type_desc AS file_type,
       size * 8.0 / 1024 AS size_mb,
       max_size * 8.0 / 1024 AS max_size_mb,
       CAST(IIF(max_size = 0, 0, 1) AS bit) AS is_autogrowth_enabled,
       CASE WHEN growth = 0 THEN growth
            WHEN growth > 0 AND is_percent_growth = 0 THEN growth * 8.0 / 1024
            WHEN growth > 0 AND is_percent_growth = 1 THEN growth
       END
       AS growth_increment_value,
       CASE WHEN growth = 0 THEN 'Autogrowth is disabled.'
            WHEN growth > 0 AND is_percent_growth = 0  THEN 'Megabytes'
            WHEN growth > 0 AND is_percent_growth = 1  THEN 'Percent'
       END
       AS growth_increment_value_unit
FROM tempdb.sys.database_files;

Coloque o banco de dados tempdb em um subsistema de E/S rápido. Arquivos de dados individuais ou grupos de arquivos de dados tempdb não precisam necessariamente estar em discos diferentes, a menos que você encontre gargalos de E/S no nível do disco.

Se houver contenção de E/S entre tempdb e bases de dados de utilizador, coloque os arquivos do tempdb em discos diferentes dos discos usados pelas bases de dados de utilizador.

Observação

Para melhorar o desempenho, de durabilidade atrasada é sempre ativado no tempdb mesmo que a opção de banco de dados DELAYED_DURABILITY esteja definida como DISABLED. Como tempdb é recriado na inicialização, ele não passa por um processo de recuperação e não fornece uma garantia de durabilidade.

Melhorias de desempenho no tempdb para SQL Server

Introduzido no SQL Server 2016 (13.x)

Tabelas temporárias e variáveis de tabela são armazenadas em cache. O cache permite que as operações que descartam e criam os objetos temporários sejam executadas muito rapidamente. O cache também reduz a alocação de páginas e a contenção de metadados.
O protocolo de fecho da página de alocação foi melhorado para reduzir o número de fechos UP (atualização) usados.
A carga adicional de registo para tempdb é reduzida para diminuir o consumo de largura de banda de E/S de disco no ficheiro de log tempdb.
A instalação do SQL adiciona vários ficheiros de dados tempdb durante a instalação de uma nova instância. Revise as recomendações e configure seu tempdb na página Configuração do Mecanismo de Banco de Dados da Instalação do SQL ou use o parâmetro de linha de comando /SQLTEMPDBFILECOUNT. Por padrão, a Instalação do SQL adiciona tantos arquivos de dados tempdb quanto o número de processadores lógicos ou oito, o que for menor.
Quando há vários arquivos de dados tempdb, todos os arquivos crescem automaticamente ao mesmo tempo e na mesma quantidade, dependendo das configurações de crescimento. sinalizador de rastreamento 1117 não é mais necessário. Para obter mais informações, leia as alterações nos -T1117 e -T1118 para o TEMPDB e os bancos de dados de usuários.
Todas as dotações em tempdb utilizam extensões uniformes. sinalizador de rastreamento 1118 não é mais necessário. Para obter mais informações sobre melhorias de desempenho no tempdb, consulte o artigo do blog TEMPDB - Files and Trace Flags and Updates, Oh My!.
A propriedade AUTOGROW_ALL_FILES está sempre ativada para o PRIMARY grupo de arquivos.

Introduzido no SQL Server 2017 (14.x)

A experiência de Instalação do SQL melhora a orientação para alocação inicial de arquivos tempdb. A configuração do SQL avisa os clientes se o tamanho inicial do arquivo estiver definido para um valor maior que 1 GB e se a inicialização instantânea de arquivo não estiver habilitada, evitando atrasos na inicialização da instância.
A visão de gerenciamento dinâmico sys.dm_tran_version_store_space_usage acompanha o uso do espaço do armazenamento de versões por base de dados. Este DMV é útil para DBAs que desejam planear de forma proativa o dimensionamento de tempdb com base no requisito de uso do armazenamento de versão por cada base de dados.
O processamento inteligente de consultas recursos como junções adaptáveis e feedback de concessão de memória reduzem os derramamentos de memória em execuções consecutivas de uma consulta, reduzindo a utilização de tempdb.

Introduzido no SQL Server 2019 (15.x)

O Mecanismo de Banco de Dados não usa a opção FILE_FLAG_WRITE_THROUGH ao abrir arquivos tempdb para permitir a taxa de transferência máxima do disco. Como tempdb é recriado na inicialização, essa opção não é necessária para fornecer durabilidade de dados. Para obter mais informações sobre FILE_FLAG_WRITE_THROUGH, consulte Algoritmos de registro em log e armazenamento de dados que estendem a confiabilidade de dados no SQL Server.
Metadados do TempDB otimizado para memória elimina a contenção de metadados de objetos temporários no tempdb.
As atualizações simultâneas da página PFS (Page Free Space) reduzem a contenção de bloqueio de página em todos os bancos de dados, um problema mais frequentemente observado em tempdb. Esta melhoria altera a gestão de simultaneidade nas atualizações de páginas PFS, permitindo que sejam feitas sob um fecho compartilhado, em vez de um fecho exclusivo. Esse comportamento está ativado por padrão em todos os bancos de dados (incluindo tempdb) a partir do SQL Server 2019 (15.x). Para obter mais informações sobre as páginas PFS, leia Por trás dos bastidores: GAM, SGAM e páginas PFS.
Por padrão, uma nova instalação do SQL Server no Linux cria vários arquivos de dados tempdb, com base no número de núcleos lógicos (com até oito arquivos de dados). Isso não se aplica a atualizações de versões menores ou maiores no local. Cada ficheiro de dados tempdb tem 8 MB, com um crescimento automático de 64 MB. Esse comportamento é semelhante à instalação padrão do SQL Server no Windows.

Introduzido no SQL Server 2022 (16.x)

Introduziu escalabilidade aprimorada com melhorias na simultaneidade do controlo de página do sistema. As atualizações simultâneas das páginas do mapa de alocação global (GAM) e das páginas do mapa de alocação global partilhada (SGAM) reduzem a contenção de travas de página enquanto alocam/desalocam páginas e extensões de dados. Esses aprimoramentos se aplicam a todos os bancos de dados de usuários e beneficiam especialmente cargas de trabalho pesadas em tempdb. Para obter mais informações sobre as páginas GAM e SGAM, leia Sob as entrelinhas: páginas GAM, SGAM e PFS. Para obter mais informações, assista Aprimoramentos de simultaneidade da página do sistema (Ep. 6) | Dados expostos.

Metadados do TempDB otimizados para memória

A contenção de metadados de objetos temporários tem sido historicamente um gargalo para a escalabilidade de muitas cargas de trabalho do SQL Server. Para resolver isso, o SQL Server 2019 (15.x) introduziu um recurso que faz parte da família de recursos de banco de dados em memória : metadados TempDB otimizados para memória.

Habilitar o recurso de metadados TempDB otimizado para memória elimina este gargalo para cargas de trabalho que foram anteriormente limitadas pela contenção temporária de metadados de objeto dentro de tempdb. A partir do SQL Server 2019 (15.x), as tabelas do sistema envolvidas no gerenciamento de metadados de objetos temporários podem se tornar tabelas sem travamento, não duráveis e com otimização de memória.

Dica

Devido às atuais limitações de , recomendamos ativar os metadados do TempDB otimizados para memória somente quando a contenção de metadados de objeto ocorrer e afetar significativamente as suas cargas de trabalho.

A consulta de diagnóstico a seguir retorna uma ou mais linhas se estiver ocorrendo contenção temporária de metadados de objeto. Cada linha representa uma tabela do sistema e retorna o número de sessões que disputam acesso a essa tabela no momento em que essa consulta de diagnóstico é executada.

SELECT OBJECT_NAME(dpi.object_id, dpi.database_id) AS system_table_name,
       COUNT(DISTINCT(r.session_id)) AS session_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.db_id, prc.file_id, prc.page_id, 'LIMITED') AS dpi
WHERE dpi.database_id = 2
      AND dpi.object_id IN (3, 9, 34, 40, 41, 54, 55, 60, 74, 75)
      AND UPPER(r.wait_type) LIKE N'PAGELATCH[_]%'
GROUP BY dpi.object_id, dpi.database_id;

Assista a este vídeo de sete minutos para obter uma visão geral de como e quando usar o recurso de metadados TempDB otimizado para memória:

Observação

Atualmente, o recurso de metadados TempDB otimizado para memória não está disponível no Banco de Dados SQL do Azure, no Banco de Dados SQL no Microsoft Fabric e na Instância Gerenciada SQL do Azure.

Configurar e usar metadados do TempDB com otimização de memória

As seções a seguir incluem etapas para habilitar, configurar, verificar e desabilitar o recurso de metadados do TempDB com otimização de memória.

Ativar

Para habilitar esse recurso, use o seguinte script:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Para obter mais informações, consulte ALTER SERVER. Essa alteração de configuração requer uma reinicialização do serviço para entrar em vigor.

Você pode verificar se tempdb está ou não otimizado para memória usando o seguinte comando T-SQL:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

Se o valor retornado for 1 e tiver ocorrido uma reinicialização após a habilitação do recurso, o recurso será habilitado.

Se o servidor falhar ao iniciar por qualquer motivo depois de habilitar os metadados do TempDB com otimização de memória, você poderá ignorar o recurso iniciando a instância do Mecanismo de Banco de Dados com de configuração mínima usando a opção de inicialização -f. Em seguida, você pode desabilitar recurso e remover a opção -f para reiniciar o Mecanismo de Banco de Dados no modo normal.

Vincular ao pool de recursos para limitar o uso de memória

Para proteger o servidor de possíveis condições de falta de memória, recomendamos que você vincule tempdb a um governador de recursos a um pool de recursos que limite a memória consumida pelos metadados do TempDB otimizados para memória. O exemplo de script a seguir cria um pool de recursos, define a sua memória máxima para 20%, habilita o governador de recursos e vincula tempdb ao pool de recursos.

Este exemplo usa 20% como limite de memória para fins de demonstração. O valor ideal em seu ambiente pode ser maior ou menor, dependendo da sua carga de trabalho, e pode mudar ao longo do tempo se a carga de trabalho mudar.

CREATE RESOURCE POOL tempdb_resource_pool
WITH (MAX_MEMORY_PERCENT = 20);

ALTER RESOURCE GOVERNOR RECONFIGURE;

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON 
(RESOURCE_POOL = 'tempdb_resource_pool');

Essa alteração também requer uma reinicialização do serviço para entrar em vigor, mesmo que os metadados do TempDB otimizados para memória já estejam habilitados.

Verificar a vinculação do pool de recursos e monitorar o uso da memória

Para verificar se tempdb está vinculado a um pool de recursos e monitorar as estatísticas de uso de memória para o pool, use a seguinte consulta:

WITH resource_pool AS
(
SELECT p.pool_id,
       p.name,
       p.max_memory_percent,
       dp.max_memory_kb,
       dp.target_memory_kb,
       dp.used_memory_kb,
       dp.out_of_memory_count
FROM sys.resource_governor_resource_pools AS p
INNER JOIN sys.dm_resource_governor_resource_pools AS dp
ON p.pool_id = dp.pool_id
)
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS is_tempdb_memory_optimized_metadata_enabled,
       rp.name AS resource_pool_name,
       rp.max_memory_percent,
       rp.max_memory_kb,
       rp.target_memory_kb,
       rp.used_memory_kb,
       rp.out_of_memory_count
FROM sys.databases AS d
LEFT JOIN resource_pool AS rp
ON d.resource_pool_id = rp.pool_id
WHERE d.name = 'tempdb';

Remover vinculação de pool de recursos

Para remover a associação do pool de recursos enquanto mantém os metadados do TempDB otimizados para memória habilitados, execute o seguinte comando e reinicie o serviço:

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Desativar

Para desabilitar os metadados do TempDB com otimização de memória, execute o seguinte comando e reinicie o serviço:

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF;

Limitações dos metadados do TempDB otimizados para memória

Habilitar ou desabilitar o recurso de metadados TempDB otimizado para memória requer uma reinicialização.
Em certos casos, poder-se-á observar um elevado uso de memória pelo gerente de memória MEMORYCLERK_XTP, resultando em erros de falta de memória na sua carga de trabalho.

Para ver o uso da memória pelo funcionário do MEMORYCLERK_XTP em relação a todos os outros funcionários de memória e em relação à memória do servidor de destino, execute a seguinte consulta:
```
SELECT SUM(IIF(type = 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS memoryclerk_xtp_pages_mb,
       SUM(IIF(type <> 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS other_pages_mb,
       SUM(committed_target_kb) / 1024. AS committed_target_memory_mb
FROM sys.dm_os_memory_clerks
CROSS JOIN sys.dm_os_sys_info;
```
Se a memória MEMORYCLERK_XTP estiver alta, pode mitigar o problema da seguinte maneira:
- Associe o banco de dados tempdb a um pool de recursos que limite o consumo de memória por metadados TempDB otimizados para memória. Para obter mais informações, consulte Configurar e usar metadados tempdb otimizados para memória.
- Um procedimento armazenado do sistema pode ser executado periodicamente para liberar MEMORYCLERK_XTP memória que não é mais necessária. Para obter mais informações, consulte sys.sp_xtp_force_gc (Transact-SQL).
Para obter mais informações, consulte metadados do tempdb otimizados para memória (HkTempDB) com erros de falta de memória.
Quando você usa In-Memory OLTP, uma única transação não tem permissão para acessar tabelas com otimização de memória em mais de um banco de dados. Por isso, qualquer transação de leitura ou escrita que envolva uma tabela otimizada para memória num banco de dados de utilizador não pode também acessar as vistas do sistema tempdb na mesma transação. Se isso ocorrer, você receberá o erro 41317:
```
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.
```
Essa limitação também se aplica a outros cenários em que uma única transação tenta acessar tabelas com otimização de memória em mais de um banco de dados.

Se consultar a vista de catálogo sys.stats num banco de dados de utilizador que contém tabelas otimizadas para memória, poderá obter o erro 41317. Isso acontece porque a consulta tenta acessar estatísticas dados em uma tabela com otimização de memória no banco de dados do usuário e os metadados otimizados para memória em tempdb.

O script de exemplo a seguir produz esse erro quando os metadados do TempDB com otimização de memória estão habilitados:
```
BEGIN TRAN;

-- Create an In-memory OLTP transaction that accesses a system view in tempdb
SELECT name
FROM tempdb.sys.tables;

-- An attempt to create an In-memory OLTP transaction in the user database fails
INSERT INTO <user database>.<schema>.<memory-optimized table>
VALUES (1);

COMMIT TRAN;
```
Observação

Esta limitação não se aplica a tabelas temporárias. Você pode criar uma tabela temporária na mesma transação que acessa uma tabela com otimização de memória em um banco de dados de usuário.
As consultas em exibições de catálogo do sistema sempre usam o nível de isolamento READ COMMITTED. Quando os metadados do TempDB otimizados para memória estão habilitados, as consultas nas exibições do catálogo do sistema em tempdb usam o nível de isolamento SNAPSHOT. Em ambos os casos, as dicas de bloqueio não são honradas.
Não podem ser criados índices de coluna em tabelas temporárias quando os metadados do TempDB com otimização de memória estão habilitados.
- Como consequência, o uso do procedimento armazenado do sistema sp_estimate_data_compression_savings com o parâmetro de compactação de dados COLUMNSTORE ou COLUMNSTORE_ARCHIVE não é suportado quando os metadados TempDB otimizados para memória estão habilitados.

Planejamento de capacidade para tempdb no SQL Server

Determinar o tamanho apropriado para tempdb depende de muitos fatores. Esses fatores incluem a carga de trabalho e os recursos do Mecanismo de Banco de Dados usados.

Recomendamos que você analise tempdb consumo de espaço executando as seguintes tarefas em um ambiente de teste onde você pode reproduzir sua carga de trabalho típica:

Habilite o crescimento automático para arquivos tempdb. Todos os tempdb arquivos de dados devem ter o mesmo tamanho inicial e configuração de crescimento automático.
Reproduza a carga de trabalho e monitorize o espaço usado tempdb.
Se utilizar de manutenção do índiceperiódica, execute as suas tarefas de manutenção e monitorize o espaço tempdb.
Use os valores de espaço máximo usado das etapas anteriores para prever o uso total da carga de trabalho. Ajuste esse valor para a atividade simultânea projetada e, em seguida, defina o tamanho da tempdb de acordo.

Monitorar o uso do tempdb

A falta de espaço em disco no tempdb pode causar interrupções significativas e tempo de inatividade do aplicativo. Você pode usar o modo de exibição de gerenciamento dinâmico sys.dm_db_file_space_usage para monitorar o espaço usado nos arquivos tempdb.

Por exemplo, o script de exemplo a seguir localiza:

Espaço livre no tempdb (não considerando o espaço em disco livre que pode estar disponível para o crescimento de tempdb)
Espaço utilizado pela loja de versões
Espaço utilizado por objetos internos
Espaço usado por objetos de usuário

SELECT SUM(unallocated_extent_page_count) * 8.0 / 1024 AS tempdb_free_data_space_mb,
       SUM(version_store_reserved_page_count) * 8.0 / 1024 AS tempdb_version_store_space_mb,
       SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS tempdb_internal_object_space_mb,
       SUM(user_object_reserved_page_count) * 8.0 / 1024 AS tempdb_user_object_space_mb
FROM tempdb.sys.dm_db_file_space_usage;

Para monitorizar a atividade de alocação ou desalocação de páginas em tempdb ao nível da sessão ou da tarefa, pode-se usar as vistas de gestão dinâmica sys.dm_db_session_space_usage e sys.dm_db_task_space_usage. Essas exibições podem ajudá-lo a identificar consultas, tabelas temporárias ou variáveis de tabela que estão usando grandes quantidades de espaço tempdb.

Por exemplo, use o seguinte script de exemplo para obter o espaço tempdb alocado e desalocado por objetos internos em todas as tarefas a decorrer atualmente em cada sessão em execução:

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;

Use o script de exemplo a seguir para localizar o tempdb espaço alocado e atualmente consumido por objetos internos e de usuário para cada sessão e solicitação, para tarefas em execução e concluídas:

WITH tempdb_space_usage AS
(
SELECT session_id,
       request_id,
       user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
       user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_task_space_usage
UNION ALL
SELECT session_id,
       NULL AS request_id,
       user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
       user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - user_objects_deferred_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_session_space_usage
)
SELECT session_id,
       COALESCE(request_id, 0) AS request_id,
       SUM(tempdb_allocations_page_count * 8) AS tempdb_allocations_kb,
       SUM(IIF (tempdb_current_page_count >= 0, tempdb_current_page_count, 0) * 8) AS tempdb_current_kb
FROM tempdb_space_usage
GROUP BY session_id, COALESCE (request_id, 0)
ORDER BY session_id, request_id;

Partilhar via

banco de dados tempdb

Visão geral

Propriedades físicas do tempdb no SQL Server

Número de arquivos de dados tempdb

Mover os dados tempdb e os arquivos de log no SQL Server

Opções de banco de dados para tempdb no SQL Server

tempdb no Banco de Dados SQL do Azure

tempdb na instância gerenciada do SQL

tempdb no banco de dados SQL no contexto do Fabric

Restrições

Permissões

Otimizar o desempenho do tempdb no SQL Server

Melhorias de desempenho no tempdb para SQL Server

Introduzido no SQL Server 2016 (13.x)

Introduzido no SQL Server 2017 (14.x)

Introduzido no SQL Server 2019 (15.x)

Introduzido no SQL Server 2022 (16.x)

Metadados do TempDB otimizados para memória

Configurar e usar metadados do TempDB com otimização de memória

Ativar

Vincular ao pool de recursos para limitar o uso de memória

Verificar a vinculação do pool de recursos e monitorar o uso da memória

Remover vinculação de pool de recursos

Desativar

Limitações dos metadados do TempDB otimizados para memória

Planejamento de capacidade para tempdb no SQL Server

Monitorar o uso do tempdb

Comentários

Recursos adicionais

Partilhar via

banco de dados tempdb

Visão geral

Propriedades físicas do tempdb no SQL Server

Número de arquivos de dados tempdb

Mover os dados tempdb e os arquivos de log no SQL Server

Opções de banco de dados para tempdb no SQL Server

tempdb no Banco de Dados SQL do Azure

tempdb na instância gerenciada do SQL

tempdb no banco de dados SQL no contexto do Fabric

Restrições

Permissões

Otimizar o desempenho do tempdb no SQL Server

Melhorias de desempenho no tempdb para SQL Server

Introduzido no SQL Server 2016 (13.x)

Introduzido no SQL Server 2017 (14.x)

Introduzido no SQL Server 2019 (15.x)

Introduzido no SQL Server 2022 (16.x)

Metadados do TempDB otimizados para memória

Configurar e usar metadados do TempDB com otimização de memória

Ativar

Vincular ao pool de recursos para limitar o uso de memória

Verificar a vinculação do pool de recursos e monitorar o uso da memória

Remover vinculação de pool de recursos

Desativar

Limitações dos metadados do TempDB otimizados para memória

Planejamento de capacidade para tempdb no SQL Server

Monitorar o uso do tempdb

Conteúdo relacionado

Comentários

Recursos adicionais