Stored Procedure Retrieves Information About Partitioned Tables

Starting in SQL Server 2005, SQL Server has offered native partitioning capabilities. Table partitioning helps DBAs and SQL Server administrators support and manage large tables and very large databases (VLDBs). Partitioning follows a simple principle: Horizontally split an existing table into chunks based on a certain condition (a partition function) and place those chunks on file groups. Managing a partitioned table is much easier because you can easily add and remove partitions.

SQL Server Management Studio (SSMS) doesn't have a GUI that lets you easily obtain information about partitioned tables, so you have to use T-SQL code. I wrote a stored procedure named sp_PartitionInfo that retrieves information about partitions and displays it in an easy-to-read format. The results reveal where partitioned data is stored, the name and location of the physical files within file groups, how a partitioned table's indexes are distributed across the file groups, and much more.

Before diving into the details on how to use the sp_PartitionInfo stored procedure, I want to mention that I wrote a script, CreateTestPartitionTable.sql, that will create a partition on which to test sp_PartitionInfo. CreateTestPartitionTable.sql creates a database named Partitioning_Sample_DB with seven file groups, each of which contains one physical file. The seven file groups are named after the seven days of the week:

CreateTestPartitionTable.sql also creates a partitioned table called Partitioned_Table_A. The condition for the partition function is a column named Day_of_the_Week. This column determines in which partition the data should be recorded. The script also creates a clustered index and a nonclustered index for the table.

You can download CreateTestPartitionTable.sql and sp_PartitionInfo.sql by clicking the 102632.zip hotlink. CreateTestPartitionTable.sql and sp_PartitionInfo.sql work on the Enterprise and Developer Editions of SQL Server 2008 and SQL Server 2005. They won't work with the Standard Edition of SQL Server 2008 and SQL Server 2005 because table partitioning isn't available in that edition.

After you download and run CreateTestPartitionTable.sql, you'll have a partitioned table on which to try sp_PartitionInfo. To execute sp_PartitionInfo against a database, follow the syntax

EXEC master..sp_PartitionInfo  DatabaseName,  [Partitions Summary | Details]

(Although this command wraps here, you'd enter it all on one line. The same holds true for the other commands in this article.) For the DatabaseName parameter, you specify the name of the user database from which you want to extract partition information. If you include the Partitions Summary parameter, the result set will include a one-line summary for each partition file group. For example, if you execute the stored procedure against the Partitioning_Sample_DB database with the command

EXEC master sp_PartitionInfo  Partitioning_Sample_DB,  Partitions Summary

you'll receive a result set similar to the one displayed in Figure 1. (Note that the result set you receive from running this command might look slightly different. The data distibution within the partition might differ based on the day of the week that you ran CreateTestPartitionTable.sql.)

Figure 1: Excerpt from the summary result set

Figure 1 contains only some of the columns in the result set. (If you'd like to see a snapshot of all the columns, check out PartitionsSummaryResultSet.jpg, which is in the 102632.zip file.) The summary result set includes eight columns:

Partitions Summary is the default parameter, so not including a second parameter has the same effect as specifying Partitions Summary.

If you specify the Details parameter, the result set includes detailed information about the data and index distributions across the partitions. For example, if you execute the stored procedure against the Partitioning_Sample_DB database using the command

EXEC master sp_PartitionInfo  Partitioning_Sample_DB,  Details

you'll receive a result set similar to the one displayed in Figure 2.

Figure 2: Excerpt from the detailed result set

Like Figure 1, Figure 2 contains only some of the columns in the result set. (The DetailsResultSet.jpg snapshot in the 102632.zip file shows all the columns.) The detailed result set includes 16 columns:

No matter whether you need basic or detailed information about your partitioned tables, the sp_PartitionInfo stored procedure can provide it to you quickly. By analyzing this information, you can gain an insight on how well your data is distributed.