Particionando Tabelas

Tempo de leitura: 4 minutos

Ola galera, hoje vou falar de um assunto super bacana. No artigo de hoje veremos como funciona o particionamento de tabelas dentro do SQL Server. As vezes me perguntou, vale a pena fazer um artigo sobre assuntos que já tem de “kilo” sobre essas informações na net? Como sempre gosto de estar estudando, e essa semana falando com meu amigo Marcelo ele disse: “Que estudar sobre isso? Escrever um artigo,é a melhor maneira”. Então lá vamos nós…

Imagine que você tem uma tabela em seu banco de dados que possuem milhões de informações por ano, e os usuários estão reclamando que quando uma consulta é feita as informações demoram para ser retornadas. Analisando a olho clinico a consulta, você percebeu que 85% das consultas eram feitas em dados menor que o ano de 2010 e, as demais são realizadas em dados acima do ano de 2010.  Que dizer que para o nosso dia-a-dia usamos mais informações que estão a partir do anode de 2010

O que podemos fazer neste caso é particionar os dados em três partes. O SQL Server pode particionar tabela, index ou index view.

O SQL Server permite realizar o particionamento horizontal dos dados. Antes de particionar os dados o SQL Server necessita de algumas passos que devem ser executados:

• Criar uma partition function.
• Definie um ou mais filegroups.
• Criar uma partition scheme.

Partition Function

Define o conjunto lógico dos dados que serão distribuídos dentro da partition scheme. Cada partition function requer um nome e um tipo de dados. O tipo de dados da partição pode ser qualquer tipo de dados nativo do SQL Server exceto text, ntext,image,varbinary(max), timestamp, xml, varchar(max), clr type and tipo de dados definido pelo usuário. Colunas calculadas no SQL Server devem ter a propriedade PERSITED para ser usado em uma partition function. O código a seguir mostra como uma partição é criada:

USE TK70450

GO

CREATE PARTITION FUNCTION PF_Year (datetime)

AS RANGE LEFT

FOR VALUES (‘2010-01-01′,’2011-01-01′,’2012-01-01’)

O left define o “ponteiro” para a partição, como foi definido left a partição ficará mais o menos desta maneira:

Para verificar o range de valores da partition function você pode executar um select na view de sistema:

select * from sys.partition_range_values

where function_id = 65539

Partition Scheme

Ao contrário da partition function que é uma definição lógica, a partition scheme é uma definição física de onde os dados serão armazenados depois de particionado. A partition scheme define a estrutura de armazenamento dos dados. Para criar a partition scheme os File Groups já devem existir no seu banco de dados. Você só pode ter uma única partition scheme para uma partition function, porém, uma partition function pode ser usada em uma ou mais partition schemes. Se uma partition function tiver três “pontos” de definições lógica a partition scheme deve conter no mínimo quatro filegroups mapeados.

USE TK70450

GO

CREATE PARTITION SCHEME PS_YEAR AS PARTITION PF_YEAR TO (FG1,FG2,FG3,FG4)

O armazenamento ficará mais o menos da seguinte forma:

Obs: Os filegroups devem ser criados antes da partition scheme.

Para verificar quais as partition functions que foram criadas, realize um select na seguinte view de sistema:

USE TK70450

GO

select * from sys.partition_schemes

Particionando uma tabela.

Após os pré-requisitos serem cumpridos, a tabela pode ser particionada. Por padrão, todo objeto que você cria no seu banco de dados é armazenado no filegroup padrão. Se a clausula ON for especificada, você pode definir qual filegroup definido pelo usuário o objeto pode ser armazenado. Porque a partition scheme é apenas a definição de armazenamento é necessário apenas definir qual partition schema particionará a tabela na cláusula ON conforme abaixo:

USE TK70450

GO

IF OBJECT_ID(‘dbo.TB_OPER’) IS NOT NULL

BEGIN

      drop table dbo.TB_OPER

END

CREATE TABLE dbo.TB_OPER

(

COD_OPER Int identity(1,1)NOT NULL,

COD_CONTRATO VARCHAR(30) NOT NULL,

DT_OPER datetime NOT NULL

)ON PS_YEAR(DT_OPER)

Após a tabela ser particionada, vamos realizar algumas inserções para verificar o resultado de particionamento da  nossa tabela.

USE TK70450

GO

INSERT INTO dbo.TB_OPER(COD_CONTRATO,DT_OPER)

VALUES (‘XYZ45690′,’2009-12-25’),(‘CVB01234′,’2010-05-05’),(‘GTHTH4569′,’2011-11-15’),(‘TYUIOP789′,’2012-05-01’)

Como pode ser visto através da query abaixo, podemos analisar que o nosso objeto está “divido em quatro pedaços” que é exatamente a quantidade de filegroups que definimos dentro da partition scheme.

USE TK70450

GO

SELECT      OBJECT_NAME(object_id) AS Nom4DaTabela

            ,partition_id, partition_number,rows

FROM sys.partitions WHERE object_id = OBJECT_ID(‘dbo.TB_OPER’)

É possível ver também a quantidade de linhas que contém em cada partição em que a nossa tabela foi “dividida”.

Por hoje é só…

Espero que tenha ficado um pouco mais claro para as pessoas que estão começando agora. Tentei fazer o mais simples possível e espero ter ajudado.