update partition statistics

Description

Updates information about the number of pages in each partition for a partitioned table.

Syntax

update partition statistics table_name [partition_number]

Parameters

table_name: is the name of a partitioned table.

partition_number: is the number of the partition for which you are updating information. If you do not specify a partition number, update partition statistics updates the number of data pages in all partitions in the specified table.

Usage

Adaptive Server keeps statistics about the distribution of pages within a partitioned table and uses these statistics when considering whether to use a parallel scan in query processing. The optimization of your queries depends on the accuracy of the stored statistics. If Adaptive Server crashes, the distribution information could be inaccurate.

To see if the distribution information is accurate, use the data_pgs function to determine the number of pages in the table, as follows:
```
select data_pgs(sysindexes.id, doampg) 
   from sysindexes
   where sysindexes.id = object_id("table_name")
```
Then, use sp_helpartition on the table and add up the numbers in the “ptn_data_pgs” column of the output. The sum of the total of the number of pages that sp_helpartition reports should be slightly greater than the number returned by data_pgs because sp_helpartition’s page count includes OAM pages.

If the distribution information is inaccurate, run update partition statistics on the table. While updating the distribution information, update partition statistics locks the OAM page and the control page of the partition.
When you run update partition statistics on a table that contains data, or you create an index on a table that contains data, the controlpage column in syspartitions is updated to point to the control page for the partition.
update partition statistics updates control page values used to estimate the number of pages in a table. These statistics are used by sp_helpartition.

Standards

SQL92 – Compliance level: Transact-SQL extension.

Permissions

update partition statistics permission defaults to the table owner and is not transferable.