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:
- Cria filegroups e datafiles usados em cada partição;
- Associa partições e filegroups;
- Cria uma tabela particionada.
(A listagem abaixo omite alguns comandos para não se tornar extensa demais. Para o script completo, clique neste link: Partition (.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
- Tabelas e Índices Particionados, por MICROSOFT
- Understanding Partitioning, por MICROSOFT
De 0 a 10, o quanto você recomendaria este artigo para um amigo?