Filegroup no SQL Server

Beeeeeleza Galera !!!!!

Neste post, vamos falar de algo mais simples, porem um assunto que gera muitas duvidas, o destemido FILEGROUP.

O que é? Porque e quando utilizar?Picture1

Bom conceitualmente, FILEGROUP (FG), é uma estrutura lógica que mapeia o banco de dados e seus objetos relacionando com os data files.

Segundo o MSDN é:

“Every database has a primary filegroup. This filegroup contains the primary data file and any secondary files that are not put into other filegroups. User-defined filegroups can be created to group data files together for administrative, data allocation, and placement purposes.”

Como assim? Quando criamos o FG informamos quais arquivos de dados estarão associados a ele, certo? Por exemplo, o filegroup FG_TableBIG está associado aos arquivos de dados D:\bdteste02.ndf e F:\bdteste03.ndf

Blz Luiz, mas para que serve o FILEGROUP na pratica?

Quando criamos um objeto, seja ele uma Table, Procedure, Index, etc, ele é associado explicitamente ou implicitamente a um FILEGROUP que determina em quais data files aquele objeto estará fisicamente alocado.

Por exemplo, usando o case anterior, o FILEGROUP FG_TableBIG tem dois data files D:\bdteste02.ndf e F:\bdteste03.ndf certo?

Ao criarmos a tabela dbo.Cliente associando ao FG_TableBIG estaremos dizendo que este objeto estará alocado nos arquivos D:\bdteste02.ndf e F:\bdteste03.ndf, ou seja, toda informação inserida nesta tabela estará fisicamente nesses diretórios.

A distribuição dos dados entre os arquivos é realizada pela engine SQL Server com base no algorítimo de Proportional Fill, que é assunto para outro artigo, porem, podemos adiantar que esse algorítimo busca utilizar os dois arquivos proporcionalmente iguais, dividindo a carga de I/O e quantidade ocupada, porem, para isso acontecer tem uma série de observações referente a configuração dos data files, que irei abordar em um próximo artigo.

A imagem a seguir, exemplifica a associação entre FILEGROUP, Data Files e Objetos:

Por padrão, todo banco de dados possui o FILEGROUP PRIMARY e também por padrão todos os objetos de sistema estão mapeados neste FILEGROUP. No SQL Server 2014 até 32,767 FILEGROUP podem ser criados por banco de dados.

Por default, o data file MDF está associado ao FILEGROUP PRIMARY, com isso, é uma boa pratica criar um novo FILEGROUP associando a um data file NDF para separação de objetos de sistemas e usuários.

Vamos a pratica:

1) Criando banco de dados com mais de um data file e associando a outro Filegroup:
CREATE DATABASE [DB01]
ON PRIMARY
(NAME = N’DB01‘, FILENAME = N’D:\DB01.mdf’ , SIZE = 1048576KB , FILEGROWTH = 1024KB ),
FILEGROUP [FG_TableBIG]
(NAME = N’DB01_02‘, FILENAME = NE:\DB01_02.ndf’ , SIZE = 1048576KB , FILEGROWTH = 1024KB )
LOG ON
(NAME = ‘, FILENAME = N’L:\DB01_log.ldf’ , SIZE = 1048576KB , FILEGROWTH = 10%)
GO

Podemos observar que existem dois FILEGROUP PRIMARY e FG_TableBIG e cada um contem seus respectivos arquivos de dados.

2) Adicionando um novo data file, utilizando um file group já existente no banco de dados:
ALTER DATABASE [DB01]
ADD FILE ( NAME = N’DB01_03‘, FILENAME = N’E:\DB01_03.ndf’,
SIZE = 1048576KB , FILEGROWTH = 1024KB )
TO FILEGROUP [FG_TableBIG]
GO

Ao executar esse script, adicionamos mais um arquivos de dados, associado ao FILEGROUP [FG_TableBIG]

3) Criando tabela e associando ao filegroup [FG_TableBIG]:
USE DB01
GO

CREATE TABLE Table01
(
ID INT IDENTITY PRIMARY KEY ,
Nome VARCHAR(50) NOT NULL,
DTNascimento DATETIME NOT NULL
)ON FG_TABLEBIG

Observe, que ao final do CREATE apareceu um comando novo, o ON, é neste ponto que especificamos qual será o FILEGROUP para a tabela. O que acontece se não especificarmos o FILEGROUP? A tabela será criada no FILEGROUP default, que neste exemplo será o PRIMARY.

4) Listando todos os objetos mapeados em cada Filegroup:
SELECT 
name = left(o.name,30),
filegroup = s.groupname
FROM sysfilegroups S, sysindexes I, sysobjects O
WHERE I.indid < 2
AND I.groupid = S.groupid
AND I.id = O.id

O result deste select irá retornar todos os objetos contidos nos FILEGROUPs FG_TableBig e PRIMARY. Note que no primeiro, existe somente a tabela TB01 e no segundo todos os objetos de sistema do SQL Server para este banco de dados.

Quando é interessante criar mais de um Filegroup por database?
Como dito anteriormente por padrão o banco de dados vem com o filegroup PRIMARY e implicitamente todos os objetos de sistemas estão mapeados nele. É sempre uma boa pratica dividir objetos de sistemas de objetos de usuários (objetos criados posteriormente), por que Luiz? Primeiro pela organização do seu database, sendo assim também é uma boa pratica criar um Filegroup para Índices, alem de estarmos mantendo o Banco de dados estruturado da melhor forma possível, teremos os ganhos em I/O.
Em segundo, tem a questão da utilização do algorítimo de Proportional Fill que possibilita ganhos extremamente eficientes no quesito de performance.

Alem disso, existe a questão das contensão da paginas GAM e SGAM. O que também possibilita ganhos de performance. Mais sobre os conceitos de GAM, SGAM, aqui, aqui e aqui.

Alem dos ganhos de performance mencionados, também existe o lado da Recuperação em caso de desastre, realizando o restore de FILEGROUP separadamente.
E por fim, organizando e auxiliando na administração do banco de dados.

Qualquer duvida ou mais material sobre o assunto .. envie e-mail (luizh.rosario@gmail.com) ou comente no post.

Abraços…

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s