Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

system stored procedures hang

7 posts in General Discussion Last posting was on 2010-09-24 08:04:59.0Z
s1 Posted on 2010-09-22 02:35:23.0Z
Sender: 3b4.4c996ab4.1804289383@sybase.com
From: s1
Newsgroups: sybase.public.ase.general
Subject: system stored procedures hang
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c996b6b.3c6.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Sep 2010 19:35:23 -0700
X-Trace: forums-1-dub 1285122923 10.22.241.41 (21 Sep 2010 19:35:23 -0700)
X-Original-Trace: 21 Sep 2010 19:35:23 -0700, 10.22.241.41
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29587
Article PK: 78819

Hi

I have a server which appears to freeze up during a
particular job. This appears to be particularly related to
tempdb .. i.e cannot run sp procs or anything which has
worktable creation requirement. However can select from
system/mon tables. Can also go into tempdb and create temp
table, insert into same temp table and create indexes
against it, order by, group by. So it doesn’t appear to be
tempdb space issue. Syswaits shows 179 & 215 as prominent
waits so it could be cpu bound but I am not sure. Cant repro
in dev, as it appears to manifest when running alongside
other jobs.

This is ASE 12.5.4 ESD 3. Have you come across anything
similar or have suggestions?


Thanks


s1 Posted on 2010-09-22 03:34:11.0Z
Sender: 3b4.4c996ab4.1804289383@sybase.com
From: s1
Newsgroups: sybase.public.ase.general
Subject: Re: system stored procedures hang
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c997933.503.1681692777@sybase.com>
References: <4c996b6b.3c6.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Sep 2010 20:34:11 -0700
X-Trace: forums-1-dub 1285126451 10.22.241.41 (21 Sep 2010 20:34:11 -0700)
X-Original-Trace: 21 Sep 2010 20:34:11 -0700, 10.22.241.41
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29588
Article PK: 78817


> Hi
>
> I have a server which appears to freeze up during a
> particular job. This appears to be particularly related to
> tempdb .. i.e cannot run sp procs or anything which has
> worktable creation requirement. However can select from
> system/mon tables. Can also go into tempdb and create temp
> table, insert into same temp table and create indexes
> against it, order by, group by. So it doesn’t appear to
be
> tempdb space issue. Syswaits shows 179 & 215 as prominent
> waits so it could be cpu bound but I am not sure.

monProcess shows quite a lot of the active processes with
EventID 39. Is this a memory issue? Cant
> repro in dev, as it appears to manifest when running
> alongside other jobs.
>
> This is ASE 12.5.4 ESD 3. Have you come across anything
> similar or have suggestions?
>
>
> Thanks


Rob V [ Sybase ] Posted on 2010-09-22 08:23:04.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.9) Gecko/20100915 Thunderbird/3.1.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: system stored procedures hang
References: <4c996b6b.3c6.1681692777@sybase.com>
In-Reply-To: <4c996b6b.3c6.1681692777@sybase.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c99bce8$1@forums-1-dub>
Date: 22 Sep 2010 01:23:04 -0700
X-Trace: forums-1-dub 1285143784 10.22.241.152 (22 Sep 2010 01:23:04 -0700)
X-Original-Trace: 22 Sep 2010 01:23:04 -0700, vip152.sybase.com
Lines: 48
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29591
Article PK: 78821


On 22-Sep-2010 04:35, s1 wrote:
> Hi
>
> I have a server which appears to freeze up during a
> particular job. This appears to be particularly related to
> tempdb .. i.e cannot run sp procs or anything which has
> worktable creation requirement. However can select from
> system/mon tables. Can also go into tempdb and create temp
> table, insert into same temp table and create indexes
> against it, order by, group by. So it doesnÂ’t appear to be
> tempdb space issue. Syswaits shows 179& 215 as prominent
> waits so it could be cpu bound but I am not sure. Cant repro
> in dev, as it appears to manifest when running alongside
> other jobs.
>
> This is ASE 12.5.4 ESD 3. Have you come across anything
> similar or have suggestions?
>
>
> Thanks

Sounds odd. Nothing in the ASE errorlog?
If you want to know if it's CPU bound, check how busy the OS CPUs are
(or use sp_sysmon).
Other than that, use MDA tables like monLock and monProcess to try and
get an idea of what the state of the hung process is.
Your next stop is probably going to be Sybase TechSupport....

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------


Rob V [ Sybase ] Posted on 2010-09-22 20:48:17.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.9) Gecko/20100915 Thunderbird/3.1.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: system stored procedures hang
References: <4c99bce8$1@forums-1-dub> <4c99e34b.e80.1681692777@sybase.com>
In-Reply-To: <4c99e34b.e80.1681692777@sybase.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c9a6b91@forums-1-dub>
Date: 22 Sep 2010 13:48:17 -0700
X-Trace: forums-1-dub 1285188497 10.22.241.152 (22 Sep 2010 13:48:17 -0700)
X-Original-Trace: 22 Sep 2010 13:48:17 -0700, vip152.sybase.com
Lines: 83
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29596
Article PK: 78825


On 22-Sep-2010 13:06, s1 wrote:
>> On 22-Sep-2010 04:35, s1 wrote:
>>> Hi
>>>
>>> I have a server which appears to freeze up during a
>>> particular job. This appears to be particularly related
>>> to tempdb .. i.e cannot run sp procs or anything which
>>> has worktable creation requirement. However can select
>>> from system/mon tables. Can also go into tempdb and
>>> create temp table, insert into same temp table and
>>> create indexes against it, order by, group by. So it
>>> doesnÂ’t appear to be tempdb space issue. Syswaits
> shows
>>> 179& 215 as prominent waits so it could be cpu bound
>>> but I am not sure. Cant repro in dev, as it appears to
>>> manifest when running alongside other jobs.
>>>
>>> This is ASE 12.5.4 ESD 3. Have you come across anything
>>> similar or have suggestions?
>>>
>>>
>>> Thanks
>> Sounds odd. Nothing in the ASE errorlog?
>> If you want to know if it's CPU bound, check how busy the
>> OS CPUs are (or use sp_sysmon).
>> Other than that, use MDA tables like monLock and
>> monProcess to try and get an idea of what the state of
>> the hung process is. Your next stop is probably going to
>> be Sybase TechSupport....
>>
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Sybase Professional DBA for ASE
>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>> 15.0.1/12.5 // TeamSybase
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>> Reference Guide" "The Complete Sybase Replication Server
>> Quick Reference Guide"
>>
>> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
>> http://www.sypron.nl
>> Sypron B.V., Amersfoort, The Netherlands
>> Chamber of Commerce 27138666
>> ----------------------------------------------------------
>> -------
>> Hi
> Would you be able to expand on mda EventID 39 - wait for
> private buffers to be claimed? If I select out of
> monProcessWaits, this is what all the active processes
> appearing to be waiting on. Thanks

This wait event has to do with a process wanting to allocate buffers in
the data cache for its own use (a "private" buffer which only that
session can access as opposed to regular buffers in the data cache that
can be accassed by everyone.)
Such private buffers may be allocated for various situations, for
example, as part of parallel query processing, for reading certain kinds
of special housekeeping pages, for some dbcc operations, and more.
Contention on this wait event could indicate that the existing
caches/buffer pools are too small for what is needed, so enlarging them
could be something to try. This is all very low-level stuff though.

The question really is: *what* is those sessions doing? What is their
status in sysprocesses? What sort of queries do they run? Do they hold
any locks? Any transactions open? Do they really *hang* (i.e.
indefinitely) or does it just crawl? (check the logical and physical I/O
counts in monSysStatement). Which query do you need to kill in order to
let things proceed?
Please provide more info on this before you try enlarging the
caches/pools -- this will be useful (and interesting) to know (for me at
least).

HTH,

Rob V.


s1 Posted on 2010-09-24 02:05:17.0Z
Sender: 3b4.4c996ab4.1804289383@sybase.com
From: s1
Newsgroups: sybase.public.ase.general
Subject: Re: system stored procedures hang
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c9c075d.3e52.1681692777@sybase.com>
References: <4c9a6b91@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Sep 2010 19:05:17 -0700
X-Trace: forums-1-dub 1285293917 10.22.241.41 (23 Sep 2010 19:05:17 -0700)
X-Original-Trace: 23 Sep 2010 19:05:17 -0700, 10.22.241.41
Lines: 226
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29601
Article PK: 78829


> On 22-Sep-2010 13:06, s1 wrote:
> >> On 22-Sep-2010 04:35, s1 wrote:
> >>> Hi
> >>>
> >>> I have a server which appears to freeze up during a
> >>> particular job. This appears to be particularly
> related >>> to tempdb .. i.e cannot run sp procs or
> anything which >>> has worktable creation requirement.
> However can select >>> from system/mon tables. Can also go
> into tempdb and >>> create temp table, insert into same
> temp table and >>> create indexes against it, order by,
> group by. So it >>> doesn’t appear to be tempdb space
> > issue. Syswaits shows
> >>> 179& 215 as prominent waits so it could be cpu bound
> >>> but I am not sure. Cant repro in dev, as it appears to
> >>> manifest when running alongside other jobs.
> >>>
> >>> This is ASE 12.5.4 ESD 3. Have you come across
> anything >>> similar or have suggestions?
> >>>
> >>>
> >>> Thanks
> >> Sounds odd. Nothing in the ASE errorlog?
> >> If you want to know if it's CPU bound, check how busy
> the >> OS CPUs are (or use sp_sysmon).
> >> Other than that, use MDA tables like monLock and
> >> monProcess to try and get an idea of what the state of
> >> the hung process is. Your next stop is probably going
> to >> be Sybase TechSupport....
> >>
> >> HTH,
> >>
> >> Rob V.
> >>
> ----------------------------------------------------------
> >> ------- Rob Verschoor >>
> >> Certified Sybase Professional DBA for ASE
> >> 15.0/12.5/12.0/11.5/11.0 and Replication Server
> >> 15.0.1/12.5 // TeamSybase
> >>
> >> Author of Sybase books (order online at
> >> www.sypron.nl/shop): "Tips, Tricks& Recipes for
> Sybase >> ASE" (ASE 15 edition) "The Complete Sybase ASE
> Quick >> Reference Guide" "The Complete Sybase Replication
> Server >> Quick Reference Guide"
> >>
> >> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> >> http://www.sypron.nl
> >> Sypron B.V., Amersfoort, The Netherlands
> >> Chamber of Commerce 27138666
> >>
> ----------------------------------------------------------
> >> ------- >> Hi
> > Would you be able to expand on mda EventID 39 - wait for
> > private buffers to be claimed? If I select out of
> > monProcessWaits, this is what all the active processes
> > appearing to be waiting on. Thanks
> This wait event has to do with a process wanting to
> allocate buffers in the data cache for its own use (a
> "private" buffer which only that session can access as
> opposed to regular buffers in the data cache that can be
> accassed by everyone.) Such private buffers may be
> allocated for various situations, for example, as part of
> parallel query processing, for reading certain kinds of
> special housekeeping pages, for some dbcc operations, and
> more. Contention on this wait event could indicate that
> the existing caches/buffer pools are too small for what
> is needed, so enlarging them could be something to try.
> This is all very low-level stuff though.
>
> The question really is: *what* is those sessions doing?
> What is their status in sysprocesses? What sort of
> queries do they run? Do they hold any locks? Any
> transactions open? Do they really *hang* (i.e.
> indefinitely) or does it just crawl? (check the logical
> and physical I/O counts in monSysStatement). Which query
> do you need to kill in order to let things proceed?
> Please provide more info on this before you try enlarging
> the caches/pools -- this will be useful (and interesting)
> to know (for me at least).
>
> HTH,
>
> Rob V.
>
>

Hi Rob

The job appears to be in the form of approx 8 sybase process
threads performing various selects, temp table index creates
etc. Very io heavy. Appears the hang is specific to any
query which has an "order by", thus many of the sp_ procs
not working.
Below are some snapshots from mon tables for waits.

Thanks

This is a snapshot from monProcess :
SPID KPID BatchID ContextID LineNumber
SecondsConnected DBID EngineNumber Priority
FamilyID Login Application
Command NumChildren
SecondsWaiting WaitEventID BlockingSPID BlockingXLOID DBName
EngineGroupName
ExecutionClass MasterTransactionID
------ ----------- ----------- ----------- -----------
---------------- ----------- ------------ -----------
-------- ------------------------------
------------------------------
------------------------------ ----------- --------------
----------- ------------ -------------
------------------------------
------------------------------
------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
23 934609832 7401 0 1
2001 4 5 5 NULL IN
Mx_1_1 SELECT
NULL 424 251
NULL NULL prod
ANYENGINE EC2
NULL
37 929563615 60175 0 1
2248 4 7 5 NULL IN
Mx_1_2 SELECT
NULL 307 39
NULL NULL prod
ANYENGINE EC2
NULL
41 934872551 30674 0 1
2001 4 0 5 NULL IN
Mx_1_2 SELECT
NULL 307 39
NULL NULL prod
ANYENGINE EC2
NULL
55 953222643 819 0 1
577 4 0 5 NULL IN
Mx_1_2 SELECT
NULL 306 39
NULL NULL prod
ANYENGINE EC2
NULL
60 932512997 9239 0 1
2053 4 5 5 NULL IN
Mx_1_1 SELECT
NULL 268 39
NULL NULL prod
ANYENGINE EC2
NULL
61 959119944 4304 0 1
476 4 6 5 NULL IN
Mx_1_1 SELECT
NULL 307 39
NULL NULL prod
ANYENGINE EC2
NULL
69 776798424 13143 0 1
11703 4 1 5 NULL IN
Mx_1_1 SELECT
NULL 777 251
NULL NULL prod
ANYENGINE EC2
NULL
71 960234658 4355 0 1
475 4 3 5 NULL IN
Mx_1_1 SELECT
NULL 307 39
NULL NULL prod
ANYENGINE EC2
NULL


MonProcessWaits:


SPID KPID WaitEventID Waits WaitTime
------ ----------- ----------- ----------- -----------
374 908984829 250 326948 2171700
8 1703962 214 337466 1299700
1054 892535173 250 350498 2442700
1577 892667176 250 353177 2535200
99 892469902 250 386009 2450000
118 393222 200 388873 581700
867 786444 200 400412 317000
823 458759 200 410598 674700
926 851981 200 422427 230400
237 925041659 251 429145 1015300
822 524296 200 442759 558400
31 917518 200 471952 301900
7 1638425 51 511090 3153100
761 147457579 251 577994 3070800
8 1703962 51 1240256 4328500
10 1835036 214 1430328 21747600
800 929825149 29 1680132 1575200
17 4915275 29 1788752 12412100
9 1769499 214 1964629 22498500
8 1703962 31 2371596 3201300



SysWaits

Waits:
WaitEventID WaitTime Waits
171 27 62389
169 281894 77445
61 794561 139400
150 19792 156919
36 1472 195538
37 9 284605
52 2016 449716
55 4077 1976889
31 3349 2444726
51 17025 3256900
200 8153 6890426
214 49278 7991640
35 13744 12505229
39 865171 21336923
124 168728 106011613
29 196330 142334089
251 4086901 215125924
250 22529279 243297140
179 4785306 526755186
215 291469 1283524028


Rob V [ Sybase ] Posted on 2010-09-24 08:04:59.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.9) Gecko/20100915 Thunderbird/3.1.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: system stored procedures hang
References: <4c9a6b91@forums-1-dub> <4c9c075d.3e52.1681692777@sybase.com>
In-Reply-To: <4c9c075d.3e52.1681692777@sybase.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c9c5bab$1@forums-1-dub>
Date: 24 Sep 2010 01:04:59 -0700
X-Trace: forums-1-dub 1285315499 10.22.241.152 (24 Sep 2010 01:04:59 -0700)
X-Original-Trace: 24 Sep 2010 01:04:59 -0700, vip152.sybase.com
Lines: 238
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29602
Article PK: 78831


On 24-Sep-2010 04:05, s1 wrote:
>> On 22-Sep-2010 13:06, s1 wrote:
>>>> On 22-Sep-2010 04:35, s1 wrote:
>>>>> Hi
>>>>>
>>>>> I have a server which appears to freeze up during a
>>>>> particular job. This appears to be particularly
>> related>>> to tempdb .. i.e cannot run sp procs or
>> anything which>>> has worktable creation requirement.
>> However can select>>> from system/mon tables. Can also go
>> into tempdb and>>> create temp table, insert into same
>> temp table and>>> create indexes against it, order by,
>> group by. So it>>> doesnÂ’t appear to be tempdb space
>>> issue. Syswaits shows
>>>>> 179& 215 as prominent waits so it could be cpu bound
>>>>> but I am not sure. Cant repro in dev, as it appears to
>>>>> manifest when running alongside other jobs.
>>>>>
>>>>> This is ASE 12.5.4 ESD 3. Have you come across
>> anything>>> similar or have suggestions?
>>>>>
>>>>> Thanks
>>>> Sounds odd. Nothing in the ASE errorlog?
>>>> If you want to know if it's CPU bound, check how busy
>> the>> OS CPUs are (or use sp_sysmon).
>>>> Other than that, use MDA tables like monLock and
>>>> monProcess to try and get an idea of what the state of
>>>> the hung process is. Your next stop is probably going
>> to>> be Sybase TechSupport....
>>>> HTH,
>>>>
>>>> Rob V.
>>>>
>> ----------------------------------------------------------
>>>> ------- Rob Verschoor>>
>>>> Certified Sybase Professional DBA for ASE
>>>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>>>> 15.0.1/12.5 // TeamSybase
>>>>
>>>> Author of Sybase books (order online at
>>>> www.sypron.nl/shop): "Tips, Tricks& Recipes for
>> Sybase>> ASE" (ASE 15 edition) "The Complete Sybase ASE
>> Quick>> Reference Guide" "The Complete Sybase Replication
>> Server>> Quick Reference Guide"
>>>> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
>>>> http://www.sypron.nl
>>>> Sypron B.V., Amersfoort, The Netherlands
>>>> Chamber of Commerce 27138666
>>>>
>> ----------------------------------------------------------
>>>> ------->> Hi
>>> Would you be able to expand on mda EventID 39 - wait for
>>> private buffers to be claimed? If I select out of
>>> monProcessWaits, this is what all the active processes
>>> appearing to be waiting on. Thanks
>> This wait event has to do with a process wanting to
>> allocate buffers in the data cache for its own use (a
>> "private" buffer which only that session can access as
>> opposed to regular buffers in the data cache that can be
>> accassed by everyone.) Such private buffers may be
>> allocated for various situations, for example, as part of
>> parallel query processing, for reading certain kinds of
>> special housekeeping pages, for some dbcc operations, and
>> more. Contention on this wait event could indicate that
>> the existing caches/buffer pools are too small for what
>> is needed, so enlarging them could be something to try.
>> This is all very low-level stuff though.
>>
>> The question really is: *what* is those sessions doing?
>> What is their status in sysprocesses? What sort of
>> queries do they run? Do they hold any locks? Any
>> transactions open? Do they really *hang* (i.e.
>> indefinitely) or does it just crawl? (check the logical
>> and physical I/O counts in monSysStatement). Which query
>> do you need to kill in order to let things proceed?
>> Please provide more info on this before you try enlarging
>> the caches/pools -- this will be useful (and interesting)
>> to know (for me at least).
>>
>> HTH,
>>
>> Rob V.
>>
>>
> Hi Rob
>
> The job appears to be in the form of approx 8 sybase process
> threads performing various selects, temp table index creates
> etc. Very io heavy. Appears the hang is specific to any
> query which has an "order by", thus many of the sp_ procs
> not working.
> Below are some snapshots from mon tables for waits.
>
> Thanks
>
> This is a snapshot from monProcess :
> SPID KPID BatchID ContextID LineNumber
> SecondsConnected DBID EngineNumber Priority
> FamilyID Login Application
> Command NumChildren
> SecondsWaiting WaitEventID BlockingSPID BlockingXLOID DBName
> EngineGroupName
> ExecutionClass MasterTransactionID
> ------ ----------- ----------- ----------- -----------
> ---------------- ----------- ------------ -----------
> -------- ------------------------------
> ------------------------------
> ------------------------------ ----------- --------------
> ----------- ------------ -------------
> ------------------------------
> ------------------------------
> ------------------------------
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 23 934609832 7401 0 1
> 2001 4 5 5 NULL IN
> Mx_1_1 SELECT
> NULL 424 251
> NULL NULL prod
> ANYENGINE EC2
> NULL
> 37 929563615 60175 0 1
> 2248 4 7 5 NULL IN
> Mx_1_2 SELECT
> NULL 307 39
> NULL NULL prod
> ANYENGINE EC2
> NULL
> 41 934872551 30674 0 1
> 2001 4 0 5 NULL IN
> Mx_1_2 SELECT
> NULL 307 39
> NULL NULL prod
> ANYENGINE EC2
> NULL
> 55 953222643 819 0 1
> 577 4 0 5 NULL IN
> Mx_1_2 SELECT
> NULL 306 39
> NULL NULL prod
> ANYENGINE EC2
> NULL
> 60 932512997 9239 0 1
> 2053 4 5 5 NULL IN
> Mx_1_1 SELECT
> NULL 268 39
> NULL NULL prod
> ANYENGINE EC2
> NULL
> 61 959119944 4304 0 1
> 476 4 6 5 NULL IN
> Mx_1_1 SELECT
> NULL 307 39
> NULL NULL prod
> ANYENGINE EC2
> NULL
> 69 776798424 13143 0 1
> 11703 4 1 5 NULL IN
> Mx_1_1 SELECT
> NULL 777 251
> NULL NULL prod
> ANYENGINE EC2
> NULL
> 71 960234658 4355 0 1
> 475 4 3 5 NULL IN
> Mx_1_1 SELECT
> NULL 307 39
> NULL NULL prod
> ANYENGINE EC2
> NULL
>
>
> MonProcessWaits:
>
>
> SPID KPID WaitEventID Waits WaitTime
> ------ ----------- ----------- ----------- -----------
> 374 908984829 250 326948 2171700
> 8 1703962 214 337466 1299700
> 1054 892535173 250 350498 2442700
> 1577 892667176 250 353177 2535200
> 99 892469902 250 386009 2450000
> 118 393222 200 388873 581700
> 867 786444 200 400412 317000
> 823 458759 200 410598 674700
> 926 851981 200 422427 230400
> 237 925041659 251 429145 1015300
> 822 524296 200 442759 558400
> 31 917518 200 471952 301900
> 7 1638425 51 511090 3153100
> 761 147457579 251 577994 3070800
> 8 1703962 51 1240256 4328500
> 10 1835036 214 1430328 21747600
> 800 929825149 29 1680132 1575200
> 17 4915275 29 1788752 12412100
> 9 1769499 214 1964629 22498500
> 8 1703962 31 2371596 3201300
>
>
>
> SysWaits
>
> Waits:
> WaitEventID WaitTime Waits
> 171 27 62389
> 169 281894 77445
> 61 794561 139400
> 150 19792 156919
> 36 1472 195538
> 37 9 284605
> 52 2016 449716
> 55 4077 1976889
> 31 3349 2444726
> 51 17025 3256900
> 200 8153 6890426
> 214 49278 7991640
> 35 13744 12505229
> 39 865171 21336923
> 124 168728 106011613
> 29 196330 142334089
> 251 4086901 215125924
> 250 22529279 243297140
> 179 4785306 526755186
> 215 291469 1283524028

I'm afraid the monProcessWaits info isn't going to be very helpful. The
SPIDs in monProcess are different from those in monProcessWaits, so
there is no way of relating these.
With wait events 250/251/179/215 ranking high, things do not look out of
the ordinary to me. Note the relatively high number of 250 events --
this means the server is simply waiting for input from the client.
Also, some of your sessions show event 200, which means that some kind
of dbcc command is happening (likely dbcc checkstorage); this is what I
said earlier about event 39 -- this could play a role in some dbcc
commands.

R.