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
- 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
- Data type changes not allowed after partitioning the table
- The Switch option is not allowed for partitioning table used in replication
- 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.
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).
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
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:
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
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
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 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
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.
- SQL Server Table Partitioning Tutorial
- PROS AND CONS OF PARTITIONING
- SQL Server Table Partitioning: Resources
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