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.

CPU Usage Never Goes Over 30%

7 posts in Windows NT Last posting was on 1997-10-14 16:20:29.0Z
Gene C Posted on 1997-09-17 12:23:30.0Z
From: "Gene C" <genec@cat.bbsr.edu>
Subject: CPU Usage Never Goes Over 30%
Message-ID: <01bcc36c$df101a30$3e01a8c0@genec>
X-Newsreader: Microsoft Internet News 4.70.1161
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Date: Wed, 17 Sep 1997 08:23:30 -0400
Lines: 12
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5525
Article PK: 1081604

I am running SQL Server 11/NT on a dual pentium server with 128 meg ram.
Then database is about 500 meg.

Whenever I issue a sql statement that takes a long time to run, over 1 min,
the CPU Usage graph never goes over about 30% for either of the processors.
Usally they remain at 5 - 10% while the query runs.

Does anyone have any suggestions as to where I should start my tuning
efforts so I can get the cpu usage up closer to 100%?

Thanks,

Gene


Michael Atallah Posted on 1997-09-17 19:28:41.0Z
From: "Michael Atallah" <mike_atallah@harlequin.ca>
Subject: Re: CPU Usage Never Goes Over 30%
References: <01bcc36c$df101a30$3e01a8c0@genec>
Organization: Harlequin Enterprises Ltd.
Message-ID: <01bcc3aa$169d5e80$8a0210ac@ma.harlequin>
X-Newsreader: Microsoft Internet News 4.70.1161
Newsgroups: sybase.public.sqlserver.nt
Date: Wed, 17 Sep 1997 15:28:41 -0400
Lines: 7
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5523
Article PK: 1081601

Most likely, if your CPU is low, and the transaction is running for
over 1 minute, then I would have to say your process is doing a
lot if I/O 's. Try tuning on SHOWPLAN for your process, and
check whether it is using the right index, if any at all.


Gene C Posted on 1997-09-18 14:28:01.0Z
From: "Gene C" <genec@cat.bbsr.edu>
Subject: Re: CPU Usage Never Goes Over 30%
References: <01bcc36c$df101a30$3e01a8c0@genec> <01bcc3aa$169d5e80$8a0210ac@ma.harlequin>
Message-ID: <01bcc447$6ce21810$3e01a8c0@genec>
X-Newsreader: Microsoft Internet News 4.70.1161
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Date: Thu, 18 Sep 1997 10:28:01 -0400
Lines: 20
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5520
Article PK: 1081598

I checked and found the following:

There is no pending I/O when the cpu usage is high.
We added additional ram (64 meg) and now one of the processors will peak at
100% usage.
When the one processor peaks, the 2nd cpu does not recieve any additional
load.

If you have any other ideas about where to look, they would be greatly
appreciated.

Thanks for you help,

Gene

Michael Atallah <mike_atallah@harlequin.ca> wrote in article
<01bcc3aa$169d5e80$8a0210ac@ma.harlequin>...
> Most likely, if your CPU is low, and the transaction is running for
> over 1 minute, then I would have to say your process is doing a
> lot if I/O 's. Try tuning on SHOWPLAN for your process, and
> check whether it is using the right index, if any at all.
>
>
>


Stefan Karlsson Posted on 1997-10-14 16:20:29.0Z
From: "Stefan Karlsson" <stefank@sybase.com>
Subject: Re: CPU Usage Never Goes Over 30%
References: <01bcc36c$df101a30$3e01a8c0@genec> <01bcc3aa$169d5e80$8a0210ac@ma.harlequin> <01bcc447$6ce21810$3e01a8c0@genec>
Organization: Sybase Sverige AB
Message-ID: <01bcd8c5$d8c1aea0$747c0cc1@ppcstefank>
X-Newsreader: Microsoft Internet News 4.70.1161
Newsgroups: sybase.public.sqlserver.nt
Date: Tue, 14 Oct 1997 12:20:29 -0400
Lines: 38
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5477
Article PK: 1081557

If it's perfmon.exe that show zero i/o remeber that perfmon.exe does not
track i/o's until you've run diskperf.exe -y and rebooted.

/Stefan
====================================================================
Stefan Karlsson SYBASE Sweden

SYBASE Sverige AB +46-8-58711000phone
Box 1248 (Postal) +46-8-58711054direct
Borgarfjordsgatan 16 (visits) +46-70-5256281mobile/voicemail
164 28 KISTA +46-8-7505420fax
SWEDEN stefank@sybase.com
"Trust me, I know what I'm doing..."
====================================================================

Gene C <genec@cat.bbsr.edu> wrote in article
<01bcc447$6ce21810$3e01a8c0@genec>...
> I checked and found the following:
>
> There is no pending I/O when the cpu usage is high.
> We added additional ram (64 meg) and now one of the processors will peak
at
> 100% usage.
> When the one processor peaks, the 2nd cpu does not recieve any additional
> load.
>
> If you have any other ideas about where to look, they would be greatly
> appreciated.
>
> Thanks for you help,
>
> Gene
>
> Michael Atallah <mike_atallah@harlequin.ca> wrote in article
> <01bcc3aa$169d5e80$8a0210ac@ma.harlequin>...
> > Most likely, if your CPU is low, and the transaction is running for
> > over 1 minute, then I would have to say your process is doing a
> > lot if I/O 's. Try tuning on SHOWPLAN for your process, and
> > check whether it is using the right index, if any at all.
> >
> >
> >
>


Doug Michael Posted on 1997-09-18 22:12:59.0Z
From: "Doug Michael" <dougmi@attachmate.com>
Subject: Re: CPU Usage Never Goes Over 30%
References: <01bcc36c$df101a30$3e01a8c0@genec> <01bcc3aa$169d5e80$8a0210ac@ma.harlequin> <01bcc447$6ce21810$3e01a8c0@genec>
Organization: Attachmate Corporation
Message-ID: <01bcc489$03073960$700a5295@dougmi.attachmate.com>
X-Newsreader: Microsoft Internet News 4.70.1162
Newsgroups: sybase.public.sqlserver.nt
Date: Thu, 18 Sep 1997 18:12:59 -0400
Lines: 22
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5519
Article PK: 1081597

To my knowledge Sybase SQL Server 11.0.x does not parallelize the work of a
single query... in other words, if you are running only one thing at a
time, all of the work will be handled by one processor or the other, but
never both. You might look at the CPU usage while executing 2 intensive
queries at the same time. From my experience Sybase is good about
spreading the CPU workload when multiple connections are requesting work
concurrently.

Doug Michael
dougmi@attachmate.com

Gene C <genec@cat.bbsr.edu> wrote in article
<01bcc447$6ce21810$3e01a8c0@genec>...
> I checked and found the following:
>
> There is no pending I/O when the cpu usage is high.
> We added additional ram (64 meg) and now one of the processors will peak
at
> 100% usage.
> When the one processor peaks, the 2nd cpu does not recieve any additional
> load.


David Lance Wolf Posted on 1997-09-19 04:06:38.0Z
Message-ID: <3421FA4E.9E2C2E07@sybase.com>
Date: Thu, 18 Sep 1997 21:06:38 -0700
From: David Lance Wolf <dwolf@sybase.com>
Organization: Sybase Professional Services
X-Mailer: Mozilla 4.02 [en] (Win95; I)
MIME-Version: 1.0
Subject: Re: CPU Usage Never Goes Over 30%
References: <01bcc36c$df101a30$3e01a8c0@genec> <01bcc3aa$169d5e80$8a0210ac@ma.harlequin> <01bcc447$6ce21810$3e01a8c0@genec> <01bcc489$03073960$700a5295@dougmi.attachmate.com>
Content-Type: multipart/mixed; boundary="------------1462412513B55513C01B2B0C"
Newsgroups: sybase.public.sqlserver.nt
Lines: 63
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5518
Article PK: 1081595

SQL Server will context switch a process out if it blocks, say on I/O.
Once a job is context switched, it is placed in a sleep queue. Once the
block is resolved, it migrates to a run queue. When the task makes it
to the top of the run queue it can be picked up by ANY available engine
(as long as the process does not need to do Network I/O). If the task
must do Network I/O it must be picked up by the engine who has its I/O
currently affined.

Don't confuse parallelizing a process with Symmetic Multi-Processing.
System XI does not parallelize at all. Adaptive Server will have this
option.

So yes, we only force a process to its 'owner' engine if it is waiting
on network I/O.

Gene, can you post a sysmon output?

Dave Wolf
Sybase Professional Services

Doug Michael wrote:
>
> To my knowledge Sybase SQL Server 11.0.x does not parallelize the work of a
> single query... in other words, if you are running only one thing at a
> time, all of the work will be handled by one processor or the other, but
> never both. You might look at the CPU usage while executing 2 intensive
> queries at the same time. From my experience Sybase is good about
> spreading the CPU workload when multiple connections are requesting work
> concurrently.
>
> Doug Michael
> dougmi@attachmate.com
>
> Gene C <genec@cat.bbsr.edu> wrote in article
> <01bcc447$6ce21810$3e01a8c0@genec>...
> > I checked and found the following:
> >
> > There is no pending I/O when the cpu usage is high.
> > We added additional ram (64 meg) and now one of the processors will peak
> at
> > 100% usage.
> > When the one processor peaks, the 2nd cpu does not recieve any additional
> > load.


Download VCard vcard.vcf


David Lance Wolf Posted on 1997-09-17 23:48:23.0Z
Message-ID: <34206C47.4D5C609F@sybase.com>
Date: Wed, 17 Sep 1997 16:48:23 -0700
From: David Lance Wolf <dwolf@sybase.com>
Organization: Sybase Professional Services
X-Mailer: Mozilla 4.02 [en] (Win95; I)
MIME-Version: 1.0
Subject: Re: CPU Usage Never Goes Over 30%
References: <01bcc36c$df101a30$3e01a8c0@genec> <01bcc3aa$169d5e80$8a0210ac@ma.harlequin>
Content-Type: multipart/mixed; boundary="------------6616F446FD81C4979C4EC055"
Newsgroups: sybase.public.sqlserver.nt
Lines: 35
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5522
Article PK: 1081600

As well, run the sp_sysmon <time in minutes> procedure. If you sitting
around with idle cycles waiting on pending I/O, you'll see that the
engine is context switching the process into the sleep queue waiting on
pending I/O.

Dave Wolf
Sybase Professional Services

Michael Atallah wrote:
>
> Most likely, if your CPU is low, and the transaction is running for
> over 1 minute, then I would have to say your process is doing a
> lot if I/O 's. Try tuning on SHOWPLAN for your process, and
> check whether it is using the right index, if any at all.


Download VCard vcard.vcf