Wednesday, 29 January 2014

Horizontal partition of MS SQL server database table


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 ]

   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
  NAME = N'Employee_DB_log',
 FILENAME = N'C:\EmployeeData\Employee_DB_log.ldf' ,
 SIZE = 9216KB , FILEGROWTH = 10%


MS SQL Server Primary Database File
MS SQL Server Secondary Database File
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.

FOR VALUES ('2013-12-31')

>   2013
<=  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.

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.

Posted by: MR. JOYDEEP DAS

1 comment: