Início / dba / Perguntas / 23252
Accepted
AmmarR
Asked: 2012-08-28 23:07:57 +0800 CST

Melhorar o desempenho de uma tabela de fatos

  • 772

Eu tenho uma tabela de fatos CardTransactionFact

Estrutura da Tabela

TABLE [dbo].[CardTransactionFact]
    [CardTransactionID] [int] IDENTITY(1,1) NOT NULL,
    [TransactionTerminalID] [int] NOT NULL,
    [SourceAccountTypeID] [int] NULL,
    [DestinationAccountTypeID] [int] NULL,
    [RimNo] [varchar](15) NULL,
    [CaptureCodeID] [int] NOT NULL,
    [RoutingCodeID] [int] NOT NULL,
    [ProcessingCodeID] [int] NOT NULL,
    [ActionCodeID] [int] NOT NULL,
    [NetworkCodeID] [int] NOT NULL,
    [ProductCodeID] [int] NOT NULL,
    [AcquiringCountryCodeID] [int] NOT NULL,
    [IssuingCountryCodeID] [int] NOT NULL,
    [TransactionCurrencyCodeID] [int] NOT NULL,
    [AmountBD] [decimal](18, 3) NOT NULL,
    [LocalCurrencyCodeID] [int] NOT NULL,
    [CardIssuerBank] [int] NOT NULL,
    [CardTypeID] [int] NOT NULL,
    [SuspectTransactionFlag] [char](1) NOT NULL,
    [ReversalTransactionFlag] [char](1) NOT NULL,
    [LocalTransactionDateKey] [int] NOT NULL,
    [LocalTransactionHourKey] [int] NOT NULL,
    [BBKRole] [char](1) NOT NULL,
    [AmountRangeKey] [int] NULL,
    [CustomerKey] [int] NULL

Tamanho: 11 GB Número de linhas: 56.959.828

Tornou-se muito difícil acessar esta tabela agora, um simples Select count(*) from CardTransactionFactleva horas para ser executado.

a maioria das colunas na tabela são apenas números inteiros, é por isso que não fiz nenhuma indexação.

O que você acha que devo fazer para melhorar esta tabela e aumentar a velocidade das consultas a esta tabela

  1. Se estiver indexando quais colunas devo indexar e por quê
  2. É uma boa ideia particionar a tabela
  3. Qualquer outra sugestão
sql-server-2008 index

3 respostas

  1. Best Answer

    Muitas coisas erradas aqui, felizmente muitas que podem ser consertadas.

    Problemas:

    • Você tem um monte. Boas chances de que isso seja altamente fragmentado e as páginas estejam espalhadas por todo o arquivo de dados de 82 GB. Consulte sys.dm_db_index_physical_stats para obter orientação sobre como verificar a fragmentação.
    • Você tem apenas 6 GB de memória, se tiver sorte, talvez 4 GB estejam disponíveis para o buffer pool.
    • Lendo nas entrelinhas, você está usando unidades giratórias SATA lentas.
    • Uma varredura da tabela exigirá 11 GB de E/S aleatórios nessa unidade lenta e agitará o pool de buffers completamente 3 vezes.

    Conserta:

    • Crie um índice clusterizado na tabela. CardTransactionId parece ser a única escolha sensata atualmente.
    • Você precisa desesperadamente de memória. 128 GB seria razoável para um data warehouse de 82 GB.
    • Você tem IO lamentavelmente inadequada. Os SSDs seriam a solução mais barata e rápida.

    11 GB não cabem em 6 GB, é realmente simples assim. Uma estimativa muito aproximada sugere que a tabela ocupará aproximadamente 1,5 milhão de páginas de 8 KB, o que, com 100 IOPS, levaria aproximadamente 4 horas para ser lida do disco (assumindo o pior caso, leitura 100% aleatória, sem leitura antecipada, etc.).

    • 9

  2. Substitua sua consulta

    SELECT COUNT(*) FROM CardTransactionFact

    Com Abaixo

    SELECT Rows FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_NAME('CardTransactionFact')

    Você deve ter Clustered Indexem sua Tabela. Execute DBCC CONTIG para verificar a fragmentação em sua tabela heap

    Create Clustered INDEX IX_Column on TableName(COLUMNNAME)

    Um problema que ocorre na mesa é a questão de ela ficar fragmentada. Dependendo da atividade executada, como DELETES, INSERTS e UPDATES, suas tabelas heap e tabelas clusterizadas podem se tornar fragmentadas. Muito disso depende da atividade, bem como dos valores-chave usados ​​para o índice clusterizado.

    1. Se sua tabela heap tiver apenas INSERTS ocorrendo, sua tabela não ficará fragmentada, pois apenas novos dados serão gravados.
    2. Se sua chave de índice clusterizado for sequencial, como um valor de identidade, e você tiver apenas INSERTS, novamente isso não ficará fragmentado, pois os novos dados são sempre gravados no final do índice clusterizado.
    3. Mas se sua tabela for um heap ou uma tabela agrupada e houver muitos INSERTS, UPDATES e DELETES, as páginas de dados podem ficar muito fragmentadas. Isso resulta em espaço desperdiçado, bem como em páginas de dados adicionais a serem lidas para satisfazer as consultas. Quando uma tabela é criada como um heap, o SQL Server não força onde as novas páginas de dados são gravadas. Sempre que novos dados são escritos, esses dados são sempre escritos no final da tabela ou na próxima página disponível que é atribuída a esta tabela. Quando os dados são excluídos, o espaço fica livre nas páginas de dados, mas não é reutilizado porque os novos dados são sempre gravados na próxima página disponível. Com um índice clusterizado, dependendo da chave de índice, novos registros podem ser gravados em páginas existentes onde existe espaço livre ou pode haver a necessidade de dividir uma página em várias páginas para inserir os novos dados.

    Estatísticas antes da reconstrução do índice

    insira a descrição da imagem aqui

    Execute DBCC CONTIG novamente para verificar a fragmentação em sua tabela heap

    Estatísticas após a reconstrução do índice

    Recriar consulta de índice para remover o índice 

    ALTER INDEX ALL 
ON TableName
REBUILD WITH 
(
     FILLFACTOR = 80, 
     SORT_IN_TEMPDB = ON,
     STATISTICS_NORECOMPUTE = ON
);

    insira a descrição da imagem aqui

    Referência

    • 2

  3. Tanto a indexação quanto o particionamento podem ajudar muito. Mas quais índices e como você divide as partições depende muito das consultas que você executa nelas.

    Sem índices ou particionamento, o otimizador de consulta terá que ler a tabela completa para cada consulta.

    Para a parte de particionamento, existe uma coluna lógica que você possa usar facilmente para separar os dados em várias partições? E é possível adicionar esta coluna à cláusula where da maioria das consultas?

    • 0

relate perguntas