banco de dados tempdb

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

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 SQL Server, Banco de Dados SQL do Azure ou 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 temporários que são explicitamente criados. Eles incluem tabelas e índices globais ou locais temporários, procedimentos armazenados temporários, variáveis de tabela, tabelas retornadas em funções com valor de tabela e cursores.

  • Objetos internos que o mecanismo de banco de dados cria. Elas 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 junção de hash ou de 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 a recursos para controle de versão de linha. Existem dois tipos de repositório de versão: um comum e um de criação de índice online. 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 READ COMMITTED por meio transações de isolamento de instantâneo ou isolamento de controle de versão de linha.
    • 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.

As operações no tempdb são registradas minimamente em log para que as transações possam ser revertidas. tempdb é recriado a cada vez que o SQL Server é iniciado, de modo que o sistema sempre começa com uma cópia limpa do banco de dados. As tabelas temporárias e procedimentos armazenados são descartados automaticamente ou desconectados e nenhuma conexão fica ativa quando o sistema é desligado.

tempdb nunca há nada em a ser salvo de uma sessão do SQL Server para outra. 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
Log templog templog.ldf 8 megabytes Aumento automático de 64 megabytes até um máximo de 2 terabytes

O número de arquivos de dados secundários depende do número de processadores (lógicos) no computador. Como regra geral, se o número de processadores lógicos for menor ou igual a oito, use o mesmo número de processadores lógicos para os arquivos de dados. Se o número de processadores lógicos for maior que oito, use oito arquivos de dados. Se, após isso, a contenção continuar, 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 faça alterações na carga de trabalho/no código.

O valor padrão para o número de arquivos de dados baseia-se nas diretrizes gerais de KB 2154845.

Para verificar o tamanho atual e os parâmetros de aumento do tempdb, consulte a exibição tempdb.sys.database_files.

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
ALLOW_SNAPSHOT_ISOLATION OFF Yes
ANSI_NULL_DEFAULT OFF Yes
ANSI_NULLS OFF Yes
ANSI_PADDING OFF Yes
ANSI_WARNINGS OFF Yes
ARITHABORT OFF Yes
AUTO_CLOSE OFF Não
AUTO_CREATE_STATISTICS ATIVADO Yes
AUTO_SHRINK OFF Não
AUTO_UPDATE_STATISTICS ATIVADO Yes
AUTO_UPDATE_STATISTICS_ASYNC OFF Yes
CHANGE_TRACKING OFF Não
CONCAT_NULL_YIELDS_NULL OFF Yes
CURSOR_CLOSE_ON_COMMIT OFF Yes
CURSOR_DEFAULT GLOBAL Yes
Opções de disponibilidade de banco de dados ONLINE

MULTI_USER

READ_WRITE		 Não

Não

Não
DATE_CORRELATION_OPTIMIZATION OFF Yes
DB_CHAINING ATIVADO Não
ENCRYPTION OFF Não
MIXED_PAGE_ALLOCATION OFF Não
NUMERIC_ROUNDABORT OFF Yes
PAGE_VERIFY CHECKSUM para novas instalações do SQL Server

NONE para atualizações do SQL Server		 Yes
PARAMETERIZATION SIMPLES Yes
QUOTED_IDENTIFIER OFF Yes
READ_COMMITTED_SNAPSHOT OFF Não
RECOVERY SIMPLES Não
RECURSIVE_TRIGGERS OFF Yes
Opções do Service Broker ENABLE_BROKER Yes
TRUSTWORTHY OFF Não

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

tempdb no SQL do Azure

O comportamento do tempdb no Banco de Dados SQL do Azure difere do comportamento SQL Server, Instância Gerenciada de SQL do Azure e SQL Server em VMs do Azure.

tempdb no Banco de Dados SQL

Os bancos de dados em pool e individuais no Banco de Dados SQL do Azure dão suporte a tabelas temporárias globais e a procedimentos armazenados temporários globais armazenados no tempdb e que estão no escopo do nível do banco de dados. As tabelas temporárias globais e os procedimentos armazenados temporários globais são compartilhados entre todas as sessões de usuários no mesmo banco de dados. As sessões de usuário de outros bancos de dados não podem acessar tabelas temporárias globais. Para obter mais informações, consulte Tabelas temporárias globais no escopo do banco de dados (Banco de Dados SQL do Azure).

Para bancos de dados únicos, cada banco de dados único em um servidor lógico tem seu próprio tempdb. Em um pool elástico, o 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 pool.

Para bancos de dados em pool e individuais no Banco de Dados SQL do Azure, de todos os bancos de dados do sistema, somente o banco de dados master e o banco de dados tempdb são acessíveis. Para obter mais informações, confira O que é um servidor lógico no Azure?

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

tempdb na Instância Gerenciada de SQL

A Instância Gerenciada de SQL do Azure dá suporte a objetos temporários da mesma maneira que o SQL Server, onde todas as tabelas temporárias globais e procedimentos armazenados temporários globais podem ser acessados por todas as sessões de usuário dentro da mesma instância gerenciada. Da mesma forma, todos os bancos de dados do sistema estão acessíveis.

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.

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

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 podem acessar somente seus próprios objetos, a menos que recebam permissões adicionais. É possível revogar a permissão de conexão para tempdb a fim de impedir que um usuário use tempdb. Não recomendamos fazer isso porque algumas operações rotineiras exigem o uso de tempdb.

Otimizar o desempenho tempdb no SQL Server

O tamanho e o posicionamento físico do banco de dados tempdb podem afetar o desempenho de um sistema. Por exemplo, se o tamanho definido para tempdb for muito pequeno, parte da carga de processamento do sistema poderá ser elevada com o aumento automático de tempdb para o tamanho necessário, de modo a oferecer suporte à carga de trabalho toda vez que você reiniciar a instância do SQL Server.

Se possível, use a inicialização instantânea de arquivo para melhorar o desempenho das operações de aumento em arquivos de dados.

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 tempdb seja expandido com muita frequência, o que afeta o desempenho. O banco de dados tempdb deve ser definido para aumento automático, a fim de ampliar o espaço em disco para exceções não planejadas.

Os arquivos de dados devem ser de tamanho igual em cada grupo de arquivos, pois o SQL Server usa um algoritmo de preenchimento proporcional que favorece alocações em arquivos com mais espaço livre. A divisão do tempdb em vários arquivos de dados de tamanho igual fornece um alto grau de eficiência paralela em operações que usam o tempdb.

Defina o incremento do aumento de arquivo para um tamanho razoável e o defina ao mesmo incremento em todos os arquivos de dados, a fim de evitar que os arquivos de banco de dados tempdb aumentem com um valor muito pequeno. Caso o aumento do arquivo seja muito pequeno em comparação à quantidade de dados que está sendo gravada no tempdb, o tempdb talvez tenha que se expandir constantemente por meio de eventos de aumento automático. Os eventos de aumento automático afetam negativamente o desempenho.

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

 SELECT FileName = df.name,
   current_file_size_MB = df.size*1.0/128,
   max_size = CASE df.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_value =
     CASE
       WHEN df.growth = 0 THEN df.growth
       WHEN df.growth > 0 AND df.is_percent_growth = 0 THEN df.growth*1.0/128.0
       WHEN df.growth > 0 AND df.is_percent_growth = 1 THEN df.growth
     END,
   growth_increment_unit =
     CASE
       WHEN df.growth = 0 THEN 'Size is fixed.'
       WHEN df.growth > 0 AND df.is_percent_growth = 0  THEN 'Growth value is MB.'
       WHEN df.growth > 0 AND df.is_percent_growth = 1  THEN 'Growth value is a percentage.'
     END
FROM tempdb.sys.database_files AS df;
GO

Coloque o banco de dados tempdb em um subsistema de E/S rápido. Use a distribuição de disco se houver muitos discos anexados diretamente. Arquivos de dados individuais ou grupos de arquivos de dados do tempdb não precisam necessariamente estar em discos ou eixos diferentes, a menos que você também esteja com gargalos de E/S.

Coloque o banco de dados tempdb em discos diferentes dos usados pelos bancos de dados do usuário.

Observação

Embora a opção do banco de dados DELAYED_DURABILITY esteja definida como DISABLED para tempdb, o SQL Server usa confirmações lentas para liberar tempdb alterações de log no disco, já que tempdb é criada na inicialização e não precisa executar o processo de recuperação.

Melhorias de desempenho no tempdb para o SQL Server

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 Instalação adiciona vários arquivos de dados tempdb durante uma nova instalação da instância. É possível realizar essa tarefa usando o novo controle de entrada da IU na seção Configuração do Mecanismo de Banco de Dados e o parâmetro de linha de comando /SQLTEMPDBFILECOUNT. Por padrão, a instalação adiciona um número de arquivos de dados do tempdb equivalente à contagem de processadores lógicos ou a 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?!.
  • No caso do grupo de arquivos primário, a propriedade AUTOGROW_ALL_FILES é ativada e não pode ser modificada.

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.
  • Uma nova DMV sys.dm_tran_version_store_space_usage foi introduzida no SQL Server 2017 para controlar o uso de armazenamento de versão por banco de dados. Essa nova DMV será útil no monitoramento tempdb do uso do armazenamento de versão para DBAs que podem planejar tempdb proativamente o dimensionamento com base no requisito de uso do armazenamento de versão por banco de dados.
  • Novos recursos inteligentes de processamento de consultas, como junções adaptáveis e feedback de concessão de memória, reduzem os vazamentos de memória em execuções consecutivas de uma consulta, reduzindo a utilização desnecessária tempdb.

Introduzido no SQL Server 2019 (15.x)

  • A partir do SQL Server 2019 (15.x), o SQL Server não usa a opção FILE_FLAG_WRITE_THROUGH ao abrir arquivos para permitir a tempdb produtividade máxima do disco. Como tempdb é recriado na inicialização do SQL Server, essas opções não são necessárias, pois são para outros bancos de dados do sistema e bancos de dados do usuário para consistência 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.
  • Os metadados de TempDB otimizados para memória eliminam um gargalo em esperas do PAGELATCH em tempdb e desbloqueiam um novo nível de escalabilidade. Para obter mais informações, assista a esta demonstração em vídeo sobre Como (e quando): metadados do TempDB otimizados para memória. Para obter mais informações, leia monitoramento e solução de problemas de metadados tempdb otimizados para memória.
  • As atualizações de página PFS (Espaço Livre de Página Simultânea) reduzem a correção da contenção de travas em todos os bancos de dados, um problema mais comumente visto no tempdb. Essa melhoria altera a maneira como a simultaneidade é gerenciada com atualizações de PFS para que elas possam ser atualizadas em 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 arquivo tempdb tem 8 MB com um aumento automático de 64 MB. Esse comportamento é semelhante à instalação padrão do SQL Server no Windows.

Introduzido no SQL Server 2022 (16.x)

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

Historicamente, a contenção de metadados do tempdb tem sido um gargalo para a escalabilidade em muitas cargas de trabalho em execução no SQL Server. O SQL Server 2019 (15.x) apresenta um novo recurso que faz parte da família de recursos do banco de dados em memória: metadados de TempDB com otimização de memória.

Esse recurso remove efetivamente esse gargalo e desbloqueia um novo nível de escalabilidade para cargas de trabalho pesadas tempdb. No SQL Server 2019 (15.x), as tabelas do sistema envolvidas no gerenciamento dos metadados da tabela temporária podem ser movidas para tabelas com otimização de memória não duráveis e livres de travas.

Observação

Atualmente, o recurso de metadados de tempdb com otimização de memória não está disponível no Banco de Dados SQL do Azure ou no Instância Gerenciada de SQL do Azure.

Assista a este vídeo de sete minutos para ter uma visão geral de como e quando usar os metadados de TempDB com otimização de memória:

Configurar e usar metadados tempdb otimizados para memória

Para aceitar esse novo recurso, use o seguinte script:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

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 a inicialização do servidor falhar por qualquer motivo depois que você habilitar os metadados de com otimização de memória, você poderá ignorar o recurso iniciando a instância do SQL Server com configuração mínima por meio da opção de inicialização -f. Você pode desabilitar o recurso e reiniciar o SQL Server no modo normal.

Para proteger o servidor contra possíveis condições de memória insuficiente, você pode associar tempdb a um pool de recursos. Isso é feito por meio do comando ALTER SERVER, em vez das etapas que você normalmente seguiria para associar um pool de recursos a um banco de dados.

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

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

Limitações de tempdb com otimização de memória

  • A ativação/desativação do recurso não é dinâmica. Devido às alterações intrínsecas que precisam ser feitas na estrutura do tempdb, uma reinicialização é necessária para habilitar ou desabilitar o recurso.

  • 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. Transações que envolvam uma tabela com otimização de memória em um banco de dados de usuário não poderão acessar as exibições do sistema do tempdb na mesma transação. Se você tentar acessar as exibições do sistema do tempdb na mesma transação como uma tabela com otimização de memória em um banco de dados de usuário, receberá o seguinte erro:

    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.

    Exemplo:

    BEGIN TRAN;

SELECT *
FROM tempdb.sys.tables;  -----> Creates a user in-memory OLTP transaction in tempdb

INSERT INTO <user database>.<schema>.<mem-optimized table>
VALUES (1); ----> Tries to create a user in-memory OLTP transaction in the user database but will fail

COMMIT TRAN;

  • As consultas nas tabelas com otimização de memória não dão suporte a dicas de bloqueio e isolamento; portanto, as consultas nas exibições do catálogo do tempdb com otimização de memória não seguirão as dicas de bloqueio e isolamento. Assim como acontece com outras exibições do catálogo do sistema no SQL Server, todas as transações nas exibições do sistema serão feitas no isolamento READ COMMITTED (ou, neste caso, READ COMMITTED SNAPSHOT).

  • 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.

  • Devido à limitação de índices columnstore, não há suporte para uso do procedimento armazenado do sistema sp_estimate_data_compression_savings com o parâmetro de compactação de dados COLUMNSTORE ou COLUMNSTORE_ARCHIVE quando metadados de TempDB com otimização de memória estão ativados.

  • Um procedimento armazenado do sistema está disponível para fazer com que manualmente o mecanismo na memória libere memória relacionada a linhas excluídas de dados na memória que são elegíveis para coleta de lixo. Isso pode ajudar na solução de problemas de erros específicos de memória insuficiente dos metadados tempdb otimizados para memória (HkTempDB). Para obter mais informações, veja sys.sp_cdc_enable_db (Transact-SQL).

Observação

Essas limitações se aplicam apenas quando você faz referência a exibições do sistema do tempdb. Você poderá criar uma tabela temporária na mesma transação ao acessar uma tabela com otimização de memória em um banco de dados do usuário, se desejar.

Planejamento de capacidade do tempdb no SQL Server

A determinação do tamanho apropriado para o tempdb em um ambiente de produção do SQL Server depende de muitos fatores. Conforme descrito anteriormente, esses fatores incluem a carga de trabalho existente e os recursos do SQL Server que são usados.

Recomendamos que você analise a carga de trabalho existente executando as seguintes tarefas em um ambiente de teste do SQL Server:

  • Defina o crescimento automático como para o tempdb.
  • Execute consultas individuais ou arquivos de rastreamento de carga de trabalho e monitore o uso de espaço do tempdb.
  • Execute operações de manutenção de índice, como reconstrução de índices, e monitore o espaço do tempdb.
  • Use os valores de uso de espaço das etapas anteriores para prever o uso total 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 em disco em tempdb pode causar interrupções significativas no ambiente de produção do SQL Server. Isso também pode impedir os aplicativos em execução de concluir as operações. Você pode usar a exibição de gerenciamento dinâmico sys.dm_db_file_space_usage para monitorar o espaço em disco utilizado nos arquivos do tempdb.

Por exemplo, os quatro scripts de exemplo a seguir localizam a quantidade de espaço livre no tempdb, a quantidade de espaço usada pelo armazenamento de versão, a quantidade de espaço usada por objetos internos e a quantidade de espaço usada por objetos de usuário:

 -- 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 tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of 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 tempdb.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 tempdb.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 tempdb.sys.dm_db_file_space_usage;

Para monitorar a atividade de alocação ou desalocação de página no tempdb no nível da sessão ou tarefa, use as exibições de gerenciamento dinâmico sys.dm_db_session_space_usage e sys.dm_db_task_space_usage. Essas exibições podem ajudar a identificar consultas grandes, tabelas temporárias ou variáveis de tabela que estão utilizando muito espaço em disco do tempdb. Você também pode usar vários contadores para monitorar o espaço livre disponível em tempdb e os recursos que estão usando o tempdb.

Por exemplo, use o seguinte script para obter o espaço consumido tempdb por objetos internos em todas as tarefas em execução em cada sessão:

-- 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;

Use o script a seguir para localizar o espaço consumido tempdb por objetos internos na sessão atual, tanto para tarefas em execução quanto concluídas:

-- 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;

Conteúdo relacionado