Update statistics is single-threaded by default - any one update statistics job will only ever run on one ASE engine.

However even in ASE 11.9.2 it is possible to force parallelism using the "with consumers = N" clause, but this can only be used with the "named column" form of update statistics, i.e.:

update statistics table (col1, col2)

with consumers = 4

This usually won't be the most effective form of update stats. (It also requires that parallel processing be enabled in the usual way, i.e. "number of worker processes", "max parallel degree" and "max scan parallel degree" must all be set higher than 1.)

If you're wanting to bring down the end to end time of your update statistics job, you can run multiple update stats commands on different tables concurrently. The useful limit here is probably not more than the number of engines. Note you say that your server has four CPUs but as you probably know that isn't the same as having four ASE engines, which you much separately configure. This will scale almost linearly, assuming you can divide the workload up between all engines evenly.

Another related idea is to have a policy of running update statistics, say, once per week on any one table, but rather than running it for all tables in the same window of opportunity, run it on one seventh of the tables each night. Assuming you can divide the work up evenly, this should also scale fairly linearly.

Both approaches can of course be combined, so in a perfect world (all tables are the same size), you should be able to run four concurrent update statistics streams on one seventh of your tables each night, for an approximate 28 times improvement in end to end time. That would take you down to about 35 minutes each night, again assuming all tables are the same size and the work can be divided up evenly.

If you're interested, I have a shell script written in ksh that will do this for you - perform any arbitrary table-level operation in an arbitrary number of concurrent streams. It's useful for table-level dbcc's, reorgs, and so on.

You should also be aware that starting in ASE 12.5.0.3, it's possible to run update statistics specifying a sample percentage. This doesn't quite do the same amount of work (it won't update the density, for example), but most of the time you really don't need to read every single row to still get a pretty good feel for what the data looks like. I've had a lot of success with 25% sampling (on large tables), which isn't four times faster but is certainly more than twice as fast. Combining this with the earlier approaches would have you doing your update stats in maybe 20 minutes a day!

I should also mention that update statistics will make use of large I/O buffer pools, if they are correctly configured, the data isn't too fragmented, and the data hasn't already been loaded into smaller buffer pools in cache.

Good luck!

However even in ASE 11.9.2 it is possible to force parallelism using the "with consumers = N" clause, but this can only be used with the "named column" form of update statistics, i.e.:

update statistics table (col1, col2)

with consumers = 4

This usually won't be the most effective form of update stats. (It also requires that parallel processing be enabled in the usual way, i.e. "number of worker processes", "max parallel degree" and "max scan parallel degree" must all be set higher than 1.)

If you're wanting to bring down the end to end time of your update statistics job, you can run multiple update stats commands on different tables concurrently. The useful limit here is probably not more than the number of engines. Note you say that your server has four CPUs but as you probably know that isn't the same as having four ASE engines, which you much separately configure. This will scale almost linearly, assuming you can divide the workload up between all engines evenly.

Another related idea is to have a policy of running update statistics, say, once per week on any one table, but rather than running it for all tables in the same window of opportunity, run it on one seventh of the tables each night. Assuming you can divide the work up evenly, this should also scale fairly linearly.

Both approaches can of course be combined, so in a perfect world (all tables are the same size), you should be able to run four concurrent update statistics streams on one seventh of your tables each night, for an approximate 28 times improvement in end to end time. That would take you down to about 35 minutes each night, again assuming all tables are the same size and the work can be divided up evenly.

If you're interested, I have a shell script written in ksh that will do this for you - perform any arbitrary table-level operation in an arbitrary number of concurrent streams. It's useful for table-level dbcc's, reorgs, and so on.

You should also be aware that starting in ASE 12.5.0.3, it's possible to run update statistics specifying a sample percentage. This doesn't quite do the same amount of work (it won't update the density, for example), but most of the time you really don't need to read every single row to still get a pretty good feel for what the data looks like. I've had a lot of success with 25% sampling (on large tables), which isn't four times faster but is certainly more than twice as fast. Combining this with the earlier approaches would have you doing your update stats in maybe 20 minutes a day!

I should also mention that update statistics will make use of large I/O buffer pools, if they are correctly configured, the data isn't too fragmented, and the data hasn't already been loaded into smaller buffer pools in cache.

Good luck!