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

asked 01 Mar '13, 12:36

DRauber's gravatar image

DRauber
21181018
accept rate: 0%


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%:

alt text

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:

alt text

permanent link

answered 01 Mar '13, 15:21

Breck%20Carter's gravatar image

Breck Carter
25.6k427586844
accept rate: 20%

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.

permanent link

answered 22 Mar, 10:38

Breck%20Carter's gravatar image

Breck Carter
25.6k427586844
accept rate: 20%

edited 22 Mar, 10:39

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×11

question asked: 01 Mar '13, 12:36

question was seen: 672 times

last updated: 22 Mar, 10:39