Criar partições de tabelas no conjunto de SQL dedicado
Recomendações e exemplos para utilizar partições de tabela no conjunto de SQL dedicado.
O que são partições de tabela?
As partições de tabela permitem-lhe dividir os dados em grupos de dados mais pequenos. Na maioria dos casos, as partições de tabela são criadas numa coluna de data. A criação de partições é suportada em todos os tipos de tabelas de conjuntos de SQL dedicados; incluindo columnstore em cluster, índice agrupado e área dinâmica para dados. A criação de partições também é suportada em todos os tipos de distribuição, incluindo hash ou round robin distribuídos.
A criação de partições pode beneficiar a manutenção de dados e o desempenho das consultas. Se beneficia tanto ou apenas um depende da forma como os dados são carregados e se a mesma coluna pode ser utilizada para ambas as finalidades, uma vez que a criação de partições só pode ser efetuada numa coluna.
Benefícios para carregamentos
O principal benefício da criação de partições no conjunto de SQL dedicado é melhorar a eficiência e o desempenho do carregamento de dados através da utilização da eliminação, mudança e intercalação de partições. Na maioria dos casos, os dados são particionados numa coluna de data que está intimamente associada à ordem pela qual os dados são carregados para o conjunto de SQL. Uma das maiores vantagens da utilização de partições para manter os dados é a prevenção do registo de transações. Ao inserir, atualizar ou eliminar dados pode ser a abordagem mais simples, com um pouco de reflexão e esforço, utilizar a criação de partições durante o processo de carregamento pode melhorar substancialmente o desempenho.
A mudança de partições pode ser utilizada para remover ou substituir rapidamente uma secção de uma tabela. Por exemplo, uma tabela de factos 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 é eliminado da tabela. Estes dados podem ser eliminados através de uma instrução delete para eliminar os dados do mês mais antigo.
No entanto, eliminar uma grande quantidade de dados linha a linha com uma instrução delete pode demorar demasiado tempo, bem como criar o risco de grandes transações que demoram muito tempo a reverter se algo correr mal. Uma abordagem mais ideal é remover a partição de dados mais antiga. Quando a eliminação das linhas individuais pode demorar horas, a eliminação de uma partição inteira pode demorar segundos.
Benefícios para consultas
A criação de partições também pode ser utilizada para melhorar o desempenho das consultas. Uma consulta que aplique um filtro a dados particionados pode limitar a análise apenas às partições elegíveis. Este método de filtragem pode evitar uma análise completa da tabela e analisar apenas um subconjunto de dados mais pequeno. Com a introdução de índices columnstore em cluster, 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 factos de vendas for particionada em 36 meses utilizando o campo data de vendas, 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 a criação de partições possa ser utilizada para melhorar o desempenho em alguns cenários, criar uma tabela com demasiadas partições pode prejudicar o desempenho em algumas circunstâncias. Estas preocupações são especialmente verdadeiras para tabelas columnstore em cluster.
Para que a criação de partições seja útil, é importante compreender quando utilizar a criação de partições e o número de partições a criar. Não existe uma regra muito rápida sobre quantas partições são demasiadas, depende dos seus dados e de quantas partições está a carregar em simultâneo. Normalmente, um esquema de criação de partições bem-sucedido tem dezenas a centenas de partições, não milhares.
Ao criar partições em tabelas columnstore em cluster , é importante considerar quantas linhas pertencem a cada partição. Para uma compressão e desempenho ideais das tabelas columnstore em cluster, é necessário um mínimo de 1 milhão de linhas por distribuição e partição. Antes de as partições serem criadas, o conjunto de SQL dedicado já divide cada tabela em 60 distribuições.
Todas as partições adicionadas a uma tabela são além das distribuições criadas em segundo plano. Com este exemplo, se a tabela de factos de vendas contiver 36 partições mensais e dado que um conjunto de SQL dedicado tem 60 distribuições, a tabela de factos vendas deve conter 60 milhões de linhas por mês ou 2,1 mil milhões de linhas quando todos os meses são preenchidos. Se uma tabela contiver menos do que o número mínimo recomendado de linhas por partição, considere utilizar menos partições para aumentar o número de linhas por partição.
Para obter mais informações, veja 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 Conjunto de SQL dedicado apresenta uma forma de definir partições mais simples do que SQL Server. As funções e esquemas de criação de partições não são utilizados no conjunto de SQL dedicado, uma vez que estão no SQL Server. Em vez disso, tudo o que precisa de fazer é identificar a coluna particionada e os pontos de limite.
Embora a sintaxe da criação de partições possa ser ligeiramente diferente da SQL Server, os conceitos básicos são os mesmos. SQL Server e conjunto de SQL dedicado suportam uma coluna de partição por tabela, que pode ser uma partição de intervalo. Para saber mais sobre a criação de partições, veja Partitioned Tables and Indexes (Tabelas e Índices Particionados).
O exemplo seguinte utiliza 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 de SQL Server
Para migrar SQL Server definições de partição para o conjunto de SQL dedicado:
- Elimine o esquema de partição SQL Server.
- Adicione a definição da função de partição à sua CREATE TABLE.
Se estiver a migrar uma tabela particionada de uma instância SQL Server, o SQL seguinte pode ajudá-lo a descobrir o número de linhas em cada partição. Tenha em atenção que, se for utilizada a mesma granularidade de criação de partições no conjunto de SQL dedicado, o número de linhas por partição diminui em 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];
Mudança de partições
O Conjunto de SQL dedicado suporta a divisão de partições, intercalação e comutação. Cada uma destas funções é executada com a instrução ALTER TABLE .
Para alternar entre duas tabelas, tem de garantir que as partições estão alinhadas nos respetivos limites e que as definições da tabela correspondem. Como as restrições de verificação não estão disponíveis para impor o intervalo de valores numa tabela, a tabela de origem tem de conter os mesmos limites de partição que a tabela de destino. Se os limites da partição não forem os mesmos, o comutador de partição falhará, uma vez que 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 (CCI) em cluster. Outras partições na mesma tabela podem conter dados. Uma partição que contenha dados não pode ser dividida, resultará num 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 para dividir uma partição que contém dados, veja 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 é utilizar uma CTAS instrução. Se a tabela particionada for um columnstore em cluster, a partição da tabela tem de estar vazia antes de poder ser dividida.
O exemplo seguinte cria uma tabela columnstore particionada. 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 seguinte localiza a contagem de linhas com a vista de sys.partitions 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 dividido 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, pode utilizar 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;
À medida que os limites da partição estão alinhados, é permitido um comutador. Isto deixará a tabela de origem com uma partição vazia que 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 com os novos limites de partição com CTASe, em seguida, mudar os dados novamente 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 o movimento dos dados, recomendamos que atualize as estatísticas na tabela de destino. A atualização das estatísticas garante que as estatísticas refletem com precisão a nova distribuição dos dados nas respetivas partições.
UPDATE STATISTICS [dbo].[FactInternetSales];
Por fim, no caso de um comutador de partição único para mover dados, pode remover as tabelas criadas para o comutador FactInternetSales_20000101_20010101 de partição e FactInternetSales_20000101. Em alternativa, poderá querer manter tabelas vazias para comutadores de partição regulares e automatizados.
Carregar novos dados para partições que contêm dados num único passo
Carregar dados em partições com a mudança de partições é uma forma conveniente de testar novos dados numa tabela que não é visível para os utilizadores. Pode ser difícil para os sistemas ocupados lidar com a contenção de bloqueio associada à mudança de partições.
Para limpar os dados existentes numa partição, é ALTER TABLE necessário utilizar para mudar os dados. Em seguida, era necessário outro ALTER TABLE para mudar os novos dados.
No conjunto de SQL dedicado, a opção TRUNCATE_TARGET é suportada no ALTER TABLE comando . Com TRUNCATE_TARGET o ALTER TABLE comando, substitui os dados existentes na partição por novos dados. Segue-se um exemplo que utiliza CTAS para criar uma nova tabela com os dados existentes, insere novos dados e, em seguida, muda todos os dados novamente 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);
Controlo de origem da criação de partições de tabelas
Nota
Se a ferramenta de controlo de origem 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 removida e recriada como parte da implementação, o que pode não ser exequível. Pode ser necessária uma solução personalizada para implementar essa alteração, conforme descrito abaixo. Verifique se a sua ferramenta de integração contínua/implementação contínua (CI/CD) permite isto. No SQL Server Data Tools (SSDT), procure as Definições de Publicação Avançadas "Ignorar esquemas de partição" para evitar um script gerado que faça com que uma tabela seja removida e recriada.
Este exemplo é útil ao atualizar esquemas de partição de uma tabela vazia. Para implementar continuamente alterações de partição numa tabela com dados, siga os passos em Como dividir uma partição que contém dados juntamente com a implementação para mover temporariamente os dados de cada partição antes de aplicar o INTERVALO DIVIDIDO da partição. Isto é necessário, uma vez que a ferramenta CI/CD não tem conhecimento das partições que têm dados.
Para evitar que a definição da tabela seja enferrujada no seu sistema de controlo de código fonte, recomendamos que 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 implementaçã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 esta abordagem, o código no controlo de origem permanece estático e os valores de limite de criação de partições podem ser dinâmicos; evolução com o conjunto de SQL ao longo do tempo.
Passos seguintes
Para obter mais informações sobre o desenvolvimento de tabelas, consulte os artigos sobre a Descrição Geral da Tabela.