Nowadays, the big environments that have large databases with a heavy amount of transaction they had one common performance issue in the OLTP transaction when the database size increased to the hundreds of gigabytes or more it will be difficult to load data.

Microsoft SQL Server provides us table partitioning to make this OLTP operation more either and manageable. Applying the partitioning on the large table it will split the table and its indexes into the smaller partition, at this time the maintenance procedure can be applied to an exact partition instead of doing it on the whole table. Additionally, the SQL Server engine will direct the filtered queries to the suitable partitions instead of the entire table. All of this will enhance the performance of OLTP transactions significantly. I will list Commons steps for database migration, including the following:

  • Concept of table partitioning
  • Why we need to implement Table Partitioning
  • Table partitioning limitation and restriction
  • Table partitioning type
  • Table partitioning dependence
  • Table partitioning implementation
  • Conclusion
  • Useful links

Concept of Table Partitioning

Table Partitioning is a technique built into some database management systems to work with large databases with a huge amount of data. Rather than store the table in a single storage location, by this technology, we can split the table into several files. Table partitioning may be the best choice for improving the performance of queries that hit only that particular partition of data.

Why we need to implement Table Partitioning

When data size increases in tables and indexes become very large, Table Partitioning can help us by splitting the data into smaller segments to be more controllable, as this large chunk of data will now be segmented into multiple partitions.

Table partitioning limitation and restriction

  1. Data type changes not allowed after partitioning the table
  2. The Switch option is not allowed for partitioning table used in replication
  3. To partition indexes of a primary key or unique key, the partitioning key must be part of the index key

Table partitioning type

In SQL Server we have two partitioning types; vertical and horizontal. We will describe the differences between them and when we can use each one.

Vertical Partitioning

The mechanism behind Vertical Partitioning is very simple; splitting one table into multiple tables with different columns. Or said another way, splitting the columns from one table to multiple secondary tables that are linked back to the main table by primary key and foreign key relationships. How effective this solution depends on the table itself (column size, the volume of data, column data type).

Horizontal Partition

In Horizontal partitioning, the table is divided into multiple different tables with the same table design and the same columns. But in the case of horizontal partitioning, the data is distributed in these multiple tables based on data range partitions, For example, the table can be partitioned based on a date range.

Table partitioning dependence

Partitioning has several requirements that we’ll review and discuss

Partitioning Function

It is the boundary between partition function used for mapping the row of table or index into partitions based on the ranges defined. So we can build the partitioning function with two kinds of ranges:

  • RANGE RIGHT:
  • RANGE LEFT:

And the column data types supported the boundary_Value are:

  • INT
  • DATETIME
  • CHAR

Partitioning Scheme

Mapping the partition table to the filegroup, a Partition scheme should be created on a partition function, and it can be used in multiple partitioned tables

Table Partitioning implementation

We have basic six steps to implement SQL Server partitioned table; I’ll list them one by one using comprehensive T-SQL package our example will be on the range right:

  • Create Database
  • Create FILEGROUP
  • Create files and add them to the FILEGROUP
  • Create a Partition Function (RANGE RIGHT)
  • Create a Partition Scheme
  • Create Table Partitioning

Create Database

I will create new database [SQLDBForPartitioning] to be for our entire workshop

CREATE DATABASE [SQLDBForPartitioning]
GO
ALTER DATABASE [SQLDBForPartitioning]
MODIFY FILE ( NAME = N'SQLDBForPartitioning', SIZE = 256MB , MAXSIZE = 10GB , FILEGROWTH = 512MB );
ALTER DATABASE [SQLDBForPartitioning]
MODIFY FILE ( NAME = N'SQLDBForPartitioning_log', SIZE = 128MB , FILEGROWTH = 128MB );
GO

Create Filegroups

Here we will create four filegroups (FG1, FG2, FG3, and FG4) to can use it on the Partition Scheme

USE [master]
GO
ALTER DATABASE [SQLDBForPartitioning] ADD FILEGROUP [FG1]
ALTER DATABASE [SQLDBForPartitioning] ADD FILEGROUP [FG2]
ALTER DATABASE [SQLDBForPartitioning] ADD FILEGROUP [FG3]
ALTER DATABASE [SQLDBForPartitioning] ADD FILEGROUP [FG4]

Create Files and add it to Filegroups

In this step I will create four files dynamically then added it on the FILEGROUPS files

Use SQLDBForPartitioning
Go
DECLARE @path NVARCHAR(256) ,
 @i TINYINT= 1 ,
 @sql NVARCHAR(4000);
SELECT TOP 1
 @path = LEFT(physical_name, LEN(physical_name) - 4)
FROM sys.database_files
WHERE name = 'SQLDBForPartitioning'
 
WHILE @i <= 4
 
 BEGIN
 SET @sql = N'ALTER DATABASE SQLDBForPartitioning ADD FILE (name=File' + CAST(@i AS NCHAR(1)) + ',
 filename=''' + @path + N'File' + CAST(@i AS NCHAR(1)) + '.ndf' + ''',
 size=128MB, filegrowth=256MB) TO FILEGROUP FG' + CAST(@i AS NCHAR(1))
 RAISERROR (@sql,0,0)
 EXEC sp_executesql @sql;
 SET @i += 1;
 END
GO

Create a Partitioning Function

In this example, I will do partition function with boundary_value DATETIME using RANGE RIGHT

Use SQLDBForPartitioning
GO
CREATE PARTITION FUNCTION PF_DBForPartitioning_RangeRight (DateTime)
AS RANGE RIGHT FOR VALUES ( '2015-01-01','2016-01-01', '2017-01-01');

To check partitioning function information

SELECT name,type_desc, fanout, boundary_value_on_right, create_date 
FROM sys.partition_functions;

Create a Partitioning Scheme

I will create a partition scheme on PF (PF_DBForPartitioning_RangeRight) on the four filegroup

Use SQLKDBForPartitioning
GO
CREATE PARTITION SCHEME PS_DBForPartitioning_RangeRight
AS PARTITION PF_DBForPartitioning_RangeRight
TO (FG1, FG2, FG3, FG4)
GO

To check partitioning schema information

SELECT Name,type_desc,type
FROM sys.partition_schemes;
GO

Create Table Partitioning

Now I will create a new table on the partitioning scheme with defining one column as boundary_value

Use SQLDBForPartitioning
GO
CREATE TABLE Partitioning_RangeRight
(Partitioning_ID int,
Partitioning_time DateTime)
ON PS_DBForPartitioning_RangeRight (Partitioning_time);
GO

Now the six basic steps for any table partitioning are finished, and I will go now to fill the table with some test data then I will show some wonderful Stored Procedures and Scripts it will return to us all the information about the Partition Function, Partition Scheme, and Table Partition, …., etc.

Fill Table by adequate volumes of data

Use SQLDBForPartitioning
GO
INSERT INTO Partitioning_RangeRight (Partitioning_ID, Partitioning_time)
SELECT 1 , '2014-01-01'UNION ALL
SELECT 2 , '2014-10-01'UNION ALL
SELECT 3 , '2015-01-01'UNION ALL
SELECT 4 , '2015-08-09'UNION ALL
SELECT 5 , '2015-12-30'UNION ALL
SELECT 6 , '2016-01-01'UNION ALL
SELECT 7 , '2016-05-24'UNION ALL
SELECT 8 ,'2017-01-24'UNION ALL
SELECT 9 ,'2017-05-24'UNION ALL
SELECT 10,'2018-05-24'
GO

Monitoring table partitioning

I will explain now how we can check this partition configuration and how we can collect information about it

  • List of all partitioned tables with partition function and scheme.
  • Partitioning information

List of all Partitioned tables

List by all tables configured with partitioning technology with the Partition Scheme and function related to each table

Create table #partition_table_info
(
[Partition_Table] Nvarchar(100),
[Partition_Scheme] Nvarchar(100),
[Partition_Function] Nvarchar(100),
)
Declare @P_Table Nvarchar(100) 
DECLARE db_cursor CURSOR FOR  
select  Distinct t.name A
from sys.partitions p
inner join sys.tables t
on p.object_id = t.object_id
where p.partition_number <> 1
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @P_Table  
WHILE @@FETCH_STATUS = 0   
BEGIN  
insert into #partition_table_info
(partition_Table,Partition_Scheme,Partition_Function) 
select @P_Table AS [Table name],ps.Name [Partition Scheme], pf.name [Partition Function]
from sys.indexes i
join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
join sys.partition_functions pf on pf.function_id = ps.function_id 
where i.object_id = object_id(@P_Table) 
       FETCH NEXT FROM db_cursor INTO @P_Table   
END  
CLOSE db_cursor   
DEALLOCATE db_cursor
Select * from #partition_table_info
Drop Table #partition_table_info

Partitioning information

Partitioning function and scheme with filegroup related to it and the boundary value for each file-group

select distinct ps.Name AS PartitionScheme, 
	pf.name AS PartitionFunction,fg.name AS FileGroupName, 
	rv.value AS PartitionFunctionValue,
	PF.type_desc as PartitionType
,(case when PF.boundary_value_on_right=0 then 'LEFT' else 'Right' end) as LeftORRightHere
    from sys.indexes i  
    join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id  
    join sys.partition_schemes ps on ps.data_space_id = i.data_space_id  
    join sys.partition_functions pf on pf.function_id = ps.function_id  
    left join sys.partition_range_values rv on rv.function_id = pf.function_id AND rv.boundary_id = p.partition_number
    join sys.allocation_units au  ON au.container_id = p.hobt_id   
    join sys.filegroups fg  ON fg.data_space_id = au.data_space_id

Conclusion

Table partitioning one of the best features in SQL Server that can help us for fasting the query process is the best way for managing the big tables with a huge volume of data. Additionally, it will enhance the OLTP process based on the boundary value; I recommended this feature.

Useful links

Keep Following

Cloud Tech Website blog survey

IF you found this blog is helpful and sharing useful content please take few second to do rate the website blog from here

Published by Mustafa EL-Masry

I am Microsoft database consultant working as a Database administrator for more than +10 Years I have very good knowledge about Database Migration, Consolidation, Performance Tuning, Automation Using T-SQL, and PowerShell and so many other tasks I do it in multiple customers here in KSA and as of now, I am working in Bank Albilad managing the core banking system that is hosted in SQL Server Database 8 TB. Also, I am Microsoft certified 2008 and 2016 in SQL Server (2x MCTS, 2x MCTIP, MCSA, MCSE) and I am Microsoft Certified Trainer (MCT) also I am azure Certified (AZ-900, AZ-103) also I was awarded by Microsoft Azure Heroes 3 times as (Azure Content hero, Azure Community hero and Azure Mentor) For more information check my page https://mostafaelmasry.com/about-me/

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.