Otimizando o desempenho de tempdb

Recomendações de tamanho e de posicionamento do tempdb

Para alcançar o desempenho ideal do tempdb, nós recomendamos a seguinte configuração para o tempdb em um ambiente de produção:

• Defina o modelo de recuperação do tempdb como SIMPLE. Esse modelo exige automaticamente espaço de log para manter exigências de pouco espaço.

  Para obter mais informações, consulte ALTER DATABASE (Transact-SQL) ou Como exibir ou alterar o modelo de recuperação de um banco de dados (SQL Server Management Studio).

• Permita que os arquivos do tempdb cresçam automaticamente conforme necessário. Isso permite que o arquivo cresça até que o disco fique cheio.

  Observação
  Se o ambiente de produção não puder tolerar o potencial de tempos limite do aplicativo que podem ocorrer durante as operações de crescimento automático, aloque espaço antecipadamente para permitir a carga de trabalho esperada.

• Defina o incremento de crescimento do arquivo com um tamanho razoável para evitar que os arquivos do banco de dados tempdb cresçam com um valor muito pequeno. Se o crescimento do arquivo for muito pequeno, comparado à quantidade de dados que está sendo gravada no tempdb, o tempdb talvez tenha que se expandir constantemente. Isso afetará o desempenho. Nós recomendamos as diretivas gerais a seguir para definição do incremento de FILEGROWTH para os arquivos do tempdb.

  Tamanho do arquivo do tempdb	Incremento de FILEGROWTH
  0 a 100 MB	10 MB
  100 a 200 MB	20 MB
  200 MB ou mais	10%*

  * Talvez você tenha que ajustar essa porcentagem com base na velocidade do subsistema de E/S em que os arquivos do tempdb estão localizados. Para evitar tempos limite de trava em potencial, recomendamos a limitação da operação de crescimento automático para aproximadamente dois minutos. Por exemplo, se o subsistema de E/S puder inicializar um arquivo a 50 MB por segundo, o incremento de FILEGROWTH deverá ser definido com um máximo de 6 GB, independentemente do tamanho do arquivo do tempdb. Se possível, use a inicialização instantânea de arquivo do banco de dados para melhorar o desempenho das operações de crescimento automático.

• Aloque espaço antecipadamente para todos os arquivos do tempdb definindo o tamanho de arquivo com um valor grande o bastante para acomodar a carga de trabalho comum no ambiente. Isso impede que o tempdb seja expandido muito freqüentemente, o que pode afetar o desempenho. O banco de dados tempdb deve ser definido como crescimento automático, mas isso deve ser usado para aumentar o espaço em disco para exceções não planejadas.

• Crie quantos arquivos forem necessários para maximizar a largura da banda do disco. O uso de vários arquivos reduz a contenção de armazenamento do tempdb e traz uma escalabilidade significativamente maior. Porém, não crie muitos arquivos porque isso pode reduzir o desempenho e aumentar a sobrecarga de gerenciamento. Como uma diretriz geral, crie um arquivo de dados para cada CPU no servidor (contabilizando qualquer configuração de affinity mask) e, depois, ajuste o número de arquivos para cima ou para baixo, conforme necessário. Observe que uma CPU de núcleo dual é considerada como sendo duas CPUs

• Faça com que cada arquivo de dados tenha o mesmo tamanho; isso permite o desempenho de preenchimento proporcional ideal.

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

• Coloque o banco de dados tempdb em discos diferentes dos usados por bancos de dados de usuários.

Modificando os parâmetros de tamanho e crescimento do tempdb

Você pode modificar os parâmetros de tamanho e crescimento dos arquivos de dados ou de log do tempdb usando um dos seguintes métodos:

• Instrução ALTER DATABASE

• SQL Server Management Studio

Os valores dos parâmetros de tamanho e crescimento de arquivo são usados sempre que o tempdb é criado. Por exemplo, se você aumentar o tamanho do arquivo de dados do tempdb para 20 MB e aumentar o incremento de crescimento do arquivo para 15%, os novos valores entrarão em vigor imediatamente. Se atividades transacionais subseqüentes fizerem com que o tamanho de tempdb cresça, o arquivo de dados retornará ao tamanho de 20 MB cada vez que você reiniciar a instância de SQL Server.

Exibindo os parâmetros de tamanho e crescimento do tempdb

Você pode exibir os parâmetros de tamanho e crescimento dos arquivos de dados ou de log do tempdb usando um dos seguintes métodos:

• SQL Server Management Studio

• Executando a seguinte consulta.

  SELECT 
      name AS FileName, 
      size*1.0/128 AS FileSizeinMB,
      CASE max_size 
          WHEN 0 THEN 'Autogrowth is off.'
          WHEN -1 THEN 'Autogrowth is on.'
          ELSE 'Log file will grow to a maximum size of 2 TB.'
      END,
      growth AS 'GrowthValue',
      'GrowthIncrement' = 
          CASE
              WHEN growth = 0 THEN 'Size is fixed and will not grow.'
              WHEN growth > 0 AND is_percent_growth = 0 
                  THEN 'Growth value is in 8-KB pages.'
              ELSE 'Growth value is a percentage.'
          END
  FROM tempdb.sys.database_files;
  GO
  

Detectando erros de caminho de E/S de disco

Quando definida para CHECKSUM, a opção PAGE_VERIFY descobre páginas de banco de dados danificadas causadas por erros de caminho de E/S de disco e reporta esses erros, como MSSQLSERVER_823, MSSQLSERVER_824 ou MSSQLSERVER_825 no log de erros do SQL. Os erros de caminho de E/S do disco podem ser a causa de problemas de corrupção de banco de dados e, em geral, são causados por quedas de energia ou falhas no disco que ocorrem no momento da gravação da página no disco. Para obter mais informações sobre erros de E/S, consulte os Fundamentos de E/S do Microsoft SQL Server, Capítulo 2.

Nas versões anteriores do SQL Server, a opção de banco de dados PAGE_VERIFY é definida como NONE para o banco de dados tempdb e não pode ser modificada. No SQL Server 2008, o valor padrão do banco de dados tempdb é CHECKSUM para instalações novas do SQL Server. Quando você atualiza uma instalação do SQL Server, o valor padrão permanece CHECKSUM . É recomendável definir a opção de PAGE_VERIFY do banco de dados tempdb como CHECKSUM.

Consulte também

Tarefas

Referência

Conceitos

Outros recursos