Data

11 abr, 2017

Trabalhando com tabelas particionadas

100 visualizações
Publicidade

Particionamento de tabelas e índices é uma solução elegante para se administrar tabelas com grandes volumes de dados. Em muitos casos, ele pode melhorar a performance de operações específicas, desde que seja planejado adequadamente.

Em termos lógicos, a tabela continua funcionando como um único objeto. As consultas continuam apontando para o nome da tabela e o SQL identifica quais partições devem ser envolvidas.

Como funciona

O particionamento descrito neste artigo é aquele que afeta individualmente tabelas e/ou índices, também conhecido como particionamento horizontal de tabelas. É um processo para segregar dados de tabelas específicas em várias unidades físicas menores.

Ele é diferente da segmentação de dados em datafiles diferentes, pois neste caso não temos controle de quais datafiles serão usados para armazenar dados específicos. O particionamento nos permite definir regras para que os dados sejam gravados em filegroups diferentes, com base nos valores de um ou mais campos da tabela.

Este recurso é bastante útil, mas não deve ser confundido com o sharding, comum em bancos que funcionam na nuvem, como o próprio AZURE SQL. Sharding envolve a criação de uma infraestrutura para que o banco todo funcione de forma “horizontal” e divida a carga de processamento entre máquinas diferentes.

Benefícios

Particionamento permite, por exemplo:

  • Melhorar performance de consultas que acessem dados de uma única partição;
  • Melhorar a performance da importação de grandes volumes de dados;
  • Reduzir tempo de execução de tarefas administrativas como reindexação, backup e restore, que são executadas na partição ao invés da tabela toda;
  • Facilitar operações de arquivamento de dados.

Uma vez definidas as regras de particionamento de cada tabela, a administração das partições é um processo simples. Portanto, é uma opção interessante a se considerar quando trabalhar com tabelas muito grandes (eu diria da ordem de 100Gb ou mais).

Criando partições

Vejamos um exemplo prático. Temos uma tabela com dados históricos que vem apresentando problemas, particularmente em razão do seu tamanho.

A tabela contém histórico de transações de muitos anos, mas a grande maioria das pesquisas envolvendo esta tabela trata das transações do ano corrente. Mesmo assim, espera-se que esta tabela continue a ser usada pelo menos nos próximos 10 anos.

Tendo isso em mente, decidiu-se criar um particionamento da tabela com base na data da transação. Implementaremos estas ações do modo mais didático possível, desconsiderando o fato de que este processo eventualmente envolveria um tempo maior de parada da aplicação.

Em primeiro lugar, vamos renomear a tabela existente, visto que a tabela particionada que criaremos precisa ter o mesmo nome da tabela antiga, assim não será necessária nenhuma alteração na aplicação.

SP_RENAME 'dbo.TabelaGigante', 'TabelaAntiga'
GO

Agora devemos criar a estrutura do particionamento, isso é:

  • Os filegroups que serão usados no particionamento;
  • Os datafiles correspondentes;
  • A função e o esquema de particionamento;
  • A nova tabela particionada.

O script a seguir executa todas estas ações:

  1. Cria filegroups e datafiles usados em cada partição;
  2. Associa partições e filegroups;
  3. Cria uma tabela particionada.

(A listagem abaixo omite alguns comandos para não se tornar extensa demais. Para o script completo, clique neste linkPartition (.zip)

 

USE MASTER
GO

ALTER DATABASE MeuBD ADD FILEGROUP fg2005
# veja script completo no arquivo anexo.
ALTER DATABASE MeuBD ADD FILEGROUP fg2027
GO

ALTER DATABASE MeuBD ADD FILE ( NAME = N'p2005_1', FILENAME = N'G:\SSD1\Data\p2005_1.ndf' , 
  SIZE = 100MB , MAXSIZE = 32GB , FILEGROWTH = 100MB ) TO FILEGROUP [fg2005]
# veja script completo no arquivo anexo.
ALTER DATABASE MeuBD ADD FILE ( NAME = N'p2027_1', FILENAME = N'G:\SSD3\Data\p2027_1.ndf' ,
  SIZE = 100MB , MAXSIZE = 32GB , FILEGROWTH = 100MB ) TO FILEGROUP [fg2027]
GO


	
USE MeuBD
GO
CREATE PARTITION FUNCTION [fnParticionamentoPorAno](SMALLDATETIME) AS RANGE RIGHT FOR VALUES 
	(
	  --NAO ESPEFIQUE O PRIMEIRO INTERVALO (01/jan a 31/dez de 2005)
	  '20060101', '20070101', '20080101', '20090101'	, '20100101', '20110101'
	, '20120101', '20130101', '20140101', '20150101'	, '20160101', '20170101'
	, '20180101', '20190101', '20200101', '20210101'	, '20220101', '20230101'
	, '20240101', '20250101', '20260101', '20270101'
	)
GO
 

CREATE PARTITION SCHEME [schParticionamentoPorAno]
AS PARTITION [fnParticionamentoPorAno]
TO (
	  fg2005
	, fg2006, fg2007, fg2008, fg2009, fg2010, fg2011 
	, fg2012, fg2013, fg2014, fg2015, fg2016, fg2017
	, fg2018, fg2019, fg2020, fg2021, fg2022, fg2023
	, fg2024, fg2025, fg2026, fg2027
	)
GO


CREATE TABLE dbo.TabelaGigante
	(
	id      	INTEGER IDENTITY (1,1) ,
	dtTransacao   	SMALLDATETIME NOT NULL,
	fkProduto   	INTEGER NOT NULL,
	fkBrick 	INTEGER NOT NULL,
	fkLinha 	INTEGER NOT NULL,
	UNIDADES    	INTEGER NOT NULL,
	VALORREAL   	MONEY NOT NULL 
CONSTRAINT pkTbGiga PRIMARY KEY CLUSTERED (id, dtTransacao)
) ON schParticionamentoPorAno (dtTransacao)
GO

Resta agora mover os dados da tabela antiga para a tabela particionada. Essa é uma operação que pode tomar muito tempo e é preciso um bom planejamento antes de executá-la. Como nosso objetivo aqui é apenas didático, executaremos uma operação de INSERT, como mostrado a seguir.

INSERT INTO dbo.TabelaGigante (
	  dtTransacao
	, fkProduto
	, fkBrick 
	, fkLinha
	, UNIDADES
	, VALORREAL
	)
SELECT *
FROM dbo.TabelaAntiga

Para verificar o resultado final de todo processo, temos a seguir duas consultas que comparam a distribuição de dados entre partições e a quantidade de dados por data de transação.

-- tabela antiga (dbo.TabelaAntiga)
SELECT YEAR(dtTransacao) AS ANO, COUNT(*) AS NumeroRegistros
FROM dbo.TabelaAntiga
GROUP BY YEAR(dtTransacao)
ORDER BY 1

ANO	NumeroRegistros
-----------------------
2005	1080615
2006	1040577
2010	1080615
2011	1040577


-- tabela particionada (dbo.TabelaGigante)
SELECT 
	  OBJECT_NAME(p.object_id) AS  Tabela
	, p.partition_number AS ParticaoN
	, f.name AS ParticaoFilegroup
	, p.rows AS NumeroRegistros 
	--, p.
FROM sys.partitions p
JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
WHERE OBJECT_NAME(OBJECT_ID) = 'TabelaGigante'

Tabela  	ParticaoN	ParticaoFilegroup	NumeroRegistros
--------------------------------------------------------------------
TabelaGigante	1       	fg2005         	1080615
TabelaGigante	2       	fg2006         	1040577
TabelaGigante	3       	fg2007         	0
TabelaGigante	4       	fg2008         	0
TabelaGigante	5       	fg2009         	0
TabelaGigante	6       	fg2010         	1080615
TabelaGigante	7       	fg2011         	1040577
TabelaGigante	8       	fg2012         	0

Observe que os dados foram alocados nas partições exatamente como esperado!

Comentários finais

Há muito tempo que o particionamento de tabelas está disponível no SQL Server e ele pode lhe dar a falsa impressão de ser simples. Mas não se iluda.

Particionamento não é um recurso elementar. Ele depende muito da infraestrutura criada para sua operação. E sua implementação em ambientes produtivos pode causar longas horas de indisponibilidade da aplicação.

Portanto, a definição da estratégia de particionamento de tabelas, sejam elas novas ou produtivas, deve ser uma iniciativa conjunta entre desenvolvedores, arquitetos e DBAs.

Leituras sugeridas

  1. Tabelas e Índices Particionados, por MICROSOFT
  2. Understanding Partitioning, por MICROSOFT