Table Partitioning
- Partitioned tables are a new feature available in SQL Server version 2005, aimed mainly at improving the performance of large database systems.
- The feature is only available for enterprise and developer edition.
- For other editions you can get a similar functionality with a partitioned view
STEP : 1 Create Database with multiple FileGroups and multiple Files
- PartitionDemo.mdf
- PartitionDemo_log.ldf
- PartitionDemoFile1.ndf
- PartitionDemoFile2.ndf
It would be advisable if you create Primary FileGroup Files and NewFileGroup Files on separate, that will boost the performance.
STEP : 2 Create Partition Range Function
USE PartitionDemo;GO--- Step 2 : Create Partition Range FunctionCREATE PARTITIONFUNCTION PartitionDemo_Range (INT)AS RANGE LEFT FOR
VALUES (10,20,30);GOBasically this will create 4 partitions
- Partition 1 ------- <= 10
- Partition 2 ------- >10 and <= 20
- Partition 3 ------- >20 and <= 30
- Partition 4 ------- >30
Here LEFT keyword specifies that boundary values would be considered in left bucket. so 20 would come under Partition2 bucket rather than Partition2 bucket
STEP : 3 Create Partition Scheme
USE PartitionDemo;GO--- Step 3 : Attach Partition Scheme to FileGroupsCREATEPARTITION SCHEME PartitionDemo_SchemeAS PARTITION PartitionDemo_RangeTO([PRIMARY], [FG1], [FG2], [FG2]);GOThis is a process of assigning partitions to different filegroups.
This says that
- Partition 1 --------- Primary FileGroup
- Partition 2 --------- FG1 FileGroup
- Partition 3,4 ------- FG2 FileGroup
STEP : 4 Create Table with Partition Key and Partition Scheme
USE PartitionDemo;GO--- Step 4 : Create Table with Partition Key and Partition SchemeCREATE TABLE TestTable(ID INT NOT NULL,Date DATETIME)ON PartitionDemo_SchemeSo this says that table has been linked with scheme and "ID" column will decide how record would be distributed among different partitions
STEP : 5 Create Index on Partitioned Table
USE PartitionDemoPartitionDemo_SchemeThis is highly recommended to improve performance
STEP : 6 Insert Data into Partitioned Table
USE PartitionDemo;GO--- Step 6 : Insert Data in Partitioned TableINSERT INTOTestTable (ID, Date) -- Inserted in Partition 1VALUES (1,GETDATE());INSERT INTOTestTable (ID, Date) -- Inserted in Partition 1VALUES (10,GETDATE());INSERT INTOTestTable (ID, Date) -- Inserted in Partition 2VALUES (11,GETDATE());INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2VALUES (20,GETDATE());INSERT INTO TestTable (ID, Date) -- Inserted in Partition 3VALUES (21,GETDATE());INSERT INTO TestTable (ID, Date) -- Inserted in Partition 3VALUES (30,GETDATE());INSERT INTO TestTable (ID, Date) -- Inserted in Partition 4VALUES (31,GETDATE());STEP : 7 Verify rows against different partitions
USE PartitionDemo;GO--- Step 8 : Verify Rows Inserted in PartitionsSELECT *FROMsys.partitionsWHERE OBJECT_NAME(OBJECT_ID)='TestTable';GOSOMETHING EXTRA
- We can add/remove partition using SPLIT and MERGE keywords
- If the index contains the partitioning column then the index is referred to as being 'aligned' with the table
- Partitioning column can be number, date, string datatype
- We can consider Calculated column as Partition key also
Horizontal partition of MS SQL server database table
Introduction
One of my friends asks me a question that, he has a large table and the table is well indexed. When he use the query (SELECT statement) he got the INDEX Seek (Not SCAN) but the problem is the response time of the query is so slow.
What u thinks? There is N number of reason related to it. Bust most common is that the table has huge data and it needs to be partitioned.
So all of you understand that, in this article we are going to discuss related to table partition.
Some general facts related to Table Partition
It is the horizontally partition the table. The technology came from MS SQL 2005 onwards. It is an MS SQL Server Enterprise edition feature. But we can test it in Developer Edition also. To get the proper effects of table partitioning we need multiple storage location (physical storage).
It allows us to different database files, which can be located on different disks to improve performance.
What is in Before MS SQL 2005
Before MS SQL 2005 we do not have the facility to make Horizontal partition of MS SQL server database table. But we can create the separate table of different file group of Database and create a VIEW by using UNION.
How to make Horizontal partition of table
Step-1 [ Create the Database with Different File group ]
CREATE DATABASE [Employee_DB] ON
PRIMARY
(
NAME = N'Employee_DB',
FILENAME = N'C:\EmployeeData\Employee_DB.mdf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB
),
FILEGROUP [EmployeeDBSecond]
(
NAME = N'Employee_DB_Second',
FILENAME =N'C:\EmployeeData\PEmployee_DB_Second.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'Employee_DB_log',
FILENAME = N'C:\EmployeeData\Employee_DB_log.ldf' ,
SIZE = 9216KB , FILEGROWTH = 10%
)
GO
Here
Employee_DB.mdf
|
MS SQL Server Primary Database File
|
PEmployee_DB_Second.ndf
|
MS SQL Server Secondary Database File
|
Employee_DB_log.ldf
|
MS SQL Server Transaction Log File
|
“Please note that in this example we are using a single storage to make the primary and secondary file group. It is better if we take the different physical storage for primary and secondary file group storage to increate the IO. So increase the performance”.
Step-2 [ Create Partition Function ]
The partition function defines that how to separate the data. The function is not related to any specified table that determines how the data split occurs.
In our example we take Sales Order information where current year records stores in Primary file group and all the older data must store in secondary file group.
CREATE PARTITION FUNCTION fnEmpDBPartFunc (DATE)
AS RANGE LEFT
FOR VALUES ('2013-12-31')
1
|
> 2013
|
2
|
<= 2013
|
Step-3 [ Creating Partition Schema ]
Here the Partition function is created, so the SQL Server knows that how to segregate the data but doesn’t know that where to put the partitioned data. This is done by Partition Schema and the Partition schema is linked with Partition function.
CREATE PARTITION SCHEME EmpDBParttScheme
AS PARTITION fnEmpDBPartFunc
TO ([EmployeeDBSecond], [PRIMARY])
File Group – PRIMARY
|
> 2013
|
File Group - EmployeeDBSecond
|
<= 2013
|
Strep-4 [ Creating Partition Table ]
Now we create Table on partition schema.
CREATE TABLE Table_SalesOrders
(
OrderID INT,
CustName VARCHAR(50),
OrderDate DATE
)
ON EmpDBParttScheme (OrderDate)
Step- 5 [ Inserting Records ]
-- Will go to [PRIMARY] File Group
INSERT INTO Table_SalesOrders
(OrderID, CustName, OrderDate)
VALUES (1, 'Joydeep Das', '2014-01-10')
-- Will go to [EmployeeDBSecond] File Group
INSERT INTO Table_SalesOrders
(OrderID, CustName, OrderDate)
VALUES (2, 'Manayan Chaturvedhi', '2013-06-22')
Step-6 [ Checking the Partition ]
SELECT partition_id, object_id, partition_number,rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('Table_Orders')
partition_id object_id partition_number rows
72057594038910976 5575058 1 1
72057594038976512 5575058 2 1
Sterp-7 [ Creating CLUSTERED Index ]
CREATE CLUSTERED INDEX [Clust_Orders] ON [dbo].[Table_SalesOrders]
(
[OrderID] ASC
) ON EmpDBParttScheme (OrderDate)
Hope you like it.
Partition Table
The Table partitioning can make very large tables and index easier to manage and improve the performance of the query. This article is related to table partitioning concept and how you crate the partition table. Hope all of my readers will like it.
History
Table partitioning was introduced in SQL Server 2005 Enterprise Edition and enhanced in SQL Server 2008 Enterprise.
Intro
Growing of database is a fact and when it grows more than the hundreds of GB, it can be more difficult to load new data, move old data and maintain indexes. Just the sheer size of the table causes such operations to make much longer. Even the data that must be loaded or removed can be very sizable, making INSERT and DELETE operations on the table impractical.
The Microsoft SQL Server 2008 provides table partitioning to make such operations more manageable.
Partitioning a large table divides the table and its indexes into smaller partitions, so that maintenance operations can be applied on a partition-by-partition basis, rather than on the entire table. In addition, the SQL Server optimizer can direct properly filtered queries to appropriate partitions rather than the entire table.
Way of Partition
There are two primary way to partition data into multiple tables.
Horizontal Partitioning
Where the selected subset of rows are placed in different tables. A view is created over all the tables, and queries directly to the view, the result is partition view.
Vertical partitioning
Where the columns of a very wide table are spread across multiple tables containing distinct subsets of the columns with the same number of rows. The result is multiple tables containing the same number of rows but different columns, usually with the same primary key column in each table. Often a view is defined across the multiple tables and queries directed against the view. SQL Server does not provide built-in support for vertical partitioning, but the new sparse columns feature of SQL Server 2008 can be a better solution for tables that require large numbers of columns.
SQL Server's table partitioning differs from the above two approaches by partitioning a single table: Multiple physical tables are no longer involved. When a table is created as a partitioned table, SQL Server automatically places the table's rows in the correct partition, and SQL Server maintains the partitions behind the scenes. You can then perform maintenance operations on individual partitions, and properly filtered queries will access only the correct partitions. But it is still one table as far as SQL Server is concerned.
The Technology at a glance
A partitioned table is a unique kind of table in SQL Server and It depends on two pre-existing objects.
1. The Partition function
2. Partition scheme
The bellow model describes it in graphically.
Partitioned Table à Partition Schema à Partition Function
A partition table has columns and that is identified as partition columns and the column is referenced when the table is created. The partitioned table must be created on a partition scheme, which defines the filegroup storage locations for the partitioned table. The partition scheme in turn depends on a previously created partition function that defines the number of partitions the table will have and how the boundaries of the partitions are defined. After all these pieces are in place, it is possible to use metadata-only operations to load and remove data from the partitioned table almost immediately.
Partitioning a SQL Server database table is a three-step process
1. Partition function
2. Partition scheme
3. Partition the table
Creating the Partition function
We define the partitions by specifying the boundaries of each partition. For example, suppose we have a Customers table that contains information on all of our customers, identified by a unique customer number, ranging from 1 to 1,000,000. We might decide to partition that table into four equally spaced partitions, using the following partition function.
CREATE PARTITION FUNCTION cust_part_func (int)
AS RANGE RIGHT
FOR VALUES (250000, 500000, 750000)
These boundaries define four partitions. The first contains all values less than 250,000. The second contains values between 250,000 and 499,999. The third contains values between 500,000 and 749,999. All values greater than or equal to 750,000 go in the fourth partition.
Notice that the "RANGE RIGHT" clause in this example. This indicates that the boundary value itself should go in the partition on the right side. Alternatively, if I had used "RANGE LEFT", the first partition would have included all values less than ]or equal to 250,000; the second partition would have included values between 250,001 and 500,000, and so on.
Notice that the "RANGE RIGHT" clause in this example. This indicates that the boundary value itself should go in the partition on the right side. Alternatively, if I had used "RANGE LEFT", the first partition would have included all values less than ]or equal to 250,000; the second partition would have included values between 250,001 and 500,000, and so on.
Creating the Partition scheme
This process is linking the partitions to filegroups.
if I had four filegroups named "fg1" through "fg4", the following partition scheme would do the trick:
CREATE PARTITION SCHEME cust_part_scheme
AS PARTITION cust_part_func
TO (fg1, fg2, fg3, fg4)
Notice that we now link a partition function to the partition scheme, but we still haven’t linked the partition scheme to any specific database table. That’s where the power of reusability comes into play. We could use this partition scheme (or just the partition function, if we desired) on any number of database tables.
Creating the Partition the table
After defining a partition scheme, you’re now ready to create a partitioned table. This is the simplest step of the process. You simply add an "ON" clause to the table creation statement specifying the partition scheme and the table column to which it applies. You don’t need to specify the partition function because the partition scheme already identifies it.
CREATE TABLE cust
(FName nvarchar(40),
LName nvarchar(40),
CNum int)
ON cust_part_scheme (CNum)
Create Portion Table [ All together ]
CREATE PARTITION FUNCTION cust_part_func (int)
AS RANGE RIGHT
FOR VALUES (250000, 500000, 750000)
GO
CREATE PARTITION SCHEME cust_part_scheme
AS PARTITION cust_part_func
TO (fg1, fg2, fg3, fg4)
GO
CREATE TABLE cust
(FName nvarchar(40),
LName nvarchar(40),
CNum int)
ON cust_part_scheme (CNum)
GO
Hope you like it.
Partitioning existing table SQL2008R2
Create partioning for existing table with more than 1120962253 rows today. It was amazing to see the query performance.
Make sure to create a files groups ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4] for partioned table
Also you can create Separate file group for Non-Clustered Index on different drive NCINDEX_FG5
--Step 2. Creating a Partition Scheme
CREATE PARTITION SCHEME SvrIdScheme
AS PARTITION PFSvrId_Left
TO ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4],[PRIMARY])
--Step 3. CREATE CLUSTERED INDEX 1
Make sure to create a files groups ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4] for partioned table
Also you can create Separate file group for Non-Clustered Index on different drive NCINDEX_FG5
USE [TestDB]
GO
GO
--Step 1. Creating a Partition Function
CREATE PARTITION FUNCTION PFSvrId_Left (numeric (10,0))
AS RANGE LEFT FOR VALUES (399, 499, 699, 799);
CREATE PARTITION FUNCTION PFSvrId_Left (numeric (10,0))
AS RANGE LEFT FOR VALUES (399, 499, 699, 799);
--The result for this RANGE LEFT assignment is:
--{min … 399}, {400 … 499}, {500 … 699}, {799 … max}
--{min … 399}, {400 … 499}, {500 … 699}, {799 … max}
--Step 2. Creating a Partition Scheme
CREATE PARTITION SCHEME SvrIdScheme
AS PARTITION PFSvrId_Left
TO ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4],[PRIMARY])
--Step 3. CREATE CLUSTERED INDEX 1
-- Now create a Partitioned using clustered index based Scheme
CREATE CLUSTERED INDEX [idx_LoadID] ON [dbo].[MyTable]
(
[M_ID] ASC
) ON SvrIdScheme(s_id)
CREATE CLUSTERED INDEX [idx_LoadID] ON [dbo].[MyTable]
(
[M_ID] ASC
) ON SvrIdScheme(s_id)
-- Step 4. CREATE NONCLUSTERED INDEX 2 on Separate file group
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
( [t_id] ASC, [s_id] ASC
)ON [NCINDEX_FG5]
GO
--– Check for new partitions
SELECT partition_id, object_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('MyTable')
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
( [t_id] ASC, [s_id] ASC
)ON [NCINDEX_FG5]
GO
--– Check for new partitions
SELECT partition_id, object_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('MyTable')
GO
