Transcript Partitioning techniques in SQL Server

Partitioning techniques in SQL
Server
Eladio Rincon ([email protected])
Javier Loria ([email protected])
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
© 2008 Solid Quality Mentors
4
I/O Basics
Operation
File
Type
SELECT
Database Random
INSERT
UPDATE
DELETE
Log
Bulk Insert
Database Random
Full Backup
Database Sequential
Synchronous
*
Log Backup
Log
Synchronous
-
?
?
Sequential
Database Random
Reindex
8
Sequential
Random
24
© 2008 Solid Quality Mentors
Read
Write
Synchronous
-
-
Synchronous
-
Asynchronous
8
24
8
2
4
S
Synchronous
S
2
4
S
2
4
S
5
Table Basics
© 2008 Solid Quality Mentors
6
Operational Benefits
Performance
Query
(OLPT/OLAP)
© 2008 Solid Quality Mentors
Bulk-Insert
(OLAP)
Availability
Full Backups
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 (EmptyHistory, Fact2006, Fact2007,
Fact2008, Fact2009, EmptyFuture)
© 2008 Solid Quality Mentors
11
Create Table Reloaded
CREATE TABLE SalesDataMart.FactVendas(
IdDimDate
INT NOT NULL
, IdDimProduct
INT NOT NULL
, IdDimCustomers
INT NOT NULL
, IdDimStore
INT NOT NULL
, OrderedQuantity
INT NOT NULL
, TotalSalesAmount DECIMAL (19, 2) NOT NULL
, TotalProductCost DECIMAL (19, 2) NOT NULL
, POSNum
INT NOT NULL
, InvoceNum
INT NOT NULL
) ON Annual(IdDimDate);
© 2008 Solid Quality Mentors
12
Create Index Reloaded
CREATE CLUSTERED INDEX IDX_FactSales
ON SalesDataMart.FactSales(IdDimDate
, IdDimStore) ON Annual(IdDimDate);
ALTER TABLE SalesDataMart.FactSales
ADD CONSTRAINT PK_FactSales
PRIMARY KEY(IdDimDate, IdDimProduct
, IdDimCustomers, IdDimStore)
ON Annual(IdDimDate);
© 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 SalesDataMart.FactSalesByStore WITH
SCHEMABINDING AS(
SELECT IdDimDate, IdDimStore
, SUM(OrderedQuantity) AS OrderedQuantity
, SUM(TotalSalesAmount) AS TotalSalesAmount
, SUM(TotalProductCost) AS TotalProductCost
, COUNT_BIG(*) AS OrderNum
FROM SalesDataMart.FactSales
GROUP BY IdDimDate, IdDimStore)
GO
CREATE UNIQUE CLUSTERED INDEX PK_FactSalesByStore
ON SalesDataMart.FactSalesByStore(IdDimDate, IdDimStore)
ON Annual(IdDimDate);
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
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
Please remember to fill out evaluations