SQL Server Partitioning

SQL Server provides partitioning feature to partition the data and distribute them within few data files to provide better manageability and also performance increment. SQL Server only provides ‘Range Partitioning’ which there are other few partitioning techniques such as:-

  • List Partitioning
  • Hash Partitioning
  • Composite Partitioning

Partitioning Methods

Horizontal partitioning

involves putting different rows into different tables. For example, customers with ZIP codes less than 50000 are stored in CustomersEast, while customers with ZIP codes greater than or equal to 50000 are stored in CustomersWest. The two partition tables are then CustomersEast and CustomersWest, while a view with a union might be created over both of them to provide a complete view of all customers.

Vertical partitioning involves creating tables with fewer columns and using additional tables to store the remaining columns. Normalization also involves this splitting of columns across tables, but vertical partitioning goes beyond that and partitions columns even when already normalized. Different physical storage might be used to realize vertical partitioning as well; storing infrequently used or very wide columns on a different device, for example, is a method of vertical partitioning. Done explicitly or implicitly, this type of partitioning is called “row splitting” (the row is split by its columns). A common form of vertical partitioning is to split dynamic data (slow to find) from static data (fast to find) in a table where the dynamic data is not used as often as the static. Creating a view across the two newly created tables restores the original table with a performance penalty, however performance will increase when accessing the static data e.g. for statistical analysis.

SQL Server supports table and index partitioning. The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. All partitions of a single index or table must reside in the same database. The table or index is treated as a single logical entity when queries or updates are performed on the data. Partitioned tables and indexes are not available in every edition of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server.

Partitioning Definitions

[info]Range partitioning: 

Selects a partition by determining if the partitioning key is inside a certain range. An example could be a partition for all rows where the column zip code has a value between 70000 and 79999.[/info]

[info]List partitioning: A partition is assigned a list of values. If the partitioning key has one of these values, the partition is chosen. For example all rows where the column Country is either Iceland, Norway, Sweden, Finland or Denmark could build a partition for the Nordic countries.[/info]

[info]Hash partitioning: The value of a hash function determines membership in a partition. Assuming there are four partitions, the hash function could return a value from 0 to 3.[/info]

[info]Composite partitioning: allows for certain combinations of the above partitioning schemes, by for example first applying a range partitioning and then a hash partitioning. Consistent hashing could be considered a composite of hash and list partitioning where the hash reduces the key space to a size that can be listed.[/info]

Partitioning Info Zone

[info]To maximize performance with parallel operations, we recommend that you use the same number of partitions as processor cores, up to a maximum of 64 (which is the maximum number of parallel processors that SQL Server can utilize). [/info]

[info]We recommend that you use at least 16 GB of RAM if a large number of partitions are in use. If the system does not have enough memory, Data Manipulation Language (DML) statements, Data Definition Language (DDL) statements and other operations can fail due to insufficient memory. [/info]

[info]Beginning with SQL Server 2012, statistics are not created by scanning all the rows in the table when a partitioned index is created or rebuilt. Instead, the query optimizer uses the default sampling algorithm to generate statistics. [/info]

Partitioning Samples

Range Partition:

[codesyntax lang=”tsql”]

Create Database Range_Partition;
go
Alter Database Range_Partition Add FileGroup [Range1To1000];
Alter Database Range_Partition Add FileGroup [Range1001To2000];
Alter Database Range_Partition Add FileGroup [Range2001ToN];
Go
Alter Database Range_Partition Add File (Name='Range1To1000',Filename='C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATARange1To1000.ndf') To Filegroup [Range1To1000];
Go
Alter Database Range_Partition Add File (Name='Range1001To2000',Filename='C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATARange1001To2000.ndf') To Filegroup [Range1001To2000];
Go
Alter Database Range_Partition Add File (Name='Range2001ToN',Filename='C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATARange2001ToN.ndf') To Filegroup [Range2001ToN];
Go
Use Range_Partition;
Go
Create Partition Function PF_RangeID (Bigint)
As Range Right For Values (1000,2000);
Go
Create Partition Scheme PS_RangeID  As Partition PF_RangeID
To ([Range1To1000],[Range1001To2000],[Range2001ToN]);
Go

Create Table Items (ID bigint Identity(1,1) Primary Key, Padding binary(10) Default 0xFFF) On PS_RangeID(ID);
Go

Insert Into Items Default Values
Go 3000

Go

Select $Partition.PF_RangeID(ID), * From Items;


[/codesyntax]

 

Composite Partition (List + Range):

[codesyntax lang=”tsql”]

Create Database List_Partition;
go
Alter Database List_Partition Add FileGroup [RangeAToG];
Alter Database List_Partition Add FileGroup [RangeHToN];
Alter Database List_Partition Add FileGroup [RangeOToZ];
Go
Alter Database List_Partition Add File (Name='RangeAToG',Filename='C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATARangeAToG.ndf') To Filegroup RangeAToG;
Go
Alter Database List_Partition Add File (Name='RangeHToN',Filename='C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATARangeHToN.ndf') To Filegroup RangeHToN;
Go
Alter Database List_Partition Add File (Name='RangeOToZ',Filename='C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATARangeOToZ.ndf') To Filegroup RangeOToZ;
Go
Use List_Partition;
Go
Create Partition Function PF_City(Char(1))
As Range Right For Values ('G','N','Z');
Go
Create Partition Scheme PS_City As Partition PF_City
To (RangeAToG,RangeHToN,RangeOToZ,[Primary]);
Go

Create Table Customers (ID Bigint Identity(1,1), Padding Binary(10) Default 0xFFF, City Varchar(10), PFCITY As Cast(Substring(City,1,1) As Char(1)) Persisted) On PS_City(PFCITY);

Go
Insert Into Customers (City) Values ('New York'),('W. DC'),('K. Lumpur'),('Tehran'),('Torrento'),('London'),('Paris'),('Arizona'),('Texas');

Go

Select $Partition.PF_City(PFCITY), * From Customers;



[/codesyntax]

 

Composite Partition (Hash + Range):

[codesyntax lang=”tsql”]

Create Database Hash_Partition;
go
Alter Database Hash_Partition Add FileGroup [Hash100];
Alter Database Hash_Partition Add FileGroup [Hash200];
Alter Database Hash_Partition Add FileGroup [Hash300];
Alter Database Hash_Partition Add FileGroup [Hash400];
Alter Database Hash_Partition Add FileGroup [Hash500];
Alter Database Hash_Partition Add FileGroup [Hash600];
Alter Database Hash_Partition Add FileGroup [Hash700];
Alter Database Hash_Partition Add FileGroup [Hash800];
Alter Database Hash_Partition Add FileGroup [Hash900];
Go
Alter Database Hash_Partition Add File (Name='Hash100',Filename='C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAHash100.ndf') To Filegroup Hash100;
Alter Database Hash_Partition Add File (Name='Hash200',Filename='C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAHash200.ndf') To Filegroup Hash200;
Alter Database Hash_Partition Add File (Name='Hash300',Filename='C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAHash300.ndf') To Filegroup Hash300;
Alter Database Hash_Partition Add File (Name='Hash400',Filename='C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAHash400.ndf') To Filegroup Hash400;
Alter Database Hash_Partition Add File (Name='Hash500',Filename='C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAHash500.ndf') To Filegroup Hash500;
Alter Database Hash_Partition Add File (Name='Hash600',Filename='C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAHash600.ndf') To Filegroup Hash600;
Alter Database Hash_Partition Add File (Name='Hash700',Filename='C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAHash700.ndf') To Filegroup Hash700;
Alter Database Hash_Partition Add File (Name='Hash800',Filename='C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAHash800.ndf') To Filegroup Hash800;
Alter Database Hash_Partition Add File (Name='Hash900',Filename='C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAHash900.ndf') To Filegroup Hash900;

Use Hash_Partition;

Go

Create Partition Function PF_HashName (Int)
As Range Right For Values (100,200,300,400,500,600,700,800,900);

Go

Create Partition Scheme PS_HashName As Partition PF_HashName
To (Hash100,Hash200,Hash300,Hash400,Hash500,Hash600,Hash700,Hash800,Hash900,[Primary]);

Go

Create Table Customers (ID Bigint Identity(1,1), Firstname Varchar(25), Lastname Varchar(25), HashName As ABS(Convert(int,(hashbytes('SHA1',Firstname+Lastname))))%999 Persisted ) On PS_HashName(HashName);

Go

Insert Into Customers (Firstname,Lastname) Values ('Hamid','J. Fard'),('John','Smith'),('Kevin','McMorphy'),('Bryan','Jackson');
Go

Select $Partition.PF_HashName(HashName),* From customers;

[/codesyntax]

 

Conclusion

Partitioning large tables or indexes can have the following manageability and performance benefits.

  • You can transfer or access subsets of data quickly and efficiently, while maintaining the integrity of a data collection. For example, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes when the data is not partitioned.
  • You can perform maintenance operations on one or more partitions more quickly. The operations are more efficient because they target only these data subsets, instead of the whole table. For example, you can choose to compress data in one or more partitions or rebuild one or more partitions of an index.
  • You may improve query performance, based on the types of queries you frequently run and on your hardware configuration. For example, the query optimizer can process equi-join queries between two or more partitioned tables faster when the partitioning columns in the tables are the same, because the partitions themselves can be joined.
  • When SQL Server performs data sorting for I/O operations, it sorts the data first by partition. SQL Server accesses one drive at a time, and this might reduce performance. To improve data sorting performance, stripe the data files of your partitions across more than one disk by setting up a RAID. In this way, although SQL Server still sorts data by partition, it can access all the drives of each partition at the same time. In addition, you can improve performance by enabling lock escalation at the partition level instead of a whole table. This can reduce lock contention on the table.

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan