Statistics in SQL Server databases

ArcSDE geodatabases stored in SQL Server databases can be set to update statistics automatically. To do this, leave the database parameters AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS enabled. Disable autoshrink.

If you do not do this, you need to update statistics with a full scan on all tables involved in your system, including the sde_state_lineages and sde_states tables. To update statistics with a full scan, use one of the following:

• Transact SQL (T-SQL)

  UPDATE STATISTICS <table name> 
  WITH FULLSCAN

  This updates statistics on one table at a time. For updates to all tables in the database, use the sp_updatestats command.
• ArcSDE sdetable administration command

  sdetable –o update_dbms_stats –t <table_name> 
  –m "with fullscan" –i <instance> –D <db>

• SqlMaint

  sqlmaint –S <server> –U <user> –P <pass> –D <db> –UpdOptiStats 100

  Consult the SQL Server Books Online for more information regarding SqlMaint. The command can be launched from a command window in the SQL Server installation\mssql\bin directory.

To update statistics for a particular table, you can also use the Analyze command in ArcGIS Desktop. See Updating statistics on a geodatabase using Analyze for instructions.

Note:

The Analyze command and geoprocessing tool do not perform a full scan when updating statistics.