SQL Server
Simplificar a manutenção de banco de dados com partições de tabelas
Noah Gomez
Visão geral:
- Criando tabelas particionadas
- Adicionando e mesclando partições
- Criando e gerenciando índices particionados
Faça download do código deste artigo: GomezPartitioning2007_03.exe (156KB)
No passado, os administradores de banco de dados que gerenciavam tabelas contendo milhões de linhas de dados eram forçados a criar várias tabelas. Após o particionamento dessas tabelas, o administrador precisava vinculá-las
novamente durante a execução de muitas de suas consultas. A vinculação de partições envolvia a criação de um modo de exibição particionado ou de um procedimento armazenado por invólucro que descobrisse a localização dos dados e que executasse outro procedimento armazenado de forma a atingir somente as partições necessárias para o retorno do conjunto de dados.
Embora esses métodos funcionassem, eles eram cansativos. A administração de várias tabelas e seus índices, bem como os métodos usados para a vinculação das tabelas, muitas vezes causavam problemas de administração e de manutenção. Além disso, a criação de várias tabelas para particionar dados resultava em um certo grau de inflexibilidade, já que os procedimentos armazenados, os trabalhos de manutenção, os trabalhos DTS (Data Transformation Services), o código de aplicação e os outros processos tinham que compreender a natureza do particionamento. Portanto, para possibilitar a adição ou a exclusão dessas partições aparentes sem modificar o código, esses elementos eram geralmente criados de maneira não dinâmica e, como resultado, não eram eficientes.
As edições Enterprise e Developer do SQL Server™ 2005 permitem particionar grandes quantidades de dados contidos em uma única tabela em várias partições menores que podem ser gerenciadas e mantidas de forma mais eficiente. A capacidade de criar segmentos de dados acessados por meio de um ponto de entrada único reduz muitos dos problemas de administração inerentes ao método antigo de se fazer as coisas. O uso de um ponto de entrada único (nome de tabela ou nome de índice) oculta os diversos segmentos de dados do código do aplicativo e permite que o administrador ou o desenvolvedor altere as partições conforme necessário sem precisar ajustar a base do código.
Em resumo, você pode criar várias partições, mover essas partições, eliminar partições antigas e até mesmo alterar a forma como os dados são particionados, sem precisar ajustar o código no aplicativo, o qual simplesmente continua a chamar a mesma tabela base ou o mesmo nome de índice. Enquanto isso, é possível reduzir a quantidade de dados contida em índices individuais, o que, por sua vezes, diminui os tempos de manutenção desses índices. Também é possível aumentar a velocidade da carga de dados, fazendo o carregamento em partições vazias.
Tecnicamente, cada tabela do SQL Server 2005 é particionada e cada uma tem pelo menos uma partição. O que o SQL Server 2005 faz é permitir que os administradores de banco de dados criem partições adicionais em cada tabela. Partições de tabela e de índice são partições em nível de linha e bem definidas (o particionamento por colunas não é permitido) que permitem um ponto de entrada único (nome da tabela ou nome do índice) sem que o código do aplicativo precise saber quantas partições existem por atrás desse ponto de entrada. Partições podem existir na tabela base e nos índices associados à tabela.
Criando tabelas particionadas
Você utiliza funções de partição e esquemas de partição para criar uma tabela capaz de crescer além da partição única padrão. Esses objetos são os que possibilitam a divisão dos dados em segmentos específicos e que permitem controlar onde esses segmentos de dados estão localizados no design de armazenamento. Por exemplo, você pode dispersar os dados em várias matrizes de unidade com base na idade dos dados ou usando outros diferenciadores comuns. Observe que uma tabela pode ser particionada com base em uma de suas colunas e cada partição deve conter dados que não podem ser inseridos em outras partições.
Funções de partições Ao particionar uma tabela, a primeira decisão é como você deseja dividir os dados em diferentes segmentos. Uma função de partição é usada para mapear as linhas individuais de dados nas diferentes partições. Essas linhas individuais de dados podem ser mapeadas por qualquer tipo de coluna, com exceção dos tipos de dados text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max) e alias ou de tipos de dados definidos pelo usuário do CLR (Common Language Runtime). Entretanto, a função de particionamento deve ser capaz de inserir uma linha de dados em apenas uma partição de tabela. Ela não permite que uma linha de dados pertença a várias partições ao mesmo tempo.
Para particionar uma tabela, você deve criar a coluna de particionamento na tabela de destino. Essa coluna de particionamento pode existir no esquema de tabela quando a tabela é criada pela primeira vez ou você pode alterar essa tabela e adicionar a coluna posteriormente. A coluna pode aceitar valores NULL, mas todas as linhas contendo esses valores serão inseridas, por padrão, na partição localizada na extremidade esquerda da tabela. Isso pode ser evitado especificando-se que os valores NULL sejam inseridos na partição localizada na extremidade direita da tabela no momento que você criar a função de particionamento. O uso da partição esquerda ou direita será uma decisão de design importante à medida em que você modificar o esquema de particionamento e adicionar mais partições ou excluir partições existentes.
Ao criar uma função de particionamento, você pode optar por uma função de partição LEFT ou RIGHT. A diferença entre partições LEFT e RIGHT é o local em que os valores de limite serão inseridos no esquema de particionamento. Partições LEFT (o padrão) incluem o valor de limite na partição, enquanto partições RIGHT inserem o valor de limite na partição seguinte.
Para compreender esse conceito um pouco melhor, observe estas partições LEFT e RIGHT simples:
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
CREATE PARTITION FUNCTION Right_Partition (int) AS RANGE RIGHT
FOR VALUES (1,10,100)
Na primeira função (Left_Partition), os valores 1, 10 e 100 são inseridos na primeira, na segunda e na terceira partição, respectivamente. Na segunda função (Right_Partition), os valores são inseridos na segunda, na terceira e na quarta partição.
Ao criar uma tabela particionada, é importante fazer com que as partições sejam as mais uniformes possíveis. Isso o ajudará a perceber o espaço necessário para uma partição. O uso de LEFT e RIGHT determinará o local em que os dados serão inseridos, o que, por sua vez, determinará o tamanho da partição e o tamanho dos índices criados nessa partição.
Use a função $PARTITION para determinar o número da partição no qual um valor de dados será colocado, como mostra a seguir:
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Right_Partition (10)
Na primeira instrução SELECT, o resultado será 2. A segunda instrução SELECT retornará 3.
Esquemas de partição Depois de criar a função e de decidir como os dados serão divididos, você precisará decidir em que local as partições individuais serão criadas no subsistema de disco. Use esquemas de partição para criar esse layout de disco. Esquemas de partição gerenciam o armazenamento de disco de partições individuais, utilizando grupos de arquivos para inserir cada partição em um local no subsistema de disco. Você pode configurar esquemas de partição de forma que todas as partições sejam inseridas em um único grupo de arquivos, que todas as partições sejam inseridas em diferentes grupos de arquivos ou que várias partições compartilhem grupos de arquivos. Esse último método dá ao administrador do banco de dados uma grande flexibilidade na dispersão da E/S de disco.
A Figura 1 mostra algumas das possíveis maneiras de atribuir um grupo de arquivo ou vários grupos de arquivos a um esquema de partição. Lembre-se de que os grupos de arquivos usados pelo esquema de partição já devem existir no banco de dados para que você possa criar o esquema de particionamento.
Figure 1 Atribuindo grupos de arquivos a um esquema de partição
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the different filegroups
CREATE PARTITION SCHEME Different_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
TO (Filegroup1, Filegroup2, Filegroup3, Filegroup4)
--Filegroups must currently exist in database
--Place multiple partitions into the different filegroups
CREATE PARTITION SCHEME Multiple_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
TO (Filegroup1, Filegroup2, Filegroup1, Filegroup2)
--Filegroups must currently exist in database
Se você criar os exemplos de funções de partição, como mostra a Figura 1, e utilizar o esquema de partição para criar uma tabela, será possível determinar em que local as linhas de dados individuais serão inseridas nas tabelas recém-particionadas. Em seguida, você pode visualizar a distribuição dessas linhas de dados depois de inseridas na tabela particionada. O código para fazer isso terá uma aparência semelhante à da Figura 2.
Figure 2 Inserindo linhas de dados e visualizando a distribuição
--Prepare database
IF OBJECT_ID('Partitioned_Table') IS NOT NULL
DROP TABLE Partitioned_Table
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
ALL TO ([PRIMARY])
CREATE TABLE Partitioned_Table
(
col1 INT
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
--Determine where values will be placed (this is not required)
--You should try to do this before executing the code
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (2)
SELECT $PARTITION.Left_Partition (3)
SELECT $PARTITION.Left_Partition (4)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (11)
SELECT $PARTITION.Left_Partition (12)
SELECT $PARTITION.Left_Partition (13)
SELECT $PARTITION.Left_Partition (14)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
SELECT $PARTITION.Left_Partition (102)
SELECT $PARTITION.Left_Partition (103)
SELECT $PARTITION.Left_Partition (104)
--Insert data into partitioned table
INSERT INTO Partitioned_Table VALUES (1,'Description')
INSERT INTO Partitioned_Table VALUES (2,'Description')
INSERT INTO Partitioned_Table VALUES (3,'Description')
INSERT INTO Partitioned_Table VALUES (4,'Description')
INSERT INTO Partitioned_Table VALUES (10,'Description')
INSERT INTO Partitioned_Table VALUES (11,'Description')
INSERT INTO Partitioned_Table VALUES (12,'Description')
INSERT INTO Partitioned_Table VALUES (13,'Description')
INSERT INTO Partitioned_Table VALUES (14,'Description')
INSERT INTO Partitioned_Table VALUES (100,'Description')
INSERT INTO Partitioned_Table VALUES (101,'Description')
INSERT INTO Partitioned_Table VALUES (102,'Description')
INSERT INTO Partitioned_Table VALUES (103,'Description')
INSERT INTO Partitioned_Table VALUES (104,'Description')
--View the distribution of data in the partitions
SELECT ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('Partitioned_Table')
Modificando tabelas particionadas
Mesmo com um cuidadoso planejamento antecipado, em certas ocasiões, será necessário ajustar as tabelas particionadas depois de criá-las e preenchê-las. O esquema de partição pode funcionar conforme planejado, mas, por exemplo, talvez você precise adicionar novas partições à medida que novos dados forem acumulados ou ainda eliminar grandes quantidades de dados particionados de uma vez. Felizmente, as tabelas particionadas e as estruturas de particionamento subjacentes possibilitam que alterações sejam feitas depois que a tabela for ativada e preenchida com dados.
Adicionar partições Muitos planos de particionamento incluem a capacidade de adicionar uma nova partição no futuro. Esse ponto no tempo pode ser uma data específica ou pode depender de um valor em uma coluna de identidade incremental. Entretanto, se você não tiver planejado essa antecipação, ainda será possível adicionar novas partições a uma tabela particionada no futuro. Considere a tabela criada na Figura 2. Você pode adicionar uma nova partição a essa tabela, de forma a conter valores superiores a 500, como este:
--Determine where values live before new partition
SELECT $PARTITION.Left_Partition (501) --should return a value of 4
--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(500)
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (501) --should return a value of 5
A capacidade de adicionar partições oferece excelente flexibilidade. A Figura 3 mostra como adicionar uma partição ao lado esquerdo da função. Nesse caso, é preciso especificar ao esquema de particionamento um local para inserir a nova partição, pois você usou todos os grupos de arquivos criados na ocasião da construção inicial desse esquema. Mesmo que você esteja usando o grupo de arquivos PRIMARY para todas as suas partições, ainda é preciso fornecer instruções para que o esquema de particionamento reutilize o grupo de arquivos PRIMARY para a nova partição.
Figure 3 Adicionando uma partição ao lado esquerdo da função
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (5) --should return a value of 2
SELECT $PARTITION.Left_Partition (1) --should return a value of 1
SELECT $PARTITION.Left_Partition (10) --should return a value of 2
--Add new filegroups to the partitioning scheme
ALTER PARTITION SCHEME Primary_Left_Scheme
NEXT USED [PRIMARY]
--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(5)
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (5) --should return a value of 2
SELECT $PARTITION.Left_Partition (1) --should return a value of 1
SELECT $PARTITION.Left_Partition (10) --should return a value of 3
Mesclar duas partições O SQL Server permite eliminar partições únicas de uma tabela e preservar os dados simultaneamente. Isso pode ser usado para mesclar dados ativos mais antigos em dados arquivados ou para reduzir o número de partições existentes, facilitando a administração da tabela particionada. Também é possível usar esse método para mesclar partições, movendo dados de um grupo de arquivos para outro de forma a liberar espaço em disco em determinadas matrizes de unidade. O código na Figura 4 mostra como é possível mover dados de uma partição para outra no mesmo grupo de arquivos.
Figure 4 Movendo dados de uma partição para outra
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
--Merge two partitions
ALTER PARTITION FUNCTION Left_Partition()
MERGE RANGE (10)
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
Mover uma única tabela de partição para uma tabela particionada Durante rotinas de carga, grandes quantidades de dados muitas vezes precisam ser carregadas no banco de dados e, em seguida, modificadas ou agregadas antes de serem movidas até a tabela real de dados. O particionamento do SQL Server 2005 permite mover uma tabela de partição única para uma tabela com várias partições. Isso significa que você pode carregar dados em uma única tabela de carga, modificar esses dados e depois mover a tabela inteira para uma tabela existente sem a sobrecarga de mover cada linha individual de dados. Essa camada de particionamento não envolve a alteração das estruturas de particionamento subjacentes, mas envolve a modificação da tabela particionada O código na Figura 5 mostra como é possível obter isso.
Figure 5 Movendo a tabela inteira para uma tabela existente
--Prepare database
IF OBJECT_ID(‘multiple_partition’) IS NOT NULL
DROP TABLE multiple_partition
GO
IF OBJECT_ID(‘single_partition’) IS NOT NULL
DROP TABLE single_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = ‘Primary_Left_Scheme’)
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = ‘Left_Partition’)
DROP PARTITION FUNCTION Left_Partition
GO
--Create single partition table
CREATE TABLE single_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
)
--Table must have a CHECK Constraint
ALTER TABLE single_partition
WITH CHECK
ADD CONSTRAINT CK_single_partition
CHECK (col1 > 100)
INSERT INTO single_partition VALUES (101,’Description’)
INSERT INTO single_partition VALUES (102,’Description’)
INSERT INTO single_partition VALUES (103,’Description’)
INSERT INTO single_partition VALUES (104,’Description’)
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
INSERT INTO multiple_partition VALUES (1,’Description’)
INSERT INTO multiple_partition VALUES (2,’Description’)
INSERT INTO multiple_partition VALUES (3,’Description’)
INSERT INTO multiple_partition VALUES (4,’Description’)
INSERT INTO multiple_partition VALUES (10,’Description’)
INSERT INTO multiple_partition VALUES (11,’Description’)
INSERT INTO multiple_partition VALUES (12,’Description’)
INSERT INTO multiple_partition VALUES (13,’Description’)
INSERT INTO multiple_partition VALUES (14,’Description’)
INSERT INTO multiple_partition VALUES (100,’Description’)
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID(‘multiple_partition’), OBJECT_
ID(‘single_partition’))
--Move the single table into the partitioned table
ALTER TABLE single_partition SWITCH TO multiple_partition PARTITION 4
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID(‘multiple_partition’), OBJECT_ID(‘single_partition’))
Mover uma partição de uma tabela para outra Uma tarefa administrativa comum é mover dados mais antigos para tabelas de arquivamento separadas. O processo de arquivamento geralmente envolve uma série de instruções que podem gerar um uso adicional de recursos nos logs de transações. Entretanto, uma maneira fácil de arquivar grandes de quantidades de dados sem a sobrecarga de logs de transações é alternar de uma tabela para a outra a propriedade de uma partição. Esse recurso permite que o administrador do banco de dados mova segmentos de dados mais antigos a partir de suas tabelas ativas para tabelas arquivadas. Mas, como os dados não são realmente movidos, o tempo necessário pode ser muito inferior em comparação à movimentação de linhas de dados individuais. A Figura 6 mostra como é possível fazer isso.
Figure 6 Movendo dados mais antigos para tabelas arquivadas
--Prepare database
IF OBJECT_ID('active_data') IS NOT NULL
DROP TABLE active_data
GO
IF OBJECT_ID('archive_data') IS NOT NULL
DROP TABLE archive_data
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Active_Scheme')
DROP PARTITION SCHEME Active_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Active_Partition')
DROP PARTITION FUNCTION Active_Partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Archive_Scheme')
DROP PARTITION SCHEME Archive_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Archive_Partition')
DROP PARTITION FUNCTION Archive_Partition
GO
--Create active function
CREATE PARTITION FUNCTION Active_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Create archive function
CREATE PARTITION FUNCTION Archive_Partition (int) AS RANGE LEFT
FOR VALUES (100,200,300)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Active_Scheme
AS PARTITION Active_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Archive_Scheme
AS PARTITION Archive_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE active_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Active_Scheme (col1)
CREATE TABLE archive_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Archive_Scheme (col1)
INSERT INTO active_data VALUES (1,'Description')
INSERT INTO active_data VALUES (2,'Description')
INSERT INTO active_data VALUES (3,'Description')
INSERT INTO active_data VALUES (4,'Description')
INSERT INTO active_data VALUES (10,'Description')
INSERT INTO active_data VALUES (11,'Description')
INSERT INTO active_data VALUES (12,'Description')
INSERT INTO active_data VALUES (13,'Description')
INSERT INTO active_data VALUES (14,'Description')
INSERT INTO active_data VALUES (100,'Description')
INSERT INTO archive_data VALUES (200,'Description')
INSERT INTO archive_data VALUES (300,'Description')
INSERT INTO archive_data VALUES (400,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Switch ownership of partition to another table
ALTER TABLE active_data SWITCH PARTITION 3 TO archive_data PARTITION 1
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
Usar uma única partição para criar uma nova tabela Você pode mover uma única partição a partir de uma tabela existente particionada para uma tabela vazia não particionada. Fazendo isso, um administrador de banco de dados pode efetuar a manutenção de índices na partição única ou pode eliminar facilmente grandes quantidades de dados sem que o processo de exclusão seja registrado em log. O exemplo na Figura 7 mostra como mover uma partição até uma tabela vazia e, depois, usar essa nova tabela para eliminar os dados.
Figure 7 Movendo e eliminando dados
--Prepare database
IF OBJECT_ID('active_data') IS NOT NULL
DROP TABLE active_data
GO
IF OBJECT_ID('archive_data') IS NOT NULL
DROP TABLE archive_data
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Active_Scheme')
DROP PARTITION SCHEME Active_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Active_Partition')
DROP PARTITION FUNCTION Active_Partition
GO
--Create active function
CREATE PARTITION FUNCTION Active_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Active_Scheme
AS PARTITION Active_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE active_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Active_Scheme (col1)
CREATE TABLE archive_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
)
INSERT INTO active_data VALUES (1,'Description')
INSERT INTO active_data VALUES (2,'Description')
INSERT INTO active_data VALUES (3,'Description')
INSERT INTO active_data VALUES (4,'Description')
INSERT INTO active_data VALUES (10,'Description')
INSERT INTO active_data VALUES (11,'Description')
INSERT INTO active_data VALUES (12,'Description')
INSERT INTO active_data VALUES (13,'Description')
INSERT INTO active_data VALUES (14,'Description')
INSERT INTO active_data VALUES (100,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Switch ownership of partition to another table
ALTER TABLE active_data SWITCH PARTITION 3 TO archive_data
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Drop all archive data without logging
DROP TABLE archive_data
GO
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
Índices particionados
A capacidade de particionar os dados de uma tabela implica a capacidade de criar índices particionados. Isso permite que o administrador do banco de dados faça o design da estrutura de índice com base nos dados divididos em vez de nos dados da tabela inteira. A criação de índices particionados resulta em árvores B individuais nos índices particionados. A divisão dos índices tem o efeito de criar índices menores que são mais facilmente mantidos pelo mecanismo de armazenamento durante a modificação, a adição e a exclusão de dados. Esses índices menores também podem ser mantidos individualmente pelo administrador do banco de dados, possibilitando uma manutenção de índices mais eficiente em grandes conjuntos de dados.
Criando índices particionados Ao criar índices particionados, você pode criar índices alinhados ou índices não alinhados. Para índices alinhados, crie o índice com uma relação direta com os dados particionados. (Para índices não alinhados, escolha um esquema de particionamento diferente.)
O método alinhado é o preferencial e será feito automaticamente se você criar a tabela de partições e, depois, criar os índices sem especificar um esquema de particionamento diferente. O uso de índices alinhados proporciona a flexibilidade de criar partições adicionais na tabela e a capacidade de alternar a propriedade de uma partição para outra tabela. Esses recursos costumam ser a causa de os administradores de banco de dados criarem tabelas particionadas em primeiro lugar e o simples uso do esquema de particionamento da tabela para os seus índices será provavelmente suficiente para a obtenção das suas metas de particionamento.
Você pode criar índices com base em tabelas nas quais os dados no índice não estão alinhados aos dados na tabela. Se os dados estiverem em uma tabela particionada, isso permitirá que você agrupe os dados de diferentes maneiras (dados particionados podem ser eficientemente agrupados a outros dados particionados pelo otimizador de consultas). Como alternativa, isso pode ser feito com uma tabela não particionada, possibilitando a criação de um índice particionado (com base na tabela única de partições) para simplificar a manutenção desse índice.
O código na Figura 8 criará um índice particionado e não agrupado em cluster em uma tabela particionada. O índice não agrupado em cluster será alinhado à tabela e utilizará a coluna de particionamento da tabela como chave de índice não agrupado em cluster.
Figure 8 Índice particionado e não agrupado em cluster em uma tabela particionada
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
--Create partitioned non-clustered index
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_
partition(col1)
INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Verify index partitions
SELECT partition_id, index_id FROM sys.partitions pt
WHERE pt.[object_id] = OBJECT_ID('multiple_partition')
O código na Figura 9 criará um índice não alinhado e não agrupado em cluster em uma tabela particionada. Esse índice não agrupado em cluster usará colunas diferentes para sua chave de índice, que pode ser usada em junções combinadas com base em outras tabelas particionadas.
Figure 9 Índice não alinhado e não agrupado em cluster em uma tabela particionada
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Index_primary_Left_Scheme')
DROP PARTITION SCHEME Index_primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Index_Left_Partition')
DROP PARTITION FUNCTION Index_Left_Partition
GO
--Create partitioned index function
CREATE PARTITION FUNCTION Index_Left_Partition (int) AS RANGE LEFT
FOR VALUES (10,50,100)
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all index partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Index_primary_Left_Scheme
AS PARTITION Index_Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT
,col2 INT
) ON Primary_Left_Scheme (col1)
--Create non-aligned partitioned nonclustered index
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_
partition(col2)
ON Index_primary_Left_Scheme (col2)
INSERT INTO multiple_partition VALUES (1,10)
INSERT INTO multiple_partition VALUES (2,10)
INSERT INTO multiple_partition VALUES (3,10)
INSERT INTO multiple_partition VALUES (4,10)
INSERT INTO multiple_partition VALUES (10,50)
INSERT INTO multiple_partition VALUES (11,50)
INSERT INTO multiple_partition VALUES (12,50)
INSERT INTO multiple_partition VALUES (13,50)
INSERT INTO multiple_partition VALUES (14,50)
INSERT INTO multiple_partition VALUES (100,100)
INSERT INTO multiple_partition VALUES (101,100)
INSERT INTO multiple_partition VALUES (102,100)
INSERT INTO multiple_partition VALUES (103,100)
INSERT INTO multiple_partition VALUES (104,100)
--Verify row count on partitioned data
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
AND p.index_id = 0
--Verify row count on partitioned index
--Row counts will not match those found in the data partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
AND p.index_id <> 0
Manter índices particionados No passado, o tempo necessário para a realização da manutenção de índices com base em tabelas grandes contendo milhões ou até mesmo bilhões de linhas de dados costumava exceder o tempo disponível dos administradores de banco de dados. Em muitas ocasiões, essa manutenção deixava de ser feita porque os dados ficavam bloqueados enquanto o índice estava sendo reconstruído. Com o SQL Server 2005, o administrador do banco de dados pode realizar a manutenção de índices online, sem bloquear a tabela subjacente por um longo período de tempo. Porém, até mesmo essa abordagem (na qual você faz a manutenção do índice enquanto os usuários estão acessando os dados) ainda pode tornar o sistema lento devido ao uso de recursos. Uma abordagem melhor é o particionamento de índices em segmentos menores e, depois, a realização da manutenção do índice com base nessas partições menores. Por exemplo, para fazer a manutenção de um índice com base em uma partição de índice, você poderia simplesmente acrescentar o snippet de código a seguir ao final do código apresentado na Figura 8.
ALTER INDEX cl_multiple_partition
ON multiple_partition
REBUILD Partition = 2
Observe que a manutenção do índice com base em partições únicas de índice deve ser feita offline e pode causar o bloqueio da tabela durante a manutenção do índice. Para evitar isso, você pode mover a partição única até uma partição separada, fazer a manutenção do índice e então mover essa partição de volta à tabela principal. Esse processo causará alguns problemas de desempenho enquanto a partição estiver sendo movida de volta à tabela e durante a atualização do índice em cluster, mas é menos problemático do que o bloqueio da tabela inteira e requer menos recursos do sistema.
Resumo
Como é possível perceber, o particionamento de tabelas do SQL Server 2005 proporciona uma flexibilidade muito maior para o armazenamento e a manutenção de dados em tabelas grandes, sem a necessidade de reformular o código do aplicativo ou os processos do SQL Server. Com essas capacidades, o SQL Server prova que é uma plataforma competente para bancos de dados críticos em nível empresarial.
Noah Gomez é administrador de banco de dados de desenvolvimento sênior do SQL Server na Verizon, sendo especializado em VLDBs e aplicativos em grande escala. Ele é membro da PASS (Professional Association for SQL Server) e fez parte da equipe de administradores de banco de dados da Verizon que trabalhou nos VLDBs de vários terabytes, vencedores dos prêmios Winter Corp. Top Ten Grand Prize em 2003.
© 2008 Microsoft Corporation e CMP Media, LLC. Todos os direitos reservados. A reprodução parcial ou completa sem autorização é proibida..