Back to Top

Update database statistics

In this topic

  1. Use the Analyze Datasets tool
  2. Run a Python script

Available with Standard or Advanced license.

The database management system optimizer uses database statistics to choose the optimal execution plan for queries. To maintain query performance, you should update statistics after the content of a table 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:

  • The table you selected to analyze and its associated indexes
  • The delta tables of versioned tables and feature classes and the indexes on the delta tables
  • The historical archive tables of datasets that are enabled for archiving, and the archive tables' indexes
  • Geodatabase system tables
Note:

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

There are several methods in ArcGIS you can use to update statistics. These are described in the following sections:

Use the Analyze Datasets tool

You can use the Analyze Datasets geoprocessing tool (located in the Geodatabase Administration toolset of the Data Management toolbox) to update statistics. This tool allows you to choose which tables in a dataset to analyze and lets the geodatabase administrator update statistics on geodatabase system tables.

You must run the tool as the owner of the tables you want to analyze. In the case of geodatabase system tables, you must run the tool as the geodatabase administrator. You should 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.

  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 into 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. Check which tables you want analyzed:
    • If you want statistics updated for the selected base 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 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. Note that this can only be run if you are connected to the geodatabase as the geodatabase administrator.
  5. Click Run.

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

Run a Python script

You can call the AnalyzeDatasets_management 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 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 = arcpy.GetParameterAsText(0)

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

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

# Get a list of all the datasets the user has access to.
# First, get all the stand alone tables, feature classes and rasters.
dataList = arcpy.ListTables() + arcpy.ListFeatureClasses() + arcpy.ListRasters()

# 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("", "Feature"):
    arcpy.env.workspace = os.path.join(workspace,dataset)
    dataList += arcpy.ListFeatureClasses() + arcpy.ListDatasets()

# reset the workspace
arcpy.env.workspace = workspace

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

# remove any datasets that are not owned by the connected user.
userDataList = [ds for ds in dataList if ds.lower().find(".%s." % userName) > -1]

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

Feedback on this topic?

In this topic
  1. Use the Analyze Datasets tool
  2. Run a Python script