can we run 'update stats' in parallel on the same database

[Former Member]

Hi community,

i want to execute an 'update index statistics' on all the tables of my database.

To do so I first retrieve the list of tables and dispatch them in 4 files with the corresponding 'update index statistics' command.

I then execute 4 'isql' in background

isql -U xx -P xx -S xx -D xx -i xx1.sql -o xx1.log &

isql -U xx -P xx -S xx -D xx -i xx2.sql -o xx2.log &

isql -U xx -P xx -S xx -D xx -i xx3.sql -o xx3.log &

isql -U xx -P xx -S xx -D xx -i xx4.sql -o xx4.log &

By doing so, I was expecting to see several update statistics running but looking at the output of sp_who, I notice only one is being executed (see attachment)

I'd like to know if I'm doing anything wrong to have those update statistics executed in parallel

Thanks for your inputs

Simonsp-who.txt

[Former Member]

Thanks Avinash, Mark for your inputs,

Indeed I didn't think of some spid might be sleeping waiting for data to be in cache knowing I'm dealing with some large tables.

I'm gonna run another test with monitoring some MDA tables.

Thanks

Simon

[former_member89972]

Interesting.

Update stats does update system tables. So some b/locking will happen. You should be able to see that with simple sp_lock or something similar. AFAIK there is no limitation on running multiple update stats (index or table) in a single database.

I have not yet ventured into "threaded" mode of ASE. So not sure how multiple SPIDs run at the same time in threaded mode look like. In our ASE 15.5 (process mode) we use very similar technique for running parallel index creations for example and run at least 4 SPIDs at the same time.

Questions for your threaded mode :

For a normal sp_who execution do you ever see more than one ( 4 in your case ?) SPIDs in actual "running" state ?

Also what does periodic sampling of monProcessWaits show for each SPID ? ( what resource SPID is waiting on ?)

HTH

Avinash

[Former Member]

Hi Avinash

If forgot to add the following information at my original question

Sybase version = Adaptive Server Enterprise/15.7.0/EBF 22639 SMP SP52
sp_configure 'max online engine' = 4
sp_configure 'kernel mode' = threaded
syb_default_pool = 4
syb_system_pool =4
number of rows in monEngine = 4

Therefore in looks like my dataserver is configured to allow several update stats to be executed in parallel unless an internal prevent such operation when they're run on the same database

Simon

[former_member89972]

How may engines do you have ?

If it is a single engine instance, SPIDs will run on that one engine by rotation !!

In the output of sp_who |grep "run" if you see only one entry as "running" and multiple entries with runnable status then you need more engines.

I normally add following to most of my isql scripts that run unattended.

select @@servername, db_name(), getdate(), "BEGIN"

go

.....

select @@servername, db_name(), getdate(), "END"

go

This confirms that the SQL ran in the expected database and the run time.

You can also probe master..monProcessWaits for each of the SPIDs to find why they are waiting.

HTH

Avinash