The Adaptive Server cost-based query processor uses statistics for the tables, indexes, and columns named in a query to estimate query costs. Based on these statistics, the query processor chooses the access method it determines has the least cost. However, this cost estimate cannot be accurate if the statistics are not accurate. You can run update statistics to ensure that the statistics are current. However, running update statistics has an associated cost because it consumes system resources such as CPU, buffer pools, sort buffers, and procedure cache.
Instead of manually running update statistics at a certain time, you can set update statistics to run automatically when it best suits your site and avoid running it at times that hamper your system. The best time for you to run update statistics is based on the feedback from the datachange function. datachange also helps to ensure that you do not unnecessarily run update statistics. You can use these templates to determine the objects, schedules, priority, and datachange thresholds that trigger update statistics, which ensures that critical resources are used only when the query processor generates more efficient plans.
Because it is a resource-intensive task, base the decision to run update statistics on a specific set of criteria. Key parameters that can help you determine a good time to run update statistics include:
How much the data characteristics changed since you last ran update statistics. This is known as the datachange parameter.
Whether there are sufficient resources available to run update statistics. These include resources such as the number of idle CPU cycles and making sure that critical online activity does not occur during update statistics.
Data change is a key metric that helps you measure the amount of altered data since you last ran update statistics, and is tracked by the datachange function. Using this metric and the criteria for resource availability, you can automate the process of running update statistics. Job Scheduler includes a mechanism to automatically run update statistics. Job Scheduler also includes a set of customizable templates that determine when to run update statistics. These inputs include all parameters to update statistics, the datachange threshold values, and the time to run update statistics. Job Scheduler runs update statistics at a low priority so it does not affect critical jobs that are running concurrently.
