Best Practices Recommendations for Table Partitioning in Azure SQL DB Hyperscale – Part 1

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

Introduction

Implementing table partitioning on a table that is exceptionally large in Azure SQL Database Hyperscale is not trivial due to the large data movement operations involved, and potential downtime needed to accomplish them efficiently. On the other hand, SQL Server Management Studio is not yet equipped to help implement partitioning in Azure SQL Database Hyperscale through a user interface assistant, as described here: Create partitioned tables and indexes with SSMS | Microsoft Learn.

Part 1 of this blog aims to help consider table partitioning fundamentals alongside other database features and concepts, in preparation to implement table partitioning in an actual table which is covered in Part 2.

Table partitioning fundamentals

The fundamentals of table partitioning in Azure SQL Database Hyperscale is no different than in SQL Server. The only particularity is that in Hyperscale, there is only one filegroup (PRIMARY) and so the partition schemes have to map all partitions to it, using the ALL TO ('PRIMARY') syntax.

For more information on partitioning fundamentals, please refer to the following product documentation:

Create partitioned tables and indexes - SQL Server, Azure SQL Database, Azure SQL Managed Instance | Microsoft Learn

Table partitioning and table maintenance

One of the benefits of implementing table partitioning is that it allows for easier index and statistics maintenance on large tables, by permitting to do them in one or a small subset of partitions. This reduces the scope for these maintenance operations, and in consequence more manageable and less resource consuming.

Many customers that use Azure SQL Database Hyperscale with tables larger than 1 TB in size may benefit from partitioning them to implement a maintenance plan tailored to a group of partitions, understanding and customizing the maintenance needs of each subgroup of partitions, instead of having to inevitably execute a maintenance plan for the table as a whole. One example is with index maintenance, when most of the data modifications are made in a reduced number of partitions. In this case you can perform index reorganize and index rebuild operations only on those partitions that really need it, thus making it more efficient by reducing the time and resources needed for this maintenance.

The recommendation is to partition your large tables (i.e., larger than 1 TB) so you can implement efficient table maintenance, reducing maintenance time and freeing up resources that can be used for a better workload performance.

For more information on index and statistics maintenance, please refer to: Optimize index maintenance to improve query performance and reduce resource consumption - SQL Server | Microsoft Learn

For a turnkey solution, you may refer to Microsoft MVP Ola Hallengren's SQL Server Index and Statistics Maintenance (hallengren.com) and use the PartitionLevel parameter.

Table partitioning and compression

One of the features that is commonly used with table partitioning is the possibility of using compressions for different sections of the partitioned table. For example, you may decide to page compress historical data in your large table while leaving the most recent data uncompressed. Another benefit of using partitioning and compression together is the possibility of implementing compression one partition at a time, reducing or spreading the amount of time and resources required, compared to having to compress the table as a whole.

Regardless of what are the benefits you are looking for, the recommendation is to implement a table partitioning strategy first, and then implement your compression strategy. Implementing partitioning first and compression second, will allow you to:

Implement compression at the partition level.
Design a compression strategy based on the type of data residing in each partition.
Reduce risks by not implementing them at the same time.

Table partitioning and columnstore indexes

The concept of partitioning is the same whether a table has a clustered index, is a heap, or has a columnstore index. For columnstore clustered and columnstore non-clustered indexes, you use the ON option of the CREATE COLUMNSTORE INDEX statement, and the basic benefits mentioned in the previous fundamentals section apply here.

The recommendation in this scenario is that each partition has to contain a minimum of 1 million rows for optimal compression and performance of clustered columnstore tables. If the resulting number of rows per partition is less when you are designing your strategy, consider reducing the number of partitions to accommodate this minimum number of rows for optimal compression.

For more information on the implementation of columnstore indexes on partitioned tables, please read: Each table partition has its own rowgroups and delta rowgroups - SQL Server and Azure SQL index architecture and design guide | Microsoft Learn

Table partitioning and tempdb

Under certain circumstances when implementing table partitioning, some data may have to be temporarily stored in tempdb as data goes through different stages of the data movement operation. This is true in particular, when sorting is required from a source non partitioned table to a destination partitioned table. Since tempdb space is limited, performing certain data movement operation sorting could result in filling the tempdb completely, resulting in a failure.

In order to avoid encountering such “tempdb full” failure scenarios, the recommendation is to implement the data copy from source table to destination partitioned table in batches. An example of how to do this is shown in section 3.2 called Partitioning into a new table structure.

Table partitioning and Index alignment

The concept of index alignment refers to an index that is physically partitioned in the same way as its corresponding base table. In other words, it is an index that is implemented on top of the same partition scheme as its base table. The main advantage of having all the indexes of a partitioned table aligned to it, is that it enables some partition switch operations to qualify as being metadata-only operations. This means that no actual data movement is performed inside the database, and hence the metadata-only operation is completed in a very short time, normally in a matter of seconds. This characteristic makes index alignment an attractive design choice.

If you are going to use table partitioning in a sliding window type of data management strategy that relies on partition switching, it is highly recommended that you keep all the indexes aligned to their base tables. For more information on index alignment, please read: Aligned index - Partitioned tables and indexes | Microsoft Learn

If your data management strategy does not use a sliding window strategy at all, then index alignment is optional and special considerations need to be taken into account when the table is designed to have a large number of partitions (> 1000) as described here: Partitioned index operations – Partitioned tables and indexes | Microsoft Learn

Table partitioning and statistics updates

When a partitioned index is created or rebuilt, its column statistics are not created by scanning all the rows in the table. Instead, the database uses the default sampling algorithm to generate statistics. This is not the case when the index is not partitioned, in which case all the rows are read in the statistics creation. Due to this reduced default sample used, the recommendation is to monitor your workload that uses partitioned indexes and determine if the statistics need to be updated with a sample rate higher than the default based on your workload performance.

For more information on this statistics update behavior change, please refer to: Behavior changes in statistics computation during partitioned index operations - Partitioned tables and indexes | Microsoft Learn

You can use the following query to retrieve information about the update statistics sample size from your database that can help you make informed decisions:

SELECT sh.[name] as [schema_name], so.[name] as [table_name], pr.[rows] as [table_rows], st.[name] as [stats_name], st.[stats_id], STRING_AGG(cl.[name], ', ') WITHIN GROUP ( ORDER BY st.[stats_id] ASC ) as [column_name], st.[auto_created], st.[is_incremental], st.[is_temporary], st.[filter_definition], sp.[last_updated], sp.[rows], sp.[rows_sampled], CAST( ( sp.[rows_sampled] * 1.00 / sp.[rows] * 1.00 )* 100 as decimal(8, 2) ) as [pct_sampled], sp.[persisted_sample_percent], sp.[modification_counter] as [row_modification_counter], CASE WHEN cp.[compatibility_level] <= 120 THEN CAST( 500 + (0.2 * sp.[rows]) AS integer ) WHEN cp.[compatibility_level] > 120 AND ( 500 + (0.2 * sp.[rows]) ) < ( SQRT(1000 * sp.[rows]) ) THEN CAST( 500 + (0.2 * sp.[rows]) AS integer ) ELSE CAST( SQRT(1000 * sp.[rows]) AS integer ) END [row_modification_threshold] FROM sys.stats st JOIN sys.stats_columns sc ON st.[object_id] = sc.[object_id] AND st.[stats_id] = sc.[stats_id] JOIN sys.columns cl ON sc.[object_id] = cl.[object_id] AND sc.[column_id] = cl.[column_id] JOIN sys.objects so ON so.[object_id] = st.[object_id] JOIN sys.schemas sh ON so.[schema_id] = sh.[schema_id] JOIN ( SELECT [object_id], SUM([rows]) [rows] FROM sys.partitions WHERE [index_id] IN (0, 1) GROUP BY [object_id] ) pr ON so.[object_id] = pr.[object_id] CROSS APPLY sys.dm_db_stats_properties(st.[object_id], st.[stats_id]) sp CROSS JOIN ( SELECT [compatibility_level] FROM sys.databases WHERE database_id = db_id() ) cp GROUP BY sh.[name], so.[name], pr.[rows], st.[name], st.[stats_id], st.[auto_created], st.[is_incremental], st.[is_temporary], st.[filter_definition], sp.[last_updated], sp.[modification_counter], sp.[rows], sp.[rows_sampled], sp.[persisted_sample_percent], cp.[compatibility_level] ORDER BY sh.[name], so.[name], st.[stats_id]

Table partitioning and Replication

There are certain guidelines that need to be strictly followed when partitioned tables are part of a replication topology. A set of properties that specify how partitioned tables and indexes should be replicated needs to be used and include properties for Publications, Articles, Subscriptions, etc. There are also requirements and limitations that are in play in this scenario such as what partition function and partition scheme commands are replicated, and in some cases, it is the responsibility of the database administrator to make table partitioning changes manually at the Subscribers.

For more information on replicating partitioned tables and indexes, please review the following documentation: Replicate Partitioned Tables and Indexes - SQL Server | Microsoft Learn

Table partitioning and Change data capture (CDC)

There are a few specific scenarios in which doing operations in a partitioned table can generate inconsistencies if the partitioned table is enabled for change data capture, and they are directly related to PARTITION SWITCH, MERGE and SPLIT. If you are partitioning your table exclusively to make table maintenance easier, and thus not relying on these three commands typically used in rolling window scenarios, then there is nothing to worry about.

For more information on table partitioning and Change data capture, please refer to [@allow_partition_switch = ] in the following documentation: sys.sp_cdc_enable_table (Transact-SQL) - SQL Server | Microsoft Learn

Table partitioning and page servers

In the Azure SQL Database architecture, all the database storage is presented as a single filegroup named PRIMARY. In the case of the Hyperscale service tier, all the storage provisioned is presented as part of this single PRIMARY filegroup.

When implementing table partitioning in Hyperscale, the partitions are laid down on the PRIMARY filegroup and all the page servers with their data files are filled proportionally, meaning there is no way to influence how the data is divided among page servers/data files. Even though this may look like a limitation, Hyperscale can reach higher levels of table partitioning scalability when compared to the other Azure SQL Database service tiers.

This design choice data distribution characteristic should by no means deter you from evaluating table partitioning for your workloads and exploiting the benefits listed in this document.

Feedback and suggestions

If you have feedback or suggestions for improving this data migration asset, please contact the Azure Databases SQL Customer Success Engineering Team (datasqlninja@microsoft.com). Thank you for your support!