banco de dados tempdb

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureBanco de Dados SQL no Microsoft Fabric

Este artigo descreve o banco de dados do tempdb sistema, um recurso global disponível para todos os usuários conectados a uma instância do Mecanismo de Banco de Dados no SQL Server, no Banco de Dados SQL do Azure ou na Instância Gerenciada de 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. Eles incluem:

  • Tabelas e índices temporários globais ou locais nessas tabelas
  • Procedimentos armazenados temporariamente
  • Variáveis grandes, incluindo variáveis de tabela
  • Tabelas retornadas em funções com valor de tabela
  • Cursores

  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, são criados sem 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. Eles incluem:

  • Tabelas de trabalho para armazenar resultados intermediários para spools, cursores, classificações e armazenamento temporário LOB (objeto grande).
  • Arquivos de trabalho para operações de correspondência de hash ou agregação de hash.
  • Resultados de classificação intermediários para operações como criação ou reconstrução de índices (se SORT_IN_TEMPDB for especificado) ou certas consultas GROUP BY, ORDER BY ou UNION.

  Cada objeto interno usa um mínimo de nove páginas: uma página de IAM e uma extensão de oito páginas. Para saber mais sobre páginas e extensões, confira Páginas e extensões.

• Repositórios de versão, que são coleções de páginas de dados que contêm as linhas de dados que dão suporte ao controle de versão de linha. Os armazenamentos de versão contêm:

  • Versões de linha geradas por transações de modificação de dados em um banco de dados que usa transações baseadas em versões de linha READ COMMITTED ou transações de isolamento SNAPSHOT.
  • Versões de linhas geradas por meio de transações de modificação de dados para recursos, como operações de índice online, MARS (conjunto de resultados ativos múltiplos) e gatilhos AFTER.

  A partir da versão prévia do SQL Server 2025 (17.x), quando a ADR (recuperação acelerada de banco de dados) está habilitadatempdb, tempdb contém dois repositórios de versão diferentes e independentes:

  • O repositório de versão tradicional, usado para versões de linha geradas por transações em bancos de dados de usuário que não têm a ADR habilitada.
  • O PVS (repositório de versão persistente), usado para versões de linha geradas por transações em tempdb.

  Certifique-se de alocar espaço em disco suficiente para tempdb os arquivos de dados tempdb conterem ambos os armazenamentos de versão, caso a ADR esteja habilitada. Dependendo da carga de trabalho, talvez seja necessário aumentar o tamanho dos arquivos de tempdb para conter os dados do PVS.

  Para obter mais informações sobre tempdb o uso de espaço pelo repositório de versões tradicionais, consulte Espaço usado no tempdb. Para obter mais informações sobre o espaço usado pela PVS, consulte Espaço usado pelo repositório de versão persistente (PVS).

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 vazio tempdb . Procedimentos armazenados temporários e tabelas temporárias locais são descartados automaticamente quando a sessão que os criou se desconecta.

tempdb nunca precisa salvar nada de um período de tempo de atividade do Mecanismo de Banco de Dados para outro. As operações de backup e restauração não são permitidas em tempdb.

Propriedades físicas do tempdb no SQL Server

A tabela a seguir lista os valores de configuração inicial dos dados de tempdb 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 um pouco em diferentes edições do SQL Server.

Arquivo	Nome lógico	Nome físico	Tamanho inicial	Aumento do arquivo
Dados primários	tempdev	tempdb.mdf	8 megabytes	Aumento automático de 64 MB até que o disco fique cheio
Arquivos de dados secundários	temp#	tempdb_mssql_#.ndf	8 megabytes	Aumento automático de 64 MB até que o disco fique cheio
Registro	templog	templog.ldf	8 megabytes	Aumento automático de 64 megabytes até um máximo de 2 terabytes

Todos os tempdb arquivos de dados devem sempre ter os mesmos parâmetros de tamanho e crescimento iniciais.

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 atenuar a contenção de alocação.

O número total recomendado de arquivos de dados depende do número de processadores lógicos no computador. 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.
• Se a contenção de alocação ainda for observada, aumente o número de arquivos de dados em múltiplos de quatro até que a contenção diminua para níveis aceitáveis, ou ajuste a carga de trabalho.

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

Para verificar o tamanho e os parâmetros de crescimento atuais de tempdb, use a exibição de catálogo sys.database_files em tempdb.

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

Para mover os arquivos de log e dados de tempdb, confira 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 de 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 exibição de catálogo sys.databases .

Opção de banco de dados	Valor padrão	Pode ser modificado
ACCELERATED_DATABASE_RECOVERY	OFF	Sim1
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, confira Nível de compatibilidade de ALTER DATABASE (Transact-SQL).	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 de banco de dados	READ_WRITE	Não
Acesso do usuário do banco de dados	MULTI_USER	Não
DATE_CORRELATION_OPTIMIZATION	OFF	Sim
DB_CHAINING	ON	Não
DELAYED_DURABILITY	DISABLED Independentemente dessa opção, a durabilidade atrasada está sempre habilitadatempdb.	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 existente PAGE_VERIFY 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ço	ENABLE_BROKER	Sim
TARGET_RECOVERY_TIME	60	Sim
TEMPORAL_HISTORY_RETENTION	ON	Sim
TRUSTWORTHY	OFF	Não

¹ Configurar ACCELERATED_DATABASE_RECOVERY para ON em tempdb é compatível a partir da prévia do SQL Server 2025 (17.x). Nas versões anteriores do SQL Server, a modificação da opção ACCELERATED_DATABASE_RECOVERY para o tempdb banco de dados não é permitida.

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

tempdb no Banco de Dados SQL do Azure

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

Para bancos de dados individuais, 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.

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 cruzada de banco de dados ao banco de dados tempdb. Por exemplo, você pode consultar a exibição sys.database_files :

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

As tabelas temporárias globais no 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 os tamanhos de tempdb no Banco de Dados SQL do Azure, examine:

• Modelo de compra vCore: bancos de dados individuais, bancos de dados em pool
• Modelo de compra DTU: bancos de dados individuais, bancos de dados em pool

tempdb na Instância Gerenciada de SQL

Na Instância Gerenciada de SQL do Azure, alguns aspectos do comportamento e da tempdb 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 tempdb configurações na Instância Gerenciada de SQL do Azure, veja Definir configurações tempdb para a Instância Gerenciada de SQL do Azure.

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

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

tempdb no banco de dados SQL no Fabric

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

Da mesma forma que o 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:

• Adição de grupos de arquivos.
• Backup ou restauração de banco de dados.
• Alteração de ordenação. A ordenação padrão é a ordenação do servidor.
• Alteração do proprietário do banco de dados. tempdb pertence a sa.
• Criação de um instantâneo do banco de dados.
• Descartando o banco de dados.
• Descartando o usuário convidado do banco de dados.
• Habilitação da captura de dados de alterações.
• 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.
• Renomeação do banco de dados ou grupo de arquivos primário.
• Execução de DBCC CHECKALLOC.
• Execução de DBCC CHECKCATALOG.
• Definição do banco de dados para OFFLINE.
• Definição do banco de dados ou do grupo de arquivos primário para READ_ONLY.

Permissões

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

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

É possível revogar a CONNECT permissão 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 tempdb no SQL Server

O tamanho e o posicionamento físico dos tempdb arquivos podem afetar o desempenho. Por exemplo, se o tamanho tempdb inicial for muito pequeno, o tempo e os recursos poderão ser necessários para aumentar automaticamente tempdb até o tamanho necessário para dar suporte à carga de trabalho sempre que a instância do Mecanismo de Banco de Dados for reiniciada.

• Se possível, use a inicialização instantânea de arquivo para melhorar o desempenho das operações de crescimento para arquivos de dados.
  • A partir do SQL Server 2022 (16.x), eventos de crescimento de arquivo de log de transações de até 64 MB também podem se beneficiar da inicialização instantânea do arquivo. Para obter mais informações, consulte a inicialização instantânea do arquivo e o log de transações.
• Aloque espaço antecipadamente para todos os arquivos do tempdb definindo o tamanho do arquivo com um valor grande o bastante para acomodar a carga de trabalho comum no ambiente. A pré-alocação impede que o recurso de crescimento automático tempdb ocorra com muita frequência, o que pode afetar negativamente o desempenho.
• Os arquivos no tempdb banco de dados devem ser definidos para aumentar automaticamente para fornecer espaço durante eventos de crescimento não planejados.
• Dividindo tempdb em múltiplos arquivos de dados de tamanho igual pode melhorar a eficiência das operações que fazem uso de tempdb.
  • Para evitar o desequilíbrio de alocação de dados, os arquivos de dados devem ter o mesmo tamanho inicial e parâmetros de crescimento porque o Mecanismo de Banco de Dados usa um algoritmo de preenchimento proporcional que favorece 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 faça com que o incremento de crescimento seja o mesmo para todos os arquivos de dados para evitar desequilíbrio de crescimento.
• A partir da versão prévia do SQL Server 2025 (17.x), considere habilitar a recuperação acelerada do banco de dados em tempdb para obter os benefícios da reversão instantânea de transações e do truncamento agressivo de log para transações em tempdb. Para obter mais informações, consulte ADR no tempdb.
  • Habilitar ou desabilitar a ADR em tempdb requer uma reinicialização do Mecanismo de Banco de Dados para que tenha efeito.

Para verificar o tamanho atual e os parâmetros de aumento do 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 tempdb dados não precisam necessariamente estar em discos diferentes, a menos que você esteja encontrando gargalos de E/S no nível do disco.

Se houver contenção de E/S entre tempdb e bancos de dados de usuário, coloque os arquivos tempdb em discos diferentes dos que são usados pelos bancos de dados de usuário.

Observação

Para melhorar o desempenho, a durabilidade atrasada é sempre habilitada tempdb mesmo se a opção DELAYED_DURABILITY de banco de dados estiver 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 no tempdb para SQL Server

Introduzido na versão prévia do SQL Server 2025 (17.x)

• Tempdb A governança de recursos de espaço impõe um limite na quantidade total de tempdb espaço consumido por um aplicativo ou carga de trabalho do usuário. Isso melhora a confiabilidade e evita interrupções impedindo que consultas ou cargas de trabalho descontrolados consumam uma grande quantidade de espaço em tempdb. Para obter mais informações, consulte a governança de recursos de espaço do Tempdb.
• Há suporte no para recuperação acelerada de banco de dados, fornecendo reversão instantânea de transações e truncamento agressivo de log para transações em tempdb.
• Proteção de Halloween otimizada reduz o uso de espaço em tempdb ao evitar o uso de um spool de proteção de Halloween para instruções DML (língua de modificação de dados).

Introduzido no SQL Server 2022 (16.x)

• Introduziu uma escalabilidade aprimorada com aprimoramentos na simultaneidade de bloqueio de página do sistema. As atualizações simultâneas de páginas do GAM (mapa de alocação global) e páginas do SGAM (mapa de alocação global compartilhado) reduz a contenção de trava de página ao alocar/desalocar páginas de dados e extensões. Esses aprimoramentos se aplicam a todos os bancos de dados de usuário e beneficiam especialmente cargas de trabalho pesadas em tempdb. Para obter mais informações sobre as páginas GAM e SGAM, leia Sob as capas: páginas GAM, SGAM e PFS. Para obter mais informações, assista a Aprimoramentos de simultaneidade de trava de página do sistema (Ep. 6) | Dados expostos.

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 tempdb arquivos para permitir a taxa de transferência máxima de disco. Como tempdb é recriada 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 log e armazenamento de dados que estendem a confiabilidade de dados no SQL Server.
• Metadados do TempDB otimizados para memória removem a contenção de metadados de objetos temporários em tempdb.
• Atualizações de página PFS (Espaço Livre de Página Simultânea) reduzem a contenção de trava da página em todos os bancos de dados, um problema que é mais frequentemente observado em tempdb. Essa melhoria altera o gerenciamento de concorrência das atualizações de página do PFS para que elas possam ser atualizadas sob uma trava compartilhada, em vez de uma trava exclusiva. Esse comportamento é ativado por padrão em todos os bancos de dados (incluindo tempdb) do SQL Server 2019 (15.x) em diante. Para obter mais informações sobre as páginas do PFS, leia Sob as capas: páginas GAM, SGAM e PFS.
• Por padrão, uma nova instalação do SQL Server em Linux cria vários arquivos de dados tempdb com base no número de núcleos lógicos (com até 8 arquivos de dados). Isso não é aplicável a upgrades de versões principais ou secundárias no local. Cada tempdb arquivo de dados 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 2017 (14.x)

• A experiência de Instalação do SQL melhora a orientação para a alocação inicial tempdb de arquivos. A Instalação do SQL avisa os clientes se o tamanho inicial do arquivo estiver definido como 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.
• O sys.dm_tran_version_store_space_usage modo de exibição de gerenciamento dinâmico controla o uso do repositório de versão por banco de dados. Essa DMV é útil para DBAs que desejam planejar de forma proativa o dimensionamento com base no requisito de uso do repositório de versões por banco de dados.
• Recursos de processamento de consulta inteligente, como junções adaptativas e feedback de concessão de memória, reduzem os despejos de memória em execuções consecutivas de uma consulta, reduzindo a utilização.

Introduzido no SQL Server 2016 (13.x)

• As tabelas temporárias e variáveis de tabela são armazenadas em cache. O armazenamento em cache permite que as operações de descarte e criação de objetos temporários sejam executadas muito rapidamente. O armazenamento em cache também reduz a alocação de páginas e a contenção de metadados.
• O protocolo de travamento da página de alocação foi aprimorado para reduzir o número de travas (atualização) de UP utilizadas.
• A sobrecarga de log para o tempdb foi reduzida para diminuir o consumo de largura de banda de E/S de disco no arquivo de log do tempdb.
• A configuração do SQL adiciona vários tempdb arquivos de dados durante a instalação de uma nova instância. Examine as recomendações e configure-as tempdb na página Configuração do Mecanismo de Banco de Dados da Instalação do SQL ou use o parâmetro /SQLTEMPDBFILECOUNTde linha de comando. Por padrão, a Instalação do SQL adiciona tantos tempdb arquivos de dados quanto o número de processadores lógicos ou oito, o que for menor.
• Quando houver vários arquivos de dados do tempdb, todos os arquivos aumentarão automaticamente ao mesmo tempo e na mesma quantidade, dependendo das configurações de aumento. O sinalizador de rastreamento 1117 não é mais necessário. Para obter mais informações, leia as alterações -T1117 e -T1118 para bancos de dados de usuários e TEMPDB.
• Além disso, todas as alocações em tempdb usam extensões uniformes. O sinalizador de rastreamento 1118 não é mais necessário. Para saber mais sobre melhorias de desempenho em tempdb, confira o artigo do blog TEMPDB – Arquivos, atualizações e sinalizadores de rastreamento. O que fazer?!.
• A AUTOGROW_ALL_FILES propriedade está sempre ativada para o PRIMARY grupo de arquivos.

Metadados do TempDB com otimização de memória

A contenção temporária de metadados de objeto 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 do banco de dados na memória : metadados tempDB com otimização de memória.

Habilitar o recurso de metadados tempDB com otimização de memória remove esse gargalo para cargas de trabalho anteriormente limitadas pela contenção de metadados de objeto temporário dentro tempdb. A partir do SQL Server 2019 (15.x), as tabelas do sistema envolvidas no gerenciamento de metadados de objeto temporário podem se tornar tabelas sem travas, não duráveis e com otimização de memória.

Dica

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

A consulta de diagnóstico a seguir retornará, caso esteja ocorrendo contenção dos metadados de objetos temporários, uma ou mais linhas. Cada linha representa uma tabela do sistema e retorna o número de sessões que lutam pelo 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 com otimização de memória:

Observação

Atualmente, o recurso de metadados TempDB com otimização de 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 de 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 tempDB com otimização de memória.

Habilitar

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 exige uma reinicialização do serviço para entrar em vigor.

Você pode verificar se o tempdb tem otimização de memória ou não usando o seguinte comando T-SQL:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

Se o valor retornado for 1 e uma reinicialização tiver ocorrido depois de habilitar o recurso, o recurso será habilitado.

Se o servidor falhar ao iniciar por qualquer motivo depois de habilitar 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 configuração mínima usando a opção -f de inicialização. Em seguida, você pode desabilitar o recurso e remover a opção -f de reiniciar o Mecanismo de Banco de Dados no modo normal.

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

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

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

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 que uma reinicialização do serviço entre em vigor, mesmo que os metadados do TempDB com otimização de memória já estejam habilitados.

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

Para verificar se tempdb está associado a um pool de recursos e monitorar as estatísticas de uso de memória do 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 associação do pool de recursos

Para remover a associação do pool de recursos mantendo os metadados do TempDB com otimização de memória habilitados, execute o seguinte comando e reinicie o serviço:

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Desabilitar

Para desabilitar 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 com otimização de memória

• Habilitar ou desabilitar o recurso de metadados tempDB com otimização de memória requer uma reinicialização.

• Em determinados casos, você pode observar o alto uso de memória pelo MEMORYCLERK_XTP controlador de memória, causando erros de falta de memória em sua carga de trabalho.

  Para ver o MEMORYCLERK_XTP uso de memória pelo funcionário 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 MEMORYCLERK_XTP a memória for alta, você poderá atenuar o problema da seguinte maneira:

  • Associe o banco de dados tempdb a um pool de recursos que limita o consumo de memória pelos metadados otimizados para memória do TempDB. Para obter mais informações, consulte Configurar e usar metadados de tempdb com otimização de 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, veja sys.sp_cdc_enable_db (Transact-SQL).

  Para obter mais informações, consulte os metadados de tempdb com otimização de memória (HkTempDB) sem erros 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 gravação que envolva uma tabela otimizada para memória em um banco de dados de usuário não pode acessar visões do sistema 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.

  Por exemplo, você poderá receber o erro 41317 se consultar a exibição do catálogo sys.stats em um banco de dados de usuário que contém tabelas com otimização de memória. Isso acontece porque a consulta tenta acessar dados de estatísticas em uma tabela com otimização de memória no banco de dados do usuário e os metadados com otimização de memória em tempdb.

  O script de exemplo a seguir produz esse erro quando os metadados 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

  Essa 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 do usuário.

• Consultas sobre as exibições do catálogo do sistema usam sempre o nível de isolamento READ COMMITTED. Quando os metadados do TempDB com otimização de memória estiverem habilitados, consultas nas exibições do catálogo do sistema em tempdb usarão o nível de isolamento SNAPSHOT. Em ambos os casos, as dicas de bloqueio não são respeitadas.

• Os índices columnstore não podem ser criados em tabelas temporárias quando os metadados do TempDB com otimização de memória estão habilitados.

  • Como consequência, o procedimento armazenado do sistema com o parâmetro de compactação de dados sp_estimate_data_compression_savings ou COLUMNSTORE não é suportado quando os metadados TempDB com otimização de memória estão habilitados.

Planejamento de capacidade do tempdb no SQL Server

Determinar o tamanho tempdb apropriado 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 o consumo de espaço executando as seguintes tarefas em um ambiente de teste em que você pode reproduzir sua carga de trabalho típica:

• Habilite o crescimento automático para tempdb arquivos. 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 monitore tempdb o uso do espaço.
• Se você usar a manutenção do índice periódica, execute seus trabalhos de manutenção e monitore o espaço .
• Utilize os valores de espaço máximo utilizado das etapas anteriores para prever o uso acumulado da carga de trabalho. Ajuste esse valor para a atividade simultânea projetada e defina o tamanho de tempdb de acordo com isso.

Monitorar o uso de tempdb

Ficar sem espaço no disco tempdb pode causar interrupções significativas e inatividade do aplicativo. Você pode usar a visão de gerenciamento dinâmico sys.dm_db_file_space_usage para monitorar o espaço usado nos tempdb arquivos.

Por exemplo, o seguinte exemplo de script localiza:

• Espaço livre em tempdb (não considerando o espaço livre em disco que pode estar disponível para o crescimento de tempdb).
• Espaço usado pelo repositório de versões tradicional.
  • Para monitorar o tamanho do repositório de versão persistente (PVS) quando a ADR (recuperação acelerada de banco de dados) estiver habilitadatempdb, consulte Examinar o tamanho da PVS.
• Espaço usado 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 monitorar a atividade de alocação ou desalocação de páginas no tempdb no nível de sessão ou no nível de tarefa, você pode usar as visões de gerenciamento dinâmico 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 tempdb espaço.

Por exemplo, use o script de exemplo a seguir para verificar o tempdb espaço alocado e desalocado por objetos internos em todas as tarefas que estão em execução no momento em cada sessã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;

Conteúdo relacionado

• Opção SORT_IN_TEMPDB para índices
• Bancos de dados do sistema
• sys.databases
• sys.master_files
• Mover arquivos de banco de dados