Question Re: SQL Profiler: 'active' vs 'elapsed' vs 'exec' times in Technology Q&A

SQL Profiler: 'active' vs 'elapsed' vs 'exec' times

Former Member — Wed, 10 May 2017 05:39:11 GMT

SQL Anywhere 17.0.6.2757

I've just started looking at the SQL Profile and I'm a bit confused by the various timing counters it uses.

I've been doing 'targeted' sessions and in the initial dialog I have set 'Statement is active for at least 1 second' (and no other criteria). After leaving it running for a while some rows appear on the 'statements' tab as in the screenshot below [edit: couldn't get embedded image to work so changed to hyperlink]:

screenshot

Initially I had assumed that the avg + max 'exec' columns corresponded to the 'Statement is active for at least 1 second' filter I am using so I didn't understand why the top statement was being shown with an avg exec of 114ms. I then double-clicked on the top statement in the lower pane and could see various other properties include 'active_time' and 'elapsed_time', which are described in the help file as follows:

active_time : Time in seconds that the database server spent executing the statement.

elapsed_time : Wall clock time in seconds between the first fetch and most recent fetch of the cursor.

So it looks like the 'exec' times from the list actually correspond to 'elapsed_time' rather that 'active_time', even though the latter seems more important from a performance point of view and also appears to be used by the 'Statement is active for at least N second' filter. Is this correct?

I can't see anyway of changing the available columns in the list (e.g. to include avg active_time). Is there any way of extracting this information using a SQL query?

Re: SQL Profiler: 'active' vs 'elapsed' vs 'exec' times

Breck_Carter — Wed, 10 May 2017 10:37:16 GMT

You have asked several questions. I will only attempt to give a partial answer to one question...

"Is there any way of extracting this information using a SQL query?"

The profiler may be making some measurements on its own, as well as performing value-added calculations on SQL Anywhere connection-level properties listed here.

There may not be any way to query the former (measurements proprietary to the profiler), but you may be able to query the latter (SELECT * FROM sa_connection_properties()).

The Very Bad News may be that the Profiler displays a lot of statement-level measurements that fall into the former category (measurements proprietary to the profiler).

Some good news may lie in sa_procedure_profile() and sa_procedure_profile_summary()... but don't get too excited.

FWIW profilers are heavyweight processes, with heavy loads placed on both computer and human processors ( your brain, trying to find stuff and figure it out :).

You may find that a lightweight monitor is a good place to start, to find the hotspots before bringing out the big guns (profiler). SQL Anywhere Monitor is one, Foxhound is another; for a full list see page 3 of the white paper here.

Re: SQL Profiler: 'active' vs 'elapsed' vs 'exec' times

Former Member — Wed, 10 May 2017 11:05:05 GMT

Thanks Breck.

I've already got some useful queries based on various connection-level properties but I'm interested in capturing the statement execution times.

I've had some success in past using the 'diagnostic tracing' facilities and querying the sa_tmp_diagnostic_* tables but the version 17 documentation says that diagnostic tracing is now deprecated in favour of the SQL Profiler, which is why I'm looking at it now.

I've looked at both the SQL Any Monitor and the Cockpit but neither have the features I'm after. I'd be interested in having a look at Foxhound - is there a trial/evaluation version?

Re: SQL Profiler: 'active' vs 'elapsed' vs 'exec' times

Breck_Carter — Wed, 10 May 2017 15:02:26 GMT

Luke: Send me an email at breck dot carter at gmail dot com to discuss.

Please understand Foxhound is not a tracing or profiling tool, it is a sampling monitor, so it will only give periodic snapshots of activity, and it may not have the features you are after... but it may satisfy your needs in other ways 🙂