Getting information about partitions

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.