Job Scheduler includes the update statistics template, which you can use to create a job that runs update statistics on a table, index, column, or partition. The datachange function determines when the amount of change in a table or partition has reached the predefined threshold. You determine the value for this threshold when you configure the template.
Templates:
Run update statistics on specific tables, partitions, indexes, or columns. The templates allow you to define the value for datachange at which you want update statistics to run.
Run update statistics at the server level, which configures Adaptive Server to sweep through the available tables in all databases on the server and update statistics on all the tables, based on the threshold you determined when creating your job.
To configure Job Scheduler to automate the process of running update statistics (the chapters listed are from the Job Scheduler Users Guide:
Install and set up Job Scheduler (Chapter 2, “Configuring and Running Job Scheduler”)
Install the stored procedures required for the templates (Chapter 4, “Using Templates to Schedule Jobs”).
Install the templates. Job Scheduler provides the templates specifically for automating update statistics (Chapter 4, “Using Templates to Schedule Jobs”).
Configure the templates. The templates for automating update statistics are in the Statistics Management folder.
Schedule the job. After you have defined the index, column, or partition you want tracked, you can also create a schedule that determines when Adaptive Server runs the job, making sure that update statistics is run only when it does not impact performance.
Identify success or failure. The Job Scheduler infrastructure allows you to identify success or failure for the automated update statistic.
The template allows you to supply values for the various options of the update statistics command such as sampling percent, number of consumers, steps, and so on. Optionally, you can also provide threshold values for the datachange function, page count, and row count. If you include these optional values, they are used to determine when and if Adaptive Server should run update statistics. If the current values for any of the tables, columns, indexes, or partitions exceed the threshold values, Adaptive Server issues update statistics. Adaptive Server detects that update statistics has been run on a column. Any query referencing that table in the procedure cache is recompiled before the next execution.
There are many forms of the update statistics command (update statistics, update index statistics, and so on); use these different forms depending on your needs.
You must specify three thresholds: rowcount, pagecount, and datachange. Although values of NULL or 0 are ignored, these values do not prevent the command from running.
Table 10-1 describes the circumstances under which Adaptive Server automatically runs update statistics, based on the parameter values you provide.
If the user |
Action taken by Job Scheduler |
|---|---|
Specifies a datachange threshold of zero or NULL |
Runs update statistics at the scheduled time. |
Specifies a datachange threshold greater than zero for a table only, and does not request the update index statistics form |
Gets all the indexes for the table and gets the leading column for each index. If the datachange value for any leading column is greater than or equal to the threshold, run update statistics. |
Specifies threshold values for the table and index but does not request the update index statistics form |
Gets the datachange value for the leading column of the index. If the datachange value is greater than or equal to the threshold, runs update statistics. |
Specifies a threshold value for a table only, and requests the update index statistics form |
Gets all the indexes for the table and gets the leading column for each index. If the datachange value for any leading column exceeds the threshold, runs update statistics. |
Specifies threshold values for table and index and requests the update index statistics form |
Gets the datachange value for the leading column of the index. If the datachange value is greater than or equal to the threshold, runs update statistics. |
Specifies threshold values for a table and one or more columns (ignores any indexes or requests for the update index statistics form) |
Gets the datachange value for each column. If the datachange value for any column is greater than or equal to the threshold, runs update statistics. |
The datachange function compiles the number of changes in a table and displays this as a percentage of the total number of rows in the table. You can use this compiled information to create rules that determine when Adaptive Server runs update statistics. The best time for this to happen can be based on any number of objectives:
The percentage of change in a table
Number of CPU cycles available
During a maintenance window
After update statistics runs, the datachange counter is reset to zero. The count for datachange is tracked at the partition level (not the object level) for inserts, and deletes and at the column level for updates.
