In this article, we will focus on the demonstration of table partitioning. The simplest explanation of table partitioning can be called dividing large tables into small ones. This topic provides scalability and manageability.
What is table partitioning in SQL Server?
Assume that we have a table and it grows day by day. In this case, the table can cause some problems which need to be solved by the steps defined below:
- Maintain this table. It will take a long time and consume more resources (CPU, IO etc.).
- Back up.
- Lock problems.
Due to above-mentioned reasons, we need a table partitioning. This approach has the following benefits:
- Management capability: If we break the table apart, we can manage each partition of the table. For example, we can make only one partition of the table.
- Archiving capability: Some partitions of the table are used only for this reason. We don’t need to back up this partition of the table. We can use a filegroup backup and back up it only by changing a partition of the table.
- Query performance: SQL Server query optimizer decides to use partition elimination. It means that SQL Server does not make any search for the unrelated partition of the table.
Now, we will make a demonstration. In this demonstration, we will not use any T-SQL script. We will handle all the table partition steps in the SQL Server Partitioning wizard.
What do you need to make SQL database partitions?
- WideWorldImporters Sample Database
- SQL Server 2017 Developer Edition
The below image shows us how to design a table partition. We will make a table partition by years and locate different filegroups.
At this step, we will create two filegroups (FG_2013, FG_2014). Right-click a database and then click the Filegroups tab.
Now, we will connect the file groups to new pdf files.
Our database storage structure is ready for table partitioning. We will locate the table which we want to be partitioned and start the Create Partition wizard.
On the screenshot below, we will select a column that we want to apply the partition function on. The selected column is “InvoiceDate”.
On the next two screens, we will name a partition function and a partition scheme.
A partition function will define how to do partition for the [Sales].[Invoices] rows based on the InvoiceDate column.
A partition scheme will define maps for the Sales.Invoices rows to filegroups.
Assign the partitions to filegroups and set the boundaries.
Left /Right Boundary defines the side of each boundary value interval which may be left or right. We will set boundaries like this and click Estimate storage. This option provides us with the information about the number of rows to be located in the boundaries.
And finally, we will select Run immediately and then click Next.
Once the operation is successful, click Close.
As you can see, our Sales.Invoices table has been partitioned. This query will show the details of the partitioned table.
SELECT OBJECT_SCHEMA_NAME(pstats.object_id) AS SchemaName ,OBJECT_NAME(pstats.object_id) AS TableName ,ps.name AS PartitionSchemeName ,ds.name AS PartitionFilegroupName ,pf.name AS PartitionFunctionName ,CASE pf.boundary_value_on_right WHEN 0 THEN 'Range Left' ELSE 'Range Right' END AS PartitionFunctionRange ,CASE pf.boundary_value_on_right WHEN 0 THEN 'Upper Boundary' ELSE 'Lower Boundary' END AS PartitionBoundary ,prv.value AS PartitionBoundaryValue ,c.name AS PartitionKey ,CASE WHEN pf.boundary_value_on_right = 0 THEN c.name + ' > ' + CAST(ISNULL(LAG(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100)) + ' and ' + c.name + ' <= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100)) ELSE c.name + ' >= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100)) + ' and ' + c.name + ' < ' + CAST(ISNULL(LEAD(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100)) END AS PartitionRange ,pstats.partition_number AS PartitionNumber ,pstats.row_count AS PartitionRowCount ,p.data_compression_desc AS DataCompression FROM sys.dm_db_partition_stats AS pstats INNER JOIN sys.partitions AS p ON pstats.partition_id = p.partition_id INNER JOIN sys.destination_data_spaces AS dds ON pstats.partition_number = dds.destination_id INNER JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id INNER JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id INNER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id INNER JOIN sys.indexes AS i ON pstats.object_id = i.object_id AND pstats.index_id = i.index_id AND dds.partition_scheme_id = i.data_space_id AND i.type <= 1 /* Heap or Clustered Index */ INNER JOIN sys.index_columns AS ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.partition_ordinal > 0 INNER JOIN sys.columns AS c ON pstats.object_id = c.object_id AND ic.column_id = c.column_id LEFT JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id AND pstats.partition_number = (CASE pf.boundary_value_on_right WHEN 0 THEN prv.boundary_id ELSE (prv.boundary_id+1) END) WHERE pstats.object_id = OBJECT_ID('Sales.Invoices') ORDER BY TableName, PartitionNumber;
MS SQL Server partitioning performance
We will compare the partitioned and non-partitioned table performance for the same table. To do this, use the below query and activate Include Actual Execution Plan.
DECLARE @Dt as date = '20131231' SELECT COUNT(InvoiceDate) FROM [Sales].[Invoices] where InvoiceDate < @Dt
When we examine the execution plan, we come to know that it includes “Partitioned”, “Actual Partition Count”, and “Actual Partitioned Accessed” properties.
The Partitioned property indicates that this table is enabled for partition.
The Actual Partition Count property is the total number of partitions which are read by SQL Server engine.
The Actual Partitioned Accessed property is partition numbers assessed by SQL Server engine. SQL Server eliminates the access for other partitions as it is called a partition elimination and gains an advantage on query performance.
Now, look at the non-partitioned table execution plan.
The main difference between these two execution plans is Number of Rows Read because this property indicates how many rows are read for this query. As you can see from the below compression chart, partitioned table values are too low. For this reason, it will consume a low IO.
Next, run the query and examine the execution plan.
DECLARE @DtBeg as date = '20140502' DECLARE @DtEnd as date = '20140701' SELECT COUNT(InvoiceDate) FROM [Sales].[Invoices] where InvoiceDate between @DtBeg and @DtEnd
Partition-level lock escalation
Lock escalation is a mechanism that is used by SQL Server Lock Manager. It arranges to lock a level of objects. When the number of rows to be locked increases, the lock manager changes a locking object. This is the hierarchy level of lock escalation “Row -> Page -> Table -> Database”. But, in the partitioned table, we can lock one partition as it increases concurrency and performance. The default level of lock escalation is “TABLE” in SQL Server.
Execute the query using the below UPDATE statement.
BEGIN TRAN DECLARE @Dt as date = '20131221' UPDATE [Sales].[Invoices] SET CreditNoteReason = 'xxx' where InvoiceDate < @Dt SP_LOCK
The red box defines an exclusive lock that ensures that multiple updates cannot be made to the same resource at the same time. It occurs in the Invoices table.
Now, we will set the escalation mode for the Sales.Invoices table to automate it and re-run the query.
ALTER TABLE Sales.Invoices SET (LOCK_ESCALATION = AUTO)
Now, the red box defines the indent exclusive lock that protects requested or acquired exclusive locks on some resources lower in the hierarchy. Shortly, this lock level allows us to update or delete other partition of tables. That means that we can start another update or insert other partition of the table.