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.