I have decided to use the SQL Profiler today, and my goal was to identify requests that take longer than 1 sec, for the execution or the blocking. This is the conditions that I have set up for the SA Profiler:
The profiler has recorded a multiple similar statements with the following properties:
What worries me is the high "Idle" value, plus these requests are called from the multiple connections that are still (I think) alive:
I have found the old thread that says that the idle connections consume memory, and a little CPU when they send "ping" packets: http://sqlanywhere-forum.sap.com/questions/6524/whats-the-performance-downside-to-having-a-lot-of-idle-connections
But I think that this is bad, because it looks like the statement wasn't finished yet (you see, the execution time was still "Unknown") and/or the connection was not returned to the server's connection pool.
A short update. The profiler has also shown me the following warning:
Can you please confirm that I am right in my assumption? Because it is only you who can stop me from the creation of the support ticket to people, who developed the server :)
p.s. I am using SA 22.214.171.1243 (Windows 2012R2 Build 9600, 4 cores).
Just in case:
update #2: I have decided to start demo.db and check it with the SQL Profiler (Profiling options: comprehensive). It showed me that dbisql.exe (I use 64-bit version), gets the number of locks, but probably doesn't close the connection:
Here you see my simple select:
And then immediately you see the "idle" connection. The source code of com.sybase.saisqlplugin.SAISQLPlugin.getLockedTableCount (I apologize for doing this, but I wanted to know how the "universe" works!) creates the SQL statement object, and then closes it later, but ResultSet is not closed. Am I correct that this is the way where we have the "connection leak" or whatever? Can someone take a look at my observation, are either the profiler, or dbisql, or me wrong? To reproduce the issue, you should simply start the profiler (comprehensive mode), dbisql and execute any SELECT.
Thank you in advance,
When a connection is idle it is, um, idle. Not doing anything. It does not consume any CPU but depending on what has happened on the connection prior to being idle it could be consuming some resources within the server including memory and locks (on rows, tables, etc).
(I believe) that the query execution time will be "unknown" if the query has not completed. There are two cases (that I can think of):
In the first case the server will show that the connection is active. In the second case the connection may be marked as "idle" but the query will not yet be "completed". Until the cursor is closed the server cannot know how the total execution time and therefore will show "unknown".
Note that another reason for a query to not yet be complete (but "idle") is that it is blocked. For example, some other connection has a lock on a row that it is trying to read/update.
Regarding the warning about connections with non-default values, this is not necessarilly a problem. It is perfectly reasonable to use connection options which are not the default. The point of the warning is for you, the DBA, to look at the options and verify that the connection is using the connection options that you are expecting.
Update... Disclaimer: This answer deals only with actual SQL Anywhere server and dbisql.exe behavior, not the behavior of the SQL Anywhere Profiler which is a brand-new feature of SQL Anywhere 17.
It is entirely possible this answer completely misunderstands the question, but here goes...
... probably doesn't close the connection ... we have the "connection leak" or whatever?
It is highly highly highly highly highly ( five "highly"s, the maximum number :) unlikely that SQL Anywhere or Interactive SQL has anything remotely like a "connection leak".
By default, the Interactive SQL program dbisql.exe connects to the target database when it is started (assuming the -c "whatever" option is correct), and then holds that single connection open until the executable is stopped. This is intentional... it is a GUI tool, after all.
If you want to close that connection but keep dbisql.exe running, try using the special ISQL-only command DISCONNECT... the connection will then disappear from the server, and, presumably, from the Profiler display. You can then use the ISQL-only CONNECT statement to open a (re)connection dialog.