Introduction to Partitioning in SQL Server Remember Legos

Introduction to Partitioning in SQL Server Remember Legos? It’s kind of like that. . .

A Little About Me @SQLServer. Nerd 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