Início / dba / Perguntas / 45613
Accepted
db2
Asked: 2013-07-03 12:00:05 +0800 CST

Particionando/indexando uma tabela extremamente grande

  • 772

Estou trabalhando na indexação e particionamento de uma única tabela de data warehouse que pesa cerca de 500 GB. A tabela é um heap, tem mais de cem TEXTcolunas e a TEXT_IN_ROWopção está habilitada. Não fui eu que desenhei esta mesa e não tenho capacidade para a alterar num futuro imediato.

Fui encarregado de particioná-lo. Estamos lidando com isso usando uma cópia do banco de dados em um servidor de teste. Ele pode enviar cerca de 2 GB por segundo para as matrizes SSD RAID, portanto, a E/S não é um gargalo significativo e possui 16 núcleos (2 nós NUMA) e 64 GB de RAM.

Minha abordagem é desabilitar todos os índices não clusterizados, criar uma função de partição e esquema de partição (cerca de 12 partições, todas no grupo de PRIMARYarquivos - eles estão usando isso para permitir a manutenção contínua e fornecer mais inserções localizadas para ETL noturno, e não para distribuir I /O), crie um índice clusterizado para a tabela usando esse esquema de partição.

Estou criando o índice clusterizado e particionando a tabela da seguinte forma:

CREATE CLUSTERED INDEX CX_DailyTable ON DailyTable (LoadDate, SeqNumber) 
  WITH (SORT_IN_TEMPDB = ON) ON monthly_on_primary (LoadDate)

Obviamente, está demorando muito (3 horas até este post) e certamente não espero que seja rápido. O que me preocupa um pouco é que o tempdb agora está empurrando quase 1 TB e subindo constantemente, apesar da tabela atual ter cerca de metade desse tamanho. Os documentos do MS que li sugerem que o uso do espaço tempdb deve ser do tamanho da tabela final/índice clusterizado.

http://msdn.microsoft.com/en-us/library/ms188281.aspx

Se SORT_IN_TEMPDB for definido como ON, deve haver espaço livre suficiente em tempdb para armazenar as execuções de classificação e espaço livre suficiente no grupo de arquivos de destino para armazenar a estrutura de índice final. As execuções de classificação contêm as linhas folha do índice.

Suas estimativas estão incorretas? O tempdb está sendo usado para muito mais do que apenas as execuções de classificação? Ou a criação desse índice clusterizado está dobrando de alguma forma o tamanho da tabela? (Parece bastante improvável; é uma tabela bastante ampla e estimo que estamos obtendo de 4 a 8 bytes extras por linha, além de páginas não folha adicionando um índice clusterizado.)

sql-server sql-server-2008-r2

1 respostas

  1. Best Answer

    Minha abordagem é desabilitar todos os índices não agrupados [...] e então construir um índice agrupado para a tabela usando este esquema de partição.

    A criação de um índice clusterizado em um heap reconstrói automaticamente todos os índices não clusterizados (mesmo os desativados). Os índices não clusterizados são reconstruídos, mas não particionados . Supondo que o estado final desejado seja uma tabela clusterizada particionada com índices alinhados, reconstruir os índices não clusterizados para não alinhados é um esforço totalmente desperdiçado.

    O que me preocupa um pouco é que o tempdb agora está empurrando quase 1 TB e subindo constantemente, apesar da tabela atual ter cerca de metade desse tamanho. Os documentos do MS que li sugerem que o uso do espaço tempdb deve ser aproximadamente do tamanho da tabela final/índice clusterizado.

    A questão do espaço de classificação é muito complexa. Para entender todos os detalhes (incluindo o efeito do paralelismo), você precisa ler cuidadosamente toda uma série de postagens da equipe de processamento de consultas do SQL Server. A conversão de um heap em uma tabela clusterizada particionada com o paralelismo ativado provavelmente está bem próxima do pior caso.

    No mais básico (negligenciando a maioria das informações importantes nas postagens da equipe QP), você está pedindo ao SQL Server para executar uma consulta como:

    SELECT *
FROM DailyTable
ORDER BY
    $partition.monthly_on_primary(LoadDate),
    LoadDate,
    SeqNumber;

    Essa consulta não será executada rapidamente, independentemente de onde você escolher gravar as execuções de classificação que não cabem na memória. Acrescente a isso o trabalho de criar uma nova cópia completa de todo o conjunto de dados em conjuntos de linhas separados e o trabalho envolvido na reconstrução dos índices não clusterizados inutilmente...

    Adendo

    Há muitas considerações para fazer com que essa alteração funcione com eficiência. Os mais importantes são evitar a classificação sempre que possível e usar carga em massa paralela minimamente registrada sempre que possível.

    Os detalhes disso dependem de detalhes não contidos na pergunta, e uma solução completa está além de uma resposta aqui. No entanto, o esboço de uma abordagem que funcionou bem para mim pessoalmente no passado é:

    • Extraia os dados existentes usando bcpum arquivo por partição final
    • Elimine a tabela existente e crie a nova
    • Carregue a nova tabela usando carregamento em massa paralelo minimamente registrado

    A extração de dados por partição precisa ser solicitada em (LoadDate, SeqNumber). Idealmente, você evitaria uma operação de classificação. Se você tiver um índice não clusterizado existente em (LoadDate, SeqNumber), poderá extrair dados na ordem correta sem classificação se construir a consulta corretamente.

    Depois que os dados por partição foram extraídos para arquivos separados (isso pode ser feito em paralelo se o seu hardware permitir), a tabela de origem pode ser descartada, liberando espaço. Um novo heap particionado ou tabela clusterizada é então criado e carregado em massa com os dados pré-classificados, possivelmente também em paralelo.

    Feito corretamente, todo o processo não requer mais do que 1x o tamanho dos dados e atinge as taxas de transferência de dados mais rápidas possíveis em ambas as direções, com o mínimo de uso de log.

    • 17

relate perguntas