Filegroups

Olá pessoal!

Minha postagem hoje é para falar de um recurso que existe no SQL Server há anos luz que são os FILEGROUPS, você conhece? Você usa? Provavelmente não.

Filegroup é um recurso muito importante na administração de banco de dados e você como DBA precisa conhecer e dominar, infelizmente isso não é comum entre quem trabalha e desenvolve no SQL Server.

Já discutiu com um DBA Oracle? Eles sempre dizem: “Nós temos Tablespaces!” e é impressionante que poucos conhecem o mesmo recurso que há no SQL Server, todos os SGBDs possuem esse recurso só que com nome diferente, veja:

  • File groups = SQL Server ;
  • Tablespaces = Oracle, DB2, PostgreSQL, MySQL e outros;
  • Database Spaces = Sybase;

Então o que é FILEGROUP? Objetos de banco de dados e arquivos podem ser agrupados em filegroups para fins de alocação e administração. Além disso, podemos dizer que a utilização deste recurso pode prover um melhor desempenho em seu banco de dados.

File groups no SQL Server

Na imagem acima, podemos visualizar que o banco de dados é composto por dois filegroups e cada um agrupa dos arquivos de dados, também distribuídos em discos diferentes.

Uma configuração muito clássica utilizada por DBAs Oracle em suas bases de dados e que eu assumo no SQL Server (e tem dado certo) é criar meu banco de dados com os seguintes Filegroups:

  • FGDATA: Destinado a armazenar as tabelas do banco de dados, separando-as do Filegroup PRIMARY, se possível coloque em um disco separado para dados (e com boa performance para gravação);
  • FGINDEX: Destinado a armazenar os índices do banco de dados, se possível, coloco em um disco separado para índices (e com boa performance  para leitura);
  • FGBLOBS: Destinado a armazenar dados do tipo BLOB (Binary Large Objects), ou seja, seria para aplicações que armazenam no banco de dados informações como PDFs, EXEs, Imagens, etc. Você pode se beneficiar colocando esta informação em um disco mais barato, já que normalmente não é exigido performance para esse tipo de dado e eles são relativamente grandes;
  • Defina padrões: Defina o tamanho máximo e crescimento automático dos arquivos de dados, por exemplo, 200 GB e 100 MB respectivamente, alcançando o máximo crie um novo arquivo de dados para o filegroup, mas não deixe ele “inchado”, ou seja, evite aqueles arquivos de dados de 400 GB, 500 GB ou mais;

A estratégia é separar o máximo possível as informações em datafiles e isto só será possível com uma estratégia utilizando filegroups. A Microsoft recomenda o seguinte:

  • O Filegroup PRIMARY deve ser utilizado apenas para armazenar os objetos e tabelas internas do SQL Server;
  • Potencialize o desempenho, criando datafiles e filegroups em discos separados fisicamente. Coloque objetos que consomem fortemente espaço em diferentes filegroups;
  • Coloque diferentes tabelas usadas nos mesmos joins em diferentes filegroups. Isto irá melhorar o desempenho, por causa do paralelismo que será possível a nível de I/O (disco);
  • Coloque os indices não clusterizados em um filegroup separado das tabelas que eles pertencem;
  • O log de transação deve ficar em um disco totalmente diferente dos demais arquivos e filegroups;

A seguir alguns comandos para iniciar a utilização de Filegroups:

[sourcecode language=”sql”]
USE [master]
GO
— Criação de um novo Filegroup
ALTER DATABASE [AdventureWorksDemo] ADD FILEGROUP [FGINDICES]
GO

— Associação do arquivo de dados ao Filegroup criado – FGINDICES
ALTER DATABASE [AdventureWorksDemo]
ADD FILE ( NAME = N’ADW_FGINDICES1′, FILENAME = N’J:\DATABASES\ADW_FGINDICES1.ndf’ , SIZE = 204800KB , FILEGROWTH = 10240KB )
TO FILEGROUP [FGINDICES]
GO

— Recriação de um índice Nonclustered para o novo Filegroup
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] ( [ProductID] ASC )
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF) ON [FGINDICES]
GO

— Recriação de um índice Clustered para o novo filegroup (sendo ele a PK)
— Isso é o mesmo que mover os dados tabela para outro filegroup
— faça isso para tabelas que deseja "trocar" o filegroup, neste exemplo,
— teremos um filegroup chamado SALES para armazenar as tabelas de vendas
— Primeiro é necessário fazer um DROP na constraint
ALTER TABLE [Sales].[SalesOrderDetail] DROP CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
GO
ALTER TABLE [Sales].[SalesOrderDetail] ADD CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED
( [SalesOrderID] ASC, [SalesOrderDetailID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [FGSALES]
GO
[/sourcecode]

Informação importante: O índice clusterizado efetivamente mantém os dados da tabela, portanto, move-lo para outro filegroup é o mesmo que movimentar os dados da tabela para outro filegroup.

Espero que esta postagem possa ajudar você leitor no dia-a-dia, bem como ter explanado melhor sobre este recurso tão pouco utilizado.

Aproveite para comentar e sugerir novos assuntos.

Até a próxima pessoal!
Vithor da Silva e Silva | DBA SQL Server


Fontes

Files and Filegroups Architecture

Using Files and Filegroups

Moving Large Table to Different File Group

2 thoughts on “Filegroups”

  1. Vithor, boa noite.

    Conheci seu blog há pouco tempo e venho descobrindo muito conhecimento legal, parabéns.

    Sobre “filegroups” realmente percebi agora ao chegar no mundo MSSQL que o pessoal não adota isso como default, como em OpenEdge Progress, por exemplo, as “StorageAreas”.
    De acordo com o que percebi pelo seu vídeo, o principal motivo dessa separação em filegroups seria quando aplicado em um ambiente onde há diversidade em disco.
    Pergunto, considerando uma mesma origem de disco física (para facilitar o entendimento) ainda assim haveria algum ganho com performance? Ou apenas, por exemplo, facilitaria a organização e tarefas como backup?
    []’s
    Gabriel P. Chierentin

    1. Gabriel, agradeço imensamente o seu feedback!
      Sobre filegroups, mesmo que você tenha apenas um disco, acredito ser importante utilizar o recurso de filegroups e também de múltiplos arquivos por filegroup. O próprio Filegroup PRIMARY, se for o caso, quebre-o em diversos arquivos. Quando temos apenas um arquivão, acabamos sobrecarregando este, enquanto que vários arquivos conseguem balancear um pouco a sobrecarga.
      Fique a vontade em comentar!

Deixe uma resposta