MICROSOFT SQL SERVER 2005 Partitioning (Versão Portuguesa)

Objectivo

I. Benefícios e Limitações de Partitioned Clustered INDEX em SQL SERVER 2005.

II. Metodologia de Implementação.

III. Gestão de Particionamento.

Descrição

I. Benefícios e Limitações de Partitioned Clustered INDEX em SQL SERVER 2005:

+

Dados podem ser acedidos através de múltiplas partições em paralelo.

Diferentes partições podem ser geridas em separado.

Gestão de Histórico mais eficiente, sem ter que sair da tabela.

-

Limite de 1000 Partições por Tabela.
Impossibilidade de utilização de Indexed Views.

II. Metodologia de Particionamento TabelaXPTO:

Esta abordagem vai ter como base de trabalho um particionamento trimestral.

1. Criar a função de particionamento com DataType SMALLDATETIME como parâmetro.

CREATE PARTITION FUNCTION PFTabelaXPTOQuarterly (SMALLDATETIME)
AS RANGE RIGHT
FOR VALUES (
‘2006-07-01′,’2006-10-01′,
‘2007-01-01′,’2007-04-01′,’2007-07-01′,
‘2007-10-01′,’2008-01-01′,’2008-04-01′
)

2. Adicionar 10 FILEGROUPS À BD.

ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_QBASE
ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_Q1
ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_Q2
ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_Q3
ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_Q4
ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_Q5
ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_Q6
ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_Q7
ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_Q8

3. Implementar a Partition Scheme PSTabelaXPTOQuarterly, com a Partition Function PFTabelaXPTOQuarterly como parametro.

CREATE PARTITION SCHEME PSTabelaXPTOQuarterly
AS PARTITION PFTabelaXPTOQuarterly
TO
(
FG_TABELAXPTO_QBASE,
FG_TABELAXPTO_Q1,
FG_TABELAXPTO_Q2,
FG_TABELAXPTO_Q3,
FG_TABELAXPTO_Q4,
FG_TABELAXPTO_Q5,
FG_TABELAXPTO_Q6,
FG_TABELAXPTO_Q7,
FG_TABELAXPTO_Q8

)

• Nesta situação, o primeiro FG tem todos os dados desde Abril 2006 até Julho 2006.
• Os seguintes FG’s tem a data que correspondente ao valor de cada partition.
• O último FG tem todos os dados com data superior a Abril 2008.

4. Implementar o Partitioned Clustered Index na TabelaXPTO na Partition Scheme PSTabelaXPTOQuarterly .

CREATE CLUSTERED INDEX PCIX on dbo.posicao_contrato (data_contrato)
WITH (PAD_INDEX = ON, FILLFACTOR = 90)
ON PSTabelaXPTOQuarterly (data_contrato)

III. Gestão do Particionamento :

Com o exemplo de particionamento actual, surge a questão do que fazer quando se

começar a inserir registos com data >=Julho (2008Q3)?.

• Antes de se chegar ao final do Trimestre, cria-se um novo DataFile e Filegroup ( exemplo: FG_TABELAXPTO_2008Q3 ).

• Altera-se a PartitionScheme, adicionando-lhe um Filegroup.
ALTER PARTITION SCHEME PSTabelaXPTOQuarterly
NEXT USED [FG_TABELAXPTO_2008Q3] .

• Altera-se a PartitionFunction, adicionando-lhe uma nova Boundary(fronteira).
ALTER PARTITION FUNCTION PFTabelaXPTOQuarterly ()
SPLIT RANGE (‘20080701′);

Como saber quantas partições estão associadas ao Clustered Index da TabelaXPTO:

SELECT * FROM sys.partitions
WHERE object_id=object_id(‘posicao_contrato’)

Informação relativa à partition function utilizada.

SELECT * FROM sys.partition_functions
WHERE name=’PFTabelaXPTOQuarterly’

Mostra as boundaries da Partition Function .

SELECT a.name,b.* FROM sys.partition_functions a, sys.partition_range_values b
WHERE a.function_id=b.function_id
and a.name=’PFTabelaXPTOQuarterly’

Informação básica de segmentação de registos por partição

SELECT *
FROM sys.partitions
WHERE object_id=object_id(‘posicao_contrato’)
Query que retorna um registo por partition_number, contem informação de
que Filegroups estão associados ás Partitions, quantos registos, quantas pages e as

respectivas Boundary.

SELECT OBJECT_NAME(i.object_id) AS Object_Name,
i.index_id AS Index_ID,
p.partition_number, fg.name AS Filegroup_Name, rows, au.total_pages,
CASE boundary_value_on_right
WHEN 1 THEN ‘less than’
ELSE ‘less than or equal to’ END as ‘comparison’, value
FROM sys.partitions p JOIN sys.indexes i
ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
JOIN sys.partition_functions f
ON f.function_id = ps.function_id
LEFT JOIN sys.partition_range_values rv
ON f.function_id = rv.function_id
AND p.partition_number = rv.boundary_id
JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
JOIN (SELECT container_id, sum(total_pages) as total_pages
FROM sys.allocation_units
GROUP BY container_id) AS au
ON au.container_id = p.partition_id
–WHERE i.index_id <2;
WHERE OBJECT_NAME(i.object_id) = ‘tabelaXPTO’