Partilhar via

  • Artigo

Otimizando o desempenho de tempdb

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 crescimento automático de tempdb para o tamanho necessário para oferecer suporte à carga de trabalho toda vez que você reiniciar a instância do SQL Server. Você pode evitar essa sobrecarga aumentando os tamanhos dos dados e dos arquivo de log do tempdb. Para obter informações sobre como determinar a quantidade apropriada de espaço em disco necessária para tempdb, consulte Planejamento de capacidade para 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çãoObservaçã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:

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.