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.

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.

Anúncios

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

Go

Alter Database Particionamento

Add FileGroup ParticionamentoFG2

Go

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

Go

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

Go

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)

GO

É 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);

GO

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 —

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,1)

Go 100000

 

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,10)

Go 100000

 

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,2)

Go 100000

 

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,9)

Go 100000

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

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,3)

Go 100000

 

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,8)

Go 100000

 

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,4)

Go 100000

 

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,7)

Go 100000

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

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,5)

Go 100000

 

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,6)

Go 100000

Após inserir todas as faixas de valores, foi possível observar que o SQL Server realizou a distribuição dos dados de acordo com as faixas definidas em nossa função, trabalhando em conjunto com o nosso esquema.

Todo este trabalho foi feito de forma transparente, sem que fosse necessário mais algum tipo de customização, na verdade toda a regra de negócio já havia sido definida na função e esquema. Isso nos mostra o quanto podemos evoluir com este tipo de cenário, fazendo uso deste recurso fantástico em nosso ambiente de trabalho, proporcionando uma forma de trabalho rápida, inteligente, simples e prática.

Como também, é importante destacar que momento em que estivermos realizando qualquer tipo de manipulação de dados, o SQL Server estará fazendo acesso a locais distintos, arquivos distintos e conseguindo ler dados distribuídos de forma contínua e simultânea.

Outro detalhe interessante foi à forma de crescimento de nossos arquivos de dados, sendo feito de acordo com a distribuição de dados, por parte do esquema, o que também indica todo processo de análise e inteligência realizado pelo SQL Server, conforme os dados estão sendo manipulados.

Bom, acredito que este artigo com certeza vai ajudar em muito o sua dia-a-dia, principalmente no que diz respeito à particionamento de tabelas, índices, dados e Filegroups. Por outro lado, poderá servir como base para outras soluções, dentre elas a melhoria para acesso a grandes tabelas e bancos de dados.

Mais uma vez agradeço a sua visita, nos encontramos em breve.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes, bem como, Professor Titular na Fatec São Roque. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

5 comentários em “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.”

  1. Junior, como devo proceder caso minha tabela não tinha o Particionamento? Apos criar as Partition o SQL fara o processo automatico para os itens que ja continham na mesma?

    Marcelo

    Curtir

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