Tabelas Particionadas – partitioned tables

Atualmente as organizações estão armazenando, cada vez mais, os dados de seus processos e mantendo-as em suas bases por mais tempo. Isso está ocorrendo porque os custos de armazenamento vem caindo ao longo do tempo, e para adequar-se às legislações ou às regras de negócios.

Tabelas que contém milhares ou milhões de registros são perfeitamente suportadas pelo SQL Server, mas quando precisamos consultar freqüentemente ou realizar manutenções em grandes tabelas, isso pode se tornar um problema, comprometendo a performance de suas operações e impactar diretamente os usuários finais.

Um recurso interessante que o SQL Server dispõe, são as tabelas particionadas (partitioned tables). O recurso consiste em dividir (horizontalmente), grandes tabelas em vários filegroups, provendo desempenho às suas queries e escalabilidade no seu armazenamento.

O SQL Server 2008 inclui um novo nível de lock scalation para tabelas particionadas, o que significa que um bloqueio pode escalar de um row ou page lock para partition lock. Enquanto que no SQL Server 2005, um bloqueio passaria, de row ou page lock, diretamente para um table lock.

Filegroups

Em SQL Server, um banco de dados possui pelo menos um filegroup, denominado PRIMARY, e é onde reside seu arquivo .mdf (primary datafile). É possível criar filegroups definidos pelo usuário (user-defined), e adicionar seus arquivos .ndf (secondary datafile).

Distribuir os arquivos em filegroups diferentes, pode ajudar a ganhar perfomance e disponibilidade, pois muitas operações podem ser realizadas de modo paralelo. Você pode fazer backups/restores e tarefas de manutenção somente nos filegroups necessários, diminuindo o tempo gasto nessas operações.

Várias partições podem residir em um filegroup, mas é altamente recomendável que você crie suas partições em filegroups diferentes, para aproveitar os benefícios descritos acima.

Para nosso exemplo, vamos criar um banco de dados, com vários filegroups e os seus arquivos.

USE master
GO

CREATE DATABASE MinhaBase
ON  PRIMARY
( NAME = 'MinhaBase_data00',
  FILENAME = 'C:\Bases\MinhaBase\MinhaBase_data00.mdf',
  SIZE = 512MB,
  FILEGROWTH = 256MB
),
FILEGROUP MinhaBaseFG01
( NAME = 'MinhaBase_data01',
  FILENAME = 'D:\Bases\MinhaBase\MinhaBase_data01.ndf',
  SIZE = 512MB,
  FILEGROWTH = 256MB
),
FILEGROUP MinhaBaseFG02
( NAME = 'MinhaBase_data02',
  FILENAME = 'D:\Bases\MinhaBase\MinhaBase_data02.ndf',
  SIZE = 512MB,
  FILEGROWTH = 256MB
),
FILEGROUP MinhaBaseFG03
( NAME = 'MinhaBase_data03',
  FILENAME = 'E:\Bases\MinhaBase\MinhaBase_data03.ndf',
  SIZE = 512MB,
  FILEGROWTH = 256MB
),
FILEGROUP MinhaBaseFG04
( NAME = 'MinhaBase_data04',
  FILENAME = 'F:\Bases\MinhaBase\MinhaBase_data04.ndf',
  SIZE = 512MB,
  FILEGROWTH = 256MB
)
LOG ON
( NAME = 'MinhaBase_log',
  FILENAME = 'C:\Bases\MinhaBase\MinhaBase_log.ldf',
  SIZE = 1MB,
  FILEGROWTH = 1MB
)
GO

No script acima, estamos criando uma base chamada MinhaBase, com um filegroup primário e nosso arquivo de log, além disso criamos mais 4 filegroups, com seus datafiles distribuídos em vários discos. Agora podemos partir para o próximo passo.

Partition Function

Para que tenhamos uma tabela particionada, nossa tabela precisa ter uma coluna que contenha valores que possam ser logicamente separados, em grupos ou em um range de dados. Essa coluna será nossa partition key, e é ela que será nossa divisão lógica entre as partições.

Um exemplo de coluna a ser utilizada é a coluna que contém a data de um pedido, numa tabela de pedidos. Nossa separação nas partições pode ser mensal, trimestral, semestral, anual, etc.

Após definirmos a coluna a ser utilizada como partition key e o número de partições, é a vez de criamos a nossa partition function. A partition function é usada para mapear os registros nas nossas partições baseado na partition key.

Vamos criar nossa partition function:

USE MinhaBase
GO

CREATE PARTITION FUNCTION pf_Pedido(DATETIME)
AS RANGE RIGHT
FOR VALUES(
   '2008-01-01 00:00:00.000',
   '2009-01-01 00:00:00.000',
   '2010-01-01 00:00:00.000'
)
GO

Estamos criando nossa partition function com quatro partições (repare que são informados apenas três valores), estas datas serão os limites de nossas partições, é ela que diz onde termina uma partição e onde começa outra.

No nosso exemplo a instrução RANGE RIGHT implica que na seguinte situação:

Partição 1 2 3 4
Valor < 2008-01-01 00:00:00.000 >= 2008-01-01 00:00:00.000 AND < 2009-01-01 00:00:00.000 >= 2009-01-01 00:00:00.000 AND < 2010-01-01 00:00:00.000 >= 2010-01-01 00:00:00.000

Se caso utilizássemos a instrução RANGE LEFT (esta é a opção default), teríamos a seguinte situação:

Partição 1 2 3 4
Valor <= 2008-01-01 00:00:00.000 > 2008-01-01 00:00:00.000 AND <= 2009-01-01 00:00:00.000 > 2009-01-01 00:00:00.000 AND <= 2010-01-01 00:00:00.000 > 2010-01-01 00:00:00.000

Partition scheme

Partition scheme mapeia as partições (criadas pela partition function), nos filegroups. É altamente recomendável que você crie uma partição para cada filegroup para obter ganhos de performance.

CREATE PARTITION SCHEME ps_Pedido
AS PARTITION pf_Pedido TO(
   MinhaBaseFG01,
   MinhaBaseFG02,
   MinhaBaseFG03,
   MinhaBaseFG04
)
GO

Com o script acima, nosso mapeamento entre partições e filegroups ficará assim:

Partição 1 2 3 4
Valor < 2008-01-01 00:00:00.000 >= 2008-01-01 00:00:00.000 AND < 2009-01-01 00:00:00.000 >= 2009-01-01 00:00:00.000 AND < 2010-01-01 00:00:00.000 >= 2010-01-01 00:00:00.000
Filegroup MinhaBaseFG01 MinhaBaseFG02 MinhaBaseFG03 MinhaBaseFG04

Criando nossa tabela e verificando as partições

Agora que temos os nossos filegroups (com seus respectivos arquivos de dados), a partition function, e a partition scheme, agora chegou o momento de criarmos a nossa tabela particionada.

CREATE TABLE Pedido(
   id INT IDENTITY,
   dataPedido DATETIME NOT NULL,
   descricao VARCHAR(100) NOT NULL
)
ON ps_Pedido(dataPedido)
GO

Vejam que o processo de criação da tabela é o mesmo que usaríamos normalmente. O que diferencia é o momento em que dizemos em qual filegroup nossa tabela residirá, que ao invés de informar o filegroup, nós passamos a nossa partition scheme e entre parênteses a nossa partition key.

Agora nós vamos inserir registros em nossa tabela com diferentes datas para a coluna dataPedido da nossa tabela.

INSERT INTO Pedido(dataPedido, descricao) VALUES('2005-05-19 15:43:51:519', 'Maçã')    --partição 1
INSERT INTO Pedido(dataPedido, descricao) VALUES('2007-12-31 10:35:43:342', 'Uva')     --partição 1
INSERT INTO Pedido(dataPedido, descricao) VALUES('2008-01-01 00:00:00:000', 'Laranja') --partição 2
INSERT INTO Pedido(dataPedido, descricao) VALUES('2008-05-15 12:23:54:000', 'Maçã')    --partição 2
INSERT INTO Pedido(dataPedido, descricao) VALUES('2009-10-29 09:05:32:192', 'Pêra')    --partição 3
INSERT INTO Pedido(dataPedido, descricao) VALUES('2010-01-01 00:00:30:000', 'Banana')  --partição 4
INSERT INTO Pedido(dataPedido, descricao) VALUES('2010-11-08 15:25:00:300', 'Uva')     --partição 4
INSERT INTO Pedido(dataPedido, descricao) VALUES('2003-07-15 08:02:40:579', 'Uva')     --partição 1
GO

Consultando a tabela Pedido. Vejam que uma tabela particionada é totalmente transparente para quem vai consultar suas tabelas.

SELECT * FROM Pedido
GO

Agora vamos consultar a sys.partitions da base MinhaBase, consultando a tabela Pedido e verificar como está a distribuição dos dados na nossa tabela:

SELECT * FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID) = 'Pedido'
GO

Veja o resultado:

sys_partitions da tabela Pedido

Veja a distribuição dos registros nas partições. Por exemplo, na partição 1 existem 3 registros e na partições 2 há 2 registros.

Observação

Tabelas particionadas são suportadas pelas edições Enterprise e Developer das versões do SQL Server 2005 e 2008. Então se você fizer um backup de uma base com tabelas particionadas, você não conseguirá restaurá-las numa edição do SQL Server Express.

Recursos

Scripts: create_partitioned_table.zip

Fonte: http://blog.caioadachi.com/?p=6