Particionamento de tabelas no pool SQL dedicado
Recomendações e exemplos para usar partições de tabela no pool SQL dedicado.
O que são partições de tabela?
As partições de tabela permitem que você divida 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 é suportado em todos os tipos de tabela de pool SQL dedicados; incluindo columnstore clusterizado, índice clusterizado e heap. A criação de partições também é suportada em todos os tipos de distribuição, incluindo hash ou round robin distribuídos.
O particionamento pode beneficiar a manutenção de dados e o desempenho da consulta. Se beneficia ambos ou apenas um, depende de como os dados são carregados e se a mesma coluna pode ser usada para ambos os fins, uma vez que o particionamento só pode ser feito em uma coluna.
Benefícios para cargas
O principal benefício do particionamento no pool SQL dedicado é melhorar a eficiência e o desempenho do carregamento de dados usando a exclusão, a comutação e a fusão de partições. 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 SQL. Um dos maiores benefícios de usar partições para manter dados é evitar o registro de transações. Embora simplesmente inserir, atualizar ou excluir dados possa ser a abordagem mais direta, com um pouco de pensamento e esforço, usar o particionamento durante o processo de carregamento pode melhorar substancialmente o desempenho.
A comutação de partições pode ser usada para remover ou substituir rapidamente uma seção de uma tabela. 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 mais antigo de dados de vendas é excluído da tabela. Esses dados podem ser excluídos usando uma instrução delete para excluir os dados do mês mais antigo.
No entanto, excluir uma grande quantidade de dados linha por linha com uma instrução delete pode levar muito tempo e criar o risco de grandes transações que levam muito tempo para reverter se algo der errado. Uma abordagem mais ideal é descartar a partição de dados mais antiga. Onde a exclusão das linhas individuais pode levar horas, excluir uma partição inteira pode levar segundos.
Benefícios 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 às partições qualificadas. Esse método de filtragem pode evitar uma verificação completa da tabela e verificar apenas um subconjunto menor de dados. Com a introdução de índices columnstore clusterizados, os benefícios de desempenho de eliminação de predicados são menos benéficos, mas em alguns casos pode haver um benefício para as consultas.
Por exemplo, se a tabela de fatos de vendas for particionada em 36 meses usando o campo de data de venda, as consultas que filtram na data de venda podem ignorar a pesquisa em partições que não correspondem ao filtro.
Dimensionamento de partições
Embora o particionamento possa ser usado para melhorar o desempenho em alguns cenários, a criação de uma tabela com muitas partições pode prejudicar o desempenho em algumas circunstâncias. Essas preocupações são especialmente verdadeiras 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á uma regra rígida e rápida sobre quantas partições são demais, depende dos 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 uma compressão e desempenho ideais de tabelas columnstore clusterizadas, é necessário um mínimo de 1 milhão de linhas por distribuição e partição. Antes das partições serem criadas, o pool SQL dedicado já divide cada tabela em 60 distribuições.
Qualquer particionamento adicionado a uma tabela é adicional às distribuições criadas nos bastidores. Usando este exemplo, se a tabela de fatos de vendas contiver 36 partições mensais e dado que um pool 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 estiverem preenchidos. Se uma tabela contiver menos do que o número mínimo recomendado de linhas por partição, 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 SQL dedicado apresenta uma maneira de definir partições que é mais simples do que o SQL Server. As funções e esquemas de particionamento não são usados no pool SQL dedicado como no SQL Server. Em vez disso, tudo o que você precisa fazer é identificar a coluna particionada e os pontos de limite.
Embora a sintaxe do particionamento possa ser ligeiramente diferente do SQL Server, os conceitos básicos são os mesmos. O SQL Server e o pool SQL dedicado oferecem suporte a uma coluna de partição por tabela, que pode ser uma partição variada. Para saber mais sobre particionamento, consulte Tabelas e índices particionados.
O exemplo a seguir usa a instrução CREATE TABLE para particionar a FactInternetSales tabela na OrderDateKey coluna:
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 pool SQL dedicado simplesmente:
- Elimine o esquema de partição do SQL Server.
- Adicione a definição da função de partição à sua CREATE TABLE.
Se você estiver migrando uma tabela particionada de uma instância do SQL Server, o SQL a seguir pode ajudá-lo a descobrir o número de linhas em cada partição. Lembre-se de que, se a mesma granularidade de particionamento for usada no pool SQL dedicado, o número de linhas por partição diminuirá por um fator de 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];
Comutação de partições
O pool SQL dedicado oferece suporte à divisão, fusão e comutação de partições. Cada uma dessas funções é executada usando a instrução ALTER TABLE .
Para alternar partições entre duas tabelas, você deve garantir que as partições estejam alinhadas em seus respetivos limites e que as definições de tabela coincidam. Como as 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 que a tabela de destino. Se os limites da partição não forem os mesmos, a opção de partição falhará, pois os metadados da partição não serão sincronizados.
Uma divisão de partição requer que a respetiva partição (não necessariamente a tabela inteira) esteja vazia se a tabela tiver um índice columnstore clusterizado (CCI). Outras partições na mesma tabela podem conter dados. Uma partição que contém dados não pode ser dividida, isso 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 alternativa para dividir uma partição que contém dados, consulte 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 uma CTAS instrução. Se a tabela particionada for um columnstore clusterizado, a partição da tabela deverá estar vazia antes de poder ser dividida.
O exemplo a seguir cria uma tabela 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 usando o sys.partitions modo de exibição de catálogo:
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 seguinte comando split 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, você pode usar CTAS para criar uma nova tabela para armazenar 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 da partição estão alinhados, um switch é permitido. Isso deixará a tabela de origem com uma partição vazia que você pode 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 usando CTASo 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;
Depois de concluir a movimentação dos dados, é uma boa ideia atualizar as estatísticas na tabela de destino. A atualização das estatísticas garante que as estatísticas reflitam com precisão a nova distribuição dos dados em suas respetivas partições.
UPDATE STATISTICS [dbo].[FactInternetSales];
Finalmente, no caso de um switch de partição único para mover dados, você pode soltar as tabelas criadas para o switch FactInternetSales_20000101_20010101 de partição e FactInternetSales_20000101. Como alternativa, você pode querer manter tabelas vazias para switches de partição regulares e automatizados.
Carregue novos dados em partições que contenham dados em uma etapa
Carregar dados em partições com a comutação de partições é 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 à comutação de partição.
Para limpar os dados existentes em uma partição, um ALTER TABLE costumava ser necessário para alternar os dados. Em seguida, outro ALTER TABLE foi obrigado a mudar os novos dados.
No pool SQL dedicado, a TRUNCATE_TARGET opção é suportada ALTER TABLE no comando. Com TRUNCATE_TARGET o ALTER TABLE comando substitui os dados existentes na partição por novos dados. Abaixo está 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 de origem de particionamento de tabela
Nota
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 uma tabela seja descartada e recriada como parte da implantação, o que pode ser inviável. Uma solução personalizada para implementar tal alteração, conforme descrito abaixo, pode ser necessária. Verifique se sua ferramenta de integração contínua/implantação contínua (CI/CD) permite isso. No SSDT (SQL Server Data Tools), procure as Configurações Avançadas de Publicação "Ignorar esquemas de partição" para evitar um script gerado que faz com que uma tabela seja descartada 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 ao lado da implantação para mover temporariamente os dados para fora de cada partição antes de aplicar a partição SPLIT RANGE. Isso é necessário, pois a ferramenta CI/CD não está ciente de quais partições têm dados.
Para evitar que a definição de tabela enferruje no sistema de controle do código-fonte, considere a seguinte abordagem:
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 do código-fonte permanece estático e os valores de limite de particionamento podem ser dinâmicos; evoluindo com o pool SQL ao longo do tempo.
Conteúdos relacionados
Para obter mais informações sobre o desenvolvimento de tabelas, consulte Visão geral da tabela.