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:

Partitioning_Sample_DB_Monday

Partitioning_Sample_DB_Tuesday

Partitioning_Sample_DB_Wednesday

Partitioning_Sample_DB_Thursday

Partitioning_Sample_DB_Friday

Partitioning_Sample_DB_Saturday

Partitioning_Sample_DB_Sunday

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

(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

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 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:

Partition_Scheme, which provides the name of the partition scheme.

Partition_Function, which gives the name of the partition function.

Logical_Name, which notes the name of the file group.

Physical_Name, which provides the name and location of the physical file within the file group.

Space Used By Index (MB), which specifies the total amount of space taken up by the indexes for all the tables using the partition.

Space Used By Data (MB), which indicates the total amount of space taken up by the data in all the tables using the partition.

Total Space (MB), which gives the total amount of space allocated to the partition.

Rows, which provides the total number of rows in all the partitioned tables.

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.

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:

Object_Name, which provides the name of the partitioned table.

Index_ID, which specifies the ID of the partitioned table's index.

Index_Type, which notes whether the partitioned table has a clustered or nonclustered index.

Index_Data_Allocation, which indicates the type of allocation unit, such as In_Row_Data or LOB_DATA. In some instances when dealing with large objects (LOBs), the data is stored outside the row and only a reference to whereabouts of the LOB will be stored within the row.

Used Space (MB), which specifies the amount of space used by the data or index within the partition. Using the Index_ID column, you can determine whether the space is being used by the data (Index_ID=1) or the index (Index_ID<>1).

Total Space (MB), which notes the total amount of space allocated to the file group that the partition table resides on.

Rows, which indicates the number of rows in each partition for the partitioned table.

Partition_Scheme, which gives the name of the partition scheme.

Partition_Scheme_ID, which provides the ID of the partition scheme.

Partition_Function, which notes the name of the partition function.

Partition_Function_ID, which indicates the ID of the partition function.

Function_Variable, which specifies the condition in the partition function.

Logical_Name, which gives the name of the file group.

Physical_Name, which notes the name and location of the physical file within the file group.

Status, which indicates the status (e.g., online) of the file group.

Data_Space_ID, which provides a value that represents the ID of the file group (when the value is 1 or higher).

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.

From the Blogs

Don’t let bad data sneak up on you when and where you least expect it. Ferret out bad data with Melissa Data’s newest Profiling Component for SSIS. Learn how to take control of your data using knowledge-base-driven metadata. The truth shall set you free!...More

Now that we’ve outlined the process to let servers in a SQL Server AlwaysOn Availability Group "talk to each other" by means of setting up linked servers, it’s possible to set up some additional or improved checks on Availability Group Health....More

In my previous post, I provided a high-level outline of the core logic (and rationale behind that logic) that would be needed to set up regular synchronization checks on SQL Server Agent Jobs for servers where AlwaysOn Availability Groups have been deployed. In this post, I’ll walk through the steps--and the code--needed to setup those checks....More