ASA9 randomly very slow

[Former Member]

Hello!

I have a server ASA9 in production and a few days ago, some users have complained of "locking", but did some testing and the database server is not locked, however, any procedure that uses connection or performing some task or the DB is very slow after a few minutes, everything is back to normal, even without any action, and back again to slow down randomly. How can I do to identify what causes this slowness?

Tanks

[Breck_Carter]

The following is a commercial message...

Foxhound works with ASA9 databases to monitor performance; here is an ASA9 database running on an 8-CPU box where three runaway connections have each pushed one of the CPUs to 100%, for an overall CPU usage of 37.5%:

If you start your server with the dbsrv9 -zl option, Foxhound's connection-level display will include the last SQL statement that was executed on that connection so you can zoom in on the CPU peaks to see who's doing what to your server:

[Breck_Carter]

SQL Anywhere 9.0.2 is one of the best, most solid releases ever, so it's not surprising you are still running it.

Here are some suggestions...

(1) Upgrade to SQL Anywhere 16, which is another of the most solid releases in the history of the product.

(2) Run the Foxhound database performance monitor. Foxhound 4 it requires a copy of SQL Anywhere 16 or 17 to run, but it works fine with Version 9 target databases.

(3) Try the "log expensive queries" feature that was secretly introduced in 9.0.2.3124.

(4) Buy my book; it's got a chapter on tuning: Index Consultant, Execution Profiler, Graphical Plan, etc.