locked
Manutenção de Filegroup - Index Cluster e Non Cluster RRS feed

  • Pergunta

  • Pessoal,
    preciso de algumas dicas de vcs. Eu vou fazer nesse final de semana, uma manutençao no banco de produçao que é um SQL SERVER 2005. Essa manutençao é separar os index e as tabelas do filegroup primary.

    Entao eu vou criar um filegroup por exemplo index01 e mover os index nao cluster e cluster. Sabendo que pela referencia da BOL para vc mover os index cluster é preciso dropar e recriar os index cluster(as PK e as Uniques). E os index nao cluster é possivel alterar direto pelo visual studio.

    O que está me pertubando é que os index cluster tem referencias de pais e filhos estou com muito receio de acontecer alguma consistencia na hora de voltar os index. Alguem ja passou por alguma experiencia igual a essa? Gostaria de receber algum conselho de como posso fazer essa manutençao da melhor forma possivel.
    Será que é melhor fazer um checkdb do banco para verificar as inconsistencias?

    Obrigada a todos pela atençao,
    Suzana Muito feliz
    • Tipo Alterado Suzana_Costa segunda-feira, 16 de abril de 2012 15:02
    quarta-feira, 21 de março de 2012 02:11

Respostas

  • Boa Noite,

    A separação em FILEGROUPs é bem vinda especialmente em situações onde os FILEGROUPs estão separados em discos diferentes, pois, isso sim proporciona um ganho maior de desempenho (se considerarmos que o disco é um gargalo). Entretanto, estou vendo um pequeno equívoco nesse enunciado.

    Os índices nonclustered são estruturas externas à tabela e de fato podem residir em um local separado da mesma. Entretanto, o índice clustered é a própria tabela, mas organizada de uma forma diferente, uma vez que o índice clustered dita o comportamento da ordem dos registros da tabela. Assim sendo, não faz sentido falar em separação do índice clustered e da tabela, pois, eles são em sua essência, a mesma coisa.

    Acredito que no seu caso, o fato das PKs estarem clusterizadas, provoque esse efeito nos relacionamentos, mas uma vez que você não conseguirá separá-los, seus problemas diminuem. No caso dos índices nonclustered, você pode gerar o script, mudar o filegroup no script e adicionar o parâmetro DROP_EXISTING para recriá-los sem ter de dropá-los explicitamente. No caso de Unique Constraints, será necessário dropar e recriar a constraint.

    Se for mover os índices nonclustered de lugar, eles não serão usados enquanto estiverem sendo reconstruídos (isso pode incorrer em lentidão). Se for mover o índice clustered (ou a tabela) de FILEGROUP, ela ficará indisponível até que a mudança ocorra.

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos:http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível

    quarta-feira, 21 de março de 2012 03:29
  • Bom Dia,

    - Abra o SSMS
    - Siga os passos do link http://www.plugmasters.com.br/sys/materias/571/3/SQL-Server-2005-Wizards%3A-Gera%E7%E3o-de-Scripts e gere os scripts de todos os índices
    - Gere o script apenas dos índices
    - Ao final certifique-se de que não existe nenhuma constraint associada a índices. Use a consulta abaixo para recuperar os índices da lista. Ex:
    SELECT NAME FROM sys.indexes WHERE name IN (
     SELECT NAME FROM sys.objects WHERE TYPE IN ('PK','UQ'))

    - Ao final remova da lista os índices clustered.
    - Nos índices inclua a opção WITH DROP_EXISTING (se houve o IF, apenas adicione o DROP_EXISTING). Ex: CREATE INDEX IX ON Tabela (Coluna) WITH (DROP_EXISTING=ON)

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos:http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível

    quarta-feira, 21 de março de 2012 13:40

Todas as Respostas

  • Boa Noite,

    A separação em FILEGROUPs é bem vinda especialmente em situações onde os FILEGROUPs estão separados em discos diferentes, pois, isso sim proporciona um ganho maior de desempenho (se considerarmos que o disco é um gargalo). Entretanto, estou vendo um pequeno equívoco nesse enunciado.

    Os índices nonclustered são estruturas externas à tabela e de fato podem residir em um local separado da mesma. Entretanto, o índice clustered é a própria tabela, mas organizada de uma forma diferente, uma vez que o índice clustered dita o comportamento da ordem dos registros da tabela. Assim sendo, não faz sentido falar em separação do índice clustered e da tabela, pois, eles são em sua essência, a mesma coisa.

    Acredito que no seu caso, o fato das PKs estarem clusterizadas, provoque esse efeito nos relacionamentos, mas uma vez que você não conseguirá separá-los, seus problemas diminuem. No caso dos índices nonclustered, você pode gerar o script, mudar o filegroup no script e adicionar o parâmetro DROP_EXISTING para recriá-los sem ter de dropá-los explicitamente. No caso de Unique Constraints, será necessário dropar e recriar a constraint.

    Se for mover os índices nonclustered de lugar, eles não serão usados enquanto estiverem sendo reconstruídos (isso pode incorrer em lentidão). Se for mover o índice clustered (ou a tabela) de FILEGROUP, ela ficará indisponível até que a mudança ocorra.

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos:http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível

    quarta-feira, 21 de março de 2012 03:29
  • Bom dia Gustavo,

    entendi perfeitamente. Eu precupada em mover as PK e as UKs existem tabelas gigantescas nesse banco. Então farei as mudanças de filegroup dos indexes nao clusters.

    Não sendo muito abusada, pode me dar uma orientação do que preciso fazer antes dessas mudanças? Com certeza um bkp vai rolar....

    Muito obrigada pela sua resposta 

    quarta-feira, 21 de março de 2012 12:28
  • Bom Dia,

    - Abra o SSMS
    - Siga os passos do link http://www.plugmasters.com.br/sys/materias/571/3/SQL-Server-2005-Wizards%3A-Gera%E7%E3o-de-Scripts e gere os scripts de todos os índices
    - Gere o script apenas dos índices
    - Ao final certifique-se de que não existe nenhuma constraint associada a índices. Use a consulta abaixo para recuperar os índices da lista. Ex:
    SELECT NAME FROM sys.indexes WHERE name IN (
     SELECT NAME FROM sys.objects WHERE TYPE IN ('PK','UQ'))

    - Ao final remova da lista os índices clustered.
    - Nos índices inclua a opção WITH DROP_EXISTING (se houve o IF, apenas adicione o DROP_EXISTING). Ex: CREATE INDEX IX ON Tabela (Coluna) WITH (DROP_EXISTING=ON)

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos:http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível

    quarta-feira, 21 de março de 2012 13:40
  • Olá Gustavo,

    eu fiz uma consulta na sys.index e veriquei e tenho numa tabela tres tipos de index: Que sao Non cluster, clustered, heap. Sendo que esse heap nao tem index name. Parece ser um index "fantasma" do SQL SERVER 2005 criado por ele para fazer algum de procura sei la...Desculpe pela minha falta de conhecimento. Nessa tabelao index heap esta com 7GB. Como eu devo proceder com esses indexes?

    Obrigada,

    Suzana

    quinta-feira, 22 de março de 2012 00:54
  • Olá Suzana,

    No meu canal do youtube estou prestes a disponibilizar um vídeo explicativo sobre HEAPs, índices clustered e índices nonclustered, mas ainda não deu (em breve sai eu prometo). As HEAP Tables são tabelas sem índices clustered e não influenciam sua manutenção, pois, o foco é mover os índices nonclustered apenas para que as tabelas (tendo ou não índices clustered) fiquem em um FILEGROUP diferente.

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos:http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível

    quinta-feira, 22 de março de 2012 02:21
  • Pessoal,

    continuando essa discursão...

    Eu fiz movimentação de alguns indexes (total de 71GB) para um outro filegroup em outro disco. Sendo que eu achava que iria liberar 71GB no disco primary porem isso nao ocorreu. Eu fiz shrinkdatase e mais tarde o job de reindex, que liberou somente 15GB.

    Existe alguma forma de liberar mais espaço? 

    Obrigada,

    Suzana : )

    segunda-feira, 16 de abril de 2012 14:57