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
5 Answers
-
Best AnswerPosted on Dec 27, 2016 at 08:01 PM
Technically speaking ... all 4 of your update stats commands *are* running; most of the processes in your sp_who snippets just happen to be swapped off a cpu at the time (ie, show status of sleeping or runnable).
Repeated snapshots of monProcessActivity and monProcessObject should show increasing counts for physical IOs, logical IOs and cpu cycles; how fast these numbers are increasing will depend on disk/read performance and amount of cpu in use by other non-update stats commands running at the same time.
----------
Size of tables (and associated indexes) currently having their stats updated?
Are said tables (and their indexes) already in cache?
I'm guessing some largish tables/indexes with majority of their pages sitting out on disk, so a good bit of the time the processes are 'sleeping' is due to waiting for pages to be read from disk into cache.
As Avinash has pointed out a couple times, keep an eye on monProcessWaits to see what events are associated with the 'sleeping' status.
----------
What else was running at the time?
You've filtered out and are only displaying those sp_who rows associated with your update stats processes.
I'm guessing you had, at least, a few other cpu-bound processes running at the same time, with the expectation that your update stats processes showing as 'runnable' were scheduled on an engine that was currently in use by some other process/spid.]
Add a comment
-
Posted on Dec 27, 2016 at 03:55 PM
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
Add a comment
-
Former MemberPosted on Dec 27, 2016 at 04:14 PMHi 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 = 4Therefore 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
Add a comment
-
Posted on Dec 27, 2016 at 06:47 PM
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
Add a comment
-
Former MemberPosted on Dec 28, 2016 at 07:53 AM
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
Add a comment
Add a comment