Planejamento de capacidade para tempdb

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

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

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

Í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

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)

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

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)

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)

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)