Olá,
o assunto de hoje trata um tema importantíssimo relacionado com a performance do SQL Server, tema referente às partições de tabelas (Partitioned Tables).
Num modo muito simples, poderemos dizer que Partitioned Tables são uma forma de repartir por localizações diferentes os dados de uma tabela.
Uma DB pode ser composta por diversos FileGroups, sendo cada um constituído por diversos Files físicos, podendo ser localizados em volumes de disco diferentes. Isto permite performance melhorada pois o SQL pode responder paralelamente a pedidos de dados, acedendo a diferentes localizações simultaneamente.
O caso das Partitioned Tables é idêntico mas a separação em diferentes ficheiros é realizada mesmo ao nível da tabela, tornando o seu acesso de leitura/escrita muito melhorado, necessitando apenas ser especificado determinado campo para a divisão dos dados – normalmente é uma data, para separação dos dados por anos.
Assim, alguns pontos de interesse nas Partitioned Tables:
- o manuseamento de grandes tabelas é mais eficiente,
- cada partição pode ser guardada num FileGroup específico, sendo cada um destes colocados em qualquer volume de dados, podendo ser em locais diferentes para a mesma tabela,
- os índices ficam alinhados automaticamente, isto é, se for criado um índice numa tabela este fica imediatamente activo nas respectivas partições da tabela.
O processo de crição das Partitioned Tables passa pelas seguintes fases:
- Criar uma Partition Function, esta define as fronteiras para as partições, por ex. 3;
- Criar uma Partition Scheme, esta mapeia as partições previamente criadas para os Filegroups desejados – serão por ex. 4, de acordo com as 3 limitações anteriores – e define o próximo Filegroup a utlizar, ou seja, no ex. passa a 5;
- Criar uma Tabela, esta é criada na Partition Scheme anteriormente tratada, especificando o campo (coluna) desejado para a separação de dados.
- Dividir partição (Split Partition) em duas, sendo adicionado uma fronteira;
- Juntar partições (Merge Partitions), sendo que duas partições passarão a formar apenas uma – remove uma fronteira;
- Alterar partição (Switch Partitions)
Iniciemos por verificar a estrutura física da DB DemoDB, criada para o efeito de demonstração:
use Master
GO
create DATABASE DemoDB
GO
Vejamos os ficheiros constituintes da DemoDB:
e os respectivos Filegroups:
Das imagens anteriores, resume-se que a DemoDB tem apenas um FileGroup (Primary) e dois ficheiros no disco, um para dados e outro para log.
Agora, iremos alterar a DemoDB de forma a contemplar mais FileGroups (FG000, FG2000, FG2001, FG2002 e FG2003) e adicionar ficheiros a cada um destes FileGroups (respectivamente F0000, F2000, F2001, F2002 e F2003), ficando residentes em “C:\Temp”:
use DemoDB;
ALTER DATABASE DemoDB ADD FILEGROUP FG000 GO ALTER DATABASE DemoDB ADD FILE (NAME=F0000, FILENAME=’C:\Temp\F0000.ndf’, SIZE=3MB, MAXSIZE = 100MB, FILEGROWTH = 3MB ) TO FILEGROUP FG000;
GO
ALTER DATABASE DemoDB ADD FILEGROUP FG2000 GO ALTER DATABASE DemoDB ADD FILE (NAME=F2000, FILENAME=’C:\Temp\F2000.ndf’, SIZE=3MB, MAXSIZE = 100MB, FILEGROWTH = 3MB ) TO FILEGROUP FG2000;
GO
ALTER DATABASE DemoDB ADD FILEGROUP FG2001 GO ALTER DATABASE DemoDB ADD FILE (NAME=F2001, FILENAME=’C:\Temp\F2001.ndf’, SIZE=3MB, MAXSIZE = 100MB, FILEGROWTH = 3MB ) TO FILEGROUP FG2001;
GO
ALTER DATABASE DemoDB ADD FILEGROUP FG2002 GO ALTER DATABASE DemoDB ADD FILE (NAME=F2002, FILENAME=’C:\Temp\F2002.ndf’, SIZE=3MB, MAXSIZE = 100MB, FILEGROWTH = 3MB ) TO FILEGROUP FG2002;
GO
ALTER DATABASE DemoDB ADD FILEGROUP FG2003 GO ALTER DATABASE DemoDB ADD FILE (NAME=F2003, FILENAME=’C:\Temp\F2003.ndf’, SIZE=3MB, MAXSIZE = 100MB, FILEGROWTH = 3MB ) TO FILEGROUP FG2003;
GO
Vejamos agora a estrutura de ficheiros da DemoDB:
e seus Filegroups:
Importante aqui salientar que cada um dos ficheiros criados para a DemoDB poderia ficar residente em diferentes volumes de disco.
1 – Criar uma Partition Function
Vamos criar agora a Partition Function com o nome de PF, definindo 3 fronteiras, isto é, dividindo os dados por ano contemplando os anteriores a 2000, depois os dados entre 2000 e 2001, entre 2001 e 2002 e maiores que 2002:
CREATE PARTITION FUNCTION PF (int) AS RANGE RIGHT FOR VALUES (20000101, 20010101, 20020101);
GO
2 – Criar uma Partition Scheme
Vamos agora criar a Partition Scheme PS dentro da Partition Function PF para os Filegroups criados anteriormente.
O FG000 ficará com os dados anteriores a 2000, o FG2000 ficará com os dados do ano 2000, o FG2001 com os do ano 2001, o FG2002 com os dados do ano 2002 e o FG2003 com os dados superiores a 2002 (este é o próximo Filegroup a utilizar):
CREATE PARTITION SCHEME PS AS PARTITION PF TO (FG000, FG2000, FG2001, FG2002, FG2003);
GO
3 – Criar uma Tabela
O próximo passo é criar uma tabela, OrderTable, especificando que a criação deve ser feita na Partition Scheme PS e o campo de divisão de dados é o campo data (datekey).
A tabela é constituída por apenas dois campos – datekey e amount, ambos numéricos.
Criar tabela OrderTable:
CREATE TABLE dbo.OrderTable
(datekey int, amount int)
ON PS (datekey);
GO
e inserir dados (datekey e amount), alterando sucessivamente as datas:
INSERT dbo.OrderTable VALUES (20000101, 100);
INSERT dbo.OrderTable VALUES (20001231, 100);
INSERT dbo.OrderTable VALUES (20010101, 100);
INSERT dbo.OrderTable VALUES (20010403, 100);
GO
Os registos inseridos contemplaram os anos de 2000 e 2001.
Poderemos utilizar o seguinte código para verificar o resumo do que acabamos de criar:
- uma Partition Scheme,
- uma Partition Function com 4 partes, cada uma identificada numa partição do ficheiro do disco
- ficheiros da DemoDB onde se inscreveram dados
SELECT ps.name as PartitionScheme, pf.name as PartitionFunction,
p.partition_number as PartitionNumber, fg.name, prv_left.value inicio,
prv_right.value fim, p.row_count registos
FROM sys.dm_db_partition_stats p
INNER JOIN sys.indexes i ON i.OBJECT_ID = p.OBJECT_ID and i.index_id = p.index_id
INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.filegroups fg ON fg.data_space_id = dds.data_space_id
LEFT OUTER JOIN sys.partition_range_values prv_right
ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
LEFT OUTER JOIN sys.partition_range_values prv_left
ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
WHERE OBJECT_NAME (p.object_id) = 'OrderTable'
AND i.index_id = 0
ORDER BY partition_number
GO
Percebe-se que os registos foram inseridos na tabela OrderTable, internamente no Filegroup FG2000 e FG2001, cujos mapeamentos físicos no disco são c:\Temp\F2000.ndf e c:\Temp\F2001.ndf.
É um método que permite uma performance melhorada na medida em que passará a ler em localizações físicas diferentes consoante os dados que se desejem. A escrita também fica melhorada uma vez que pode ser efetuado paralelismo de escrita em ficheiros diferentes.
Na prática, efectuamos uma divisão de uma tabela utilizando como fronteira a coluna do ano.
Os pontos 4, 5 e 6:
- Dividir partição (Split Partition) em duas, sendo adicionado uma fronteira;
- Juntar partições (Merge Partitions), sendo que duas partições passarão a formar apenas uma – remove uma fronteira;
- Alterar partição (Switch Partitions)
serão publicados no próximo artigo.
Espero que vos seja útil,
JG
JGInformática - Jorge Gomes 