Planejamento de capacidade para tempdb
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 armazenamento 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 armazenamento 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 armazenamento 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. |
|
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. |
|
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. |
|
Database Mail |
Consulte “Agente de Serviços” posteriormente nesta tabela. |
|
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. |
|
Notificações de eventos |
Consulte “Agente de Serviços” posteriormente nesta tabela. |
|
Í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. As operações simultâneas de atualização e exclusão de usuários durante operações de índice online necessitam de espaço para os registros 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 necessitam de espaço adicional em disco para criar e manter um índice de mapeamento temporário. Para obter mais informações, consulte Requisitos de espaço em disco para operações de índice DDL. |
Diretrizes especiais para índices particionados Requisitos de espaço em disco para operações de índice DDL |
Variáveis e parâmetros do tipo dados de 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 armazenamento de versão em tempdb. |
|
Vários conjuntos de resultados ativos (MARS) |
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 armazenamento de versão em tempdb. Consulte “Controle de versão de linha” posteriormente nesta tabela. |
|
Notificações de consultas |
Consulte “Agente de Serviços” posteriormente nesta tabela. |
|
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. |
|
Controle de versão de linha |
O controle de versão de linha é uma estrutura geral utilizada para oferecer suporte aos seguintes recursos:
As versões de linhas são mantidas no armazenamento de versão tempdb durante o tempo em que uma transação ativa deva acessá-las. O conteúdo do armazenamento 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 armazenamento 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 Armazenamento 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 armazenamento de versão gerados por transações que estão ativas quando o instantâneo é iniciado também devem ser mantidos. Esta é uma fórmula básica: [Tamanho do armazenamento de versão] = 2 * [Dados do armazenamento de versão gerados por minuto] * [Tempo mais longo de execução (minutos) de sua transação] |
Compreendendo níveis de isolamento com base em controle de versão de linha |
Service Broker |
O Service Broker ajuda os desenvolvedores a criar 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 Broker implicitamente. |
|
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
|
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 de 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:
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. Em vez 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ê deveria 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 páginas por tabela temporária * número médio de tabelas temporárias por procedimento * número máximo de execuções simultâneas do procedimento |
|
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 que as 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. |
|
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. |
|
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 |
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:
Defina crescimento automático para tempdb.
Execute consultas individuais ou arquivos de rastro de carga de trabalho e monitore a utilização de espaço de tempdb.
Execute operações de manutenção de índice, como recriar índices e monitore o espaço de tempdb.
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
Ficar sem 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.
Consulte também