100% CPU usage

[Former Member]

What my BOFHs observe daily:

• All 16 CPU cores maxed out at 100%
• It is possible to connect to the server instance using Sybase Central, but it takes forever and they have given up further diagnostics (easier to just turn the process 'off and on again')
• Deadlock logging enabled, does not show anything according to one of my colleagues
• SQL Anywhere 10 could "phreak out" as well I'm told, but would not max out all CPU cores (at most just one)

Given the extremeness of the situation (100% CPU usage), I am hoping that someone may fill me in on what we could be doing that could possibly cause this situation. We have not been able to reproduce this yet nor have we discovered any infinite loops in our code. Any pointers are welcome.

I am basically a n00b when it comes to SQL Anywhere, so I have to read up on application profiling I guess.

[reimer_pods]

Please tell us the SQL Anywhere version number, e.g. 12.0.1.3436

[Former Member]

Ooops, I forgot to mention that.

But you guessed right, we are still on build 3436. I have not checked out the latest EBF yet.

[Breck_Carter]

Two suggestions: First, http://sqlanywhere.blogspot.com/2011/08/new-maxbps768-set-maxquerytasks-1.html

Second, run the Foxhound monitor against your server. Version 1.1 is available here http://risingroad.com/

...and version 1.2 is discussed here http://sqlanywhere.blogspot.com/2011/10/whats-cool-whats-new-in-foxhound-12.html

[MCMartin]

You could use ProcDump from technet to create a dump of the running process during the high CPU usage and provide it to Sybase support for analysis. It reminds me of one of my own questions:

a sequential scan blocks all new connections

[jeff_albion]

I have not checked out the latest EBF yet.

It is definitely recommended to try this to see if this is a known (fixed) issue.

Given the extremeness of the situation (100% CPU usage), I am hoping that someone may fill me in on what we could be doing that could possibly cause this situation.

Does the server ever get out of the 100% CPU spin, or does it stay there forever / a long period of time? Having a "temporary" 100% CPU "spike" may not be unexpected (e.g. you have an intense CPU-bound, parallelized query).

Have you tried using the Diagnostic Tracing utility to determine which queries are being run during these 100% CPU times?

[Former Member]

This trick isn't likely to solve your problem, but could give you a place to start if (like me) you're too busy (or lazy) to really track it down properly. Sometimes you can get a sense of who's doing what if you connect using ISQL and run call sa_conn_info(). It's a relatively quick peek into what's going on.

The last time we had a very slow server it was duelling backups. And the time before that it was three different reporting queries calling the same monster sub-query at the same time. We were able to track both of these down pretty quickly starting with sa_conn_info() because we could track down what the different connections to our server were actually doing by hunting down the people responsible for each of those connections, rather than trying to get the database to tell us everything.

Good luck!

[Former Member · Accepted solution]

I had to defer the troubleshooting to my colleague, as I was otherwise engaged.

Eventually he found a query that would run all CPUs flat out and never finish. He waited for 15 minutes before killing it.

Then he looked at the query plan. In his words "it was messy".

The solution was to add an index to one of the temporary tables used. That whacked the query execution plan back into shape and we are back in business. The query now takes 20 seconds to run.

Not sure this deserves to be marked as 'an answer', but my colleague's workaround will hopefully see us through.

[VolkerBarth · Accepted solution]

IMHO, what "deserves to be marked as an (accepted) answer" should be answered by the one with the question - in short by you:)

[Former Member · Accepted solution]

IMHO that's not a workaround - that's fixing the problem. This is exactly what indexes are for!

[Former Member · Accepted solution]

Yeah, indexes are nice and all, but the code in question ran just fine with SQL Anywhere 10. We think we tripped over a bug introduced with later versions of SQL Anywhere. Even if a lack of an index (for a temporary table mind you) triggers a table scan, one would assume the query would finish eventually. To go flat out on all 24 CPU cores for a long time (on one occasion they waited for more than an hour before killing it) does not seem right to me.

[VolkerBarth · Accepted solution]

So then you might consider a bug report?

[Former Member · Accepted solution]

We will look into a reliable way of reproducing this eventually, yes.