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.

Query consuming CPU

5 posts in General Discussion Last posting was on 2012-06-27 22:22:41.0Z
sybanva Posted on 2012-06-26 06:47:24.0Z
Sender: 3f84.4fe9503f.1804289383@sybase.com
From: sybanva
Newsgroups: sybase.public.ase.general
Subject: Query consuming CPU
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4fe95afc.41ba.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 25 Jun 2012 23:47:24 -0700
X-Trace: forums-1-dub 1340693244 172.20.134.41 (25 Jun 2012 23:47:24 -0700)
X-Original-Trace: 25 Jun 2012 23:47:24 -0700, 172.20.134.41
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31206
Article PK: 74098

Team,

In one of dataserver, we had performance issue, server was
responding very slow due to that user batches were slow.

I did analysis and found sp_monitor was showing 100% cpu
utilization,. Through MDA tables I found the queries which
were taking maximum cpu on server and finally killed that
spids after confirmation from app team. Finally cpu
utilization came down and server response increased. Issue
Resolved.

I saw the showplan for culprit spid , indexing was proper so
it was not more io bound.
Now I have one basis question, why queries were more bound
to CPU?
What operation make query to more cpu bound? Sorting, join ,
arithmetic calculation, data volume ( I guess, it requests
more io)..etc, Still not sure ?

Please guide me. Thanks.


"Mark A. Parsons" <iron_horse Posted on 2012-06-26 20:25:59.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Query consuming CPU
References: <4fe95afc.41ba.1681692777@sybase.com>
In-Reply-To: <4fe95afc.41ba.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 120603-1, 06/03/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4fea1ad7@forums-1-dub>
Date: 26 Jun 2012 13:25:59 -0700
X-Trace: forums-1-dub 1340742359 172.20.134.152 (26 Jun 2012 13:25:59 -0700)
X-Original-Trace: 26 Jun 2012 13:25:59 -0700, vip152.sybase.com
Lines: 36
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31209
Article PK: 74097

Proper index usage doesn't mean anything if the 'wrong' join order was in use.

For starters:

- while the query is running take a snapshot of monProcessObject where SPID = <spid_of_interest>; of interest will be
any tables/indexes with a large volume of logical reads

- analyze the query plan to insure a 'good' query plan in is use

- analyze the query plan to see if there are any merge and/or hash joins; in some cases a nested loop join may be better
(ie, merge/hash joins can lead to excessive logical IOs due to excessively large intermediate worktables)

On 06/26/2012 00:47, sybanva wrote:
> Team,
>
> In one of dataserver, we had performance issue, server was
> responding very slow due to that user batches were slow.
>
> I did analysis and found sp_monitor was showing 100% cpu
> utilization,. Through MDA tables I found the queries which
> were taking maximum cpu on server and finally killed that
> spids after confirmation from app team. Finally cpu
> utilization came down and server response increased. Issue
> Resolved.
>
> I saw the showplan for culprit spid , indexing was proper so
> it was not more io bound.
> Now I have one basis question, why queries were more bound
> to CPU?
> What operation make query to more cpu bound? Sorting, join ,
> arithmetic calculation, data volume ( I guess, it requests
> more io)..etc, Still not sure ?
>
> Please guide me. Thanks.


sybanva Posted on 2012-06-27 14:57:45.0Z
Sender: dfd.4feb1ebb.1804289383@sybase.com
From: sybanva
Newsgroups: sybase.public.ase.general
Subject: Re: Query consuming CPU
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4feb1f69.e13.1681692777@sybase.com>
References: <4fea1ad7@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 27 Jun 2012 07:57:45 -0700
X-Trace: forums-1-dub 1340809065 172.20.134.41 (27 Jun 2012 07:57:45 -0700)
X-Original-Trace: 27 Jun 2012 07:57:45 -0700, 172.20.134.41
Lines: 50
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31211
Article PK: 74099

Mark,

Thanks for your reply.

Do you mean if the spid is doing excessive logical IO , it
can be more CPU bound?

> Proper index usage doesn't mean anything if the 'wrong'
> join order was in use.
>
> For starters:
>
> - while the query is running take a snapshot of
> monProcessObject where SPID = <spid_of_interest>; of
> interest will be any tables/indexes with a large volume
> of logical reads
>
> - analyze the query plan to insure a 'good' query plan in
> is use
>
> - analyze the query plan to see if there are any merge
> and/or hash joins; in some cases a nested loop join may be
> better (ie, merge/hash joins can lead to excessive
> logical IOs due to excessively large intermediate
> worktables)
>
>
>
> On 06/26/2012 00:47, sybanva wrote:
> > Team,
> >
> > In one of dataserver, we had performance issue, server
> > was responding very slow due to that user batches were
> slow. >
> > I did analysis and found sp_monitor was showing 100% cpu
> > utilization,. Through MDA tables I found the queries
> > which were taking maximum cpu on server and finally
> > killed that spids after confirmation from app team.
> > Finally cpu utilization came down and server response
> > increased. Issue Resolved.
> >
> > I saw the showplan for culprit spid , indexing was
> > proper so it was not more io bound.
> > Now I have one basis question, why queries were more
> > bound to CPU?
> > What operation make query to more cpu bound? Sorting,
> > join , arithmetic calculation, data volume ( I guess, it
> > requests more io)..etc, Still not sure ?
> >
> > Please guide me. Thanks.


"Mark A. Parsons" <iron_horse Posted on 2012-06-27 22:01:50.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Query consuming CPU
References: <4fea1ad7@forums-1-dub> <4feb1f69.e13.1681692777@sybase.com>
In-Reply-To: <4feb1f69.e13.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 120603-1, 06/03/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4feb82ce$1@forums-1-dub>
Date: 27 Jun 2012 15:01:50 -0700
X-Trace: forums-1-dub 1340834510 172.20.134.152 (27 Jun 2012 15:01:50 -0700)
X-Original-Trace: 27 Jun 2012 15:01:50 -0700, vip152.sybase.com
Lines: 56
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31212
Article PK: 74101

Generally speaking ... high logical IOs means high(er) cpu usage however, in ASE 15.x high cpu usage can be due to other
issues besides logical IOs (eg, merge/hash join processing, compilation overhead, infinite while loop doing little/no
IOs; bug in dataserver) ... so first thing you need to do is figure out *what* the high cpu is related to.

On 06/27/2012 08:57, sybanva wrote:
> Mark,
>
> Thanks for your reply.
>
> Do you mean if the spid is doing excessive logical IO , it
> can be more CPU bound?
>
>> Proper index usage doesn't mean anything if the 'wrong'
>> join order was in use.
>>
>> For starters:
>>
>> - while the query is running take a snapshot of
>> monProcessObject where SPID =<spid_of_interest>; of
>> interest will be any tables/indexes with a large volume
>> of logical reads
>>
>> - analyze the query plan to insure a 'good' query plan in
>> is use
>>
>> - analyze the query plan to see if there are any merge
>> and/or hash joins; in some cases a nested loop join may be
>> better (ie, merge/hash joins can lead to excessive
>> logical IOs due to excessively large intermediate
>> worktables)
>>
>>
>>
>> On 06/26/2012 00:47, sybanva wrote:
>>> Team,
>>>
>>> In one of dataserver, we had performance issue, server
>>> was responding very slow due to that user batches were
>> slow.>
>>> I did analysis and found sp_monitor was showing 100% cpu
>>> utilization,. Through MDA tables I found the queries
>>> which were taking maximum cpu on server and finally
>>> killed that spids after confirmation from app team.
>>> Finally cpu utilization came down and server response
>>> increased. Issue Resolved.
>>>
>>> I saw the showplan for culprit spid , indexing was
>>> proper so it was not more io bound.
>>> Now I have one basis question, why queries were more
>>> bound to CPU?
>>> What operation make query to more cpu bound? Sorting,
>>> join , arithmetic calculation, data volume ( I guess, it
>>> requests more io)..etc, Still not sure ?
>>>
>>> Please guide me. Thanks.


"Mark A. Parsons" <iron_horse Posted on 2012-06-27 22:22:41.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Query consuming CPU
References: <4fea1ad7@forums-1-dub> <4feb1f69.e13.1681692777@sybase.com> <4feb82ce$1@forums-1-dub>
In-Reply-To: <4feb82ce$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 120603-1, 06/03/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4feb87b1$1@forums-1-dub>
Date: 27 Jun 2012 15:22:41 -0700
X-Trace: forums-1-dub 1340835761 172.20.134.152 (27 Jun 2012 15:22:41 -0700)
X-Original-Trace: 27 Jun 2012 15:22:41 -0700, vip152.sybase.com
Lines: 59
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31213
Article PK: 74102

And obviously (?) an infinite loop is not limited to just ASE 15.x ...

On 06/27/2012 16:01, Mark A. Parsons wrote:
> Generally speaking ... high logical IOs means high(er) cpu usage however, in ASE 15.x high cpu usage can be due to other
> issues besides logical IOs (eg, merge/hash join processing, compilation overhead, infinite while loop doing little/no
> IOs; bug in dataserver) ... so first thing you need to do is figure out *what* the high cpu is related to.
>
>
> On 06/27/2012 08:57, sybanva wrote:
>> Mark,
>>
>> Thanks for your reply.
>>
>> Do you mean if the spid is doing excessive logical IO , it
>> can be more CPU bound?
>>
>>> Proper index usage doesn't mean anything if the 'wrong'
>>> join order was in use.
>>>
>>> For starters:
>>>
>>> - while the query is running take a snapshot of
>>> monProcessObject where SPID =<spid_of_interest>; of
>>> interest will be any tables/indexes with a large volume
>>> of logical reads
>>>
>>> - analyze the query plan to insure a 'good' query plan in
>>> is use
>>>
>>> - analyze the query plan to see if there are any merge
>>> and/or hash joins; in some cases a nested loop join may be
>>> better (ie, merge/hash joins can lead to excessive
>>> logical IOs due to excessively large intermediate
>>> worktables)
>>>
>>>
>>>
>>> On 06/26/2012 00:47, sybanva wrote:
>>>> Team,
>>>>
>>>> In one of dataserver, we had performance issue, server
>>>> was responding very slow due to that user batches were
>>> slow.>
>>>> I did analysis and found sp_monitor was showing 100% cpu
>>>> utilization,. Through MDA tables I found the queries
>>>> which were taking maximum cpu on server and finally
>>>> killed that spids after confirmation from app team.
>>>> Finally cpu utilization came down and server response
>>>> increased. Issue Resolved.
>>>>
>>>> I saw the showplan for culprit spid , indexing was
>>>> proper so it was not more io bound.
>>>> Now I have one basis question, why queries were more
>>>> bound to CPU?
>>>> What operation make query to more cpu bound? Sorting,
>>>> join , arithmetic calculation, data volume ( I guess, it
>>>> requests more io)..etc, Still not sure ?
>>>>
>>>> Please guide me. Thanks.