Michael J. Swart

I explain a little about what partitioned tables are in SQL Server and then I give a recap of various methods for managing partitioned tables.

What are Partitioned Tables? (2005)

SQL Server partitioned tables were introduced with SQL Server 2005 Enterprise Edition. Partitions are used to improve maintenance and management of large tables. I’m not going to explain the what and the why of partitions. That would be like reinventing the wheel. Partitions are best explained with Kimberly Tripp’s white paper Partitioned Tables and Indexes in SQL Server 2005. Whether you’re new to partitioning, or whether you’re the MS employee who designed partitioning, I highly recommend reading the article it’s got something for everybody.

What’s new with 2008?

See above. There hasn’t been much that’s changed in terms of basic concepts. However, there are a couple features that have been introduced:

• SQL Server’s locking mechanism is more aware of partitions than it was in 2005. For syntax, see the LOCK_ESCALATION option for tables in books on-line and for details/examples/commentary see this article by Paul Randal.
• SQL Server Management Studio (SSMS) has a Create Partition Wizard and a Manage Partition Wizard. These help create, manage or modify partitions.
• Update April 8, 2009. See the comment section for a couple 2008 features that I missed.

Great, so now you know all there is about partitioning, sliding window scenarios, partition switching, aligned vs. unaligned indexes. So now what’s the best way to go about managing these partitions? There are a few options that I’ve come across that each have their benefits and drawbacks. I’d like to list them out here.

T-SQL (of course):

Using straight T-SQL scripts does everything you need without the handholding.

SQL Partition Manager (from DonWellSoft).

According to their website, SQL Partition Manager “automates partition management tasks”. It does a very good job of enabling the DBA to perform all tasks that deal with SQL Server partitioning. The price tag starts at $199 which is a small fraction of the price of SQL Server Enterprise Edition.

The biggest advantage that this software has over the other options is its user interface. You can browse partitioned tables faster and easier.

It’s nice and well worth trying the limited time demo version.
There is one thing that I wish for it. That it has a “view script” feature that you can use before implementing a change. I mean, it’s always possible to capture everything using a server side trace with SQL Profiler, but that reminds me of the old Saturday Night Live joke: “For a transcript of today’s episode, write down everything I’m saying”

SQL Server Partition Management

A codeplex project by Stuart Ozer from Microsoft. This program is a command line tool that allows people to manage sliding window scenarios. It can also create staging tables to be able to switch data in or out. In fact that is the only focus of this program; to prepare staging tables and to switch partitions with such partitions. It is an excellent alternative to maintaining T-SQL scripts to be used during weekly or monthly partition maintenance tasks. But – as with T-SQL scripts – there is no hand-holding here. Also, there is no output-as-script feature available either.

SSMS wizards:

As mentioned earlier, these wizards helps create, manage and modify partitions. They are very script-able and easy to use.

• The Create Partition Wizard can be used to partition an existing table. If needed, the wizard can help create a partition scheme and partition function.
• The Manage Partition Wizard can create a staging table for partition switching or it can switch in or switch out data.

In short these wizards accomplish what the command line tool SQL Server Partition Management does except through a UI.

Toad for SQL Server (Up and Coming version 4.5)

I also understand that Toad for SQL Server version 4.5 will have partitioning features and partition management in Capacity Manager as well. (Thanks Brent!)

Summary

• For automated management I recommend T-SQL plus the command line tool SQL Server Partition Management.
• For day to day and manual management I recommend the windows application SQL Partition Manager.