Intro to table partitioning
- Enterprise Edition required
- LOTS of data – highly transactional tables with 50GB+
How can it help
- reduce maintenacne tasks – indexing, restores/backups
- improves purging/archiving
Partition Table -> Partition scheme -> Partition function
Selecting Parition Colums
- You only get one – pick wisely!
- Highly used filter – review indexes; review missing indexes/stats; review queries
- column must be pasrt of clustered index or PK
When partitioning on dates, rounding can affect the date and possibly what patition your data will be in
Improving maintenance tasks
- backup/restore filegroups based on priority
- index maintenance by partition
- new in SQL 2014 – rebuild index by partition; update statistics by partition; can also be done online in 2014
Performance
Partition Elimination – view in execution plan
How does it improve archiving and purging – can reduce the time processes take from hours to minutes by utilizing the sliding window
Meta dataswap – requires schema lock; alter table <blah> switch partition 2 to <blah_staging>
Sliding window goals
- split and merge emplty partitions
- switch to do metadata swaps
- minimize physical data movement
Sliding Window Steps
- Create partition swap
- Insert partition swap meta data
- Create staging table
- Meta data swap
- Merge partitions 1 and 2
- Mark next used
- Split to create new partition
- Update processed partition swap meta data
http://johnsterrett.com/go/partition