Eladio Rincon ERinconsolidqcom Javier Loria javiersolidqcom Solid Quality Mentors Agenda 2 2008 Solid Quality Mentors Agenda 3 2008 Solid Quality Mentors Definition ID: 360991
Download Presentation The PPT/PDF document "Partitioning techniques in SQL Server" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Partitioning techniques in SQL Server
Eladio Rincon (ERincon@solidq.com)Javier Loria (javier@solidq.com)Solid Quality MentorsSlide2
Agenda
2© 2008 Solid Quality Mentors Slide3
Agenda
3© 2008 Solid Quality Mentors Slide4
Definition
4© 2008 Solid Quality Mentors
Physical segregation
of a single logical table
name into multiple,
identical physical structuresSlide5
I/O Basics
OperationFileType
Read
Write
SELECT
Database
Random
Synchronous
-
INSERT
UPDATE
DELETE
Log
Sequential
-
Synchronous
DatabaseRandom-AsynchronousBulk InsertDatabaseRandomSynchronousFull Backup DatabaseSequentialSynchronous*Log BackupLogSequentialSynchronous-ReindexRandom??
5
© 2008 Solid Quality Mentors
OLTP
8x5
8
8
8
24x7
OLTP
24
24
24
24
24
OLAP
S
S
S
SSlide6
Table Basics
6© 2008 Solid Quality Mentors Slide7
Operational Benefits
7
© 2008 Solid Quality Mentors Slide8
Poor’s Mans Table Partitioning
Creating and using partitioned viewsSlide9
Agenda
9© 2008 Solid Quality Mentors Slide10
Partition Function
10© 2008 Solid Quality Mentors
CREATE PARTITION FUNCTION Annual
(
INT
)
AS RANGE
RIGHT
FOR VALUES
(20060101
, 20070101, 20080101, 20090101, 20100101);Slide11
Partition Scheme
11© 2008 Solid Quality Mentors
CREATE PARTITION SCHEME Annual
AS PARTITION Annual
TO
(
EmptyHistory
, Fact2006, Fact2007, Fact2008, Fact2009,
EmptyFuture
)Slide12
Create Table Reloaded
12© 2008 Solid Quality Mentors
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);Slide13
Create Index Reloaded
13© 2008 Solid Quality Mentors
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);Slide14
Partition 101
Partitioned Hello WorldSlide15
Agenda
15© 2008 Solid Quality Mentors Slide16
Table and Design Patterns
16© 2008 Solid Quality Mentors OLTPReference
Transactional
History/Audit
OLAP
Dimension
Fact TablesSlide17
Partition Types: Horizontal
17
Out of the Box
: in SQL 2005/2008Slide18
Horizontal Partition Methods
RANGEOut of the Box (SQL 2005, SQL 2008)HASHBuild your own (Calculated Column/Hash/Range)LISTBuild your own (RANGE or Partitioned Views)
18
© 2008 Solid Quality Mentors Slide19
Partition Types: Vertical
19
Out of the Box
: (n)text, image, xml, (n)
varchar
(max),
varbinary
(max) and CLR Types.
Build your own
: Views and Instead of TriggerSlide20
Index Structures
20© 2008 Solid Quality Mentors
Clustered Index
Non-clustered IndexSlide21
Partitioned Table/non-partitioned Index
21© 2008 Solid Quality Mentors
Clustered Index
Non-clustered IndexSlide22
Non Partitioned Table/Partitioned Index
22© 2008 Solid Quality Mentors
Clustered Index
Non-clustered Index
Filtered IndexesSlide23
Partitioned Table/Aligned Index
23© 2008 Solid Quality Mentors
Clustered Index
Non-clustered IndexSlide24
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 OrderNumFROM SalesDataMart.FactSalesGROUP BY IdDimDate, IdDimStore)GOCREATE UNIQUE CLUSTERED INDEX PK_FactSalesByStoreON SalesDataMart.FactSalesByStore(IdDimDate, IdDimStore)ON Annual(IdDimDate);GOSlide25
Partitioning
and Query PerformanceSlide26
Agenda
26© 2008 Solid Quality Mentors Slide27
Without Partitions
Disk
File
Filegroup
Partition
PrimarySlide28
Simple: Partitioned
Disk
File
Filegroup
Partition
Primary
2006
2007
2008
2009Slide29
Multiple: Without Partitions
Disk
File
Filegroup
Partition
PrimarySlide30
Multiple: with Partitions (1/3)
Disk
File
Filegroup
Partition
Primary
2006
2007
2008
2009
High AvailabilitySlide31
Multiple: with Partitions (2/3)
Disk
File
Filegroup
Partition
Primary
2006
2007
2008
2009
PerformanceSlide32
Multiple: with Partitions (3/3)
Disk
File
Filegroup
Partition
Primary
2006
2007
2008
2009
PrioritizeSlide33
Partitioning
Availability and MaintainabilityBackup/RestoreSlide34
Agenda
34© 2008 Solid Quality Mentors Slide35
PruningSlide36
Sliding Window LoadingSlide37
Agenda
37© 2008 Solid Quality Mentors Slide38
Thank You!!
Please remember to fill out evaluations