Criando Funções e Esquemas de Particionamento de Dados para oferecer possíveis ganhos desempenho e distribuição de dados no Microsoft SQL Server 2008 e Microsoft SQL Server 2008 R2.

Pessoal, bom dia.

Estou de volta, com mais um artigo. Hoje vou falar um pouco de Particionamento de Dados no Microsoft SQL Server 2008 e Microsoft SQL Server 2008 R2, através do Partition Function e Partition Scheme.

Recentemente venho recebendo de alguns contatos, questionamentos sobre como podemos tentar melhorar o desempenho de nossos servidores SQL Server, de uma forma, que não impacte na estrutura interna de aplicações que estão fazendo acesso aos dados.

A princípio veio a minha mente utilizar o particionamento do banco de dados em diversos Filegroups, algo que normalmente eu utilizo e consigo mensurar e observar melhoras de desempenho.

Por outro lado, também pensei em utilizar particionamento horizontal de tabelas, fazendo com o banco de dados, cresça no seu tamanho de tabelas, mas isso vai impactar diretamente na aplicação.

Então, me deparei com a possibilidade de utilizar o Particionamento de Dados através do uso de Funções e Esquemas, o que poderia aliar ainda o uso de outros Filegroups.

Muito bem, um dos cenários mais atraentes para se utilizar este tipo de técnica, consiste na identificação de tabelas que possuem um volume grande de manipulações de dados, como também, sua quantidade de registro cresce constantemente, passando de centenas de dados, milhares ou bilhões de linhas de registros em pouco tempo.

Como de costumo vou destacar um pouco das finalidades e funcionalidades destes recursos, inicialmente pela Função de Particionamento.

Partition Function (Funções de Particionamento): Cria uma função no banco de dados atual que mapeia as linhas de uma tabela ou índice em partições com base nos valores de uma coluna especificada. Usar a instrução CREATE PARTITION FUNCTION é a primeira etapa na criação de uma tabela particionada ou índice.

No momento da criação uma função de particionamento, temos a necessidade de especificar o lado, sendo ele: Left ou Right, que determinada à forma de controle para alocação do dado em uma partição.

Ao especificar qual lado de cada intervalo de valor de limite, esquerdo ou direito, os valores devem pertencer. Estamos definindo a forma com que os intervalos serão classificados pelo Mecanismo de Banco de Dados em ordem crescente da esquerda para a direita. Se não for especificado, LEFT é o padrão.

O escopo de uma função de partição está limitado ao banco de dados no qual ele for criado. No banco de dados, as funções de partição residem em um namespace separado das outras funções.

Quaisquer linhas cuja coluna de particionamento tenha valores nulos serão colocadas na partição mais à esquerda, a menos que NULL seja especificado como um valor de limite e RIGHT sejam indicados. Nesse caso, a partição mais à esquerda será uma partição vazia e os valores NULL serão colocados na partição seguinte.

Qualquer uma das permissões a seguir pode ser usada para executar CREATE PARTITION FUNCTION:

Permissão ALTER ANY DATASPACE. Essa permissão tem como padrão os membros da função de servidor fixa sysadmin e das funções de banco de dados fixas db_owner e db_ddladmin.
Permissão CONTROL ou ALTER no banco de dados no qual a função de partição está sendo criada.
Permissão CONTROL SERVER ou ALTER ANY DATABASE no servidor do banco de dados no qual a função de partição está sendo criada.

Partition Scheme (Schemas de Particionamento): Cria um esquema no banco de dados atual que mapeia as partições de uma tabela particionada ou índice para grupos de arquivos.

Uma função de partição deve primeiramente ser criada em uma instrução CREATE PARTITION FUNCTION antes de criar um esquema de partição.

Ao criarmos um Esquema de Particionamento, temos a obrigatoriedade de vincular as áreas de particionamento e alocação de dados, de acordo com os limites especificados, como faixa de valores na Função de Particionamento. Neste caso, se especificamos três limites de valores em nossa função, deveremos utilizar quatro áreas de alocação de dados em nosso esquema, para podermos armazenados todos os possíveis limites de dados.

Para criar um Esquema de particionamento, através do comando CREATE PARTITION SCHEME, devemos ter uma das seguintes permissões:

Permissão ALTER ANY DATASPACE. Esta permissão tem como padrão os membros da função de servidor fixa sysadmin e das funções de banco de dados fixas db_owner e db_ddladmin.
Permissão CONTROL ou ALTER no banco de dados no qual o esquema de partição está sendo criado.
Permissão CONTROL SERVER ou ALTER ANY DATABASE no servidor do banco de dados no qual o esquema de partição está sendo criado.

Agora que conhecemos um pouco do que uma Função de Particionamento e um Esquema de Particionamento devem realizar, vamos agora, começar a criar o nosso ambiente, para colocarmos em uso estes dois recursos.

Vale ressaltar que somente a Edição Enterprise do Microsoft SQL Server 2005 em diante tem a capacidade de trabalhar com Funções e Esquemas de Particionamento de Tabelas e Índices.

Começamos pela criação do nosso banco de dados, vou definir o nome deste Banco como Particionamento, conforme apresenta o Código 1.

— Código 1 – Criando o Banco de Dados –

Create Database Particionamento

ON PRIMARY

(NAME = Particionamento_Dados,

FILENAME = N’C:\SQL\Teste_Dados.mdf’,

SIZE = 2MB,

MAXSIZE = Unlimited,

FILEGROWTH = 10%)

LOG ON

(NAME = Particionamento_Log,

FILENAME = N’C:\SQL\Particionamento_Log.ldf’,

SIZE = 4MB,

MAXSIZE = Unlimited,

FILEGROWTH = 10%)

O próximo consiste no adicionamento de novos Filegroups (Agrupamento de Arquivos) e em seguida a criação de novos arquivos de dados, que serão utilizados em conjunto com a nossa função e esquema de particionamento. Na primeira parte do Código 2, vamos criar os dois novos Filegroups, denominados: ParticionamentoFG1 e ParticionamentoFG2.

— Código 2 – Parte I – Criação de novos Filegroups –

Alter Database Particionamento

Add FileGroup ParticionamentoFG1

Alter Database Particionamento

Add FileGroup ParticionamentoFG2

Nossos Filegroups já estão criados, falta agora vincular os nossos arquivos de dados e estes repositórios, conforme apresenta a segunda parte do Código 2.

— Código 2 – Parte II – Criação de novos arquivos de dados e vínculo com os Filegroups –

Alter Database Particionamento

Add File

(Name = Particionamento_Dados_Segundo,

FileName = ‘C:\SQL\Particionamento_Dados_Segundo.ndf’,

Size = 2MB,

MaxSize = Unlimited,

Filegrowth = 10%)

To Filegroup ParticionamentoFG1

Alter Database Particionamento

Add File

(Name = Particionamento_Dados_Terceiro,

FileName = ‘C:\SQL\Particionamento_Dados_Terceiro.ndf’,

Size = 5MB,

MaxSize = Unlimited,

Filegrowth = 10%)

To Filegroup ParticionamentoFG2

Pois bem, nossos novos arquivos de dados estão criados e vinculados com seus respectivos filegrous, vamos utilizar a Visão de Catálogo de Sistema, sys.FileGroups para obter a relação de agrupamento de arquivos definidos para o nosso Banco de Dados Particionamento. Para isso podemos executar o Código 3, conforme apresento abaixo:

— Código 3 – Consultando a relação de Filegroups –

Select * from sys.FileGroups

Após executarmos este código recebemos o seguinte resultado:

Figura 1 – Relação de Filegroups configurados para o Banco de Dados – Particionamento.

Podemos constatar e comprovar a existência dos três Filegroups alocados para este banco de dados, o próximo passo consiste justamente na parte mais importante deste artigo, onde estaremos fazendo a criação da nossa Função de Particionamento e Esquema de Particionamento de Dados.

Para isso, vou utilizar o Código 4 e Código 5, respectivamente:

— Código 4 – Criando a Função de Particionamento PF_Valores –

CREATE PARTITION FUNCTION PF_Valores (Int)

AS RANGE Left FOR VALUES (1,4,8)

É possível notar que a função de particionamento PF_Valores, possui como faixa de valores os Número 1, 4 e 8, além disso, o controle para delimitação dos valores esta definido ao Esquerda. Outro detalhe importante que estes valores serão tratados e reconhecidos como Inteiros.

Podemos confirmar a criação desta função, através da Visão de Catálogo de Sistema: Sys.Partition_Functions, através do código: Select * from sys.partition_functions

— Código 5 – Criando a Esquema de Particionamento PS_Valores –

CREATE PARTITION SCHEME PS_Valores

AS PARTITION PF_Valores

TO (ParticionamentoFG1, ParticionamentoFG1, ParticionamentoFG2, ParticionamentoFG2);

Fazendo uma rápida análise, podemos observar que o esquema de particionamento PS_Valores, esta vinculado em sua opção Partition a função PF_Valores, para as seguintes áreas de alocação de dados:

ParticionamentoFG1 e ParticionamentoFG2

Sendo assim, como a função PF_Valores tem o controle de limite de valores, a partir da Esquerda, respeitando sua faixa de valores: 1, 4 e 8. O SQL Server vai criar a seguinte estrutura para armazenamento de dados, conforme apresenta a Tabela 1:

Partition	1	2	3	4
Valores	Int <= 1	Int > 1 AND Int <= 4	Int > 4 AND col1 <=8	Int > 8

Tabela 1 – Estrutura para alocação de dados de acordo com a partição.

A partir do momento em que o vínculo entre o Esquema de Particionamento é definido com a Função de Particionamento, o SQL Server define a sua falta de distribuição de dados, conforme a Tabela 1, mas atrelando os Filegroups especificados no esquema. Com isto, teremos a distribuição de dados apresentada na Tabela 2:

Partition	1	2	3	4
Valores	Int <= 1	Int > 1 AND col1 <= 4	Int > 4 AND Int <=8	Int > 8
Filegroups	ParticionamentoFG1	ParticionamentoFG1	ParticionamentoFG2	ParticionamentoFG2
Data_Space	2	2	3	3

Tabela 2 – Estrutura para alocação de dados de acordo com a partição e filegroup.

Estamos chegando ao final, agora falta pouco para concluirmos este artigo, nesta parte final vamos criar uma tabela chamada Valores e ao invés de vincular esta tabela com um Filegroups, vamos fazer o vínculo com nosso Esquema de Particionamento PS_Valores, através da coluna Valor que é do tipo inteiro, o mesmo tipo que configuramos na criação da Função PF_Valores. Com isso o SQL Server, terá a capacidade de distribuir os dados entre os Filegroups, conforme apresenta o Código 6.

— Código 6 – Criação da Tabela Valores –

Create Table Valores

(Codigo Int Identity(1,1),

Descritivo Varchar(20) Not Null,

Valor Int Not Null)

On PS_Valores(Valor)

Nossa tabela esta criada e pronta para receber os dados, agora podemos realizar a série de comandos Inserts e observar o crescimento dos nossos arquivos de dados, vinculados de acordo com seus Filegroups.

Vamos então executar em partes do Código 7, neste código estamos realizando a inserção de massas de dados divididas em faixas de valores para forçar e apresentar o particionamento de dados. Vale ressaltar que estamos inserindo em cada porção o número de 100 mil linhas de registros, o que poderá demandar um certo tempo de processamento.

— Código 7 – Parte I – Inserção de Dados —