Partitioning in SQL Server 2012 Databases - Part 1

Partitioning in Database – An Overview

Just assume you are working with a database application and one table having data in terabytes, the performance for that table is very slow, what will you do? – The best answer is dividing the table using partition concept.

Why? Because partitioning / dividing brings several benefits:

·       Management of a single large table is a pain for DBA if the data in the table continues to grow. Small table size easy to maintain.

·       On large table, only a subset of data will be volatile/read-write and rest, may be up to 90% or even more, will be read-only data. With varying access patterns you need more indexes for read-only data whereas you need fewer indexes for read-write data.

·        Creating and maintaining (Rebuild/Reorganize) indexes on a single large table takes significantly longer time and even increases the downtime. The divided data sets mean smaller indexes, fewer intermediate pages, and faster performance.

·         Backup and restore takes significantly longer as it needs to back or restore the whole data every time.

·         Frequent lock escalation issue at table level.

·       The divided data sets can reside on separate physical servers, thus scaling out and lowering costs and improving performance.

Partitioning Strategies

Table partitioning produces great benefits for some applications, but causes giant headaches for others. How do you know if you should invest your time in table partitioning?

Partitions are most effective when the partition key is a column often used to select a range of data so that a query has a good chance to address only one of the segments, such as the following:

·         A company partitions data by Sales Territory.

·         A school partitions data by School Year.

·         A manufacturing company partitions data by departments.

The common factor among each strategy is that each enables the querying of smaller sets of data. Instead of the queries searching the entire data set, only the necessary data are queried.

History: Partitioning in SQL Server is not a new concept and has improved with every new release of SQL Server.

·         SQL Server 7 or later: read only partitioned views

·         SQL Server 2000 or later: updatable partitioned views

·         SQL Server 2005 or later: partitioned tables

·         SQL Server 2008 or later: enhanced partitioned tables

Horizontal and Vertical Partition:

Horizontal partitioning divides a table into multiple tables. Each table then contains the same number of columns, but fewer rows. For example, a table that contains 1 billion rows could be partitioned horizontally into 12 tables, with each smaller table representing one month of data for a specific year. Any queries requiring data for a specific month only reference appropriate table.

Vertical partitioning divides a table into multiple tables that contain fewer columns. SQL Server 2012 does not directly support vertical partitioning as a feature but vertical partitioning can be implemented using normalization and row splitting:

• Normalization is the standard database process of removing redundant columns from a table and putting them in secondary tables that are linked to the primary table by primary key and foreign key relationships.
• Row splitting divides the original table vertically into tables with fewer columns. Each logical row in a split table matches the same logical row in the other tables as identified by a UNIQUE KEY column that is identical in all of the partitioned tables. For example, joining the row with ID 712 from each split table re-creates the original row.

Like horizontal partitioning, vertical partitioning lets queries scan less data. This increases query performance. For example, a table that contains seven columns of which only the first four are generally referenced may benefit from splitting the last three columns into a separate table. Vertical partitioning should be considered carefully, because analyzing data from multiple partitions requires queries that join the tables. Vertical partitioning also could affect performance if partitions are very large.

There are two ways that handle partitioning in SQL Server 2012: partitioned views and partitioned tables.