update all statistics

Updates all statistics information for a given table, including histograms on all columns, regardless of whether they are indexed. You can run update all statistics on a single data partition.


update all statistics
	table_name [partition data_partition_name]
	[using step values]
	[with consumers = consumers] 
	[, sampling=N [percent]]
	[, no_hashing | partial_hashing | hashing] 
		[, max_resource_granularity = N [percent]]
		[, histogram_tuning_factor = int ]
		[, print_progress = int]




  • update all statistics updates all the statistics information for a given table. The SAP ASE server keeps statistics about the distribution of pages within a table, and uses these statistics when considering whether or not to use a parallel scan in query processing on partitioned tables, and which index (es) to use in query processing. The optimization of your queries depends on the accuracy of the stored statistics.

  • Histogram statistics are created on each column, either through an index scan of a leading column, a projection of the column into a work table followed by a sort, or by using hashing to concurrently generate histograms on several columns for respective scans.

  • Density statistics are created for all the prefix subsets of the columns of index (es) whose statistics is being updated. For example, if an index is on columns c1, c2 and c3, then the prefix subsets are (c1,c2) and (c1, c2, c3).

  • When you run update all statistics on a single data partition, histograms are generated for each leading column of the local indexes using an index scan. If hash-based statistics gathering is enabled, then statistics are gathered on all the minor attributes as well during the index scans. For all other columns, including leading columns of a global index, update all statistics performs either a data scan followed by a sort or performs a data scan that uses hash-based statistics gathering.

    The advantage of hash-based statistics is that one data scan can collect histograms on all columns, whereas sort-based statistics uses a separate scan for each column.

  • update statistics commands create partition-specific statistics. Global statistics are implicitly created during partition statistics creation. The partition statistics serve as input to global statistics creation and enable per-partition DDL operations. Global statistics are used by the optimizer.

  • update all statistics regenerates and update the table statistics stored in systabstats for each data and index partition of the table. If the update all statistics command is run for a specific data partition, the table statistics are generated and updated only for that data partition and any local index partitions. Global indexes are skipped.

When using hash-based statistics:
  • The partial_hashing parameter uses hashing on columns only when it produces histograms that are as accurate and of equivalent quality as those produced by sorting (low domain cases), otherwise, the partial_hashing parameter uses sorting for high-domain cases.

  • Hashing may produce less accurate histograms than sorting in some cases (high-domain cases).

  • Although hash-based statistics do not require the tempdb disk space or procedure cache memory used by sorting, it may use a significant amount of tempdb buffer cache memory.

  • Large sorts used for the no_hashing parameter may scavenge statements in the statement cache and stored procedures to release procedure cache memory to support the sort.

  • max_resource_granularity limits the amount of tempdb buffer cache memory used for hashing or partial_hashing. It does not affect the amount of memory used by the no_hashing parameter or sorting.

  • If you include the partial_hashing parameter, and a previous histogram on a column exists that indicates a high-domain column, then the SAP ASE server assumes that sort-based statistics are required on this column. If no previous histogram exists on a column, then the SAP ASE server assumes this column is low-domain until the high domain limit is reached.


ANSI SQL – Compliance level: Transact-SQL extension.


You must be the table owner or a user with update statistics permission on the table to run update all statistics.

Related reference
update statistics
update index statistics
update table statistics