locked
Transformando índice noclustered em clustered RRS feed

  • Pergunta

  • Pessoal, estou com uma pequena dúvida. Tenho uma situação, no qual preciso transformar um índices não agrupado em um índice agrupado com intenção de obter desempenho. Identifiquei os índices usando o seguinte comando:

    EXEC sp_helpindex ('table_1')

    GO

    EXEC sp_helpindex ('table_2')

    GO

    O comando retorna os seguintes resultados:

    Index_name                                                            Index_Description                                                     Index_Keys

    _dta_index_table_1_8_1547868581__K1_K2_K8  nonclustered located on PRIMARY table_1_serie, table_1_num, table_1_str
    table_1_cnv_cml_idx nonclustered located on PRIMARY table_1_CML_CNV_COD
    table_1_cnv_dthr_idx nonclustered located on PRIMARY table_1_cnv, table_1_dthr
    table_1_cnv_idx nonclustered located on PRIMARY table_1_cnv
    table_1_ctle_idx nonclustered located on PRIMARY table_1_ctle_cnv
    table_1_dt_result_idx nonclustered located on PRIMARY table_1_dt_result
    table_1_dthr_idx nonclustered located on PRIMARY table_1_dthr
    table_1_lib_pag_dthr_idx nonclustered located on PRIMARY table_1_LIB_PAG_DTHR
    table_1_nfl_idx nonclustered located on PRIMARY table_1_LIB_PAG_NFL_SERIE, table_1_LIB_PAG_NFL_NUM
    table_1_num_ext_idx nonclustered located on PRIMARY table_1_NUM_EXTERNO_ALFA
    table_1_pac_idx clustered located on PRIMARY table_1_pac, table_1_dthr
    table_1_sap_idx nonclustered located on PRIMARY table_1_SAP_COD
    table_1_str_idx nonclustered located on PRIMARY table_1_str, table_1_dthr
    table_1_w_login_idx nonclustered located on PRIMARY table_1_W_LOGIN, table_1_W_SENHA
    PK_table_1 nonclustered, unique, primary key located on PRIMARY table_1_num, table_1_serie

    Irei determinar que o Índice table_1_dthr_idx passe a ser clustered. Com isso o índice table_1_pac_idx passará a ser noclustered. Estou correto?

    A mesma situação no resultado abaixo:

    Index_name                                                                                    Index_Description                                                     Index_Keys

    _dta_index_table_2_8_129435535__K1_K2_K41_K5_K9_K10_K3  nonclustered located on PRIMARY table_2_osm_serie, table_2_osm, table_2_tipo_fatura, table_2_cod, table_2_fat_serie, table_2_fat, table_2_num
    idx_table_2_cod_ext nonclustered located on PRIMARY table_2_COD_EXTERNO
    idx2 nonclustered located on PRIMARY table_2_COD_EXTERNO
    PK_table_2 clustered, unique, primary key located on PRIMARY table_2_osm_serie, table_2_osm, table_2_num
    table_2_cml_fat_idx nonclustered located on PRIMARY table_2_CML_FAT_SERIE, table_2_CML_FAT_NUM
    table_2_cod_amo_idx nonclustered located on PRIMARY table_2_COD_AMOSTRA
    table_2_cth_idx nonclustered located on PRIMARY table_2_pac_reg, table_2_hsp_num, table_2_cth_num
    table_2_ctle_idx nonclustered located on PRIMARY table_2_CTLE_CNV
    table_2_dt_result_idx nonclustered located on PRIMARY table_2_DT_RESULT
    table_2_dthr_alter_idx nonclustered located on PRIMARY table_2_dthr_alter
    table_2_dthr_idx nonclustered located on PRIMARY table_2_dthr_exec, table_2_cod, table_2_tpcod
    table_2_dthr_ix nonclustered located on PRIMARY table_2_tpcod, table_2_cod, table_2_dthr_exec
    table_2_fat_idx nonclustered located on PRIMARY table_2_fat_serie, table_2_fat
    table_2_hon_idx nonclustered located on PRIMARY table_2_hon_seq
    table_2_lac_idx nonclustered located on PRIMARY table_2_pac_reg, table_2_hsp_num, table_2_lac_num
    table_2_mns_idx nonclustered located on PRIMARY table_2_mns_serie, table_2_mns_num
    table_2_mte_idx nonclustered located on PRIMARY table_2_mte_serie, table_2_mte_seq
    table_2_pac_lanc_idx nonclustered located on PRIMARY table_2_pac_reg, table_2_dthr_lanc
    table_2_rcl_idx nonclustered located on PRIMARY table_2_osm_serie, table_2_osm, table_2_num, table_2_tpcod, table_2_cod
    table_2_sma_idx nonclustered located on PRIMARY table_2_SMA_SERIE, table_2_SMA_NUM, table_2_ISM_SEQ

    Irei determinar que o Índice table_2_rcl_idxpasse a ser clustered. Com isso o índice PK_table_2 passará a ser noclustered. Estou correto?

    Mais duas perguntas, após a conclusão da mudança é evidente a necessidade de fazer um rebuild em todas as tabelas? A mudança a ser realizada acima é preciso apenas mudar a descrição dos índices já existentes como sugerido, sem alterações nas chaves?

    Agradeço desde já.

     
    quinta-feira, 6 de junho de 2013 14:21

Respostas

  • Boa tarde Jerfeson !

    Primeiramente voce teria que tornar os seus indices que são cluster em noncluter ja que só é permitido 1 indice cluster por tabela, o indice que esta como cluster não passara a ser noncluster automaticamente ja que dará erro quando voce tentar criar um indice cluster em uma tabela que ja tenha 1.

    Fazer ou não o rebuild é relativo, verifique como esta a sua performance após a mudança e como esta a fragmentação dos indices, os novos indices com certeza não terá a necessidade.

    Voce pode manter as chaves de relacionamento independente dos indices.


    Alexandre Matayosi Conde Mauricio.

    quinta-feira, 6 de junho de 2013 20:55

Todas as Respostas

  • Boa tarde Jerfeson !

    Primeiramente voce teria que tornar os seus indices que são cluster em noncluter ja que só é permitido 1 indice cluster por tabela, o indice que esta como cluster não passara a ser noncluster automaticamente ja que dará erro quando voce tentar criar um indice cluster em uma tabela que ja tenha 1.

    Fazer ou não o rebuild é relativo, verifique como esta a sua performance após a mudança e como esta a fragmentação dos indices, os novos indices com certeza não terá a necessidade.

    Voce pode manter as chaves de relacionamento independente dos indices.


    Alexandre Matayosi Conde Mauricio.

    quinta-feira, 6 de junho de 2013 20:55
  • Alexandre, perderei OS Relacionamentos (FKs e PKs) existentes nasTabelas? Sândalo Risco de ter um Integridade Comprometida Das Tabelas? Essas Duas Tabelas ficaria indisponíveis enquanto estivesse Sendo Feito uma Operação de Inversão Dos índices?

    Poderia explicar Melhor " Você. pode manter como Chaves de relacionamento Independente dos índices".

    ESTOU usando a Versão 8.0 do SQL Server. Poderia propor OS Comandos necessários parágrafo Transformar Todos los noclustered?

    Faço essas perguntas, pois executei os comandos:

    - Dropando uma chave primaria ALTER TABLE dbo . table_1DROP CONSTRAINT PK_table_2
    
    - incluíndo uma chave primaria Como noclustered
    ALTER TABLE dbo . table_2 ADD CONSTRAINT PK_table_2 PRIMARY KEY NONCLUSTERED 
        (PK_table_2
        ) ON [ PRIMARY ]

    E obtive o seguinte Retorno de Erro: "EXISTE UMA Referência à Restrição 'PK_table_2' na Tabela 'table_3', Restrição da chave Estrangeira 'table_3_table_2_fk.

    Seria esse o Processo correto? E porque o Erro?




    quinta-feira, 6 de junho de 2013 23:54
  • Jerferson, no seu script voce não esta somente dropando e criando indices, voce esta tambem dropando e recriando as chaves das suas tabelas com isto voce podera sim perder o relacionamento, o certo seria focar somente nos indices ja que o seu ponto não esta nos relacionamentos, seria somente drop index e create index...

    Alexandre Matayosi Conde Mauricio.

    sexta-feira, 7 de junho de 2013 14:24
  • O que acontece é o seguinte: O SQL Server impede por default que faça drop num índice clustered em PK quando o mesmo faz referência de FKs em outras tabelas. O drop do índice só ocorre quando são excluídos as FKs das outras tabelas.

    Ressalto que essa situação é apenas para os índices da table_2, pois na table_1 como o índice cluster não está para uma PK foi fácil alcançar o objetivo que era a trocar dos tipos de índices sem ter a necessidade de excluir FKs nenhuma. Pra table_2 planajei da seguinte forma:

    1 - Alterar configuração do Banco para Singler User (garantir acesso único);

    2 - Dropar a constraint FKs das tabelas referências;

    3 - Dropar a constraint PK da tabela;

    4 - Dropar o índice cluster PK_table_2;

    5 - Dropar o índice cluster table_2_rcl_idx;

    6 - Recriar a PK com tipo de índice nocluster;

    7 - Recriar as FKs das tabelas (restabelecendo as referências);

    8 - Recriar o índice table_2_rcl_idx como cluster.

    São essas as etapas que pensei para resolver o problema, existe outra sugestão ou forma de não mexer com as FKs de outras tabelas. Pesquisei e não encontrei uma forma melhor de apenas alterar table_2_rcl_idx para cluster e PK_table_2 para nocluster sem ter que excluir as FKs.

    Segue imagem acima do erro na tentativa de excluir o índice cluster da PK_table_2 sem mexer com as FKs.


    sexta-feira, 7 de junho de 2013 15:11
  • Partindo do principio que voce não vai alterar os dados em si seria um bom planjamento dropar as PKs e FKs, alterar os indices e depois recriar as PKs e FKs novamente.

    Um detalhe seria a janela de tempo que voce terá para fazer isto, ja que dependendo do tamanho das suas tabelas a criação dos indices principalmente os cluster podem levar um bom tempo.


    Alexandre Matayosi Conde Mauricio.

    sexta-feira, 7 de junho de 2013 15:17
  • Pensei numa estimativa de 2 horas para conclusão da mudança. Seria um bom tempo?

    Tamanho das tabelas:

    exec sp_spaceused 'table_1'

    name       rows       reserved           data               index_size      unused   
    table_1   6630138  8345864 KB    4055928 KB      3454824 KB    835112 KB

    exec sp_spaceused 'table_2'
    name       rows        reserved           data               index_size      unused   

    tabel_2   1313672  919576 KB    530016 KB          386712 KB    2848 KB


    sexta-feira, 7 de junho de 2013 18:15
  • Pela quantidade de registros acredito que de tempo sim, contando que sua base vai estar em single user, somente se tiver outros processamentos mais pesados em outra base ou no servidor que irão consumir grandes recursos fora isto acredito que de tempo.

    Alexandre Matayosi Conde Mauricio.

    sexta-feira, 7 de junho de 2013 18:18
  • Pronto irei agendar a execução pra esse final de semana. Na conclusão posto os resultados.
    sexta-feira, 7 de junho de 2013 19:01
  • Ok, qualquer duvida só postar.

    Alexandre Matayosi Conde Mauricio.

    sexta-feira, 7 de junho de 2013 19:05
  • Alexandre, propósito concluído com êxito. Agora estou agendando o Rebuild pra melhorar a performance, entendo que a bastante tempo não se faz esse processo no ambiente, apenas reorganização dos índices com atualizações das estatísticas. Agendei um job pra rodar 1 hora da manhã desta terça com a instrução:  

    USE [Minha_Base]
    EXEC SP_MSFOREACHTABLE @command1= "DBCC DBREINDEX ('?', '', 90) WITH NO_INFOMSGS"

    A intenção desta instrução é de executar o rebuild em todos os índices das tabelas da Base. É possível determinar quanto tempo aproximadamente a instrução permanecerá rodando até a conclusão?

    Agradecimentos pelas dicas!

    segunda-feira, 10 de junho de 2013 12:25
  • É muito dificil determinar quanto tempo vai demorar, depende principalmente do tamanho da tabela, tamanho do indice, como esta a fragmentação, etc.

    O que eu faria seria durante a madrugada rodar para as principais tabelas e no final de semana voce deixar agendado para fazer para todos e colocar um log para saber quanto tempo demora cada um e todos no final.


    Alexandre Matayosi Conde Mauricio.

    segunda-feira, 10 de junho de 2013 15:08
  • Executei essa madrugada em todas as tabelas usando o comando citado acima e levou 2 horas a execução. O que devo configurar de agora em diante é um plano de manutenção em que durante a madrugada de cada dia faça a reorganização dos índices de todas as tabelas e 1 vez por semana (madrugada de domingo) o rebuild de todos os índices nas tabelas, sendo que terei que reagendar o backup Full pra depois desse rebuild, correto?


    terça-feira, 11 de junho de 2013 11:45
  • Acredito que não va fazer diferença para o processo voce colocar o backup para rodar antes ou depois da reorganização dos indices, somente em casos onde a janela é muito apertada e pode acabar entrando no horario comercial o backup full seria melhor ja que no backup haveria concorrencia mas não o bloqueio de alguma tabela na reorganização dos indices.

    Alexandre Matayosi Conde Mauricio.

    terça-feira, 11 de junho de 2013 11:53