1 / 13

Data Partitioning – SQL Server Satya

Data Partitioning – SQL Server Satya. Agenda. Data Partitioning in SQL Server Architecture Demo. Data Partitioning. Also known as Horizontal Partitioning Large table/index can be split into multiple manageable portions Advantages Improves Query Performance

james-estrada
Download Presentation

Data Partitioning – SQL Server Satya

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Partitioning – SQL Server Satya

  2. Agenda • Data Partitioning in SQL Server • Architecture • Demo

  3. DataPartitioning • 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

  4. DataPartitioning Architecture Table/Indexed View Logical Representation P1 Pn Index /Heap Index/Heap Data pages Data pages

  5. PartitionConcepts • PARTITION FUNCTION • Used to specify partition boundary values • Two types (represents boundary data directions) • LEFT -myself and my left range values • RIGHT -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

  6. Creating Partition Function <=10 <10 >10 & <=50 >=10 & <50 >50 >=50

  7. PartitionPhysical Architecture Database File Group Partition scheme Database File Group A Partition scheme File Group B File Group C File Group D

  8. PartitionConcepts (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”

  9. SPLIT – LEFT Boundary <=10 >10 & <=50 >50 >10 & <=25 >25 & <=50 >50 <=10

  10. MERGE – LEFT Boundary >10 & <=25 >25 & <=50 >50 <=10 <=25 >25 & <=50 >50 <=10

  11. SWITCH OUT Partitioned Table Non Partitioned table Partition 1 Partition 2 Partition 1 SWITCH IN/OUT says “Hey, its your partition by updating metadata.” Partitioned Table Non Partitioned table Partition 1 Partition 1

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

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

More Related