Worker processes getting stuck on 'sync sleep' state

Hi,

We have enabled I/O parallelism on our Sybase instance.

sp_configure 'max parallel degree',4
sp_configure 'max scan parallel degree',2

We are seeing cases where a query that uses parallel scan with two
worker processes gets stuck. Both the worker process (process with
difference spid but different family_id) has the status 'sync sleep'.
This even happens for very simple queries that hardly takes 10ms.

Could someone please let know if this a bug or is this the first time
you are hearing it.

Regards,
Thyagu

Hey There!

I take it you have 'number of worker processes' and 'memory per worker
process' set OK (following are examples):

Parameter Name Default Memory Used Config Value Run
Value
------------------------------ ----------- ----------- ------------
-----------
memory per worker process 1024 65 1024
1024
number of worker processes 0 7316 64
64

1024 is the default for the memory per worker process and *usually*
does not need to be changed. Under normal parallel processing, the
parent spid is in the 'sync sleep' state while the worker processes go
in and out of this state during their work.

What version are you running? Also, what do you mean by 'stuck'?

Cheers,

Sara ...

dellit wrote:
> Hi,
>
> We have enabled I/O parallelism on our Sybase instance.
>
> sp_configure 'max parallel degree',4
> sp_configure 'max scan parallel degree',2
>
> We are seeing cases where a query that uses parallel scan with two
> worker processes gets stuck. Both the worker process (process with
> difference spid but different family_id) has the status 'sync sleep'.
> This even happens for very simple queries that hardly takes 10ms.
>
> Could someone please let know if this a bug or is this the first time
> you are hearing it.
>
> Regards,
> Thyagu