602SQL Documentation Index  

602SQL Server Runtime Profiling

Profiling allows you to inspect actions executed on the server. The server may gather and provide information on:

Profiling Process

Profiling continuously gathers server runtime information. Profiling is governed by the built-in 602SQL Server procedures or the user interface that can be open from the 602SQL Development client using the Profiler item in the System / Tools folder.

You may profile all threads on the server or specific threads only. The set of threads being profiled can be modified at runtime. Profiling threads concurrently is enabled or disabled by the _sqp_profile_all procedure or by checking the Profile all threads checkbox. Profiling of individual threads is enabled and disabled by the _sqp_profile_thread procedure or by checking the Profile checkbox in the thread list. If you want to check profiling on all threads call the Get_server_info function with the OP_GI_PROFILING_ALL parameter. If you want to check profiling of some threads is running call the _iv_logged users system query.

The _sqp_profile_reset routine or the Clear profile button will erase the current contents of the profile.

You need to have configuration administrator privileges to conduct profiling. There is one exception to this rule. If profiling was once enabled by the administrator after server start, each thread may enable or disable profiling itself.

Profiling Source Lines

If routine source profiling is enabled, then the profile contains one special record for each routine (or trigger) source line that executed an SQL statement. You may then view the time that was spent processing this line and the number of times this line was processed. The rows are numbered from 1.

Routine source profiling is enabled or disabled by the _sqp_profile_lines routine or by checking the Profile source line in routines checkbox. You may find out whether routine source profiling is enabled with the Get_server_info function called with the OP_GI_PROFILING_LINES parameter.

Getting Profiling Results

Results of profiling may be retrieved by the system query _iv_profile. This query may be used in other queries that sort and select data from the profile. The result of this query may be viewed in the grid by the Show profile button. You may interactively sort and filter the result contents in the grid.

If profiling is currently running, the result contents vary continuously. If the result is displayed in the grid, you can get the current contents by clicking the Refresh data from the Data menu.

Accessing Routine Source Profiling Results in the Editor

The routine source profiling results may be displayed directly in the editor opened on the routine being tested. If routine source profiling is enabled and the routine was executed before (at least once), you may use the Design/Display profile action to display the following information in the editor:

You may display the profile for all routines that were already executed by pressing the Show profile of lines button in Profiler window.

If the numbers of lines contained in the profile should correspond with the actual lines in the routines, you may not edit the routine being profiled. That's why every editing change clears the window and every compilation deletes the current profile.

Interpreting Profile

Each profile row provides information about a process or access to some source. For details see the _iv_profile system query description.

The first column describes the category of the inspected activity, according to the list above.

The other columns specify the object (with number and name), the SQL statement (with text and a line number), a thread (with a unique number or a name if assign by _sqp_set_thread_name function)

Other columns provide information about the activity of the object defined by the previous columns. Hit count states the number of times a routine or SQL statement was executed, the wait count for a lock, or the number of times a critical section was entered.

The following three columns specify the gross and net time spent in seconds. These two entries differ for routines and SQL statements that call other routines. Net time is gross time minus the time spent in other routines.

Calculation of the Spent Time

The total time spent in execution of an action described in the profile is designated as a "gross" time.

The "net" time is introduced on the top of that for routines, triggers and SQL commands. That, in contrast to gross time, does not include the time spent by execution of other routines. Thus the net time is less than gross time, provided that during routine or trigger execution, or an SQL command some other routine or trigger called.

Apart from those, for routines, triggers, SQL commands and threads, an "active" time is introduced. That, in contrast to the gross time, does not include the time spent in waiting for locks, by means of which other threads temporarily block execution of some actions.