Início / dba / Perguntas / 49719
Accepted
paulH
Asked: 2013-09-13 03:09:43 +0800 CST

Melhore a velocidade de reconstrução do índice no servidor SQL

  • 772

Estou importando uma grande quantidade de dados para um banco de dados vazio e, antes de começar, desativei todos os índices não clusterizados não exclusivos para ver se poderia melhorar o desempenho da importação.

Agora quero reativar os índices e gostaria de saber se há algo que eu possa fazer para otimizar isso.

Existem > 100 tabelas e quase 2.000 índices a serem reconstruídos. O banco de dados tem 200 GB de tamanho.

A seção principal do script que estou executando é esta:

declare c_toggle_index cursor FORWARD_ONLY READ_ONLY for
    select  'alter index ' + QUOTENAME(i.name) + ' on ' + o.name + ' rebuild'
    from    sys.indexes as i
    Inner Join sys.objects o
    On o.object_id = i.object_id
    Where o.is_ms_shipped = 0
    And i.index_id >= 1
    and i.type > 1
    and i.is_disabled = 1

Considerei definir ONLINE=OFF para a instrução alter index, mas como os índices começam desabilitados, não tinha certeza se essa configuração teria algum efeito. Também considerei definir SORT_IN_TEMPDB = ON, mas como os arquivos tempdb estão na mesma unidade que os arquivos .mdf dos bancos de dados, presumi que também não haveria benefício em fazer isso.

Ao executar o script de reconstrução, notei que tenho muitos tipos de espera CXPACKET. Eu realmente não entendo por que isso aconteceria ou se é um problema que eu deveria procurar resolver.

Um ponto final que pode ser relevante: todo o meu servidor está atualmente inativo, exceto esta importação de dados para o banco de dados. Não há nenhuma outra atividade do usuário a ser considerada ou com a qual se preocupar; minha única preocupação é importar os dados para o banco de dados no menor tempo possível.

index sql-server-2008-r2

1 respostas

  1. Best Answer

    Alcançar o desempenho de importação ideal neste cenário requer três coisas:

    1. Inserções de tabela base minimamente registradas
    2. Compilações de índices não clusterizados minimamente registrados
    3. Evitando leituras físicas

    Registro Mínimo

    A obtenção de inserções minimamente registradas em uma tabela clusterizada vazia sem índices não clusterizados requer:

    1. Usando os modelos de recuperação de banco de dados SIMPLEouBULK_LOGGED
    2. Especificando um bloqueio de tabela e entrada ordenada (por exemplo TABLOCK, e ORDERdicas)

    Nota:

    Também é possível obter inserções minimamente registradas em uma tabela clusterizada que tenha índices não clusterizados, desde que o sinalizador de rastreamento 610 esteja habilitado. Se as inserções de índice não clusterizadas são minimamente registradas ou não, depende do plano de consulta selecionado pelo otimizador de consulta.

    Se o plano de consulta usar um iterador separado para o índice não clusterizado e o iterador tiver a DMLRequestSortpropriedade definida como true, as inserções de índice não clusterizado serão minimamente registradas, desde que as outras condições mencionadas anteriormente sejam atendidas.

    Como criar índices não clusterizados separadamente

    As vantagens de fazer isso são:

    1. As inserções de índice clusterizado podem ser minimamente registradas sem habilitar o TF 610
    2. CREATE INDEXé minimamente registrado se o modelo de recuperação não forFULL

    Evitando leituras físicas

    Idealmente, os dados a serem importados serão armazenados em uma máquina separada, ou pelo menos em um armazenamento físico separado daquele usado para hospedar o banco de dados.

    O servidor de banco de dados deve ter memória suficiente para manter a maior tabela base no cache, com sobra suficiente para operações de classificação necessárias ao criar índices não clusterizados.

    Um bom padrão é carregar rapidamente a tabela base (carregamento de índice clusterizado minimamente registrado) e, em seguida, criar todos os índices não clusterizados para essa tabela enquanto suas páginas de dados ainda estão armazenadas em cache.

    A questão descreve um processo pelo qual as tabelas base são carregadas primeiro e, em seguida, os índices não clusterizados são criados. A definição de cursor não usa uma ORDER BYcláusula para pelo menos agrupar construções de índice não clusterizadas na mesma tabela.

    O resultado provável é que as páginas de dados para tabelas diferentes são lidas repetidamente no cache e, em seguida, descartadas à medida que os índices não clusterizados são criados em uma ordem não determinística.

    O custo de leituras físicas repetidas domina completamente os benefícios do registro mínimo obtido com a criação de índices não clusterizados separadamente. Isso explica por que você descobriu que carregar tabelas com índices existentes é mais rápido (porque todos os índices não clusterizados para uma determinada tabela são mantidos antes de passar para a próxima tabela).

    Resumo

    O processo de importação deve ser reformulado para carregar em massa uma tabela por vez. Isso significa carregar a tabela e criar todos os índices não agrupados antes de passar para o próximo. A instância do SQL Server deve ter memória suficiente disponível para manter a maior tabela e executar a maior classificação de índice não clusterizado ao mesmo tempo.

    Você também pode tentar habilitar o TF 610 antes de carregar os dados em tabelas com índices não clusterizados já existentes. Isso geralmente não é tão rápido quanto o método anterior, mas pode ser rápido o suficiente.

    Consulte o seguinte para obter mais informações:

    O Guia de Desempenho de Carregamento de Dados

    Operações que podem ser minimamente registradas

    • 10

relate perguntas