Movendo Tabelas entre Filegroups


Créditos ao meu amigo Rodrigo Fernandes (Microsoft Brasil), que utiliza estas técnicas com suas pequenas tabelas (11 TB por exmplo). O importante não é ser o cara e sim amigo dele..abraços velhinho !!!! 

Pessoal, uma das perguntas que vejo bastante é como mudar tabelas de filegroups.

Podemos criar uma nova tabela neste filegroup, passar os dados e depois dropar a tabela antiga e renomear a nova no novo filegroup.

Aqui eu mostro como fazer este processo de uma maneira mais simples e ocupando menos recursos do sql server, sendo indicado principalmente para tabelas com grande volume de dados.

Pessoal, tudo se baseia em a gente conseguir criar um indice cluster no novo filegroup. Por que ?

BOL diz :

Note: Because the leaf level of a clustered index and its data pages are the same by definition,
creating a clustered index and using the ON filegroup clause effectively moves a table from the
file on which the table was created to the new filegroup.
Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index.
It is important that the filegroup has at least 1.2 times the space required for the entire table.

Ou seja, por definição o nivel folha de um índice cluster e as suas paginas de dados são os mesmos.
Movendo o índice cluster estaremos movendo a tabela.

Pros nossos testes temos o filegroup Primary e estaremos passando pro filegroup NovoFilegroup

Vamos lá..primeiro caso

1 – Tenho uma tabela com Campo Identity e PK clustered nele.

Podemos fazer assim :

o Alter Table With Move dropa a constraint e recria a tabela no novo filegroup
sendo que depois temos que criar a constraint novamente                               

ALTER TABLE [Teste_1]
DROP CONSTRAINT [PK_Teste_1] with (move to NovoFilegroup)

ALTER TABLE [Teste_1]
WITH NOCHECK ADD CONSTRAINT [PK_Teste_1]
PRIMARY KEY CLUSTERED ([Codigo] ASC  )
ON [NovoFilegroup]
OU                               
Dropar constraint e recria-la novo filegroup
Eu particularmente acho este método mais eficiente pois teremos que recriar a constrainst de qualquer maneira.
ALTER TABLE [Teste_1]
                                DROP CONSTRAINT [PK_Teste_1]
ALTER TABLE [Teste_1]
                                WITH NOCHECK ADD CONSTRAINT [PK_Teste_1]
                                PRIMARY KEY CLUSTERED ([Codigo] ASC  )
                                ON [NovoFilegroup]           

                   
2 – Tenho uma tabela com Campo Identity sem PK ou Indice Cluster, ou seja uma heap.

Criamos um indice cluster no campo identity apontando para o novo filegroup e
depois dropamos este indice para a tabela voltar ao estado original
CREATE CLUSTERED INDEX [MeuIndiceCluster]
ON [Teste_7]([codigo])
ON [NovoFilegroup]

DROP INDEX [Teste_7].MeuIndiceCluster

3 – Tenho uma tabela com Unique Index NONCLUSTERED Sem IDentity

Adiciono um campo identity e depois crio um indice cluster nele apontando para o novo filegroup.
Logo apos dropamos o indice cluster e o campo identity
Dropamos o Unique index ([IX_Teste_2]) e logo após o criamos no novo filegroup
Assim a tabela voltará ao estado original

ALTER TABLE [Teste_2]
ADD [MinhaColunaIdentity] BIGINT IDENTITY (1, 1)

CREATE CLUSTERED INDEX MeuIndiceCluster
ON [Teste_2]([MinhaColunaIdentity])
ON [NovoFilegroup]

DROP INDEX [Teste_2].MeuIndiceCluster

ALTER TABLE [Teste_2]
DROP COLUMN [MinhaColunaIdentity]

DROP INDEX [Teste_2].[IX_Teste_2]

CREATE UNIQUE NONCLUSTERED INDEX [IX_Teste_2]
ON [Teste_2]([Codigo] ASC  )
ON [NovoFilegroup]

4 – Tenho  uma tabela com Unique Index NON CLUSTERED Com IDentity

Criamos um indice cluster neste campo identity apontando para o novo filegroup, dropamos este indice após finalizado.
Dropamos o unique index ([IX_Teste_3]) e o recriamos no novo filegroup

CREATE CLUSTERED INDEX MeuIndiceCluster
ON [Teste_3]([Codigo])
ON [NovoFilegroup]

DROP INDEX [Teste_3].MeuIndiceCluster

DROP INDEX [Teste_3].[IX_Teste_3]

CREATE UNIQUE NONCLUSTERED INDEX [IX_Teste_3]
ON [Teste_3]([Codigo] ASC  )
ON [NovoFilegroup]

5 – Tenho uma tabela com Unique Index CLUSTERED Sem IDentity

Dropamos este unique index([IX_Tabela_4]), recriamos ele apontando para o novo filegroup.

DROP INDEX [Teste_4].[IX_Tabela_4]

CREATE UNIQUE CLUSTERED INDEX [IX_Tabela_4]
ON [Teste_4]([Codigo] ASC  )
ON [NovoFilegroup]

6 – Tenho  uma tabela Unique Index CLUSTERED Com IDentity

Dropamos este unique index([IX_Teste_5]), recriamos ele apontando para o novo filegroup.

DROP INDEX [Teste_5].[IX_Teste_5]
CREATE UNIQUE CLUSTERED INDEX [IX_Teste_5]
ON [Teste_5]([Codigo] ASC  )
ON [NovoFilegroup]

É isso galerinha, sempre lembrando que o novo filegroup precisa ter 1.2 vezes a tabela de espaço livre para isso.

Abraços

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in SQL SERVER EM GERAL. Bookmark the permalink.

2 Responses to Movendo Tabelas entre Filegroups

  1. Hudson says:

    Bom dia,

    Gostei do post, mas não entendi.
    Efetuei um teste em uma tabela com 2731352 rows.
    toda a base contendo 2G estava em um MDF.
    Criei um filegroup com file1 de 50/5MB.
    movi como especificado esta tabela com 2 milhoes de registros para este file group e apaguei o indice, pois, ela não existia um indice. “heap”.
    (Sim, dentro do mesmo disco, só que em diretórios diferentes).
    o file1 passou para 235MB. Até ai tudo bem, isso indica que a tabela foi para o file1.
    Mas, efetuando uma consulta trazendo todas as tuplas de um dos
    campos…

  2. Hudson says:

    não obitive um resultado efetivo de tempo de consulta.
    Pelo contrario, ele me subiu em 2 segundos.
    Não era para ter um ganho siguinificativo de performace mesmo que seja no mesmo disco?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s