Introduction to SQL Server Partitioning

1. Introduction to SQL Server Partitioning Kendra Little

2. About Kendra

3. Index A sample case. What is partitioning? When is partitioning helpful? What’s the fine print? Revisiting our sample case. You are here

4. Should this client use partitioning?

5. Index A sample case. What is partitioning? When is partitioning helpful? What’s the fine print? Revisiting our sample case. You are here

6. All tables have at least one partition. “In SQL Server, all tables and indexes in a database are considered partitioned, even if they are made up of only one partition. Essentially, partitions form the basic unit of organization in the physical architecture of tables and indexes. This means that the logical and physical architecture of tables and indexes comprised of multiple partitions mirrors that of single-partition tables and indexes.” …Partitioned Table and Index Concepts (msdn) One Partition

7. “Partitioning” actually means “horizontal partitioning”

8. Horizontal ranges are based on a partition key. • A single column in the table. • Just one! • Use a computed column if you must, but make sure it performs well as a criterion and works for joins. • Typically a date or integer value • Consider: • A column you will join on • A column you can always use as a criterion I must choose wisely.

9. Ranges of data are defined by a partition functionwhich uses the key. The partition function defines your boundary points and can use either RANGE LEFT or RIGHT. • LEFT: the first value is an UPPER boundary point in partition #1 • RIGHT: the first value is a LOWER boundary point in partition #2 Keep to the right. It’s easier.

10. RIGHT based partition function for Doll Orders keyed on OrderDate Partition 1 1/1/2008 Partition 2 1/1/2009 Partition 3 1/1/2010 Partition 4 1/1/2011 Partition 5

11. RIGHT based partition function keyed on PartName (effectively LIST) Question: how do we get rows into Partition 1? Partition 1 Boundary Point 1: BODY Partition 2 Boundary Point 2: SHOE Partition 3

12. Filegroups are mapped to the partition function using a partition scheme. Partition 1: Compressed Slow, Read-only FG_A 1/1/2008 Partition 2: Compressed 1/1/2009 FG_B Partition 3 1/1/2010 FG_C Partition 4 1/1/2011 Partition 5 FG_D

13. Objects are created on the partition scheme.

14. Indexes can be created on the partition scheme. Or not.

15. Switching • Requires all indexes to be aligned. • Compatible with filtered indexes • Data may be switched in or out only within the same filegroup. • Is a metadata-only operation requiring a schema modification lock. This can be blocked by DML operations, which require a schema stability lock. • Is an exceptionally fast way to load or remove a large amount of data from a table!

16. Creating the partition function Our hero.

17. Creating filegroups We left the Primary FG default on purpose!

18. Creating the partition scheme The partition scheme can map each partition to a specific filegroup, or all partitions to the PRIMARY filegroup. Where the rubber meets the road.

19. Query FGs mapped to the partition function via the partition scheme This gets a little complicated.

20. Creating a table on the partition scheme and add some rows. A partitioned heap: you can totally do that.

21. Let’s have a look at that heap. We’ll use this query again, but not show it on every slide for obvious reasons.

22. Adding indexes Someone’s not in line.

23. Notice that aligned indexes always have the clustering key That’s not usually there!

24. Adding another partition We now have a full staging table and empty partition on dailyFG4

25. Switching in! Don’t forget to drop ordersDaily20101230: your staging table is still there, it’s just empty now. And you’re gonna have to rebuild that non-aligned NC if you want it back.

26. Index A sample case. What is partitioning? When is partitioning helpful? What’s the fine print? Revisiting our sample case. You are here

27. Is maintenance a significant problem for availability? Maintenance includes index rebuilds, loading data, and deleting data.

28. Are query patterns defined by regions? Data regions may be dates, integers, codes

29. Can applications and queries be optimized for partitioning? Some assembly required.

30. Do you have resources to support the partitioned system? • Can your disk configuration be optimized? • Is enough buffer pool available for what will need to be read into memory concurrently? • Will you be able to tune and configure parallelism appropriately for the workload? • Do you have a system you can test with a production-like workload, or a suitable rollback plan?

31. Index A sample case. What is partitioning? When is partitioning helpful? What’s the fine print? Revisiting our sample case. You are here

32. Editions with partitioning

33. Support for HOW MANY partitions? • 15,000 partitions are available in SQL 2008 with SP2 applied • SQL Server 2005, 2008, and 2008 R2 (for now) are limited to 1,000 partitions. This is less than 3 years for daily partitioning. What problems could happen with lots of partitions?

34. Parallelism • In 2005, a query touching more than one partition typically had only one thread per partition. • In 2008, the Partitioned Table Parallelism improvement allows multiple threads to be used on each partition for parallel plans. Partition 3! Partition 1! Partition 1! Partition 2! Partition 2! Partition 3!

35. Lock escalation AUTO • Lock escalation can be set to AUTO for a table. If the table is partitioned, locks will escalate to the partition level rather than the table level. • What’s awesome: greater concurrency! Partition level deadlocks are not awesome. Test your workload (like with any feature).

36. Partition aware seeks • In SQL 2008, the optimizer has been made more clever and has a greater chance at achieving partition elimination. This has been done by: • Changing the internal representation of a partitioned table to be more optimized for seeking on the PartitionID (even when the table’s CX is on another column) • A “skip scan” operation has been added to allow the optimizer greater flexibility. More optimized optimizin.

37. Be careful with your statistics • Statistics are not maintained per partition, they are maintained for the entire index or column. Since there is a limit to the number of steps in the histogram, the statistics can become invalid, and on very large tables may take a long time to update. • Filtered statistics can be used to help with this in 2008: you can create new filtered statistics for your new partition. This sounds like work.

38. Index rebuilds and compression • Individual partitions cannot be rebuilt online. • The entirety of a partitioned index can be rebuilt online. • Individual partitions can be compressed. For fact tables with archive data, older partitions can be be rebuilt once with compression. Their filegroups can then be made read-only. I’d better check my maintenance jobs.

39. Switching Feature Compatibility • Works with replication in 2008 and later • Some subscribers can have the partitioning scheme, others don’t have to • This means you can have some subscribers on Standard. • Works with Change Data Capture (with some special steps) • Does not work with Change Tracking @SQLFool replicates her partitioned tables, check out her blog.

40. Index A sample case. What is partitioning? When is partitioning helpful? What’s the fine print? Revisiting our sample case. You are here

41. So, should this client use partitioning?

42. Resources/ Contact There is a very large amount of documentation online for horizontal table partitioning. Get my recommendations here: http://littlekendra.com/resources/partition/ This presentation would not have been possibly without whitepapers and blogs by Kimberly Tripp, Michelle Ufford, and Ron Talmage. Twitter: @kendra_little Email: littlekendra@gmail.com LinkedIn: http://www.linkedin.com/in/kendralittle