Lists the partition number, first page, control page, and number of data pages and summary size information for each partition in a partitioned table.
sp_helpartition [table_name]
is the name of a partitioned table in the current database. If the table name is not supplied, the owner, tables name, and number of partitions is printed for all user tables in the database.
Returns information about the partitions in sales:
sp_helpartition sales
partitionid firstpage controlpage ptn_data_pages ----------- ----------- ----------- -------------- 1 313 314 4227 2 12802 12801 4285 3 25602 25601 4404 4 38402 38401 4523 5 51202 51201 4347 6 64002 64001 4285 (6 rows affected) Partitions Average Pages Maximum Pages Minimum Pages Ratio (Max/Avg) ---------- ------------- ------------- ------------- --------------- 6 4345 4523 4227 1.040967
sp_helpartition lists the partition number, first page, control page, and number of data pages for each partition in a partitioned table. The number of pages per partition shows how evenly the data is distributed between partitions.
The summary information display the number of partitions, the average number of pages per partition, the minimum and maximum number of pages, and the ratio between the average number of pages and the maximum number. This ratio is used during query optimization. If the ratio is 2 or greater (meaning that the maximum size is twice as large as the average size), the optimizer chooses a serial query plan rather than a parallel plan.
Partitioning a table creates additional page chains. Use the partition clause of the alter table command to partition a table. Each chain has its own last page, which is available for concurrent insert operations. This improves insert performance by reducing page contention. If the table is spread over multiple physical devices, partitioning improves insert performance by reducing I/O contention while Adaptive Server is flushing data from cache to disk.
Partitioning a table does not affect its performance for update or delete commands.
Use the unpartition clause of the alter table command to concatenate all existing page chains.
Neither partitioning nor unpartitioning a table moves existing data.
To change the number of partitions in a table, first use the unpartition clause of alter table to concatenate its page chains. Then use the partition clause of alter table to repartition the table.
sp_helpartition looks only in the current database for the table.
Use sp_helpsegment to display the number of used and free pages on the segment on where the partitioned table is stored.
The values reported in the “data_pages” column may be greater than the actual values. To determine whether the count is inaccurate, run sp_statistics and sp_helpartition to compare the data page count. The count provided by sp_statistics is always accurate.
If the page count reported by sp_statistics differs from the sum of the partition pages reported by sp_helpartition by more then 5 percent, run one of the following commands to update the partition statistics:
dbcc checkalloc
dbcc checkdb
dbcc checktable
update all statistics
update partition statistics
Then, rerun sp_helpartition for an accurate report.
Any user can execute sp_helpartition.
Catalog system procedures sp_statistics
Commands alter table, insert
System procedures sp_helpsegment
