dbsrv9.exe CPU usage 100% (SQL Anywhere 0902 3302)

[Sybase SQLAnywhere]

Hi all,
we have a customer running SQL Anywhere 9.0.2.3302 under a Terminal Server
environment.
The engine is running on a FileServer and all clients connect into a
seperate TerminalServer.
Recently they have noticed that DBSRV9.exe will jump to 100% cpu usage a
couple of times a day (2-3). This then resolves itself after approx 10
minutes.
For the last few days however it has happened as many as 20-30 times and can
take up to 30 minutes to resolve itself.

They are running Windows2003 with 2Gb of FB-Ram.

Has anyone seen this and more importantly know how to resolve it.

Please let me knwo what other information is needed.

Regards

David MacDonald

[Frank Ploessel]

David,

It may be completely normal that the engine uses a lot of CPU and disk
ressources if it needs to process a lot of complex stuff: E. g. summing up
50 million numbers, maybe based on complex conditions, just takes a lot of
ressources. So without some more details about your application and
environment it is difficult to answer.

If you say that the engine is running on a file server, I assume it is
running as a service on a computer that is also used as a file server?
The terminal server that you mention is running on another computer?
What type of application are the users using, and how much data is
involved in the typical query? Just some few records, or a lot of data?
Can you isolate the high CPU consumption to some specific SQL statements?

What is the size of the database(s) used?
What is the start line of the database service?

Frank

Am 17.09.2008, 08:46 Uhr, schrieb Sybase SQLAnywhere

Post by Sybase SQLAnywhere
Hi all,
we have a customer running SQL Anywhere 9.0.2.3302 under a Terminal Server
environment.
The engine is running on a FileServer and all clients connect into a
seperate TerminalServer.
Recently they have noticed that DBSRV9.exe will jump to 100% cpu usage a
couple of times a day (2-3). This then resolves itself after approx 10
minutes.
For the last few days however it has happened as many as 20-30 times and can
take up to 30 minutes to resolve itself.
They are running Windows2003 with 2Gb of FB-Ram.
Has anyone seen this and more importantly know how to resolve it.
Please let me knwo what other information is needed.
Regards
David MacDonald

...

[Jeff Albion (Sybase iAnywhere)]

David,

You'll need to identify "why" the CPU is spinning so high. As Frank has
mentioned, this may be expected behaviour. Since it "clears itself"
after 10-30 minutes, I'd argue that one (or more) of your users must be
hammering the server, causes a lot of activity, then goes away. If it
was a bug, I'd expect the CPU to "stay" at 100%, no matter how long you
waited.

I'd recommend identifying the queries/events that are taking so long. In
v9, you can do this by using the "LogExpensiveQueries" flag on the
server start-line. (See:
http://search.sybase.com/kbx/changerequests?bug_id=386756 )

Essentially, you define a request-level log (RLL) and a threshold of
execution time and any queries that took longer than the execution time
will be logged into the RLL. The threshold value is specified in
milliseconds.

Example:

dbsrv9 -zx 60000 -zo rll.txt ...

(e.g. log anything to 'rll.txt' that takes longer than a minute to execute).

Depending on your server load, you may need to use different values of
the cost threshold in order to narrow down which queries to examine.

Hopefully between the RLL and a performance monitor output, you'll be
able to identify roughly which queries/events you'll need to dig into.

Cheers,

Post by Sybase SQLAnywhere
Hi all,
we have a customer running SQL Anywhere 9.0.2.3302 under a Terminal Server
environment.
The engine is running on a FileServer and all clients connect into a
seperate TerminalServer.
Recently they have noticed that DBSRV9.exe will jump to 100% cpu usage a
couple of times a day (2-3). This then resolves itself after approx 10
minutes.
For the last few days however it has happened as many as 20-30 times and can
take up to 30 minutes to resolve itself.
They are running Windows2003 with 2Gb of FB-Ram.
Has anyone seen this and more importantly know how to resolve it.
Please let me knwo what other information is needed.
Regards
David MacDonald

...

[Sybase SQLAnywhere]

Hi guys, thanks for your quick feedback.

DB is currently 950Mb.

Start Line = -n D4w -x tcpip -tq 23:45 -c 15%% -os 524288 -o
C:\D4w\ASALog-Svc_D4w.txt c:\D4w\d4wtrain.db C:\D4w\Data\D4w.db -zr all -zo
C:\D4w\ASALog_TS_f.txt



The -zr has been turned on to diagnose another issue where our programs
executable (d4w97.exe) on the Terminal Server was not releasing memory after
certain aspects of the software were closed. Due to this the memory used
would approach 100Mb and when it did that persons application would slow
down to a crawl and eventually hang.



Is it possible to have more than 1 -zo in the start line? (ie: -zr all -zo
n:\nnnn -zx 60000 -zo b:\bbbb)



The FileServer and TerminalServer are separate physical servers.

Our program (Dental4Windows) is running as a Service on the FS.



Thanks guys :)

Post by Frank Ploessel
David,
You'll need to identify "why" the CPU is spinning so high. As Frank has
mentioned, this may be expected behaviour. Since it "clears itself" after
10-30 minutes, I'd argue that one (or more) of your users must be
hammering the server, causes a lot of activity, then goes away. If it was
a bug, I'd expect the CPU to "stay" at 100%, no matter how long you
waited.
I'd recommend identifying the queries/events that are taking so long. In
v9, you can do this by using the "LogExpensiveQueries" flag on the server
http://search.sybase.com/kbx/changerequests?bug_id=386756 )
Essentially, you define a request-level log (RLL) and a threshold of
execution time and any queries that took longer than the execution time
will be logged into the RLL. The threshold value is specified in
milliseconds.
dbsrv9 -zx 60000 -zo rll.txt ...
(e.g. log anything to 'rll.txt' that takes longer than a minute to execute).
Depending on your server load, you may need to use different values of the
cost threshold in order to narrow down which queries to examine.
Hopefully between the RLL and a performance monitor output, you'll be able
to identify roughly which queries/events you'll need to dig into.
Cheers,

Post by Sybase SQLAnywhere
Hi all,
we have a customer running SQL Anywhere 9.0.2.3302 under a Terminal
Server environment.
The engine is running on a FileServer and all clients connect into a
seperate TerminalServer.
Recently they have noticed that DBSRV9.exe will jump to 100% cpu usage a
couple of times a day (2-3). This then resolves itself after approx 10
minutes.
For the last few days however it has happened as many as 20-30 times and
can take up to 30 minutes to resolve itself.
They are running Windows2003 with 2Gb of FB-Ram.
Has anyone seen this and more importantly know how to resolve it.
Please let me knwo what other information is needed.
Regards
David MacDonald

--
Jeff Albion, Engineering
Sybase iAnywhere
http://www.sybase.com/developer/library/sql-anywhere-techcorner
http://www.ianywhere.com/developer/product_manuals
http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0

...