Problem:
How to create a table partition and what do I need to create a partition on the
table.?
Partition: Partition is a unit
of data which is created based on grouping a range of values in a column of a
table or index. The data of this units from a Partitioned table in a database
can be stored across into different file groups. From a query perspective, the inserts
and updates the table or index is treated like a single entity.
Learn more
on how and when to partition a table and what it’s benefits and dis advantages.
Please go through this article. what-does-partitioning-do
For Partition to happen we have some pre-requisites that needs to be
in place before partitioning.
·
you need to have a developer edition or enterprise
edition of SQL Server or 2016 Standard Edition 2016 SP1.
·
You need to have enough space to create file groups
and spread the partitions.
·
Good RAM specifications.
Also, few steps are required to make this partitioning happen:
Step 1: Create a filegroup or filegroups and corresponding files
that will hold the partitions specified by the partition scheme. It is
recommended to add filegroups based on number of partitions you would like to
use. And always create an extra filegroup for spare. So, for 3 partitions I would
create 4 filegroups. So, you can separate those partitions in different
directories based on the access speeds you like.
ALTER DATABASE DB ADD FILEGROUP [filegroupname];
ALTER DATABASE DB ADD FILE
(
NAME = [filegroupname],
FILENAME = path,
SIZE = starting size,
MAXSIZE = maximum size alloted,
FILEGROWTH = grow in increments.
)
TO FILEGROUP filegroup1;
Step 2: Create a partition function that maps the rows of a
table or index into partitions based on the values of a specified column. You
can break the table based on this function boundaries you specify or ranges you
choose. If you want to separate every 1000 rows choose 1000,2000,3000 extra..
CREATE PARTITION FUNCTION
yourname (datatype) AS RANGE LEFT FOR VALUES (paritions range) ;
Step 3: Create a partition scheme that maps the partitions of a
partitioned table or index to the new filegroups.
CREATE PARTITION SCHEME yourname
AS PARTITION yourname
TO (filegroupname1, filegroupname2,.. filegroupname’n’)
;
GO
Step 4: Create or modify a table or index and specify the
partition scheme as the storage location.
CREATE TABLE / ALTER TABLE – index to be created or dropped if same column
existing.
Example:
In this
example, I would like to create a file group for an employees table and make
the partition on employee id, primary key of that column.
I would
like to break into small steps as in the topic I have explained.
Step 1:
USE DB;
GO
--
Adds four new filegroups to the DB database
ALTER DATABASE DB ADD FILEGROUP filegroup1;
GO
ALTER DATABASE DB ADD FILEGROUP filegroup2;
GO
ALTER DATABASE DB ADD FILEGROUP filegroup3;
GO
ALTER DATABASE DB ADD FILEGROUP filegroup4;
GO
--
Adds one file for each filegroup.
ALTER DATABASE DB ADD FILE (NAME = test1dat1, FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\t1dat1.ndf', SIZE = 5MB, MAXSIZE =
100MB,
FILEGROWTH = 5MB )
TO FILEGROUP filegroup1;
GO
ALTER DATABASE DB ADD FILE (NAME = test2dat2, FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\t2dat2.ndf', SIZE = 5MB, MAXSIZE =
100MB,
FILEGROWTH = 5MB )
TO FILEGROUP filegroup2;
GO
ALTER DATABASE DB ADD FILE (NAME = test3dat3, FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\t3dat3.ndf', SIZE = 5MB, MAXSIZE =
100MB,
FILEGROWTH = 5MB )
TO FILEGROUP filegroup3;
GO
ALTER DATABASE DB ADD FILE (NAME = test4dat4, FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\t4dat4.ndf', SIZE = 5MB, MAXSIZE =
100MB,
FILEGROWTH = 5MB )
TO FILEGROUP filegroup4;
GO
Step 2:
--
Creates a partition function to adjust the table into a 10 million row
partition
CREATE PARTITION FUNCTION
TPartRange (int)
AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000) ;
GO
Step 3:
--
Creates a partition scheme
CREATE PARTITION SCHEME SPartRange
AS PARTITION TPartRange
TO (filegroup1,
filegroup2, filegroup3,
filegroup4) ;
GO
Step 4:
--
alter the table add Primary Key employees.
ALTER TABLE dbo.Employees
ADD CONSTRAINT
PK_Employees PRIMARY KEY
NONCLUSTERED (EmployeeID)
WITH (STATISTICS_NORECOMPUTE
= OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
--
create index and partitions on the column using the partition scheme.
CREATE CLUSTERED INDEX
IX_Employees_Employees ON dbo.Employees (EmployeeID)
WITH (STATISTICS_NORECOMPUTE
= OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON)
ON SPartRange(EmployeeID)
GO
--
query to see the partitions
SELECT o.name objectname,i.name indexname, partition_id, partition_number,
[rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
LEFT JOIN
sys.tables t ON t.object_id=p.object_id
WHERE t.is_ms_shipped=0
and t.name='Employees'
OUTPUT:
objectname
|
indexname
|
partition_id
|
partition_number
|
rows
|
Employees
|
IX_Employees_Employees
|
72057594057850880
|
1
|
889879
|
Employees
|
IX_Employees_Employees
|
72057594057916416
|
2
|
999998
|
Employees
|
IX_Employees_Employees
|
72057594057981952
|
3
|
851348
|
Employees
|
IX_Employees_Employees
|
72057594058047488
|
4
|
0
|
In the next
article let’s see how this can improve the reads versus writes and what should
be the performance gains be like.
0 comments:
Post a Comment