Data Partitioning SQL Server Satya Agenda Data Partitioning

  • Slides: 13
Download presentation
Data Partitioning – SQL Server Satya

Data Partitioning – SQL Server Satya

Agenda • Data Partitioning in SQL Server • Architecture • Demo

Agenda • Data Partitioning in SQL Server • Architecture • Demo

Data Partitioning • Also known as Horizontal Partitioning ▫ Large table/index can be split

Data Partitioning • Also known as Horizontal Partitioning ▫ Large table/index can be split into multiple manageable portions • Advantages ▫ ▫ ▫ Improves Query Performance Better Data Manageability and Cost Effective Deleting and Moving data is faster from partitions Narrow downs the index maintenance window OLTP/DSS – Operational/Non-Operational data

Data Partitioning Architecture Table/Indexed View Logical Representation P 1 Pn Index /Heap Index/ Heap

Data Partitioning Architecture Table/Indexed View Logical Representation P 1 Pn Index /Heap Index/ Heap Data pages

Partition Concepts • PARTITION FUNCTION ▫ Used to specify partition boundary values ▫ Two

Partition Concepts • PARTITION FUNCTION ▫ Used to specify partition boundary values ▫ Two types (represents boundary data directions) �LEFT �RIGHT -myself and my left range values -myself and my right range values • PARTITION SCHEME ▫ Logical & Partitions physically aligned ▫ Span over single or multiple file groups ▫ Can specify each partition can go to a individual file group or all partitions can go into a single file group

Creating Partition Function CREATE PARTITION FUNCTION My. Partition. Funcation. Name_pfn(int) AS RANGE LEFT FOR

Creating Partition Function CREATE PARTITION FUNCTION My. Partition. Funcation. Name_pfn(int) AS RANGE LEFT FOR VALUES(10, 50) <=10 >10 & <=50 >50 CREATE PARTITION FUNCTION My. Partition. Funcation. Name_pfn(int) AS RANGE RIGHT FOR VALUES(10, 50) <10 >=10 & <50 >=50

Partition Physical Architecture CREATE PARTITION SCHEME My. Partition. Scheme_ps AS PARTITION My. Partition. Funcation.

Partition Physical Architecture CREATE PARTITION SCHEME My. Partition. Scheme_ps AS PARTITION My. Partition. Funcation. Name_pfn ALL TO ([FG]) --- specifying single file group --TO ([FGA], [FGB]) --- specifying multiple file groups Database Partition scheme File Group A Database Partition scheme File Group B File Group C File Group D

Partition Concepts (2) • SPLIT/MERGE ▫ SPLIT �Introduces new boundary �Partition will be added

Partition Concepts (2) • SPLIT/MERGE ▫ SPLIT �Introduces new boundary �Partition will be added to respective side (L/R) ▫ MERGE �Deletes boundary �Partition will be merged to the respective side (L/R) • SWITCH IN/OUT ▫ Moving partition from partitioned table to other partitioned table called “in” ▫ Moving partition from partitioned table to non partitioned table called “out”

SPLIT – LEFT Boundary <=10 >10 & <=50 >50 ALTER PARTITION SCHEME My. Partition.

SPLIT – LEFT Boundary <=10 >10 & <=50 >50 ALTER PARTITION SCHEME My. Partition. Scheme_ps NEXT USED [FG]; -- specifying file group for new boundary ALTER PARTITION FUNCTION My. Partition. Function. Name_pfn() SPLIT RANGE(25) <=10 >10 & <=25 >25 & <=50 >50

MERGE – LEFT Boundary <=10 >10 & <=25 >25 & <=50 >50 ALTER PARTITION

MERGE – LEFT Boundary <=10 >10 & <=25 >25 & <=50 >50 ALTER PARTITION FUNCTION My. Partition. Function. Name_pfn() MERGE RANGE(10) <=10 <=25

SWITCH OUT Partitioned Table Partition 1 Partition 2 Non Partitioned table Partition 1 ALTER

SWITCH OUT Partitioned Table Partition 1 Partition 2 Non Partitioned table Partition 1 ALTER TABLE Partitioned. Table. Name SWITCH PARTITION 2 TO Non. Partitioned. Table. Name -- <Partition number> TO Destination. Partitioned. Table. Name<destination Partition number> SWITCH IN/OUT says “Hey, its your partition by updating metadata. ” Partitioned Table Partition 1 Non Partitioned table

Demo Presentation and Scripts will be mailed you at the end Or Alternatively available

Demo Presentation and Scripts will be mailed you at the end Or Alternatively available on http: //satyasql. wordpress. com To become master…

Additional Information • Would not support for composite key • SQL Server 2008 SP

Additional Information • Would not support for composite key • SQL Server 2008 SP 2 or Later versions boosted creation of partitions up to 15000