Transcript Introduction to Partitioning in SQL Server

Introduction to Partitioning in SQL
Server
Remember Legos? It’s kind of like that...
A Little About Me
@SQLServerNerd
Event Chair
Forums Administrator
2
Have you ever wanted to…/secrets of
heavy hitter servers
• Truncate just part of a table
• Migrate data from one table into another in
milliseconds
• Spread your table over different tiers of storage.
• Perform maintenance on just part of a table
• Access just part of a table/index for less IO
• Have locks escalate to just a segment of a table
instead of a table lock
3
What is partitioning?
• A way to break tables down into smaller chunks
for manageability and performance
4
You mean it will help me to…
• Reduce IO impact via partition elimination
• Improve join operations for equi-joins when both
tables are partitioned on the same column and
joined on the partitioned column
• Move data in and out of tables really fast
5
Horizontal partitioning VS vertical
partitioning
• Vertical partitioning is splitting out extra columns
into their own table(s)
• Horizontal partitioning is when we break up our
table based on rows
– This is what we traditionally mean when using the word
partition
– This is what we will talk about today
6
What techniques will we cover.
• We’ll start with the much simpler partitioned view
• We’ll spend a lot more time on table partitioning.
7
An Oldie but a goodie: The partitioned
view
• Separate tables with a view that brings them
together
• Check constraints on the underlying tables allow
the optimizer to perform “partition elimination”
8
Demo: Partitioned views
9
Table Partitioning Overview
•
•
•
•
Introduced in SQL Server 2005
Its and enterprise only feature
Invisible to queries
More complex than partitioned views
10
How is it implemented in SQL Server
•
•
•
•
Every table has at least one partition
These partitions exist in files
The files are part of a filegroup
Each partition is like a mini table, with its own
IAM chain. In other words it’s a separate “object”
in the database
• It’s the combination of all partitions that form the
actual table
11
How is it implemented in SQL Server
• Partitions also allow another level of locking called
a partition lock. This can be amazingly beneficial
12
Data Files
• These are the actual files on disk with an mdf or
ndf extension
• All objects in a database are stored in one or more
files
• If multiple files exist within a file group then data
is spread across them with a round robin algorithm,
with preference given to the smallest file
13
Filegroups
• Provide a “Container” to group data files into
• Objects in the database are created against a
filegroup
14
Partition Functions
• Are used to assign rows to partitions
• Can only work on one column known as the
partition key
• Range left and Range right determine where the
boundry value falls
15
Partition Schemes
• Are used to place partitions into one or more
filegroups
• They specify the destination of the data
• ALL TO allows you to have all partitions exist in
one filegroup
• Partitioned tables are built not on a single filegroup
but on a partition scheme
16
Aligned Indexes
• Indexes can be partitioned as well
• If an index is created on the same partition scheme
as its parent table, it is said to be aligned.
• This allows for maintenance on just one part of the
index
17
Demo: Example partitioned
table/files/filegroups
• Creating a partitioned table
• Verifying our partitions:
sys.dm_db_partition_stats/sys.partitions
18
Partition management Overview
• We can move chunks of data in and out of tables
really fast using partitions as long as the data is all
in the same filegroup
• We can switch data in, or out, from another table in
milliseconds regardless of size. This is possible
because the switch is really only a metadata one.
• The tables must have the same design however
• We can merge partitions together, or split them
apart, during our loads. Again this is a metadata
operation
19
Splitting partitions up
• We can split partitions up using the split command
• The split command is part of the alter partition
function statement
• We split by defining a new range
20
Merging partitions together.
• The merge command allows you to combine
partitions into one partition
• This is done by merging our ranges
• It is part of the alter partition function statement
21
Moving partitions between tables
• We can use the switch command to move
partitions between tables
• Indexes must be identical on both tables
• The tables must also share the same constraints
• The tables must be part of the same filegroup
• Both tables must exist already
• Transfer into a partitioned table, requires that the
destination partition exist and be empty
• Non partitioned tables must be empty
22
DEMO: Switch
23
24