Table Partitioning Implementation with advanced scenarios (Part 2)


Dwonlad Scripts used in this Part : 1-Basic Configuration

Hi  guys today i will complete my series about Table Partitioning Implementation Last post i explain the Concept of Table Partitioning and i covered the below points let’s go today to See How we can Implement Table Partitioning by T-SQL Step by Step with multiple scenarios

Points covered in the last post :

  • What is table partitioning?
  • Why we need to use table partitioning?
  • PROS and CONS of table partitioning?
  • Partitioned Table Rules and Gotchas
  • What is difference Table partitioning Type in SQL Server?

before going to the Technical demo we should know what is table partitioning dependence

Table5

PARTITION FUNCTION:

that maps the rows of a table or index into partitions based on the boundary_value , we can build the Partition Function by two ways based on our Requirement and our business needed

  • RANGE RIGHT
  • RANGE LEFT

another thing the boundary_value we can do it on three data type

  • INT COLUMN
  • DATETIME COLUMN
  • CHAR COLUMN

PARTITION SCHEME:

that maps the partitions of a partitioned table or index to filegroups , partition scheme must be created on partition function , partition scheme can be used for one or more partitioned tables, indexes, and indexed views.

PARTITION TABLE/INDEX:

is tied to a particular partition scheme when it is created , partition table has only an indirect relationship, through the partition scheme, to the partition function at the end The relationship between a partition function and a partition scheme is one-to-many as is the relationship between a partition scheme and partitioned tables and indexes , because moving data in and out of a partitioned table usually requires modifying the partition function for more information :

let’s go now for the TECHNICAL DEMO to know How we can implement the table partitioning step by step from Scratch

How to Implement Partitioned Tables 

to implement any partitioned table from SCRATCH we have basic 6 steps we will do it now by T-SQLand by SQL SERVER WIZARD

  • Create Database (T-SQL / Wizard)
  • Create FILEGROUP (T-SQL / Wizard)
  • Create files and add it to the FILEGROUP (T-SQL / Wizard)
  • Create partition Function (RANGE RIGHT / RANGE LEFT) (T-SQL only)
  • Create partition Scheme (T-SQL only)
  • Create Table Partitioning (T-SQL only)

at the first i will do it by T-SQL

  • Create database

I will create new database [DBForPartitioning] to be for all our workshop to the end of the series

CREATE DATABASE [DBForPartitioning]
GO
ALTER DATABASE [DBForPartitioning]
MODIFY FILE ( NAME = N'DBForPartitioning', SIZE = 256MB , MAXSIZE = 10GB , FILEGROWTH = 512MB );
ALTER DATABASE [DBForPartitioning]
MODIFY FILE ( NAME = N'DBForPartitioning_log', SIZE = 128MB , FILEGROWTH = 128MB );
GO
  • Create FILEGROUP

here we will create four filegroup (FG1 ,FG2 , FG3 , FG4) to can use it on the Partition Scheme

USE [master]
GO
ALTER DATABASE [DBForPartitioning] ADD FILEGROUP [FG1]
ALTER DATABASE [DBForPartitioning] ADD FILEGROUP [FG2]
ALTER DATABASE [DBForPartitioning] ADD FILEGROUP [FG3]
ALTER DATABASE [DBForPartitioning] ADD FILEGROUP [FG4]
GO
  • Create / Add files to filegroup

in this step i will create four files and add it on the FILEGROUP and all of this i will do it dynamically

Use DBForPartitioning
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 = 'DBForPartitioning'
WHILE @i <= 4
 BEGIN
 SET @sql = N'ALTER DATABASE DBForPartitioning ADD FILE (name=File' + CAST(@i ASNCHAR(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 files

Range Direction Right and Datetime Boundary Values

Next three steps i will do every thing on the RANGE RIGHT and RANGE LEFT so i will work on parallel

  • Create partitioning function

in this case i will do partition function with boundary_value DATETIME and

Use DBForPartitioning
GO
CREATE PARTITION FUNCTION PF_DBForPartitioning_RangeRight (datetime)
AS RANGE RIGHT FOR VALUES ( '2008-01-01','2009-01-01', '2010-01-01');
  • Create Partition Scheme

i will create Partition scheme on PF(PF_DBForPartitioning_RangeRight) on the four filegroup

</pre>
Use DBForPartitioning
GO
CREATE PARTITION SCHEME PS_DBForPartitioning_RangeRight
AS PARTITION PF_DBForPartitioning_RangeRight
TO (FG1, FG2, FG3 ,FG4)
GO
<pre>

Range RIght

  • Create Table partitioning

now i will create table Partitioning on Partitioning Scheme [PS_DBForPartitioning_RangeRight] on datetime Column

Use DBFOrPartitioning
GO
CREATE TABLE Partitioning_RangeRight
(Partitioning_ID int ,
Partitioning_time datetime)
ON PS_DBForPartitioning_RangeRight (Partitioning_time);
GO

Now the 6 basic steps for any table partitioning is finished and i will go now for fill the table by 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 the data in the table
</pre>
Use DBFOrPartitioning
GO
INSERT INTO Partitioning_RangeRight (Partitioning_ID, Partitioning_time)
SELECT 1 ,'2007-01-01'UNION ALL
SELECT 2 ,'2007-10-01'UNION ALL
SELECT 3 ,'2008-01-01'UNION ALL
SELECT 4 ,'2008-08-09'UNION ALL
SELECT 5 ,'2008-12-30'UNION ALL
SELECT 6 ,'2009-01-01'UNION ALL
SELECT 7 ,'2009-05-24'UNION ALL
SELECT 8 ,'2010-01-24'UNION ALL
SELECT 9 ,'2010-05-24'UNION ALL
SELECT 10,'2011-05-24'
GO
<pre>

Return Partition Information by T-SQL Scripts

  • Select table Partitioned in DB
Use DBFOrPartitioning
GO
select distinct t.name
from sys.partitions p
inner join sys.tables t
on p.object_id = t.object_id
where p.partition_number <> 1
  • Stored procedure to Return the Partitioned Columns

Exec partitioning_Column ‘Partitioning_RangeRight’

Create Proc partitioning_Column (@Table_Name Nvarchar(500))
AS
Begin
SELECT CAST(ic.partition_ordinal AS INT) AS [ID],
 c.name AS ColumnName
FROM sys.tables AS tbl
 INNER JOIN sys.indexes AS idx
 ON idx.object_id = tbl.object_id
 AND idx.index_id < 2
 INNER JOIN sys.index_columns ic
 ON (ic.partition_ordinal > 0)
 AND (ic.index_id = idx.index_id
 AND ic.object_id = CAST(tbl.object_id AS INT))
 INNER JOIN sys.columns c
 ON c.object_id = ic.object_id
 AND c.column_id = ic.column_id
WHERE (tbl.name = @Table_Name
 AND SCHEMA_NAME(tbl.schema_id) = 'dbo')
ORDER BY [ID]
END
  • Select Partition_id with Partition_number
select partition_id, index_id, partition_number, Rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='Partitioning_RangeRight'
GO
  • Select the PartitionFunction and PartitionScheme Name
select ps.Name PartitionScheme, pf.name PartitionFunction
 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('Partitioning_RangeRight')
  • Stored procedure to return all the detail about table Partitioned and the data exist on it
Create proc PartitionTableDetail(@tablename Nvarchar(500))
As
begin
SELECT
ISNULL(quotename(ix.name),'Heap') as IndexName
,ix.type_desc as type
,prt.partition_number
,prt.data_compression_desc
,ps.name as PartitionScheme
,pf.name as PartitionFunction
,fg.name as FilegroupName
,case when ix.index_id < 2 then prt.rows else 0 END as Rows
,au.TotalMB
,au.UsedMB
,case when pf.boundary_value_on_right = 1 then 'less than' whenpf.boundary_value_on_right is null then '' else 'less than or equal to' End asComparison
,fg.name as FileGroup
,rv.value As Rang_value,
T.name AS Range_Type
FROM sys.partitions prt
inner join sys.indexes ix
on ix.object_id = prt.object_id and
ix.index_id = prt.index_id
inner join sys.data_spaces ds
on ds.data_space_id = ix.data_space_id
left join sys.partition_schemes ps
on ps.data_space_id = ix.data_space_id
left join sys.partition_functions pf
on pf.function_id = ps.function_id
inner join sys.partition_parameters PP
on pf.function_id = PP.function_id
inner join sys.types t
on t.system_type_id = PP.system_type_id
left join sys.partition_range_values rv
on rv.function_id = pf.function_id AND
rv.boundary_id = prt.partition_number
left join sys.destination_data_spaces dds
on dds.partition_scheme_id = ps.data_space_id AND
dds.destination_id = prt.partition_number
left join sys.filegroups fg
on fg.data_space_id = ISNULL(dds.data_space_id,ix.data_space_id)
inner join (select str(sum(total_pages)*8./1024,10,2) as [TotalMB]
,str(sum(used_pages)*8./1024,10,2) as [UsedMB]
,container_id
from sys.allocation_units
group by container_id) au
on au.container_id = prt.partition_id
WHERE prt.OBJECT_ID = object_id(@tablename)
order by ix.type_desc;
END
Exec PartitionTableDetail 'Partitioning_RangeRight'

Table Info

this not the end still i have lot of Scripts it will help any DBA to know what he need about Partitioning table in any Environment without much effort and without time only 5 SEC he can know every thing next post i will explain the below point

  • Range Direction left and Datetime Boundary Values
  • Table partitioning by SQL Server wizard
  • new Scripts for investigation

Follow me to know more Secrets in Database partitioning To See all my posts you can go here  View all my tips

Advertisements

About Mustafa EL-Masry

I love fixing problems and learning new technologies that can help me fix those problems faster and better than I did the time before. In my spare time I like to read, speak, write blogs and learn new things. I currently work as a Principal Database Administrator where I solve fairly interesting problems on fairly large databases. And troubleshooting High sensitive Performance Cases ► I am Microsoft Certified in SQL Server Administration and Development 2008 , 2012 MCTS, MCTIP , MCSA , MCSE ► I have deep practical knowledge about T-SQL performance , HW Performance issues, Data Warehousing and data mart solutions , SQL Server Replication, Clustering solutions (Active Active and active passive)and Database Designs for different kinds of systems , HAG, I worked on All SQL Server Versions (2005,2008,2008R2,2012,2014) diving deeply more check the below information about me : ► Founder of Community :SQL Server Consultation :http://MostafaElmasry.com/ ► Co-Founder : http://www.SQLGULF.net ► Audience Marketing Manager and Executive Board member: SQLSERVER PERFORMANCE TUNING http://sqlserver-performance-tuning.net/ ► LinkedIn: https://www.linkedin.com/in/mostafaelmasry ► My Community Annual report : http://mostafaelmasry.com/2014/annual-report/ ► I am Technical Writer and Reviewer : http://www.slideshare.net/MostafaElmasry3/ ► Fluent Participator at Microsoft Forums of SQL Server at http://Social.technet.microsoft.com ► +200 blogs in SQL Server technology English and Arabic: http://mostafaelmasry.com/community-founder/all-tips/ ► Microsoft Profile: https://www.mcpvirtualbusinesscard.com/VBCServer/EngMostafaElamsry/profile For any inquiries you can contact me on my phone or by mail: Mobile: +966 543990968, +2 01114668821 Mail: dbconsultant@mostafaelmasry.com & SQLGULF@mostafaelmasry.com
Gallery | This entry was posted in Home. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s