MICROSOFT SQL SERVER 2005 Partitioning (Versão Portuguesa)

No comments

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’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s