26 Janeiro 2018

Replicação de Tabelas Particionadas no SQL Server

1. INTRODUÇÃO

Toda a informação gravada no SQL Server reside em tabelas. O volume de dados de cada tabela varia conforme o seu objetivo, no entanto, numa base de dados de produção, e principalmente numa arquitetura data warehouse, algumas tabelas podem ter milhares e milhões de registos diários.

As técnicas mais comuns para aumentar a performance na leitura de informação dessas tabelas são a criação de índices e particionamento de tabelas.

Por omissão uma tabela tem uma partição única num filegroup único. Quando se define particionamento numa tabela, os dados são distribuídos e mapeados horizontalmente em disco, podendo também ocupar vários filegroups. Com particionamento é possível aceder aos dados de forma mais rápida e eficiente. Outra vantagem é a possibilidade de trabalhar segmentos de dados de forma isolada, tipicamente em tabelas auxiliares na área de staging, recorrendo posteriormente ao comando switch para “transferir” a partição para a tabela final.

2. OBJETIVO

Este artigo tem como objetivo demonstrar a forma mais eficiente de replicar tabelas com grandes volumes de dados, estando estas já particionadas. O backup de uma tabela nessas condições pode ser feito com um simples bulk insert, ou através de um processo mais elaborado que replica os registos por cada partição existente na tabela, permitindo escalar essa solução a várias tarefas executáveis em simultâneo.

Foram identificados dois cenários:

2.1. CENÁRIO 1

Recorrer à instrução SQL “SELECT INTO”. Esta instrução única tem como vantagem a simplicidade, mas implica a perda do particionamento na tabela de destino.

Apesar de ser uma instrução com mínimo de logs, esta é uma operação que pode demorar muito tempo a executar quando estão envolvidas tabelas de grande dimensão. Não é possível escalar essa execução por várias tarefas, o que afeta bastante a performance como será demonstrado mais à frente. Mesmo pensando numa solução onde é executado o primeiro “SELECT INTO” de forma a fazer uma cópia parcial dos dados, e depois recorrer ao comando “INSERT..SELECT”, a mesma não seria aconselhável pelas seguintes razões: Em primeiro lugar, as instruções “INSERT..SELECT” teriam que ser executadas de forma sequencial, pois em tarefas paralelas acabaria por gerar locks nas tabelas. O segundo motivo deve-se ao facto de a instrução “SELECT INTO” criar automaticamente a tabela de destino com base no universo de dados que recebe, que poderá não coincidir com a definição da tabela original, e por isso potencia a ocorrência de erros nas cópias seguintes.

 

2.2. CENÁRIO 2

Como alternativa, é possível criar um script, através de um store procedure, que replique os dados de forma particionada.

Este processo percorre cada partição, copiando os dados para uma tabela auxiliar com a mesma estrutura da original, e no final faz switch para a tabela final. Esta solução é altamente escalável, podendo no limite haver tantas tarefas a executar em paralelo quanto o número de partições da tabela original, o que faz aumentar drasticamente a performance como será demonstrado mais à frente.

Esta solução tem como pré-requisito a existência da tabela de destino com a mesma estrutura da tabela original, com a mesma partition function e o mesmo partition schema. Após fazer backup com este método, a tabela final encontrar-se-á particionada.

 

3. INSTALAÇÃO

Os exemplos criados neste artigo requerem as ferramentas SSMS (SQL Server Management Studio) e SSIS (SQL Server Integration Services), e é usada a tabela “FactInternetSales” da base de dados “AdventureWorksDW2012”.

De forma a simular um ambiente próximo a um sistema de produção, onde são escritos milhares e milhões de registos diariamente em tabelas factuais, foi criada uma nova tabela, “FactInternetSales_XL”. Esta tabela tem as seguintes características:

1. Volumetria: O número de registos desta tabela é de 19.029.643, ao invés dos originais 60.398.
2. Particionamento: A tabela original não tem particionamento definido. A nova tabela criada será particionada por ano e mês, entre janeiro de 2007 e junho de 2008, com um volume médio de 1.000.000 de registos em 19 partições.
3. Integridade dos dados: A nova tabela criada não terá qualquer tipo de contraint, como por exemplo chaves estrangeiras que façam referência a outras tabelas.

3.1. CRIAÇÃO DA TABELA “FACTINTERNETSALES_XL”

/* Replicate the original table */

IF OBJECT_ID(‘FactInternetSales_XL’) IS NOT NULL DROP TABLE FactInternetSales_XL

SELECT * INTO FactInternetSales_XL

FROM FactInternetSales

GO

/* Create the partition function */

IF NOT EXISTS (SELECT * FROM sys.partition_functions WHERE name = N’PFnFactInternetSales_XL’)

CREATE PARTITION FUNCTION [PFnFactInternetSales_XL](bigint) AS RANGE LEFT FOR VALUES (200701)

GO

/* Create the partition schema */

IF NOT EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N’PScFactInternetSales_XL’)

CREATE PARTITION SCHEME [PScFactInternetSales_XL] AS PARTITION [PFnFactInternetSales_XL] TO ([PRIMARY],[PRIMARY])

GO

/* Add a new column that will be used for partition */

alter table FactInternetSales_XL

add DateMonth bigint

GO

/* Create index on partition schema defined */

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[FactInternetSales_XL]’) AND name = N’IdxClU_DateMonth’)

CREATE CLUSTERED INDEX [IdxClU_DateMonth] ON [FactInternetSales_XL]

(

       [DateMonth] ASC

) ON [PScFactInternetSales_XL]([DateMonth])

GO

/* Fill the [DateMonth] column with data */

UPDATE FactInternetSales_XL SET DateMonth = LEFT(CONVERT(varchar,OrderDate,112),6)

GO

/* Retain only partitions for year 2007 and 2008 */

DELETE FROM FactInternetSales_XL WHERE LEFT(CONVERT(varchar,OrderDate,112),6) < 200701

GO

/* Populate each partition with ~ 1.000.000 records */

IF OBJECT_ID(‘tempdb..#DateMonths’) IS NOT NULL DROP TABLE #DateMonths

DECLARE @CurrentDateMonth INT = 0;

DECLARE @i INT = 0;

SELECT DISTINCT DateMonth INTO #DateMonths FROM FactInternetSales_XL

WHILE (SELECT count(*) FROM #DateMonths) > 0

BEGIN

       SET @CurrentDateMonth = (SELECT max(DateMonth) FROM #DateMonths)

       while (SELECT COUNT(*) FROM FactInternetSales_XL WHERE DateMonth = @CurrentDateMonth) < 1000000

       BEGIN

              PRINT @CurrentDateMonth

              PRINT @i

              insert into FactInternetSales_XL

              SELECT *,LEFT(CONVERT(VARCHAR,OrderDate,112),6)

              FROM FactInternetSales

              where LEFT(CONVERT(VARCHAR,OrderDate,112),6) = @CurrentDateMonth

              SET @i = @i +1;

       END

       SET @i = 0;

       DELETE FROM #DateMonths WHERE DateMonth = @CurrentDateMonth

END

3.2. CRIAÇÃO DO STORE PROCEDURE

USE [AdventureWorksDW2012]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[Usp_BackupTable]

@SourceSchemaName nVARCHAR(200),

@SourceTableName nVARCHAR(200),

@DestinationTableName nVARCHAR(200) = ”,

@FirstPartitionValue nVARCHAR(200) = ”,

@LastPartitionValue nVARCHAR(200) = ”

AS

DECLARE @TableInformation as table (

       IndexName VARCHAR(200)

       ,PartitionScheme VARCHAR(200)

       ,PartitionFunction VARCHAR(200)

       ,TableName VARCHAR (50)

       ,PartitionId int

       ,PartitionRows bigint

       ,PartitionBoundaryValue VARCHAR(200)

       ,PartitionColumn VARCHAR(200)

);

INSERT INTO @TableInformation

       SELECT

       i.Name IndexName

       ,ps.Name PartitionScheme

       ,pf.Name PartitionFunction

       ,s.name + ‘.’ + t.name TableName

       ,p.partition_number PartitionId

       ,p.rows

       ,cast(prv.value as VARCHAR(200)) value

       ,c.name

FROM sys.indexes i

JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id

JOIN sys.partition_functions pf ON pf.function_id = ps.function_id

JOIN sys.tables t ON i.object_id = t.object_id

JOIN sys.schemas s ON t.schema_id = s.schema_id

JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

JOIN sys.partition_range_values prv ON prv.function_id = ps.function_id AND prv.boundary_id = p.partition_number

JOIN sys.index_columns AS ic on i.object_id=ic.object_id AND i.index_id=ic.index_id

JOIN sys.columns AS c ON ic.object_id=c.object_id and ic.column_id=c.column_id

WHERE i.object_id = object_id(@SourceSchemaName + ‘.’ + @SourceTableName)

AND p.rows != 0

/*Partition Column Name*/

DECLARE @PartitionColumn VARCHAR(200) = (SELECT max(PartitionColumn) FROM @TableInformation);

DECLARE @PartitionsToProcess TABLE ([Partition] VARCHAR(200));

/* Partitions to process */

insert into @PartitionsToProcess

SELECT PartitionBoundaryValue

FROM @TableInformation

WHERE PartitionBoundaryValue between  @FirstPartitionValue and @LastPartitionValue

/* Schema.Table Orignal*/

DECLARE @TableName VARCHAR(200) = (SELECT max(TableName) FROM @TableInformation);

/* Schema.Table Backup*/

DECLARE @TableNameBck VARCHAR(200) = IIF(isnull(@DestinationTableName,”) = ”,@TableName + ‘_Bck_’ + convert(VARCHAR(10),GETDATE(),112) + ‘_’ +  RIGHT(‘0’ + convert(VARCHAR(2),datepart(hh,getdate())),2) + RIGHT(‘0’ + convert(VARCHAR(2),datepart(mi,getdate())),2),@SourceSchemaName + ‘.’ + @DestinationTableName);

/* Schema.Table Aux Table*/

DECLARE @TableNameBckAux VARCHAR(200) = @TableNameBck + ‘_’ + cast(@FirstPartitionValue as VARCHAR) + ‘_’ + cast(@LastPartitionValue as VARCHAR) +’_Aux’;

/* Table Aux Table – Usada para a constraint */

DECLARE @TableNameBckAux2 VARCHAR(200) = SUBSTRING(@TableNameBckAux,LEN(@SourceSchemaName)+2,LEN(@TableNameBckAux));

/* Get Index Name */

DECLARE @IndexName VARCHAR(200) = (SELECT max(IndexName) FROM @TableInformation);

/* Get Partition Scheme */

DECLARE @PartitionScheme VARCHAR(200) = (SELECT max(PartitionScheme) FROM @TableInformation);

/* Get Partition Scheme */

DECLARE @PartitionFunction VARCHAR(200) = (SELECT max(PartitionFunction) FROM @TableInformation);

/* Get Constraint */

DECLARE @TableAuxConstraint VARCHAR(200) = ‘CNST_’ + @TableNameBckAux2 + ‘_’ + @PartitionColumn;

DECLARE @Query nVARCHAR(max) = ”;

WHILE ((SELECT COUNT(1) FROM @PartitionsToProcess) > 0)

BEGIN

       DECLARE @Partition VARCHAR(200) = (SELECT TOP 1 [Partition] FROM @PartitionsToProcess ORDER BY [Partition]);

       DECLARE @partitionID INT = (SELECT PartitionId FROM @TableInformation WHERE PartitionBoundaryValue = @Partition);

       SET @Query =

              ‘IF (SELECT OBJECT_ID(‘ + ”” +  @TableNameBckAux + ”” + ‘)) IS NOT NULL DROP TABLE ‘ + @TableNameBckAux + ‘; ‘ +

              ‘SELECT * INTO ‘ + @TableNameBckAux + ‘ FROM ‘ + @TableName + ‘ WHERE ‘ + @PartitionColumn + ‘ = ‘ + @Partition +’;’

       EXEC sp_executesql @Query;

       SET @Query = ‘ALTER TABLE ‘ + @TableNameBckAux + ‘ WITH CHECK ADD CONSTRAINT ‘ + @TableAuxConstraint + ‘ CHECK  ((‘ + @PartitionColumn + ‘=(”’ + @Partition + ”’) AND ‘ + @PartitionColumn + ‘ IS NOT NULL));’;

       EXEC sp_executesql @Query;

       SET @Query = ‘CREATE CLUSTERED INDEX ‘ + @IndexName + ‘ ON ‘ + @TableNameBckAux + ‘

              (

                     ‘ + @PartitionColumn + ‘ ASC

              )WITH (

                     PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = NONE)

              ON [PRIMARY]’;

       EXEC sp_executesql @Query;

       SET @Query = N’ALTER TABLE ‘ + @TableNameBckAux + ‘ SWITCH TO ‘ + @TableNameBck + ‘ PARTITION ‘ + convert(VARCHAR, @partitionID)

       EXEC sp_executesql @Query;

       DELETE FROM @PartitionsToProcess WHERE [Partition] = @Partition

END

SET @Query = ‘DROP TABLE ‘ + @TableNameBckAux

EXEC sp_executesql @Query

3.3. CRIAÇÃO DA TABELA DE DESTINO

Uma vez que este processo requer a preexistência da tabela de destino, que receberá os dados da original, a forma mais simples de o fazer é através no SSMS, gerando o script “DROP CREATE” com base na tabela “FactInternetSales”. Após o script ser gerado, alterar “FactInternetSales_XL” para “FactInternetSales_XL_Partitioned_BCK”, com exceção da partition funcion e partition scheme que deverão ser mantidos. Alterar as opções de scripting caso os objetos dependentes partition table e partition schema não sejam automaticamente criados.

3.4. CRIAÇÃO DO PACKAGE SSIS

De forma a escalar a execução da replicação de dados, foi criado um package SSIS com 10 tarefas executáveis em paralelo. Cada tarefa tem uma expression que indica se está ativa ou não, e depende do valor configurado na variável “NumberOfThreads”. Exemplo da expression para a propriedade “Disabled” da Task 1: “@[User::NumberOfThreads] < 1”. Outros valores são configuráveis em variáveis, como o nome da tabela e os valores da primeira e da última partição a replicar.

 

Cada tarefa (Script task) ativa irá avaliar qual o intervalo de partições que lhe compete, recorrendo à função SQL “NTILE”. O script usado em todas as tasks é igual, à exceção da variável “@Thread” que terá o valor da tarefa correspondente.

3.4.1. CÓDIGO T-SQL PARA OS SQL TASKS

DECLARE @SourceSchemaName VARCHAR(200) = ?

DECLARE @SourceTableName VARCHAR(200) = ?

DECLARE @DestinationTableName VARCHAR(200) = ?

DECLARE @FirstPartitionValue VARCHAR(200) = ?

DECLARE @LastPartitionValue VARCHAR(200) = ?

DECLARE @NumberOfThreads INT = ?

DECLARE @Thread INT = 1

SELECT DISTINCT [DateMonth] [Value] INTO #Values

FROM dbo.FactInternetSales_XL

WHERE [DateMonth] between @FirstPartitionValue and @LastPartitionValue

SET @FirstPartitionValue = (SELECT min([Value]) FirstValue

FROM (

SELECT *, NTILE(@NumberOfThreads) OVER (ORDER BY [Value]) RN

FROM #Values

) A WHERE RN = @Thread)

SET @LastPartitionValue  = (SELECT max([Value]) LastValue

FROM (

SELECT *, NTILE(@NumberOfThreads) OVER (ORDER BY [Value]) RN

FROM #Values

) A WHERE RN = @Thread)

EXEC [dbo].[Usp_BackupTable] @SourceSchemaName, @SourceTableName, @DestinationTableName, @FirstPartitionValue, @LastPartitionValue;

4. EXECUÇÃO E MEDIÇÃO DE TEMPOS

Foram realizadas dez execuções com o package criado anteriormente, primeiro com uma única tarefa ativa, e posteriormente foi-se incrementando a variável @NumberOfThreads em cada execução.

No final de cada execução, mediu-se o número de segundos que a última tarefa demorou a executar. Os resultados são visíveis no gráfico abaixo.

 

Como se pode observar no gráfico, logo na segunda execução com duas tarefas em paralelo obteve-se um tempo bastante inferior (-46,6%). A melhor performance obtida foi na nona execução, com nove tarefas a executar em simultâneo e a última a terminar em 34 segundos, representando uma melhoria de 70% face à primeira execução.

Numa segunda experiência, duplicou-se o volume da tabela FactInternetSales, e voltou-se a repetir o processo. Voltando a executar o package para simular o segundo cenário obteve-se os seguintes tempos.

 

Nesta segunda experiência, observou-se uma redução ainda maior (-48%) entre a primeira execução, com uma tarefa ativa, e a segunda execução, com duas tarefas ativas. O melhor tempo a ser atingido foi de novo a iteração com nove tarefas ativas, que demorou 78 segundos (menos -74%).

4.1. TIRAR AINDA MAIS PARTIDO DO PARALELISMO

Em ambas as experiências se observou uma clara diminuição de tempo até à quarta iteração, havendo uma estabilização nas restantes. Existem diversos fatores que contribuem para este fenómeno, que vão desde os recursos de hardware que compõe a máquina, como por exemplo o número de cores do processador, e a arquitetura da disposição dos discos (ex. RAID).

De forma a tirar melhor partido do hardware disponível, é essencial uma boa gestão dos filegroups. Nos exemplos demonstrados foi usado apenas um filegroup “PRIMARY”, o que faz estabilizar a performance quando se configura mais tarefas a executar em simultâneo, pois todas elas fazem operações de leitura e escrita no mesmo filegroup. A melhor forma de gerir os filegroups, de maneira a aproveitar o paralelismo, é dispersar a leitura e escrita de dados. Para tal, é recomendado atribuir um filegroup próprio a cada partição, atribuindo-lhe um ficheiro próprio, mapeando cada ficheiro em discos rígidos distintos.

 

5. CONCLUSÃO

Como se pôde verificar nos gráficos apresentados, obteve-se ganhos de performance visíveis ao escalar o processo a várias tarefas. Não é fácil prever qual o número de tarefas em simultâneo que devem ser usadas de forma a obter a melhor performance, pois isso depende das condições em que o software é executado, como o poder de processamento da máquina, memória, e arquitetura de armazenamento.

De forma de aumentar a performance quando é usado paralelismo, os dados deverão ser separados em filegroups distintos, atribuindo um filegroup a cada partição, e finalmente mapeando esses filegroups em ficheiros dispersos por vários discos rígidos, reduzindo assim o tráfego de leitura e escrita de dados de cada tarefa.