Update database statistics

Available with Standard or Advanced license.

The database management system optimizer uses database statistics to choose the optimal plan for queries. To maintain query performance, update statistics after the content of a table or feature class changes significantly—for example, after you load a large amount of records to it or perform a lot of edits on the table.

Statistics can be updated on the following types of tables and indexes in an enterprise geodatabase:

• User-defined tables and feature classes and their associated indexes
• The delta tables of tables and feature classes that are registered for traditional versioning and the indexes on the delta tables
• The historical archive tables of tables and feature classes that are enabled for geodatabase archiving, and the archive tables' indexes
• Geodatabase system tables

Note:

Updating database statistics is an input/output (I/O)-intensive operation. Update statistics when database traffic is at its lightest.

The database administrator can use database tools to update database statistics for user-created tables and feature classes. However, when updating statistics on tables and feature classes in an enterprise geodatabase, the database administrator must also update statistics on the correct geodatabase internal tables that are related to them. To ensure that statistics on these internal tables are updated, run the Analyze Datasets geoprocessing tool or AnalyzeDatasets_management ArcPy function.

Also use the Analyze Datasets geoprocessing tool or AnalyzeDatasets_management ArcPy function to update database statistics for geodatabase system tables. Update statistics on the geodatabase system tables after many new tables or feature classes have been added to the geodatabase, a large number of versioned edits have been performed, or the geodatabase has been compressed.

You must run the tool or function as the owner of the tables. In the case of geodatabase system tables, the owner is the geodatabase administrator.

Use the Analyze Datasets tool

You can use the Analyze Datasets geoprocessing tool to update database statistics. This tool allows data owners to choose which tables and feature classes in a dataset to analyze and allows the geodatabase administrator to update statistics on geodatabase system tables.

1. Start ArcGIS Pro and connect to the geodatabase that contains the tables for which you want to update statistics.

   Connect as the owner of the tables to be analyzed.

2. Open the Analyze Datasets geoprocessing tool.

   You can type the tool name in the Search field to find the tool and open it, or browse to the tool in the Geodatabase Administration toolset of the Data Management toolbox.

3. Use the database connection file you created in step 1 as the input workspace.
4. Choose one or more of the options below to specify the tables to analyze:
   • To update statistics for the selected user-defined tables, leave Analyze Base Tables for Selected Dataset(s) checked.
   • Check Analyze Delta Tables for Selected Dataset(s) to update statistics on the adds and deletes tables of traditional versioned datasets.
   • Check Analyze Archive Tables for Selected Dataset(s) to update statistics on the historical archive tables of datasets that are enabled for archiving.
   • Check Include System Tables to update statistics on geodatabase system tables. The database connection file specified in the previous step must connect as the geodatabase administrator to use this option.
5. Click Run.

Statistics are updated for the tables you chose and their indexes.

Run a Python script

You can call the AnalyzeDatasets_management ArcPy function in a Python script to update statistics on base tables, delta tables, archive tables, or geodatabase system tables. Just as with the Analyze Datasets tool, you must run the AnalyzeDatasets_management ArcPy function as the owner of the tables you are analyzing.

When you run the following Python script, specify a database connection file (.sde file) when you run it. The script updates statistics for all tables owned by the user specified in the connection.

For example, to run this script on Linux using the database connection file entgdb.sde, located in /usr/connections, type the following:

./ AnalyzeDatasets.py /usr/connections/entgdb.sde

To run this script to update statistics on system tables, specify a connection file that connects as the geodatabase administrator.

# Name: AnalyzeDatasets.py
# Description: analyzes all datasets in an enterprise geodatabase
#              for a given user.

# Import system modules
import arcpy
import os

# set workspace
# the user in this workspace must be the owner of the data to analyze.
workspace = "C:\\MyProject\\MyDataConnection.sde"

# set the workspace environment
arcpy.env.workspace = workspace

# NOTE: Analyze Datasets can accept a Python list of datasets.

# Get the user name for the workspace
userName = arcpy.Describe(workspace).connectionProperties.user

# Get a list of all the datasets the user owns by using a wildcard that incldues the user name
# First, get all the stand alone tables, feature classes and rasters.
dataList = arcpy.ListTables(userName + "*") + arcpy.ListFeatureClasses(userName + "*") + arcpy.ListRasters(userName + "*")

# Next, for feature datasets get all of the datasets and featureclasses
# from the list and add them to the master list.
for dataset in arcpy.ListDatasets(userName + "*", "Feature"):
    arcpy.env.workspace = os.path.join(workspace,dataset)
    dataList += arcpy.ListFeatureClasses(userName + "*") + arcpy.ListDatasets(userName + "*")

# reset the workspace
arcpy.env.workspace = workspace

# Execute analyze datasets
# Note: to use the "SYSTEM" option the workspace user must be an administrator.
arcpy.management.AnalyzeDatasets(workspace, "NO_SYSTEM", dataList, "ANALYZE_BASE", "ANALYZE_DELTA", "ANALYZE_ARCHIVE")
print("Analyze Complete")