Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
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. Partitioning is supported on all dedicated SQL pool table types; including clustered columnstore, clustered index, and heap. Partitioning is also supported on all distribution types, including both hash or round robin distributed.
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.
Benefits to loads
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.
Benefits to queries
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. With the introduction of clustered columnstore indexes, the predicate elimination performance benefits are less beneficial, but in some cases there can be a benefit to queries.
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. SQL Server and dedicated SQL pool support one partition column per table, which can be ranged partition. 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.
Para que uma divisão de partição ocorra, é necessário que a respetiva partição (e não necessariamente a tabela inteira) esteja vazia, caso a tabela possua 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);
The following query finds the row count by using the sys.partitions catalog view:
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;
As the partition boundaries are aligned, a switch is permitted. 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 CTAS, e, em seguida, transferir 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 efetuada 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];
Finally, in the case of a one-time partition switch to move data, you could drop the tables created for the partition switch, FactInternetSales_20000101_20010101 and 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. It can be challenging on busy systems to deal with the locking contention associated with partition switching.
Para limpar os dados existentes numa partição, costumava ser necessário usar um ALTER TABLE para substituir os dados. Then another ALTER TABLE was required to switch in the new data.
No pool SQL dedicado, a opção TRUNCATE_TARGET é suportada no comando ALTER TABLE. Com TRUNCATE_TARGET o ALTER TABLE comando substitui os dados existentes na partição por novos dados. Abaixo está um exemplo que utiliza CTAS para criar uma nova tabela com os dados existentes, insere novos dados e, em seguida, transfere 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);
Table partitioning source control
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. To continuously deploy partition changes on a table with data, follow the steps in How to split a partition that contains data alongside deployment to temporarily move data out of each partition before applying the partition SPLIT RANGE. Isso é necessário, pois a ferramenta CI/CD não está ciente de quais partições têm dados.
To avoid your table definition from rusting in your source control system, you may want to consider the following approach:
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 a Visão Geral da Tabela.