Início / dba / Perguntas / 108704
Accepted
Gabe
Asked: 2015-08-01 10:31:39 +0800 CST

Quando usar sort_in_tempdb ao reconstruir índices?

  • 772

Estamos debatendo se devemos usar a opção SORT_IN_TEMPDB para nossas tabelas DW. Meu entendimento é que há mais gravações ao usar essa opção, embora sejam mais sequenciais. Temos uma SAN (que tem sido notoriamente lenta às vezes), portanto, em nosso caso, queremos limitar o número de gravações o máximo possível. Acredito que o tempdb esteja em um LUN separado (conjunto de discos).

Temos bastante espaço em disco em nosso arquivo de dados e em nosso arquivo tempdb. Nesse caso, nos beneficiaríamos do uso de SORT_IN_TEMPDB?

Uma coisa que me impressionou foi este comentário sobre esta resposta

Ao reconstruir um índice, você precisaria do dobro do espaço do índice + 20% para a classificação. Portanto, em geral, para reconstruir todos os índices em seu banco de dados, você precisa apenas de 120% do seu maior índice em seu banco de dados. Se você usar SORT_IN_TEMPDB, você ganha apenas 20%, ainda precisa de 100% adicionais em seu arquivo de dados. Além disso, usar sort em tempdb aumenta drasticamente sua carga de E/S, pois em vez de gravar o índice uma vez no arquivo de dados, agora você o grava uma vez no tempdb e depois o grava no arquivo de dados. Então isso nem sempre é o ideal.

Definitivamente, não queremos aumentar nossa carga de E/S com nossa SAN lenta/possivelmente mal configurada.

Qual seria a melhor maneira de testar isso? Simplesmente reconstruindo a tabela com e sem a opção e logando os tempos?

Edit : Temos 8 arquivos tempdb, cada um com 15 GB. Temos os sinalizadores TF 1117/1118 definidos e o IFI está ativado. Atualmente fazemos uma mistura de reconstrução com a opção sort_in_tempdb e sem ela.

Obrigado!

SQL Server 2012 Corporativo

sql-server sql-server-2012

1 respostas

  1. Best Answer

    SORT_IN_TEMPDBsignifica que o SQL Server usará tempdbpara alocar o espaço temporário em vez de alocar espaço no banco de dados do usuário cujo índice está sendo recompilado. Isso significa que você precisará de menos espaço livre no banco de dados do usuário durante uma operação de reconstrução de índice e mais espaço livre no tempdb.

    Ele oferece uma vantagem melhor quando o tempdb está em um conjunto diferente de discos (LUNs) do banco de dados do usuário.

    Da opção SORT_IN_TEMPDB - BOL :

    Se a opção SORT_IN_TEMPDB estiver definida como ON e tempdb estiver em um conjunto separado de discos do grupo de arquivos de destino, durante a primeira fase, as leituras das páginas de dados ocorrerão em um disco diferente das gravações na área de trabalho de classificação em tempdb. Isso significa que as leituras de disco das chaves de dados geralmente continuam mais serialmente no disco e as gravações no disco tempdb também são geralmente seriais, assim como as gravações para criar o índice final. Mesmo que outros usuários estejam usando o banco de dados e acessando endereços de disco separados, o padrão geral de leituras e gravações é mais eficiente quando SORT_IN_TEMPDB é especificado do que quando não é.

    Certifique-se de ler os requisitos de espaço em disco quando SORT_IN_TEMPDB estiver ON .

    SAN lenta/possivelmente mal configurada

    Você conhece o ponto de dor. Por que você não trabalha com o administrador da SAN para corrigi-lo? SAN mal configurado e ou lento causará todos os tipos de problemas como lentidão .

    Alguns pontos importantes a serem observados:

    Qual seria a melhor maneira de testar isso?

    Sim, você precisa testá-lo analisando as estatísticas de espera ao reconstruir o índice com e sem SORT_IN_TEMPDB. Meça o tempo de execução também e, ao fazer no PROD, certifique-se de fazê-lo durante uma janela de manutenção ou menos atividade do servidor. Verifique também seus dados de leitura/gravação e latência de log .

    Não tenho certeza se você tem inicialização de arquivo instantânea , mas será beneficiado ao restaurar, durante o crescimento automático de arquivos de dados e ao criar um novo banco de dados (apenas mencionando para completar).

    • 28

relate perguntas