SQL Saturday #223 – Storage Tuning Deep Dive with Wes Brown Joh Sterrett

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


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

  1. Create partition swap
  2. Insert partition swap meta data
  3. Create staging table
  4. Meta data swap
  5. Merge partitions 1 and 2
  6. Mark next used
  7. Split to create new partition
  8. Update processed partition swap meta data



Leave a Reply

Your email address will not be published. Required fields are marked *