As a best practice, update statistics only as required, using a schedule appropriate for your environment for statistics maintenance during off hours. Consider the need and frequency of the Maintenance Window based on:

  • The database environment
  • The amount and frequency of data changes in the database
  • When queries are running slower than expected
  • When data is initially added to the empty tables

Specify the duration, date, time and the frequency to trigger the Maintenance Window and update the statistics. The configuration details of the AUS Maintenance Window are stored in the SYSSQL_PROPERTIES system table.

Run the AUS Service

To run the AUS service, perform these steps.

  1. Start the database with the database startup parameter (broker option) for AUS -SQLAutoStats.

    PROSERVE -SQLAutoStats ON <other_options>

    Note: The <other_options> refers to the database startup parameters used to start the database.

    In addition to using the AUS command, you can also choose to control the AUS functionality i.e. enable or disable the AUS service with the PROMON utility or the _DbParams in the VST as described in Monitor AUS Server activities. AUS provides a default configuration for the AUS maintenance window to update statistics, if you have not explicitly specified a maintenance window.

  2. Switch ON AUS service using the AUS SQL command. Below is a simple example of doing this.
    AUTOUPDATE STATISTICS ON 
MAINTENANCE WINDOW EVERY DAY FOR 1 HOUR STARTS AT 22:00:00;

    Once the AUS service is up and running, it continually performs a check to trigger update statistics in the Maintenance Window. In the meantime, the AUS client collects the data points for every table that is part of the executed queries. If a data point is returned as isPositive 1, the table statistics needs an update. Once the maintenance window starts, the AUS Server updates the statistics for tables for which positive data points are recorded, which in this case, occurs at 10:00PM for 1 hour every day.

    Each data point represents whether the table statistics are up-to-date or not, which is represented in the system table in the form of a boolean value. For a table, value of 1 denotes that the statistics might not be up-to-date and requires update statistics, whereas, a value of 0 represents statistics that are already up-to-date.

Syntax for the AUS SQL statement

AUTOUPDATE STATISTICS ON 
MAINTENANCE WINDOW EVERY (DAY | WEEK | MONTH)
[INTERVAL <N>]
FOR <N> HOUR STARTS AT <TIME>
[FROM NOW | <DATE>]
[CHANGE THRESHOLD <change_perc>]
[CONFIDENCE THREHSOLD <conf_perc>]
[PARALLEL TASKS <N>]

AUS Maintenance Window

The Maintenance Window informs the SQL server about the schedule at which to run update statistics when it finds the statistics to be stale or absent. Following are the various options related to the Maintenance Window:

Options Description
EVERY Specify the frequency for the AUS server to update statistics when they are stale or do not exist.

Valid values are DAY, WEEK and MONTH. Specifying this option is mandatory, otherwise, a syntax error is thrown.
INTERVAL Specify the interval between each maintenance window. This is optional and default value will be 1.

If the INTERVAL value specified is 3 and the value of EVERY is day, it means that the Maintenance Window is configured to occur every 3 days.
STARTS AT Specify the time of the day for the Maintenance Window to begin in a 24 hour format. Specifying the start date is mandatory. Otherwise, a syntax error is thrown.
FROM Specify effective start date of the first maintenance window.

Valid values are NOW or any date which is equal or greater than today's date. This is optional and default value is NOW, which means the date on which this command is executed.
CHANGE THRESHOLD Represents the percentage of change in data which denotes that a data point should be marked as positive. Positive data points give an indication that the statistics might be stale. The default value is 20%.
CONDFIDENCE THRESHOLD Represents the percentage of positive data point instances among a total number of times a table is accessed. If positive data point instances meet the specified confidence threshold, then statistics will be considered as stale.
PARALLEL TASKS <n> Specify how many threads should run in parallel for updating statistics. For example, if the specified value is 6, in the Maintenance Window,6 threads can start updating statistics in parallel with each thread updating the statistics for one table.

Examples

Here are a few examples on how to use the command to configure the various AUS options.

Schedule monthly

In this example, the command turns ON AUS service with the Maintenance Window at 22:00:00 hours once in every month starting from the time when this command is executed.

AUTOUPDATE STATISTICS ON MAINTENANCE WINDOW EVERY MONTH FOR 2 HOUR STARTS AT 22:00:00;

Schedule fortnightly

In this example, the command turns ON AUS service with the Maintenance Window at 10:00 PM once in two weeks starting from 30 June 2018 22:00:00. This means that the Maintenance Window will be considered starting from 30 June 2018, though the above command is executed on a different date - for instance, 15 June 2018.

AUTOUPDATE STATISTICS ON MAINTENANCE WINDOW EVERY WEEK INTERVAL 2 FOR 2 HOUR STARTS AT 22:00:00 FROM 30-06-2018;

Schedule fortnightly with multiple threads running in parallel

In this example, the command is same as in the Schedule fortnightly above, but with configuration, 5 threads will run in parallel during the Maintenance Window to update the statistics. 

AUTOUPDATE STATISTICS ON MAINTENANCE WINDOW EVERY WEEK INTERVAL 2 FOR 2 HOUR STARTS AT 22:00:00 FROM 30-06-2018 PARALLEL TASKS 5;

AUTOUPDATE STATISTICS ALTER

The ALTER command enables a user to change any one or all of the AUS configuration parameters that were initially set using the ON command.

Syntax


AUTOUPDATE STATISTICS ALTER alter_options;

Where, alter_options are:

[MAINTENANCE WINDOW EVERY (DAY | WEEK | MONTH)
[INTERVAL <N>]
FOR <N> HOUR STARTS AT <TIME>
[FROM NOW | <DATE>]]

[CHANGE THRESHOLD <change_perc>]
[CONFIDENCE THREHSOLD <conf_perc>]
[PARALLEL TASKS <N>]

While altering the maintenance window, you must specify the EVERY, FOR and STARTS AT Maintenance Window options. Otherwise, a syntax error is thrown.

Apart from the Maintenance Window options, you can also alter any one of the AUS options.

Example

Schedule fortnightly - Maintenance Window parameters

In this example, the Maintenance Window is configured to run FOR 2 HOUR to update the statistics.

AUTOUPDATE STATISTICS ON MAINTENANCE WINDOW EVERY WEEK INTERVAL 1 FOR 3 HOUR STARTS AT 22:00:00 PARALLEL TASKS 5;

To change the configuration of the Maintenance Window parameter from 2 hours to 3 hours, run the below command.

AUTOUPDATE STATISTICS ALTER ON MAINTENANCE WINDOW EVERY WEEK FOR 3 HOUR STARTS AT 22:00:00;

Schedule fortnightly with multiple threads running in parallel - optional parameters

In this example, the parallel number of tasks are configured to 5, which will result in five threads running in parallel during the Maintenance Window to update the statistics.

AUTOUPDATE STATISTICS ON MAINTENANCE WINDOW EVERY WEEK INTERVAL 2 FOR 2 HOUR STARTS AT 22:00:00 PARALLEL TASKS 5;

To change the configuration of the parallel number of tasks to 3, alter the AUS command as shown below;

AUTOUPDATE STATISTICS ALTER PARALLEL TASKS 3;

AUS database connection details

AUS runs on a dedicated AUS Server and also on each SQL Server, where a key AUS data point collection service is executed.

The number of database connections used by AUS includes a sum of AUS connections, the connections configured by an AUS DBA and Mn.
  • The number of AUS Server connections is 4 (AUS Server metadata management connections).
  • The default number of AUS DBA user configured connections is 3 with a maximum of 10 AUS tasks that a DBA user can configure.
  • Mn represents the number of SQL Server processes.
AUS Server Connections DBA Configured AUS Connections -Mn broker Configuration Connections used by AUS
4 3 (Default) -Mn 7+Mn
4 5 -Mn 9+Mn
4 6 -Mn 10+Mn
4 7 -Mn 11+Mn
4 8 -Mn 12+Mn
4 9 -Mn 13+Mn
4 10 -Mn 14+Mn