Tabelas de particionamento no pool de SQL dedicado
Recomendações e exemplos para usar partições de tabelas no pool de SQL dedicado.
O que são as partições de tabela?
Partições de tabela permitem dividir seus dados em grupos menores de dados. Na maioria dos casos, as partições de tabela são criadas em uma coluna de data. O particionamento tem suporte em todos os tipos de tabela do pool de SQL dedicado, incluindo columnstore clusterizado, índice clusterizado e heap. O particionamento também tem suporte em todos os tipos de distribuição, incluindo hash ou round robin.
O particionamento pode melhorar o desempenho da consulta e a manutenção de dados. Se ele beneficia ambos ou apenas um depende de como os dados são carregados e se a mesma coluna pode ser usada para ambas as finalidades, já que o particionamento só pode ser feito em uma coluna.
Benefícios para cargas
O principal benefício do particionamento no pool de SQL dedicado é melhorar a eficiência e o desempenho do carregamento de dados por meio do uso de exclusão de partição, comutação e mesclagem. Na maioria dos casos, os dados são particionados em uma coluna de data que está intimamente ligada à ordem em que os dados são carregados no pool de SQL. Uma das maiores vantagens de usar partições para manter dados é evitar o registro de transações em log. Embora a simples inserção, atualização ou exclusão de dados possa ser a abordagem mais simples, com um pouco de empenho, o uso de particionamento durante o processo de carregamento pode melhorar consideravelmente o desempenho.
A alternância de partição pode ser usada para remover ou substituir uma seção de uma tabela rapidamente. Por exemplo, uma tabela de fatos de vendas pode conter apenas dados dos últimos 36 meses. No final de cada mês, o mês de dados de vendas mais antigo é excluído da tabela. Esses dados poderiam ser excluídos usando uma instrução delete para excluir os dados do mês mais antigo.
No entanto, a exclusão de uma grande quantidade de dados linha por linha com uma declaração DELETE pode demorar muito tempo, bem como criar o risco de transações grandes, o que pode demorar muito para reverter se algo der errado. Uma abordagem ideal é remover a partição dos de dados mais antiga. A exclusão de linhas individuais pode levar horas. A exclusão de uma partição inteira pode demorar segundos.
Vantagens para consultas
O particionamento também pode ser usado para melhorar o desempenho da consulta. Uma consulta que aplica um filtro a dados particionados pode limitar a verificação apenas para as partições qualificadas. Este método de filtragem pode evitar uma verificação de tabela completa e apenas examinar um subconjunto de dados menor. Com a introdução de índices columnstore clusterizados, os benefícios de desempenho de eliminação de predicado são menores, mas em alguns casos pode haver vantagem para as consultas.
Por exemplo, se a tabela de fatos de vendas for particionada em 36 meses usando o campo de data das vendas, as consultas que forem filtradas pela data da venda podem pular a pesquisa de partições que não correspondam ao filtro.
Dimensionamento da partição
Embora o particionamento possa ser usado para melhorar o desempenho de alguns cenários, a criação de uma tabela com muitas partições pode prejudicar o desempenho em algumas circunstâncias. Esses problemas são especialmente verdadeiros para tabelas columnstore clusterizadas.
Para que o particionamento seja útil, é importante entender quando usar o particionamento e o número de partições a serem criadas. Não há nenhuma regra rígida sobre o que é o excesso de partições. Isso depende de seus dados e de quantas partições você está carregando simultaneamente. Um esquema de particionamento bem sucedido geralmente tem dezenas a centenas de partições, não milhares.
Ao criar partições em tabelas columnstore clusterizadas, é importante considerar quantas linhas pertencem a cada partição. Para compactação e desempenho ideais de tabelas columnstore clusterizadas, é necessário um mínimo de um milhão de linhas por distribuição, e também é necessário haver partição. Antes que as partições sejam criadas, o pool de SQL dedicado já divide cada tabela em 60 distribuições.
O particionamento adicionado a uma tabela é além das distribuições criadas nos bastidores. Usando esse exemplo, se a tabela de fatos de vendas contiver 36 partições mensais, e uma vez que o pool de SQL dedicado tem 60 distribuições, a tabela de fatos de vendas deverá conter 60 milhões de linhas por mês, ou 2.1 bilhões de linhas quando todos os meses forem populados. Se uma tabela possuir menos linhas do que o mínimo recomendado, considere usar menos partições para aumentar o número de linhas por partição.
Para obter mais informações, consulte o artigo Indexação, que inclui consultas que podem avaliar a qualidade dos índices columnstore do cluster.
Diferenças de sintaxe do SQL Server
O pool de SQL dedicado introduz uma maneira de definir partições que é mais simples que o SQL Server. As funções e os esquemas de particionamento não são usados no pool de SQL dedicado como são no SQL Server. Em vez disso,tudo o que você precisa fazer é identificar a coluna particionada e os pontos delimitadores.
Embora a sintaxe de particionamento possa ser ligeiramente diferente do SQL Server, os conceitos básicos são os mesmos. O SQL Server e o pool de SQL dedicado dão suporte a uma coluna de partição por tabela, que pode ser partição com intervalo. Para saber mais sobre particionamento, consulte Tabelas e índices particionados.
O seguinte exemplo usa a instrução CREATE TABLE para particionar a tabela FactInternetSales na coluna OrderDateKey:
CREATE TABLE [dbo].[FactInternetSales]
(
[ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
, [SalesOrderNumber] nvarchar(20) NOT NULL
, [OrderQuantity] smallint NOT NULL
, [UnitPrice] money NOT NULL
, [SalesAmount] money NOT NULL
)
WITH
( CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = HASH([ProductKey])
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101,20020101
,20030101,20040101,20050101
)
)
);
Migrar partições do SQL Server
Para migrar definições de partição do SQL Server para o pool de SQL dedicado, basta:
- Elimine o esquema de partição do SQL Server.
- Adicione a definição função de partição para CREATE TABLE.
Se você estiver migrando uma tabela particionada de uma Instância do SQL Server, o SQL a seguir poderá ajudá-lo a descobrir o número de linhas em cada partição. Tenha em mente que se a mesma granularidade de particionamento for utilizada no pool de SQL dedicado, o número de linhas por partição será reduzido por 60.
-- Partition information for a SQL Server Database
SELECT s.[name] AS [schema_name]
, t.[name] AS [table_name]
, i.[name] AS [index_name]
, p.[partition_number] AS [partition_number]
, SUM(a.[used_pages]*8.0) AS [partition_size_kb]
, SUM(a.[used_pages]*8.0)/1024 AS [partition_size_mb]
, SUM(a.[used_pages]*8.0)/1048576 AS [partition_size_gb]
, p.[rows] AS [partition_row_count]
, rv.[value] AS [partition_boundary_value]
, p.[data_compression_desc] AS [partition_compression_desc]
FROM sys.schemas s
JOIN sys.tables t ON t.[schema_id] = s.[schema_id]
JOIN sys.partitions p ON p.[object_id] = t.[object_id]
JOIN sys.allocation_units a ON a.[container_id] = p.[partition_id]
JOIN sys.indexes i ON i.[object_id] = p.[object_id]
AND i.[index_id] = p.[index_id]
JOIN sys.data_spaces ds ON ds.[data_space_id] = i.[data_space_id]
LEFT JOIN sys.partition_schemes ps ON ps.[data_space_id] = ds.[data_space_id]
LEFT JOIN sys.partition_functions pf ON pf.[function_id] = ps.[function_id]
LEFT JOIN sys.partition_range_values rv ON rv.[function_id] = pf.[function_id]
AND rv.[boundary_id] = p.[partition_number]
WHERE p.[index_id] <=1
GROUP BY s.[name]
, t.[name]
, i.[name]
, p.[partition_number]
, p.[rows]
, rv.[value]
, p.[data_compression_desc];
Alternância de partição
O pool de SQL dedicado dá suporte à divisão, mesclagem e comutação de partição. Todas essas funções são executadas usando a instrução ALTER TABLE.
Para alternar as partições entre duas tabelas, você deve garantir que as partições alinhem em seus respectivos limites e que correspondam as definições de tabela. Como restrições de verificação não estão disponíveis para impor o intervalo de valores em uma tabela, a tabela de origem deve conter os mesmos limites de partição da tabela de destino. Se os limites de partição não forem os mesmos, a troca de partição falhará, pois os metadados da partição não serão sincronizados.
Uma divisão de partição exige que a respectiva partição (não necessariamente a tabela inteira) esteja vazia se a tabela tiver um índice columnstore em cluster (CCI). Outras partições na mesma tabela podem conter dados. Uma partição que contém dados não pode ser dividida, pois resultará em erro: ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete. como solução temporária para dividir uma partição que contém dados, confira Como dividir uma partição que contém dados.
Como dividir uma partição que contém dados
O método mais eficiente para dividir uma partição que já contém dados é usar um CTAS instrução. Se a tabela de partição for um columnstore em cluster, então a partição da tabela deverá estar vazia antes que possa ser dividida.
O exemplo a seguir cria uma tabela do columnstore particionada. Ele insere uma linha em cada partição:
CREATE TABLE [dbo].[FactInternetSales]
(
[ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
, [SalesOrderNumber] nvarchar(20) NOT NULL
, [OrderQuantity] smallint NOT NULL
, [UnitPrice] money NOT NULL
, [SalesAmount] money NOT NULL
)
WITH
( CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = HASH([ProductKey])
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101
)
)
);
INSERT INTO dbo.FactInternetSales
VALUES (1,19990101,1,1,1,1,1,1);
INSERT INTO dbo.FactInternetSales
VALUES (1,20000101,1,1,1,1,1,1);
A consulta a seguir localiza a contagem de linhas utilizando a exibição do catálogo sys.partitions:
SELECT QUOTENAME(s.[name])+'.'+QUOTENAME(t.[name]) as Table_name
, i.[name] as Index_name
, p.partition_number as Partition_nmbr
, p.[rows] as Row_count
, p.[data_compression_desc] as Data_Compression_desc
FROM sys.partitions p
JOIN sys.tables t ON p.[object_id] = t.[object_id]
JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
JOIN sys.indexes i ON p.[object_id] = i.[object_Id]
AND p.[index_Id] = i.[index_Id]
WHERE t.[name] = 'FactInternetSales';
O comando Dividir a seguir recebe uma mensagem de erro:
ALTER TABLE FactInternetSales SPLIT RANGE (20010101);
Msg 35346, Level 15, State 1, Line 44
SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.
No entanto, é possível utilizar CTAS para criar uma nova tabela para reter os dados.
CREATE TABLE dbo.FactInternetSales_20000101
WITH ( DISTRIBUTION = HASH(ProductKey)
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101
)
)
)
AS
SELECT *
FROM FactInternetSales
WHERE 1=2;
Como os limites de partição estão alinhados, uma alternância é permitida. Isso deixará a tabela de origem com uma partição vazia que você poderá dividir posteriormente.
ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO FactInternetSales_20000101 PARTITION 2;
ALTER TABLE FactInternetSales SPLIT RANGE (20010101);
Tudo o que resta é alinhar os dados aos novos limites de partição utilizando CTAS e, em seguida, alternar os dados de volta para a tabela principal.
CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
WITH ( DISTRIBUTION = HASH([ProductKey])
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101
)
)
)
AS
SELECT *
FROM [dbo].[FactInternetSales_20000101]
WHERE [OrderDateKey] >= 20000101
AND [OrderDateKey] < 20010101;
ALTER TABLE dbo.FactInternetSales_20000101_20010101 SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2;
Após concluir a movimentação dos dados, é recomendável atualizar as estatísticas na tabela de destino. A atualização de estatísticas garante que as estatísticas reflitam com precisão a nova distribuição dos dados em suas respectivas partições.
UPDATE STATISTICS [dbo].[FactInternetSales];
Por fim, no caso de uma troca de partição única para mover os dados, você pode remover as tabelas criadas para a troca de partição, FactInternetSales_20000101_20010101 e FactInternetSales_20000101. Como alternativa, talvez queira manter as tabelas vazias para as trocas de partição automatizadas regulares.
Carregar novos dados em partições que contêm dados em uma única etapa
Carregar dados em partições com a alternância de partição é uma maneira conveniente de preparar novos dados em uma tabela que não é visível para os usuários. Pode ser desafiador em sistemas ocupados lidar com a contenção de bloqueio associada à alternância de partição.
Para limpar os dados existentes em uma partição, é necessário usar um ALTER TABLE para extrair os dados. Em seguida, outro ALTER TABLE era necessário para inserir os novos dados.
No pool de SQL dedicado, a opção TRUNCATE_TARGET tem suporte no comando ALTER TABLE. Com TRUNCATE_TARGET, o comando ALTER TABLE substitui os dados existentes na partição por novos dados. Veja abaixo um exemplo que usa CTAS para criar uma nova tabela com os dados existentes, insere novos dados e, em seguida, alterna todos os dados de volta para a tabela de destino, substituindo os dados existentes.
CREATE TABLE [dbo].[FactInternetSales_NewSales]
WITH ( DISTRIBUTION = HASH([ProductKey])
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101
)
)
)
AS
SELECT *
FROM [dbo].[FactInternetSales]
WHERE [OrderDateKey] >= 20000101
AND [OrderDateKey] < 20010101
;
INSERT INTO dbo.FactInternetSales_NewSales
VALUES (1,20000101,2,2,2,2,2,2);
ALTER TABLE dbo.FactInternetSales_NewSales SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2 WITH (TRUNCATE_TARGET = ON);
Controle da origem do particionamento da tabela
Observação
Se sua ferramenta de controle do código-fonte não estiver configurada para ignorar esquemas de partição, alterar o esquema de uma tabela para atualizar partições pode fazer com que ela seja descartada e recriada como parte da implantação, o que pode ser impossível. Pode ser necessária uma solução personalizada para implementar essa alteração, conforme descrito abaixo. Verifique se a ferramenta de integração contínua/implantação contínua (CI/CD) permite isso. No SQL Server Data Tools (SSDT), procure por “Ignorar esquemas de partição” das configurações de publicação avançadas para evitar gerar um script que faça com que a tabela seja removida e recriada.
Este exemplo é útil ao atualizar esquemas de partição de uma tabela vazia. Para implantar continuamente alterações de partição em uma tabela com dados, siga as etapas em Como dividir uma partição que contém dados em conjunto com a implantação para mover temporariamente os dados para fora de cada partição antes de aplicar o INTERVALO DE DIVISÃO da partição. Isso é necessário, pois a ferramenta CI/CD não reconhece quais partições contêm dados.
Para evitar a definição da tabela de rusting em seu sistema de controle de origem, convém considerar a abordagem a seguir:
Criar a tabela como uma tabela particionada, mas sem valores de partição
CREATE TABLE [dbo].[FactInternetSales] ( [ProductKey] int NOT NULL , [OrderDateKey] int NOT NULL , [CustomerKey] int NOT NULL , [PromotionKey] int NOT NULL , [SalesOrderNumber] nvarchar(20) NOT NULL , [OrderQuantity] smallint NOT NULL , [UnitPrice] money NOT NULL , [SalesAmount] money NOT NULL ) WITH ( CLUSTERED COLUMNSTORE INDEX , DISTRIBUTION = HASH([ProductKey]) , PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES () ) );SPLITa tabela como parte do processo de implantação:-- Create a table containing the partition boundaries CREATE TABLE #partitions WITH ( LOCATION = USER_DB , DISTRIBUTION = HASH(ptn_no) ) AS SELECT ptn_no , ROW_NUMBER() OVER (ORDER BY (ptn_no)) as seq_no FROM ( SELECT CAST(20000101 AS INT) ptn_no UNION ALL SELECT CAST(20010101 AS INT) UNION ALL SELECT CAST(20020101 AS INT) UNION ALL SELECT CAST(20030101 AS INT) UNION ALL SELECT CAST(20040101 AS INT) ) a; -- Iterate over the partition boundaries and split the table DECLARE @c INT = (SELECT COUNT(*) FROM #partitions) , @i INT = 1 --iterator for while loop , @q NVARCHAR(4000) --query , @p NVARCHAR(20) = N'' --partition_number , @s NVARCHAR(128) = N'dbo' --schema , @t NVARCHAR(128) = N'FactInternetSales' --table; WHILE @i <= @c BEGIN SET @p = (SELECT ptn_no FROM #partitions WHERE seq_no = @i); SET @q = (SELECT N'ALTER TABLE '+@s+N'.'+@t+N' SPLIT RANGE ('+@p+N');'); -- PRINT @q; EXECUTE sp_executesql @q; SET @i+=1; END -- Code clean-up DROP TABLE #partitions;
Com essa abordagem, o código no controle de origem permanece estático e são permitidos valores de limite de particionamento dinâmicos, evoluindo com o pool de SQL ao longo do tempo.
Próximas etapas
Para obter mais informações sobre como desenvolver tabelas, consulte Visão geral da tabela.