Table Partitioning in SQL Server: A Magic Solution for Better Performance? (Pragmatic Works)

Recording available on Pragmatic Works' website: http://pragmaticworks.com/Training/FreeTraining/ViewWebinar/WebinarID/1743

Table Partitioning in SQL Server: A Magic Solution for Better Performance? (Presented during Pragmatic Works' Free Training on the T's on March 26th, 2015)

1. Table Partitioning in SQL Server
A Magic Solution for Better Performance?

2. Cathrine Wilhelmsen
@cathrinew
cathrinewilhelmsen.net
Data Warehouse Architect
Business Intelligence Developer

3. You?
New to table partitioning
"Everything is slow"
Today?
Basic Introduction
What, Why & How

4. A Magic Solution for Better Performance?
Spoiler Alert!

5. A Magic Solution for Better Performance?
Implementing table partitioning is not a trivial task
and can actually cause worse performance...

6. A Magic Solution for Better Performance?
...but don't worry, I made plenty of mistakes
so you can avoid them 

7. Enterprise Edition only

8. What?
Partition Key
Partition Function
Partition Scheme
Why?
Backup & Restore
Maintenance
Load & Archive
How?
Partition Elimination
Switch, Split & Merge
Sliding Windows

9. Table Partitioning Basics

10. What is a partitioned table?
2012-01-01... ...
2012-12-31... ...
2013-01-01... ...
2013-12-31... ...
2014-01-01... ...
2014-12-31... ...
2015-01-01... ...
2015-12-31... ...
Data is physically stored in groups
of rows called partitions
Each partition can be accessed
and maintained separately
Partitioning is not visible to users,
it behaves like one logical table

11. Partition Key
2012-01-01... ...
2012-12-31... ...
2013-01-01... ...
2013-12-31... ...
2014-01-01... ...
2014-12-31... ...
2015-01-01... ...
2015-12-31... ...
Data is partitioned based on a
single column, the Partition Key
The Partition Keyshould always be
used as a filter in queries
This ensures Partition Elimination:
only relevant partitions are accessed

12. Partition Function
2012-01-01... ...
2012-12-31... ...
2013-01-01... ...
2013-12-31... ...
2014-01-01... ...
2014-12-31... ...
2015-01-01... ...
2015-12-31... ...
The Partition Functiondefines how to
partition the data
It specifies boundary values, the points
between two partitions
It specifies if the boundary value
belongs to its left(upper) partition or
its right(lower) partition

13. Partition Function: Range Left and Range Right
2012-01-01... ...
2012-12-31... ...
2013-01-01... ...
2013-12-31... ...
2014-01-01... ...
2014-12-31... ...
2015-01-01... ...
2015-12-31... ...
2012-01-01... ...
2012-12-31... ...
2013-01-01... ...
2013-12-31... ...
2014-01-01... ...
2014-12-31... ...
2015-01-01... ...
2015-12-31... ...

14. Partition Function: Range Left and Range Right
Range Left means the boundary value is
the last value in the left partition
CREATEPARTITIONFUNCTION
pfLeft(INT) AS RANGELEFT
FORVALUES (20,30,40);
Range Right means the boundary value
is the first value in the right partition
CREATEPARTITIONFUNCTION
pfRight(INT) AS RANGERIGHT
FORVALUES (20,30,40);
...2021-30 41...31-40 ...1920-39 40...30-39
20 30 40 20 30 40

15. Partition Scheme
2012-01-01... ...
2012-12-31... ...
2013-01-01... ...
2013-12-31... ...
2014-01-01... ...
2014-12-31... ...
2015-01-01... ...
2015-12-31... ...
The Partition Scheme maps logical
partitions to physical filegroups
Filegroups?
Data files on one or more disks
Backed up and restored individually
Can be Read-Only

16. Map all partitions to one filegroup
FILEGROUP
2012-01-01... ...
2012-12-31... ...
2013-01-01... ...
2013-12-31... ...
2014-01-01... ...
2014-12-31... ...
2015-01-01... ...
2015-12-31... ...

17. Map partitions to separate filegroups
FILEGROUP1
(Read-Only)
FILEGROUP2
(Read-Only)
2012-01-01... ...
2012-12-31... ...
2013-01-01... ...
2013-12-31... ...
2014-01-01... ...
2014-12-31... ...
2015-01-01... ...
2015-12-31... ...
FILEGROUP3
FILEGROUP4

18. How are partitions mapped to filegroups?
...2021-30 41...31-40 ...1920-39 40...30-39
20 30 40 20 30 40
The partition function specified the boundary values and partitions:

19. How are partitions mapped to filegroups?
CREATEPARTITIONSCHEME
psLeftASPARTITIONpfLeft
TO (FG1,FG2,FG3,FG4);
CREATEPARTITIONSCHEME
psRightASPARTITIONpfRight
TO (FG1,FG2,FG3,FG4);
...2021-30 41...31-40 ...1920-39 40...30-39
20 30 40 20 30 40
The partition scheme uses the partition function...

20. How are partitions mapped to filegroups?
CREATEPARTITIONSCHEME
psLeftASPARTITIONpfLeft
TO (FG1,FG2,FG3,FG4);
CREATEPARTITIONSCHEME
psRightASPARTITIONpfRight
TO (FG1,FG2,FG3,FG4);
...2021-30 41...31-40 ...1920-39 40...30-39
20 30 40 20 30 40
FG1 FG2 FG4FG3 FG1 FG2 FG4FG3
...to map each partition to filegroups:

21. Filegroups
Partition Scheme
Partitioned Table
2012-01-01 ... ...
2012-12-31 ... ...
2013-01-01 ... ...
2013-12-31 ... ...
2014-01-01 ... ...
2014-12-31 ... ...
2015-01-01 ... ...
2015-12-31 ... ...
A partitioned table is created on
a partition scheme instead of
directly on a filegroup
The partition scheme uses the
partition key to store rows in the
correct partition and filegroup
based on the definition specified
in the partition function

22. Table Partitioning Basics

23. Why Table Partitioning?

24. Partition Elimination
SELECTCOUNT(*) FROMTable
WHEREYear =2012;
SELECTCOUNT(*) FROMTable;

25. Partition Elimination
SELECTCOUNT(*) FROMTable
WHEREYear =2012;
SELECTCOUNT(*) FROMTable;

26. Backup & Restore Partitions
Filegroups can be backed up
and restored individually
If each partition is mapped to
a separate filegroup, partitions
with the most critical data can
be restored first

27. Backup & Restore Partitions
Filegroups can be read-only
If each partition is mapped
to a separate filegroup,
partitions with historical,
unchanging data can be
excluded from regular
backups

28. Index Maintenance per Partition
Rebuild and reorganize indexes per
partition
Rebuild indexes onlineper partition
was introduced in SQL Server 2014
Set data compression per
partition
ALTERINDEXIndexName
ONTableName
REBUILDPARTITION=2
WITH (ONLINE=ON);

29. Statistics Maintenance per Partition
Update statistics on specific
partitions instead of scanning
and updating the whole table
UPDATESTATISTICS
TableName(StatisticsName)
WITHRESAMPLE
ONPARTITIONS(3,5);
CREATESTATISTICS
StatisticsNameON
TableName(ColumnName)
WITHINCREMENTAL =ON;
Incremental Statistics was
introduced in SQL Server 2014

30. Load & Archive: Partition Switching
Partitions can be switched between tables,
called switching inor switching out
Partition switching is a metadata operation
that updates the location of the data, no
data is physically moved
Extremely fast compared to inserting
into or deleting from a large table
2013-01-01......
2013-12-31......
2014-01-01......
2014-12-31......
2015-01-01......
2015-12-31......
2012-01-01......
2012-12-31......
ALTERTABLETable1
SWITCH PARTITION5
TOTable2 PARTITION5;
SELECT
$PARTITION.pf(2014);

31. Load & Archive: Switch out
2012-01-01 ... ...
2012-12-31 ... ...
2013-01-01 ... ...
2013-12-31 ... ...
2014-01-01 ... ...
2014-12-31 ... ...
2015-01-01 ... ...
2015-12-31 ... ...
Called switch out when you move data out of a table (archive)

32. Load & Archive: Switch out
2013-01-01 ... ...
2013-12-31 ... ...
2014-01-01 ... ...
2014-12-31 ... ...
2015-01-01 ... ...
2015-12-31 ... ...
2012-01-01 ... ...
2012-12-31 ... ...
Called switch out when you move data out of a table (archive)

33. Load & Archive: Switch in
2013-01-01 ... ...
2013-12-31 ... ...
2014-01-01 ... ...
2014-12-31 ... ...
2015-01-01 ... ...
2015-12-31 ... ...
2016-01-01 ... ...
2016-12-31 ... ...
Called switch inwhen you move data into a table (load)

34. Load & Archive: Switch in
2013-01-01 ... ...
2013-12-31 ... ...
2014-01-01 ... ...
2014-12-31 ... ...
2015-01-01 ... ...
2015-12-31 ... ...
2016-01-01 ... ...
2016-12-31 ... ...
Called switch inwhen you move data into a table (load)

35. Sliding Windows
The Sliding Windows technique automates data loading, data archiving
and partition management
It keeps a certain number of partitionsby continuously switching out
the oldest data and switching in new data
2013-01-01......
2013-12-31......
2014-01-01......
2014-12-31......
2012-01-01......
2012-12-31......
2015-01-01......
2015-12-31......

36. Sliding Windows: Split & Merge
2012-01-01... ...
2012-12-31... ...
2013-01-01... ...
2013-12-31... ...
2014-01-01... ...
2014-12-31... ...
2015-01-01... ...
2015-12-31... ...
Partitions are not actually added or
removed, they are splitor merged
Be careful!
Splitting and merging partitions
cancause data movement!!

37. Sliding Windows: Split & Merge
2012-01-01... ...
2012-12-31... ...
2013-01-01... ...
2013-12-31... ...
2014-01-01... ...
2014-12-31... ...
2015-01-01... ...
2015-12-31... ...
Splitone partition in two by
adding a new boundary value
ALTERPARTITIONFUNCTIONpf()
SPLITRANGE ('2013-06-01');
Data movement will occur if there is data
on both sides of the new boundary value!

38. Sliding Windows: Split & Merge
2012-01-01... ...
2012-12-31... ...
2013-01-01... ...
2013-12-31... ...
2014-01-01... ...
2014-12-31... ...
2015-01-01... ...
2015-12-31... ...
Mergetwo partitions to one by
removing a boundary value
ALTERPARTITIONFUNCTIONpf()
MERGERANGE ('2014-01-01');
Data movement will occur if there is data
on both sides of the old boundary value!

39. Sliding Windows Steps
1.Add new filegroup and file
2.Create switch out (archive) table
3.Create switch in (load) table and load it with new data
4.Splitto add new partition
5.Switch in new partition
6.Switch outold partition
7.Mergeto remove old partition
8.Delete switch out and switch in tables
9.Delete old file and filegroup

40. Sliding Windows Demo

41. Alternative Sliding Windows Steps
1.Add new filegroup and file
2.Create switch out (archive) table
3.Switch out old partition
4.Mergeto remove old partition
5.Create switch in (load) table and load it with new data
6.Splitto add new partition
7.Switch in new partition
8.Delete switch out and switch in tables
9.Delete old file and filegroup

42. Why Table Partitioning?

43. Case Study: What Went Wrong?

44. Background: Financial Data Warehouse
Periodic Snapshot Fact Tables
Daily, Weekly and Monthly Balances
SQL Server, DB2, Oracle, .csv, .txt
Loaded at different times during the day
1 2 3 4 5 6 7
Pension Insurance Bank Fund

45. First version
Dailyfact table:
Keep 1 day per source
1 2
Pension
6 7
Bank Fund
Monthlyfact table:
Keep 36 months per source
3 4 5
Insurance

46. Second version
Dailyfact table:
Keep 1 day per source
1 2
Pension
Monthly / Weeklyfact table:
Keep 36 months for some sources
Keep 106 weeks for some sources
3 4 5
Insurance
!
6 7
Bank Fund

47. Third version
Dailyfact table:
Keep 1 day per source
1 2
Pension
Monthly / Weekly / Dailyfact table:
Keep 36 months for some sources
Keep 106 weeks for some sources
Keep 7 days for some sources
3 4 5 6 7
Insurance Bank Fund
!!

48. "Everything is slow"

49. "Let's try partitioning"

50. Daily fact table partitioning
1 ... ...
2 ... ...
3 ... ...
4 ... ...
5 ... ...
6 ... ...
7 ... ...
Partition Key: source number
One partition per source
Helped with deadlock issues
Easy to switch data in and out

51. Monthly / Weekly / Daily fact table partitioning
12011 ... ...
12012 ... ...
12013 ... ...
12014 ... ...
19990 ... ...
19991 ... ...
19992 ... ...
Partition Key: Source number + Period type
Period type:
YYYY for monthly data
9991 for weekly data
9992 for daily data
Helped with deadlock issues, but…

52. Monthly / Weekly / Daily fact table partitioning
12011 ... ...
12012 ... ...
12013 ... ...
12014 ... ...
19990 ... ...
19991 ... ...
19992 ... ...
Partition key was difficult to remember
and was not used in queries
Data had to be inserted into and
deleted from partitions with existing
data, instead of switching partitions in
and out

53. What went wrong?
"The usual suspects": Conflicting priorities, changing
requirements and high pressure to deliver on time
Fact tables were expanded, notchanged, when new
sources and requirements were implemented
Partitioning was implemented on fact tables with
multiple grains, instead of correcting the fact tables first

54. What did we learn?

55. test, test, test, test

56. References and Resources: Table Partitioning
Brent Ozar Unlimited: SQL Server Table Partitioning Resources
brentozar.com/sql/table-partitioning-resources/
Bradley Ball: Partitioning in SQL Server 2012
pragmaticworks.com/Training/FreeTraining/ViewWebinar/WebinarID/541

57. References and Resources: Further Reading
Partial Backupsin SQL Server 2014
msdn.microsoft.com/en-us/library/ms191539.aspx
Piecemeal Restoresin SQL Server 2014
msdn.microsoft.com/en-us/library/ms177425.aspx
Benjamin Nevarez: SQL Server 2014 Incremental Statistics
benjaminnevarez.com/2015/02/2014-incremental-statistics/

58. Thank you!
@cathrinew
cathrinewilhelmsen.net
no.linkedin.com/in/cathrinewilhelmsen
[email protected]