SQL Server Table Partitioning

Problem

In this article, we will focus on the demonstration of table partitioning. The simplest explanation of table partitioning can be called as dividing large tables into small ones. This topic provides scalability and manageability.

Why do we need table partitioning?

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.

Requirements:

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.

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.

 

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.

Partition-level lock escalation

Lock escalation is a mechanism which 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.

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.

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.

References

Lock Modes

Partitioned Tables and Indexes

Esat Erkeç

Esat Erkeç

Esat Erkec is an SQL Server professional that began his career as a Software Developer over 8 years ago. He is an SQL Server Microsoft Certified Solutions Expert. Most of his career has focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence.
Esat Erkeç
1,044 views