Planejamento de capacidade para tempdb

SQL Server 2008 R2

Outras versões

Este tópico fornece diretrizes para determinar o espaço adequado em disco necessário para tempdb. Este tópico também inclui recomendações sobre como configurar tempdb para obter o desempenho ideal em um ambiente de produção e informações sobre como monitorar a utilização de espaço de tempdb.

Como utilizar tempdb

O banco de dados do sistema tempdb é um recurso global disponível a todos os usuários conectados a uma instância do SQL Server. O banco de dados tempdb é utilizado para armazenar os seguintes objetos: objetos do usuário, objetos internos e repositórios de versão.

Objetos do usuário

Os objetos do usuário são criados explicitamente pelo usuário. Esses objetos podem estar no escopo de uma sessão de usuário ou no escopo da rotina na qual o objeto é criado. Uma rotina é um procedimento armazenado, gatilho ou função definida pelo usuário. Os objetos do usuário podem ser um dos seguintes:

• Tabelas e índices definidos pelo usuário
• Índices e tabelas do sistema
• Tabelas e índices temporários globais
• Tabelas e índices temporários locais
• Variáveis de tabela
• Tabelas retornadas em funções com valor de tabela

Objetos internos

Os objetos internos são criados quando necessário pelo Mecanismo de banco de dados do SQL Server para processar instruções SQL Server. Os objetos internos são criados e posicionados dentro do escopo de uma instrução. Os objetos internos podem ser um dos seguintes:

• Tabelas de trabalho para operações de cursor ou spool e armazenamento temporário de LOB (Objeto Grande).
• Arquivos de trabalho para operações de junção de hash ou de agregado de hash.
• Resultados intermediários de classificação para operações como criar ou recriar índices (se SORT_IN_TEMPDB for especificado) ou determinadas consultas GROUP BY, ORDER BY ou UNION.

Cada objeto interno usa um mínimo de nove páginas; uma página IAM e uma extensão de oito páginas. Para obter mais informações sobre essas páginas e extensões, consulte Compreendendo páginas e extensões.

Armazenamento de versão

Um repositório de versão é uma coleção de páginas de dados que contém linhas de dados necessárias para oferecer suporte aos recursos que utilizam controle de versão de linha. Existem dois armazenamentos de versão: um repositório de versão comum e um armazenamento de versão de criação de índice online. Os armazenamentos de versão contêm o seguinte:

• Versões de linhas geradas através de transações de modificação de dados em um banco de dados que usa instantâneo ou leitura confirmada utilizando níveis de 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, vários conjuntos de resultados ativos (MARS) e gatilhos AFTER.

A tabela a seguir lista os recursos do SQL Server que criam objetos de usuário, objetos internos ou versões de linha em tempdb. Sempre que possível, são fornecidos os métodos para calcular a utilização do espaço em disco.

Recurso	Utilização de tempdb	Informações adicionais
Operações de carregamento em massa com gatilhos habilitados	As otimizações de importação em massa ficam disponíveis quando os gatilhos são habilitados. O SQL Server usa o controle de versão de linha para gatilhos que atualizam ou excluem transações. Uma cópia de cada linha excluída ou atualizada é adicionada ao armazenamento de versão. Consulte “Gatilhos” posteriormente nesta tabela.	Otimizando o desempenho de importação em massa
Consultas de expressões comuns da tabela	Podemos pensar em uma expressão comum da tabela como sendo um conjunto de resultados temporário definido no escopo de execução de uma única instrução SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW. Quando o plano de consulta para uma consulta de expressão comum da tabela usa um operador de spool para salvar os resultados intermediários de consulta , o Mecanismo de Banco de Dados cria uma tabela de trabalho em tempdb para oferecer suporte a essa operação.	Usando expressões de tabela comuns WITH common_table_expression (Transact-SQL)
Cursores	Os cursores controlados por conjuntos de chaves e os cursores estáticos usam tabelas de trabalho internas do tempdb. Os cursores controlados por conjuntos de chaves usam as tabelas de trabalho para armazenar o conjunto de chaves que identifica as linhas no cursor. Os cursores estáticos usam uma tabela de trabalho para armazenar todo o conjunto de resultados do cursor. A utilização do espaço em disco para cursores pode variar, dependendo do plano de consulta selecionado. Se o plano de consulta for o mesmo que as versões anteriores do SQL Server, a utilização do espaço em disco será aproximadamente a mesma.	Sobre como escolher um tipo de cursor
Database Mail	Consulte “Service Broker” posteriormente nesta tabela.	Database Mail
DBCC CHECKDB	DBCC CHECKDB utiliza as tabelas de trabalho do tempdb para manter os resultados intermediários e para operações de classificação. Para determinar a necessidade de espaço em disco do tempdb para a operação, execute DBCC CHECKDB WITH ESTIMATEONLY.	DBCC CHECKDB (Transact-SQL) Otimizando o desempenho de DBCC CHECKDB
Notificações de eventos	Consulte “Service Broker” posteriormente nesta tabela.	Compreendendo notificações de eventos
Índices	Quando você cria ou recria um índice (offline ou online) e define a opção SORT_IN_TEMPDB para ON, o Mecanismo de Banco de Dados utiliza o tempdb para armazenar os resultados intermediários de classificação utilizados para criar o índice. Quando for especificado SORT_IN_TEMPDB e for necessária uma classificação, o tempdb deverá ter espaço em disco suficiente para manter o maior índice somado ao espaço em disco que será igual ao valor da opção index create memory. Para obter mais informações, consulte Exemplo de espaço em disco de índice. As tabelas e os índices podem ser particionados. Para índices particionados, se for especificada a opção de índice SORT_IN_TEMPDB e o índice estiver alinhado com a tabela base, deverá haver espaço suficiente em tempdb para manter as execuções intermediárias de classificação da partição maior. Se o índice não estiver alinhado, deverá haver espaço suficiente em tempdb para manter as execuções intermediárias de classificação de todas as partições. Para obter mais informações, consulte Diretrizes especiais para índices particionados. As operações de índice online utilizam o controle de versão de linha para isolar a operação de índice dos efeitos de modificações feitas por outras transações. O controle de versão de linha remove a necessidade de solicitar bloqueios de compartilhamento de linhas que já foram lidas. Operações simultâneas de atualização e exclusão de usuários durante operações de índice online precisam de espaço para o registro de versão em tempdb. Quando as operações de índice online utilizam SORT_IN_TEMPDB e é necessária uma classificação, o tempdb também deverá ter espaço em disco adicional descrito anteriormente para resultados intermediários de classificação. As operações de índice online que criam, cancelam ou recriam um índice clusterizado também precisam de espaço adicional em disco para criar e manter um índice de mapeamento temporário. As operações CREATE e UPDATE STATISTICS podem usar tempdb para classificar o exemplo de linhas para compilação de estatísticas. Para obter mais informações, consulte Requisitos de espaço em disco para operações de índice DDL.	tempdb e criação de índice Diretrizes especiais para índices particionados Requisitos de espaço em disco para operações de índice DDL Exemplo de espaço em disco de índice Como funcionam as operações de índice online
Variáveis e parâmetros do tipo de dados LOB (Objeto Grande)	Os tipos de dados de objetos grandes são varchar(max), nvarchar(max), varbinary(max)text, ntext, image e xml. Esses tipos podem ter até 2 GB e podem ser utilizados como variáveis ou parâmetros em procedimentos armazenados, funções definidas pelo usuário, lotes ou consultas. Os parâmetros e as variáveis definidos como tipo de dados de LOB utilizam a memória principal como armazenamento se os valores forem pequenos. Entretanto, os valores grandes são armazenados no tempdb. Quando são armazenados variáveis e parâmetros LOB no tempdb, eles são tratados como objetos internos. Você pode consultar a exibição dinâmica de gerenciamento sys.dm_db_session_space_usage para informar as páginas alocadas a objetos internos para uma determinada sessão. Algumas funções intrínsecas de cadeia de caracteres, como SUBSTRING ou REPLICATE, podem exigir armazenamento intermediário temporário em tempdb quando estiverem funcionando em valores LOB. Da mesma forma, quando um nível de isolamento da transação baseada em controle da versão de linha é habilitado no banco de dados e são feitas modificações de objetos grandes, o fragmento alterado do LOB é copiado no repositório de versão em tempdb.	Usando tipos de dados de valor grande
MARS (Vários Conjuntos de Resultados Ativos)	Vários conjuntos de resultados ativos podem acontecer em uma única conexão; isso geralmente é chamado de MARS. Se uma sessão de MARS emite uma instrução de modificação de dados (como INSERT, UPDATE ou DELETE) quando há um conjunto de resultados ativo, as linhas afetadas pela instrução de modificação são armazenadas no repositório de versão em tempdb. Consulte “Controle de versão de linha” posteriormente nesta tabela.	Usando MARS (vários conjuntos de resultados ativos)
Notificações de consultas	Consulte “Service Broker” posteriormente nesta tabela.	Usando notificações de consulta
Consultas	As consultas que contêm instruções SELECT, INSERT, UPDATE e DELETE podem utilizar objetos internos para armazenar resultados intermediários para junções de hash, agregados de hash ou classificação. Quando um plano de execução de consulta é armazenado em cache, as tabelas de trabalho exigidas pelo plano são armazenadas em cache. Quando uma tabela de trabalho é armazenada em cache, a tabela é truncada e nove páginas permanecem no cache para reutilização. Isso melhora o desempenho da próxima execução da consulta. Se o sistema estiver com pouca memória, o Mecanismo de Banco de Dados poderá remover o plano de execução e cancelar as tabelas de trabalho associadas.	Reutilização e armazenamento em cache do plano de execução
Controle de versão de linha	O controle de versão de linha é uma estrutura geral utilizada para oferecer suporte aos seguintes recursos: Gatilhos MARS (Vários Conjuntos de Resultados Ativos) Operações de índice que especificam a opção ONLINE Níveis de isolamento de transação baseada em controle de versão de linha: Uma implementação nova de nível de isolamento de confirmação de leitura que utiliza o controle de versão de linha para fornecer a consistência de leitura de nível de instrução. Um nível de isolamento do instantâneo para fornecer a consistência de leitura de nível de transação. As versões de linhas são mantidas no repositório de versão tempdb durante o tempo em que uma transação ativa deva acessá-las. O conteúdo do repositório de versão atual é retornado em sys.dm_tran_version_store. As páginas de armazenamento de versão são controladas no nível de arquivo porque são recursos globais. Você pode utilizar a coluna version_store_reserved_page_count em sys.dm_db_file_space_usage para exibir o tamanho atual do armazenamento de versão. A limpeza total do armazenamento de versão deve considerar a transação mais longa em execução que requer acesso à versão particular. A transação mais longa em execução relacionada com a limpeza do repositório de versão pode ser descoberta exibindo a coluna elapsed_time_seconds em sys.dm_tran_active_snapshot_database_transactions. Os contadores Espaço Livre em tempdb (KB) e Tamanho do Repositório de Versão (KB) no objeto Transações podem ser utilizados para monitorar o tamanho e a taxa de crescimento do armazenamento de controle de versão de linha em tempdb. Para obter mais informações, consulte SQL Server, objeto de transações. Para calcular o espaço necessário em tempdb para o controle de versão de linha, primeiramente você precisa levar em consideração que uma transação ativa deve manter todas as suas alterações no armazenamento de versão. Isso significa que uma transação de instantâneo iniciada posteriormente pode acessar as versões antigas. Da mesma forma, se houver uma transação de instantâneo ativa, todos os dados de repositório de versão gerados por transações que estiverem ativas quando o instantâneo for iniciado também deverão ser mantidos. Esta é uma fórmula básica: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction]	Compreendendo níveis de isolamento com base em controle de versão de linha Uso do recurso de controle de versão de linha
Service Broker	O Service Broker ajuda os desenvolvedores a criarem aplicativos assíncronos, livremente acoplados, nos quais os componentes independentes trabalham em conjunto para realizar uma tarefa. Esses componentes de aplicativo trocam mensagens que contêm as informações necessárias para conclusão da tarefa. O Service Broker utiliza explicitamente o tempdb para preservar caixas de diálogo de contexto existentes que não podem ficar na memória. O tamanho é de aproximadamente 1 KB por caixa de diálogo. Além disso, o Service Broker utiliza implicitamente tempdb pelo cache de objetos no contexto de execução de consulta, como tabelas de trabalho utilizadas para eventos de timer e plano de fundo de conversações entregues. Database Mail, Notificações de eventos e Notificações de consulta utilizam Service Brokerimplicitamente.	Visão geral (Service Broker)
Procedimentos armazenados	O procedimentos armazenados podem criar objetos de usuário como tabelas temporárias globais ou locais e seus índices, variáveis ou parâmetros. Os objetos temporários nos procedimentos armazenados podem ser armazenados em cache para aperfeiçoar as operações que cancelam e criam tais objetos. Esse comportamento pode aumentar as exigências de espaço em disco de tempdb. São armazenadas até nove páginas por objeto temporário para reutilização. Consulte “Tabelas temporárias e variáveis de table” posteriormente nesta tabela.	Criando procedimentos armazenados (Mecanismos de Banco de Dados)
Tabelas temporárias e variáveis de table Tabelas e índices definidos pelo usuário Índices e tabelas do sistema Tabelas e índices temporários globais Tabelas e índices temporários locais Variáveis de table Tabelas retornadas em funções com valor de tabela	São armazenadas tabelas temporárias e variáveis de table em tempdb. As exigências de espaço em disco para objetos de tabela temporária são iguais às versões anteriores do SQL Server. O método para calcular o tamanho de uma tabela temporária é o mesmo utilizado para calcular o tamanho de uma tabela padrão. Para obter mais informações, consulte Estimando o tamanho de uma tabela. Uma variável table se comporta como uma variável local. Uma variável de table é do tipo table e é utilizada principalmente para o armazenamento temporário de um conjunto de linhas retornadas como o conjunto de resultados de uma função com valor de tabela. O espaço em disco exigido para manter uma variável de table depende do tamanho da variável declarada e do valor armazenado na variável. As tabelas temporárias locais e as variáveis são armazenadas em cache quando as seguintes condições são satisfeitas: Não são criadas restrições nomeadas. Não são executadas instruções DDL (linguagem de definição de dados) que afetam a tabela depois da criação da tabela temporária, como instruções CREATE INDEX ou CREATE STATISTICS. O objeto temporário não é criado utilizando o SQL dinâmico, como: sp_executesql N'create table #t(a int)'. O objeto temporário é criado dentro de outro objeto, como um procedimento de armazenamento, gatilho, função definida pelo usuário; ou é a tabela de retorno de uma função definida pelo usuário, com valor de tabela. Quando uma tabela temporária ou uma variável de table é armazenada em cache, o objeto temporário não é excluído quando seu objetivo é alcançado. Ao invés disso, o objeto temporário é truncado. Na próxima vez que o objeto de chamada é executado são armazenadas e reutilizadas até nove páginas. O armazenamento em cache permite que as operações de cancelamento e criação de objetos sejam executadas rapidamente e reduz a contenção de alocação de página. Para otimizar o desempenho, você deve calcular o espaço em disco necessário para armazenar em cache tabelas temporárias locais ou variáveis de table no tempdb utilizando a seguinte fórmula: 9 page per temp table * number of average temp tables per procedure * number of maximum simultaneous executions of the procedure	CREATE TABLE (Transact-SQL) Usando variáveis e parâmetros (Mecanismo de Banco de Dados) DECLARE @local_variable (Transact-SQL)
Gatilhos	As tabelas inseridas e excluídas utilizadas em gatilhos AFTER são criadas no tempdb. Ou seja, as linhas que são atualizadas ou excluídas pelo gatilho são controladas por versão. Isso inclui todas as linhas modificadas pela instrução que acionou o gatilho. Ou seja, as linhas inseridas pelo gatilho não são controladas por versão. Os gatilhos INSTEAD OF utilizam tempdb de modo semelhante para consultas. A utilização do espaço em disco para gatilhos INSTEAD OF é a mesma das versões anteriores do SQL Server. Consulte “Consultas” previamente nesta tabela. Quando você carrega dados em massa com gatilhos habilitados, uma cópia de cada linha excluída ou atualizada é adicionada ao armazenamento de versão.	CREATE TRIGGER (Transact-SQL) Otimizando o desempenho de importação em massa Uso do recurso de controle de versão de linha
Funções definidas pelo usuário	As funções definidas pelo usuário podem criar objetos de usuário temporários, como tabelas globais ou locais e seus índices, variáveis ou parâmetros. Por exemplo, a tabela de retorno de uma função com valor de tabela é armazenada em tempdb. Os tipos de dados permitidos para obter parâmetros e valores de retorno em funções escalares e funções com valor de tabela incluem a maioria dos tipos de dados de LOB. Por exemplo, um valor de retorno pode ser do tipo xml ou varchar(max). Consulte “Variáveis e parâmetros do tipo dados de LOB (Objeto Grande)” previamente nesta tabela. Os objetos temporários nas funções definidas pelo usuário com valor de tabela podem ser armazenados em cache para aperfeiçoar as operações que cancelam e criam tais objetos. Consulte “Tabelas temporárias e variáveis de table” previamente nesta tabela.	CREATE FUNCTION (Transact-SQL)
XML	Variáveis e parâmetros do tipo xml podem ter até 2 GB. Eles utilizam a memória principal como armazenamento contanto que os valores sejam pequenos. Entretanto, os valores grandes são armazenados no tempdb. Consulte “Variáveis e parâmetros do tipo dados de LOB (Objeto Grande)” previamente nesta tabela. O procedimento armazenado do sistema sp_xml_preparedocument cria uma tabela de trabalho em tempdb. O analisador MSXML utiliza a tabela de trabalho para armazenar o documento XML analisado. As exigências de espaço em disco para tempdb são praticamente proporcionais ao tamanho do documento XML especificado quando é executado o procedimento armazenado.	Implementando XML no SQL Server sp_xml_preparedocument (Transact-SQL) Consultando XML usando OPENXML

Planejamento de capacidade para atualizações do SQL Server

A determinação do tamanho apropriado para tempdb em um ambiente de produção depende de muitos fatores. Como previamente descrito neste tópico, esses fatores incluem a carga de trabalho existente e os recursos SQL Server utilizados. Nós recomendamos que você analise a carga de trabalho existente executando as seguintes tarefas em um ambiente de teste do SQL Server:

1. Defina crescimento automático para tempdb.
2. Execute consultas individuais ou arquivos de rastro de carga de trabalho e monitore a utilização de espaço de tempdb.
3. Execute operações de manutenção de índice, como recriar índices e monitore o espaço de tempdb.
4. Utilize os valores de utilização de espaço das etapas anteriores para prever sua utilização total de carga de trabalho; ajuste esse valor para atividades simultâneas projetadas e defina adequadamente o tamanho de tempdb.

Para obter mais informações sobre como monitorar o espaço de tempdb, consulte Solucionando problemas de espaço insuficiente em disco em tempdb. Para obter mais informações sobre como calcular a utilização de tempdb durante operações de índice, consulte Exemplo de espaço em disco de índice.

Configurando tempdb para ambientes de produção

Para obter o melhor desempenho de tempdb, siga as diretrizes e as recomendações fornecidas em Aperfeiçoando o desempenho de tempdb.

Como monitorar a utilização de tempdb

A execução fora do espaço em disco em tempdb pode causar interrupções significativas no ambiente de produção do SQL Server e pode impedir que aplicativos que estão em execução concluam as operações. Você pode utilizar a exibição dinâmica de gerenciamento sys.dm_db_file_space_usage para monitorar o espaço em disco utilizado por esses recursos nos arquivos tempdb. Além disso, para monitorar a alocação de página ou a atividade de desalocação em tempdb em nível de sessão ou tarefa, você pode utilizar as exibições dinâmicas de gerenciamento sys.dm_db_session_space_usage e sys.dm_db_task_space_usage. Essas exibições podem ser utilizadas para identificar consultas grandes, tabelas temporárias ou variáveis de tabela que estão utilizando muito espaço em disco de tempdb. Existem vários contadores que podem ser utilizados para monitorar o espaço livre disponível em tempdb e também os recursos que estão utilizando tempdb. Para obter mais informações, consulte Solucionando problemas de espaço insuficiente em disco em tempdb.