Find Out What's Going On Inside Your Partitions

One area in which SQL Server Management Studio (SSMS) is sorely lacking is partitioned tables. There are no built-in GUI tools to create partition functions or partition schemes. And after you've hammered out your T-SQL queries to set them up, well, that's pretty much it. Although you can use the $PARTITION statement to get a rough idea of what's in each partition, this statement doesn't provide information about file-group usage and other important details.

Although I don't have a silver bullet for creating and maintaining partitions with a few clicks, I do have a script—PartitionOverview.sql—that will make it a lot easier to get an idea of what's going on inside your partitions. You can run PartitionOverview.sql in any database to get detailed information about your partitioned tables and indexed views. PartitionOverview.sql isn't a stored procedure, but you could easily turn it into one.

To use PartitionOverview.sql, all you need to do is run it. No tweaking is necessary. So what will you see when you run it? Figure 1 shows an excerpt from a sample results grid created with PartitionOverview.sql.

Although Figure 1 shows only several columns and rows, you'll get a result set with 13 columns and one row for each partition. The 13 columns are as follows:

PartitionOverview.sql contains a few other columns that are commented out. Most of them are for internal SQL Server IDs (e.g., data space ID, parameter ID) that aren't directly related to partitions but might be of interest to those DBAs who want to further develop the tool.

While writing this script, I think I discovered why I never found similar tools in the past. Collecting all the information requires 13 JOIN operations of various views. So, the code doubles as a learning tool for those DBAs curious about how views like sys.destination_data_spaces and sys.index_columns relate to partitioning.

But wait, there's more! As an excuse to better familiarize myself with SSMS's Custom Reports feature, I used PartitionOverview.sql as the foundation for an easy-to-read report named PartitionOverview.rdl. PartitionOverview.rdl provides the same data as PartitionOverview.sql, but the data is formatted so that the information is easier to read, especially if you have a lot of tables and indexes. Figure 2 shows an excerpt from a sample report.

To use PartitionOverview.rdl, you need to open SSMS, right-click your database, drill down to Custom Reports, and choose the PartitionOverview.rdl file. You don't need to tweak PartitionOverview.rdl.

You can download PartitionOverview.rdl, PartitionOverview.sql, a sample PartitionOverview.rdl report, and a sample PartitionOverview.sql results grid by clicking the 101051.zip hotlink at the top of the page. PartitionOverview.sql and PartitionOverview.rdl work on SQL Server 2005 and later.

—Dave Britten, DBA, Great Lakes Computer Source