Partitioning techniques in SQL Server Eladio Rincon ERinconsolidq

Partitioning techniques in SQL Server Eladio Rincon (ERincon@solidq. com) Javier Loria (javier@solidq. com) Solid Quality Mentors

Agenda Why? How? What? Where? Partitioning Toolbox © 2008 Solid Quality Mentors 2

Agenda Why? • • Definition I/O Basics Table Basics Operational Benefits How? What? Where? Toolbox © 2008 Solid Quality Mentors 3

Definition Physical segregation of a single logical table name into multiple, identical physical structures © 2008 Solid Quality Mentors 4

I/O Basics Operation File SELECT Database Random INSERT UPDATE DELETE Log Bulk Insert Database Random Full Backup Database Sequential Synchronous * Log Backup Log Synchronous - Read ? ? Sequential Database Random Reindex 8 Type OLTP 8 x 5 © 2008 Solid Quality Mentors Sequential Random 24 Write Synchronous - - Synchronous - Asynchronous 8 24 8 2 4 S Synchronous OLTP 24 x 7 S 2 4 S S 2 4 OLAP 5

Table Basics © 2008 Solid Quality Mentors 6

Operational Benefits Performance Availability Bulk. Insert (OLAP) Full Backups Query (OLPT/OLAP) © 2008 Solid Quality Mentors Maintainability Archiving Pruning Reindex 7

Poor’s Mans Table Partitioning Creating and using partitioned views

Agenda Why? How? • • Partition Function Partition Scheme Create Table Reloaded Create Index Reloaded What? Where? Toolbox © 2008 Solid Quality Mentors 9

Partition Function CREATE PARTITION FUNCTION Annual (INT) AS RANGE RIGHT FOR VALUES (20060101 , 20070101, 20080101, 20090101, 20100101); © 2008 Solid Quality Mentors 10

Partition Scheme CREATE PARTITION SCHEME Annual AS PARTITION Annual TO (Empty. History, Fact 2006, Fact 2007, Fact 2008, Fact 2009, Empty. Future) © 2008 Solid Quality Mentors 11

Create Table Reloaded CREATE TABLE Sales. Data. Mart. Fact. Vendas( Id. Dim. Date INT NOT NULL , Id. Dim. Product INT NOT NULL , Id. Dim. Customers INT NOT NULL , Id. Dim. Store INT NOT NULL , Ordered. Quantity INT NOT NULL , Total. Sales. Amount DECIMAL (19, 2) NOT NULL , Total. Product. Cost DECIMAL (19, 2) NOT NULL , POSNum INT NOT NULL , Invoce. Num INT NOT NULL ) ON Annual(Id. Dim. Date); © 2008 Solid Quality Mentors 12

Create Index Reloaded CREATE CLUSTERED INDEX IDX_Fact. Sales ON Sales. Data. Mart. Fact. Sales(Id. Dim. Date , Id. Dim. Store) ON Annual(Id. Dim. Date); ALTER TABLE Sales. Data. Mart. Fact. Sales ADD CONSTRAINT PK_Fact. Sales PRIMARY KEY(Id. Dim. Date, Id. Dim. Product , Id. Dim. Customers, Id. Dim. Store) ON Annual(Id. Dim. Date); © 2008 Solid Quality Mentors 13

Partition 101 Partitioned Hello World

Agenda Why? How? What? • • Table Design Patterns Partition Types Partition Methods Index Partitions Where? How? © 2008 Solid Quality Mentors 15

Table and Design Patterns OLTP • Reference • Transactional • History/Audit © 2008 Solid Quality Mentors OLAP • Dimension • Fact Tables 16

Partition Types: Horizontal Out of the Box: in SQL 2005/2008 17

Horizontal Partition Methods • RANGE – Out of the Box (SQL 2005, SQL 2008) • HASH – Build your own (Calculated Column/Hash/Range) • LIST – Build your own (RANGE or Partitioned Views) © 2008 Solid Quality Mentors 18

Partition Types: Vertical Out of the Box: (n)text, image, xml, (n)varchar(max), varbinary(max) and CLR Types. Build your own: Views and Instead of Trigger 19

Index Structures Clustered Index © 2008 Solid Quality Mentors Non-clustered Index 20

Partitioned Table/non-partitioned Index Clustered Index © 2008 Solid Quality Mentors Non-clustered Index 21

Non Partitioned Table/Partitioned Index Clustered Index © 2008 Solid Quality Mentors Non-clustered Index Filtered Indexes 22

Partitioned Table/Aligned Index Clustered Index © 2008 Solid Quality Mentors Non-clustered Index 23

Partitioned View CREATE VIEW Sales. Data. Mart. Fact. Sales. By. Store WITH SCHEMABINDING AS( SELECT Id. Dim. Date, Id. Dim. Store , SUM(Ordered. Quantity) AS Ordered. Quantity , SUM(Total. Sales. Amount) AS Total. Sales. Amount , SUM(Total. Product. Cost) AS Total. Product. Cost , COUNT_BIG(*) AS Order. Num FROM Sales. Data. Mart. Fact. Sales GROUP BY Id. Dim. Date, Id. Dim. Store) GO CREATE UNIQUE CLUSTERED INDEX PK_Fact. Sales. By. Store ON Sales. Data. Mart. Fact. Sales. By. Store(Id. Dim. Date, Id. Dim. Store) ON Annual(Id. Dim. Date); GO

Partitioning and Query Performance

Agenda Why? How? What? Where? • Logical/Physical • Method Toolbox © 2008 Solid Quality Mentors 26

Without Partitions Partition Filegroup File Disk Primary

Simple: Partitioned Partition Filegroup File Disk Primary 2006 2007 2008 2009

Multiple: Without Partitions Partition Filegroup File Disk Primary

Multiple: with Partitions (1/3) Partition Filegroup lity File Disk Primary 2006 2007 2008 2009

Multiple: with Partitions (2/3) Partition Filegroup File Disk Primary 2006 2007 2008 2009

Multiple: with Partitions (3/3) Partition Filegroup File Disk Primary 2006 2007 2008 2009

Partitioning Availability and Maintainability Backup/Restore

Agenda Why? How? What? Where? Toolbox • Pruning • Loading © 2008 Solid Quality Mentors 34

Pruning

Sliding Window Loading

Agenda Why? How? What? Where? Partitioning Toolbox © 2008 Solid Quality Mentors 37

Thank You!! Please remember to fill out evaluations