I delivered a short talk about table partitioning at the Gwinnett, Georgia, Microsoft User Group meeting last night.
Here is a link to the Power Point:
Table_Partitioning.ppt (163.00 kb)
I basically presented basic what-is-it information and summarized the tests from my last post.
Here is a text version of part of the presentation.
What is the problem?
- Your table is like a dictionary
- A GIANT, unalphabetized dictionary
- To find a word, you have to search EVERY page
Add an index!
- If you add an index, you can find the page number of the word you want, but…
- You still have to flip through a bunch of pages to find that page
How about an Encyclopedia?
- Split your book up into multiple books
- Group your words alphabetically into books – One letter per book
- Flip through the pages of only ONE book
- Much faster!
How do you do that to a table?
- Decide how you want to break up your table – pick a column
- Create a set of Filegroups
- One per partition
- Can spread over multiple drives
- Create a Partition Function
- Create a Partition Scheme
- Build table that references the Partition Scheme
Gotchas?
- If you query without the partition column, it could actually be slower than a non-partitioned table
- If you hit multiple partitions it can be slow
- Threading in SQL Server 2005
- Supposed to be better in 2008
- Locking… very sad
- 2005 can lock ENTIRE TABLE
- 2008 can lock a partition
Benefits
- Speed
- Search fewer records to find what you want
- Spread files over multiple drives
Maintenance
- Work with filegroups
- Set filegroup as read-only when no longer active
- Back up inactive filegroups ONCE
- Back up active filegroups daily, etc.