In SQL Server 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 so that ‘groups of rows’ are mapped into individual partitions
Table Partitioning is an Enterprise Edition feature only
What are primary files and filgroups?
A filegroup can be considered a logical storage unit to house database objects that maps to a file system file or multiple files
SQL Server Partitioned Table Creation
- Create file groups in individual disk drive (if possible)
- Create a partition function
- Create a partition scheme
- Partition the table based on the datetime column
Information about partition
SELECT ps.name,pf.name,boundary_id,value
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id
INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id
Benefits of partitioning
- Data partitioning improves the performance of SQL queries, data management, index management and storage management
- Allows you to spread data onto different physical disks, leveraging the concurrent performance of those disks to optimize query performance
- Separate the data into partition based on date and then only index the most current date data
- transfer or access subsets of data quickly and efficiently
- You can perform maintenance operations on one or more partitions more quickly
- operations are more efficient because they target only these data subsets, instead of the whole table
- 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
- Improve performance by enabling lock escalation at the partition level to reduce lock contention on the table
- extracting, moving, removing information from tables with hundreds of millions of rows without creating blockages
- Partitioning is not visible to end users, a partitioned table behaves like one logical table when queried
- Less frequently accessed data can be placed on slower disks and more frequently accessed data can be placed on faster disks.
- Historical, unchanging data can be set to read-only and then be excluded from regular backups.
- If data needs to be restored it is possible to restore the partitions with the most critical data first.
Partition function
Partition function defines ranges of data to be partitioned; the last partition function catches collects all data outside the boundaries of the predefined ranges
Partition scheme
Partition scheme uses the partition function to map data ranges to appropriate file groups
Scripts
USE SQL2
GO
SELECT * FROM [dbo].[People2]--1000
SELECT * FROM [dbo].[People3] -- 10,000,000 --2 MIN 10 SECS
SELECT * FROM [dbo].[People] -- 100,000,000 --LONGER THAN 10,000,000
--DROP DATABASE
--use master
--go
--DROP DATABASE AdventureWorks2012
--GO
----RESTORE DATABASE
USE [master]
RESTORE DATABASE [AdventureWorks2012]
FROM DISK = N'C:\backup122215\Database backups\ADVENTUREWORKS2012.BAK'
WITH FILE = 1,
MOVE N'AdventureWorks2012_Data'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf',
MOVE N'AdventureWorks2012_Log'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_log.ldf',
NOUNLOAD,
STATS = 5
GO
---- row count
SELECT COUNT(*) AS Total_Rows
FROM AdventureWorks2012.Production.TransactionHistoryArchive
----89253
----Find distinct datetime per year so as to partition
SELECT DISTINCT YEAR(TransactionDate) AS Year, COUNT(*) AS Total_Rows
FROM AdventureWorks2012.Production.TransactionHistoryArchive
GROUP BY YEAR(TransactionDate)
ORDER BY 1
--1: Before creating the file groups, create 3 seperate folders
--on drive C: to represent 'physical drives' (drive d, drive e, drive f)
----Create 4 file groups for each year - 2005, 2006, 2007 and all data not in first three partitions
--1 ADD FILEGROUPS FOR EACH YEAR PARTITION
USE [master]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILEGROUP [DRIVE D]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILEGROUP [DRIVE E]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILEGROUP [DRIVE F]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILEGROUP [DRIVE G]
GO
----With in those four folders (drives d, drive e, drive f, drive g), create 4 seperate data files (.ndf)
----for each year in the table
---- Create a partition function for each year range (note no table has been assigned to any partition as of yet)
----thus, you can associate any table to the partition at this point
USE AdventureWorks2012
GO
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
WHERE o.name LIKE '%TransactionHistoryArchive%'
USE [AdventureWorks2012]
GO
--2: CREATE 4 DATA (NDF)FILES IN FILEGROUP FOR EACH PARTITION
USE [master]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILE ( NAME = N'TRANS2005',
FILENAME = N'C:\drive d\TRANS2005.ndf' ,
SIZE = 4096KB ,
FILEGROWTH = 1024KB )
TO FILEGROUP [DRIVE D]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILE ( NAME = N'TRANS2006',
FILENAME = N'C:\drive e\TRANS2006.ndf' ,
SIZE = 4096KB ,
FILEGROWTH = 1024KB )
TO FILEGROUP [DRIVE E]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILE ( NAME = N'TRANS2007',
FILENAME = N'C:\drive f\TRANS2007.ndf' ,
SIZE = 4096KB ,
FILEGROWTH = 1024KB )
TO FILEGROUP [DRIVE F]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILE ( NAME = N'TRANS2015',
FILENAME = N'C:\drive g\TRANS2015.ndf' ,
SIZE = 4096KB ,
FILEGROWTH = 1024KB )
TO FILEGROUP [DRIVE G]
GO
--AT THIS POINT WE HAVE CREATED THE 'CONTAINERS' AND FILES FOR THE TABLE TO BE PARTITIONED TOO!!
-- CREATE PARTITION ON TABLE [TransactionHistoryArchive]
USE [AdventureWorks2012]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [FUNCTION_TRANSHISTORY](datetime)
AS RANGE LEFT
FOR VALUES (N'2005-12-31T23:59:59.997', N'2006-12-31T23:59:59.997', N'2007-12-31T23:59:59.997')
CREATE PARTITION SCHEME [SCHEMA_TRANSHISTORY]
AS PARTITION [FUNCTION_TRANSHISTORY]
TO ([DRIVE D], [DRIVE E], [DRIVE F], [PRIMARY])
ALTER TABLE [Production].[TransactionHistoryArchive] DROP CONSTRAINT [PK_TransactionHistoryArchive_TransactionID]
ALTER TABLE [Production].[TransactionHistoryArchive] ADD CONSTRAINT [PK_TransactionHistoryArchive_TransactionID] PRIMARY KEY NONCLUSTERED
([TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
CREATE CLUSTERED INDEX [ClusteredIndex_on_SCHEMA_TRANSHISTORY_635922165997540276] ON [Production].[TransactionHistoryArchive]
([TransactionDate]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [SCHEMA_TRANSHISTORY]([TransactionDate])
DROP INDEX [ClusteredIndex_on_SCHEMA_TRANSHISTORY_635922165997540276] ON [Production].[TransactionHistoryArchive]
COMMIT TRANSACTION
--VERIFY
USE AdventureWorks2012
GO
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
WHERE o.name LIKE '%TransactionHistoryArchive%'
-- INSERT DATA TO VERIFY DIRECTION OF DATA TO PARTITION
USE [AdventureWorks2012]
GO
INSERT INTO [Production].[TransactionHistoryArchive]
([TransactionID]
,[ProductID]
,[ReferenceOrderID]
,[ReferenceOrderLineID]
,[TransactionDate]
,[TransactionType]
,[Quantity]
,[ActualCost]
,[ModifiedDate])
VALUES
(89254, 1,1,1,'2016-02-28 00:00:00.000','P',999,50.2600,'2016-02-28 00:00:00.000')
GO
--VIEW ACTULA DATA IN THE PARTITION
SELECT * FROM [Production].[TransactionHistoryArchive]
WHERE $PARTITION.[FUNCTION_TRANSHISTORY](TransactionDate) = 4 ;--<< PARTITION 4 CONTAIN ONLY DATA THAT IS NOT IN RANGE OF PARTITION 1,2,3
[DRIVE D]
[DRIVE E]
[DRIVE F]
[DRIVE G]
TRANS2005
TRANS2006
TRANS2007
TRANS2015
2005-12-31T23:59:59.997
2006-12-31T23:59:59.997
2007-12-31T23:59:59.997
BACKUP DATABASE
BACKUP DATABASE [AdventureWorks2012]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\ADVENTUREWORKS2012.BAK'
WITH COPY_ONLY,
NOFORMAT,
NOINIT,
NAME = N'AdventureWorks2012-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
--USE SQL2
--GO
--SELECT * FROM [dbo].[People2]--1000
--SELECT * FROM [dbo].[People3] -- 10,000,000 --2 MIN 10 SECS
--SELECT * FROM [dbo].[People] -- 100,000,000 --LONGER THAN 10,000,000
--DROP DATABASE
use master
go
DROP DATABASE AdventureWorks2012
GO
--RESTORE DATABASE
USE [master]
RESTORE DATABASE [AdventureWorks2012]
FROM DISK = N'C:\backup122215\Database backups\ADVENTUREWORKS2012.BAK'
WITH FILE = 1,
MOVE N'AdventureWorks2012_Data'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf',
MOVE N'AdventureWorks2012_Log'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_log.ldf',
NOUNLOAD,
STATS = 5
GO
---- row count
SELECT COUNT(*) AS Total_Rows
FROM AdventureWorks2012.Production.TransactionHistoryArchive
----89253
----Find distinct datetime per year so as to partition
SELECT DISTINCT YEAR(TransactionDate) AS Year, COUNT(*) AS Total_Rows
FROM AdventureWorks2012.Production.TransactionHistoryArchive
GROUP BY YEAR(TransactionDate)
ORDER BY 1
--1: Before creating the file groups, create 4 seperate folders
--on drive C: to represent 'physical drives' (drive d, drive e, drive f, drive g)
----Create 4 file groups for each year - 2005, 2006, 2007 and all data not in first three partitions
--1 ADD FILEGROUPS FOR EACH YEAR PARTITION
USE [master]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILEGROUP [DRIVE D]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILEGROUP [DRIVE D]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILEGROUP [DRIVE F]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILEGROUP [DRIVE G]
GO
----With in those four folders (drives d, drive e, drive f, drive g), create 4 seperate data files (.ndf)
----for each year in the table
---- Create a partition function for each year range (note no table has been assigned to any partition as of yet)
----thus, you can associate any table to the partition at this point
USE AdventureWorks2012
GO
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
WHERE o.name LIKE '%TransactionHistoryArchive%'
SELECT DISTINCT YEAR(TransactionDate) AS Year, COUNT(*) AS Total_Rows
FROM AdventureWorks2012.Production.TransactionHistoryArchive
GROUP BY YEAR(TransactionDate)
ORDER BY 1
USE [AdventureWorks2012]
GO
--2: CREATE 4 DATA (NDF)FILES IN FILEGROUP FOR EACH PARTITION
USE [master]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILE ( NAME = N'TRANS2005',
FILENAME = N'C:\drive d\TRANS2005.ndf' ,
SIZE = 4096KB ,
FILEGROWTH = 1024KB )
TO FILEGROUP [DRIVE D]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILE ( NAME = N'TRANS2006',
FILENAME = N'C:\drive e\TRANS2006.ndf' ,
SIZE = 4096KB ,
FILEGROWTH = 1024KB )
TO FILEGROUP [DRIVE E]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILE ( NAME = N'TRANS2007',
FILENAME = N'C:\drive f\TRANS2007.ndf' ,
SIZE = 4096KB ,
FILEGROWTH = 1024KB )
TO FILEGROUP [DRIVE F]
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILE ( NAME = N'TRANS2015',
FILENAME = N'C:\drive g\TRANS2015.ndf' ,
SIZE = 4096KB ,
FILEGROWTH = 1024KB )
TO FILEGROUP [DRIVE G]
GO
--AT THIS POINT WE HAVE CREATED THE 'CONTAINERS' AND FILES FOR THE TABLE TO BE PARTITIONED TOO!!
-- CREATE PARTITION ON TABLE [TransactionHistoryArchive]
USE [AdventureWorks2012]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [FUNCTION_TRANSHISTORY](datetime)
AS RANGE LEFT
FOR VALUES (N'2005-12-31T23:59:59.997', N'2006-12-31T23:59:59.997', N'2007-12-31T23:59:59.997')
CREATE PARTITION SCHEME [SCHEMA_TRANSHISTORY]
AS PARTITION [FUNCTION_TRANSHISTORY]
TO ([DRIVE D], [DRIVE E], [DRIVE F], [drive g])
ALTER TABLE [Production].[TransactionHistoryArchive] DROP CONSTRAINT [PK_TransactionHistoryArchive_TransactionID]
ALTER TABLE [Production].[TransactionHistoryArchive] ADD CONSTRAINT [PK_TransactionHistoryArchive_TransactionID] PRIMARY KEY NONCLUSTERED
([TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
CREATE CLUSTERED INDEX [ClusteredIndex_on_SCHEMA_TRANSHISTORY_635922165997540276] ON [Production].[TransactionHistoryArchive]
([TransactionDate]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [SCHEMA_TRANSHISTORY]([TransactionDate])
DROP INDEX [ClusteredIndex_on_SCHEMA_TRANSHISTORY_635922165997540276] ON [Production].[TransactionHistoryArchive]
COMMIT TRANSACTION
--VERIFY
USE AdventureWorks2012
GO
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
WHERE o.name LIKE '%TransactionHistoryArchive%'
-- INSERT DATA TO VERIFY DIRECTION OF DATA TO PARTITION
USE [AdventureWorks2012]
GO
INSERT INTO [Production].[TransactionHistoryArchive]
([TransactionID]
,[ProductID]
,[ReferenceOrderID]
,[ReferenceOrderLineID]
,[TransactionDate]
,[TransactionType]
,[Quantity]
,[ActualCost]
,[ModifiedDate])
VALUES
(89265, 1,1,1,'2016-03-04 00:00:00.000','P',999,50.2600,'2016-03-04 00:00:00.000')
GO
--VIEW ACTULA DATA IN THE PARTITION
SELECT * FROM [Production].[TransactionHistoryArchive]
WHERE $PARTITION.[FUNCTION_TRANSHISTORY](TransactionDate) = 4 ;--<< PARTITION 4 CONTAIN ONLY DATA THAT IS NOT IN RANGE OF PARTITION 1,2,3
Also See: