in advantage database is a query "active" until it is "complete"?

Question

I'm trying to track down a performance issue by looking at the "Active Queries" tab in the Advantage Management Utility.

The documentation for this tab says:

Active: True if the query is being actively processed by the server. A query must be active to be cancelled.

Is a query Active until it completes? Or can it become inactive for another reason, like waiting on a resource (disk IO or a lock)?

I ask because I only 1-2 queries in an "active" state at a given time, but I also have 20+ worker threads running. Which makes little sense to me.

Answer 1

Active means the server is actively looking for rows to populate the cursor with for the request. It will remain active until enough rows have been made to satisfy the request. If the query needs to wait on a lock or disk I\O it will remain active. One caveat to this is live cursors. Live cursors are treated as tables by the client rather than SQL statements. Are the SQL statements that are open, but not active live cursors?

You might try calling the stored procedure sp_mgGetWorkerThreadActivity to see what commands the other threads are doing.