sp_helpartition prints information about table partitions. For partitioned tables, it shows the number of data pages in the partition and summary information about data distribution. Issue sp_helpartition, giving the table name. This example shows data distribution immediately after creating a clustered index:
sp_helpartition sales
partitionid firstpage controlpage ptn_data_pages
----------- ----------- ----------- --------------
1 6601 6600 2782
2 13673 13672 2588
3 21465 21464 2754
4 29153 29152 2746
5 36737 36736 2705
6 44425 44424 2732
7 52097 52096 2708
8 59865 59864 2755
9 67721 67720 2851
(9 rows affected)
Partitions Average Pages Maximum Pages Minimum Pages Ratio (Max/Avg)
----------- ------------- ------------- ------------- -----------------
9 2735 2851 2588 1.042413
sp_helpartition shows how evenly data is distributed between partitions. The final column in the last row shows the ratio of the average column size to the maximum column size. This ratio is used to determine whether a query can be run in parallel. If the maximum is twice as large as the average, the optimizer does not choose a parallel plan.
Uneven distribution of data across partitions is called partition skew.
If a table is not partitioned, sp_helpartition prints the message “Object is not partitioned.” When used without a table name, sp_helpartition prints the names of all user tables in the database and the number of partitions for each table. sp_help calls sp_helpartition when used with a table name.
