Skip to main content
Previous sectionNext section

SQL Performance Analysis Toolkit

This chapter explains analysis tools that can be used to actively profile specific SQL statements. These tools gather detailed information about the execution of these SQL statements. Using this information, developers can take steps to improve the performance of inefficient SQL statements.

This active profiling, depending on the requested level of detail, can significantly increase the load on the server. Therefore, the SQL Performance Analysis Toolkit is meant for a concerted code analysis effort. It is not intended for continuous monitoring of executing code.

The Analysis Tools Interface

The SQL Performance Analysis Toolkit offers developers and support specialists the ability to profile specific SQL statements or groups of statements. By using these tools during the execution of specific SQL statements, they can gather detailed information that can be used to analyze problematic statements in isolation or across a live workload.

The level of detail to be recorded is configurable, with the most fine-grained setting collecting information at the module level, providing information for the different "steps" in a statement's query plan.

Using Performance Analysis ToolKit Methods

You can use %SYSTEM.SQL.PTools class methods to:

This section also contains program examples using these methods.

Note:

%SYSTEM.SQL.PTools class methods are the preferred APIs for invoking this functionality. The %SYSTEM.SQL.PTools methods interface regroups and reorganizes functionality implemented in methods of the %SYS.PTools base classes.

Activate the Gathering of Statistics

You activate statistics (Stats) code generation to collect performance statistics using the %SYSTEM.SQL.PTools class methods. The following methods are provided to gather performance statistics for:

  • The entire system: setSQLStatsFlag()

  • A specific namespace: setSQLStatsFlagByNS()

  • The current process or job: setSQLStatsFlagJob()

  • A specified process or job: setSQLStatsFlagByPID(). If the first parameter is unspecified, or specified as $JOB or as an empty string (""), setSQLStatsFlagJob() is invoked. Thus SET SQLStatsFlag=$SYSTEM.SQL.SetSQLStatsFlagByPID($JOB,3) is equivalent to SET SQLStatsFlag=$SYSTEM.SQL.SetSQLStatsFlagJob(3).

These methods take an integer action option. They return a colon-separated string, the first element of which is the prior statistics action option. You can determine the current settings using the getSQLStatsFlag() or getSQLStatsFlagByPID() method.

You can invoke these method from ObjectScript or from SQL as shown in the following examples:

  • from ObjectScript: SET rtn=##class(%SYSTEM.SQL.PTools).setSQLStatsFlag(2,,8)

  • from SQL: SELECT %SYSTEM_SQL.PTools_setSQLStatsFlag(2,,8)

Action Option

For setSQLStatsFlag() and setSQLStatsFlagByNS() you specify one of the following Action options: 0 turn off statistics code generation; 1 turn on statistics code generation for all queries, but do not gather statistics (the default); 2 record statistics for just the outer loop of the query (gather statistics at the open and close of the query); 3 record statistics for all module levels of the query. Modules can be nested. If so, the MAIN module statistics are inclusive numbers, the overall results for the full query.

For setSQLStatsFlagJob() and setSQLStatsFlagByPID() the Action options differ slightly. They are: -1 turn off statistics for this job; 0 use the system setting value. The 1, 2, and 3 options are the same as setSQLStatsFlag() and override the system setting. The default is 0.

To gather SQL Stats data, queries need to be compiled (Prepared) with statistics code generation turned on (option 1, the default):

  • To go from 0 to 1: after changing the SQL Stats option, Routines and Classes that contain SQL will need to be compiled to perform statistics code generation. For xDBC and Dynamic SQL, you must purge cached queries to force code regeneration.

  • To go from 1 to 2: you simply change the SQL Stats option to begin gathering statistics. This allows you to enable SQL performance analysis on a running production environment with minimal disruption.

  • To go from 1 to 3 (or 2 to 3): after changing the SQL Stats option, Routines and Classes that contain SQL will need to be compiled to record statistics for all module levels. For xDBC and Dynamic SQL, you must purge cached queries to force code regeneration. Option 3 is commonly only used on an identified poorly-performing query in a non-production environment.

  • To go from 1, 2, or 3 to 0: to turn off statistics code generation you do not need to purge cached queries.

Collect Option

If the Action option is 2 or 3, when you invoke one of these methods you can specify a Collect option value to specify which performance statistics to collect. The default is to collect all statistics.

You specify a Collect option by adding together the integer values associated with each type of statistic that you wish to collect. The default is 15 (1 + 2 + 4 + 8).

These methods return the prior value of this Collect option as the second colon-separated element. You can determine the current setting using the getSQLStatsFlag() or getSQLStatsFlagByPID() method. By default all statistics are collected, returning 15 as the second element value.

Refer to %SYSTEM.SQL.PTools for further details.

Terminate Option

Statistics collection continues until terminated. By default, collection continues indefinitely until it is terminated by issuing another setSQLStatsFlag[nnn]() method. Or, if the Action option is 1, 2, or 3, you can specify a setSQLStatsFlag[nnn]() terminate option, either an elapsed period (in minutes) or a specified timestamp. You then specify the Action option re-set when that period elapses. For example, the string "M:120:1" sets M (elapsed minutes) to 120 minutes, at the end of which the Action option re-sets to 1. All other options reset to the default values appropriate for that Action option.

These methods return the prior value of this Terminate option value as the fifth colon-separated element as an encoded value. See Get Statistics Settings.

Get Statistics Settings

The setSQLStatsFlag[nnn]() methods return the prior statistics settings as a colon-separated value. You can determine the current settings using the getSQLStatsFlag() or getSQLStatsFlagByPID() method.

The 1st colon-separated value is the Action option setting. The 2nd colon-separated value is the Collect option. The 3rd and 4th colon-separated values are used for namespace-specific statistics gathering. The 5th colon-separated value encodes the Terminate option. The 6th colon-separated value specifies the FlagType: 0=System flag, 1=Process/Job flag.

You can use the ptInfo array to display the Terminate option settings in greater detail, as shown in the following example:

  KILL
  DO ##class(%SYSTEM.SQL.PTools).clearSQLStatsALL("USER")
  DO ##class(%SYSTEM.SQL.PTools).setSQLStatsFlagByNS("USER",3,,7,"M:5:1")
DisplaySettings
  SET SQLStatsFlag = ##class(%SYSTEM.SQL.PTools).getSQLStatsFlag(0,.ptInfo)
  WRITE "ptInfo array of SQL Stats return value:",!
  ZWRITE ptInfo,SQLStatsFlag
Copy code to clipboard

Export Query Performance Statistics

You can export query performance statistics to a file using the exportStatsSQL() method of %SYSTEM.SQL.PTools. This method is used to export statistics data from %SYSTEM.SQL.PTools classes to a file.

You can invoke exportSQLStats() as shown in the following examples:

  • from ObjectScript: SET status=##class(%SYSTEM.SQL.PTools).exportSQLStats("$IO") (defaults to format T).

  • from SQL: CALL %SYSTEM_SQL.PTools_exportSQLStats('$IO') (defaults to format H).

If you don't specify a filename argument, this method exports to the current directory. By default, this file is named PT_StatsSQL_exportSQLStats_ followed by the current local date and time as YYYYMMDD_HHMMSS. You can specify $IO to output the data to the Terminal or Management Portal display. If you specify a filename argument, this method creates a file in the Mgr subdirectory for the current namespace, or in the path location you specify. This export is limited to data in the current namespace.

You can specify the output file format as P (text), D (comma-separated data), X (XML markup), H (HTML markup), or Z (user-defined delimiter).

By default this method exports the query performance statistics. You can specify that it instead export the SQL query text or the SQL Query Plan data, as shown in the following examples:

  • Query Text: CALL %SYSTEM_SQL.PTools_exportStatsSQL('$IO',,0,1,0)

  • Query Plan: CALL %SYSTEM_SQL.PTools_exportStatsSQL('$IO',,0,1,1)

exportSQLStats() modifies the query text by stripping out comments and performing literal substitution.

The same query text and query plan data can be returned by ExportSQLQuery().

Stats Values

The following statistics are returned:

  • RowCount - The total number of rows returned in the MAIN module for the given query.

  • RunCount - The total number of times the query has been run since the last time it was compiled/prepared.

  • ModuleCount - The total number of times a given module was entered during the run of the query.

  • TimeToFirstRow - The total time spent to return the first resultset row to the MAIN module for the given query.

  • TimeSpent - The total time spent in a given module for the given query.

  • GlobalRefs - The total number of global references done in a given module for the given query.

  • LinesOfCode - The total number of lines of ObjectScript code executed in a given module for the given query.

  • DiskWait (also known as Disk Latency) - The total number of milliseconds spent waiting for disk reads in a given module for the given query.

Delete Query Performance Statistics

You can use the clearSQLStatsALL() method to delete performance statistics. By default, it deletes statistics gathered for all routines in the current namespace. You can specify a different namespace, and/or limit deletion to a specific routine.

Performance Statistics Examples

The following example gathers performance statistics on the main module of a query (Action option 2) that was prepared by the current process, then uses the exportSQLStats() to display the performance statistics to the Terminal.

  DO ##class(%SYSTEM.SQL.PTools).clearSQLStatsALL()
  DO ##class(%SYSTEM.SQL.PTools).setSQLStatsFlagJob(2)
  SET myquery = "SELECT TOP 5 Name,DOB FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET pStatus = ##class(%SYSTEM.SQL.PTools).exportSQLStats("$IO")
    IF pStatus'=1 {WRITE "Performance stats display failed:"
       DO $System.Status.DisplayError(qStatus) QUIT}
Copy code to clipboard

The following example gathers performance statistics on all modules of a query (Action option 3) that was prepared by the current process, then calls exportSQLStats() from Embedded SQL to display the performance statistics to the Terminal:

  DO ##class(%SYSTEM.SQL.PTools).clearSQLStatsALL()
  DO ##class(%SYSTEM.SQL.PTools).setSQLStatsFlagJob(3)
  SET myquery = "SELECT TOP 5 Name,DOB FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  &sql(CALL %SYSTEM_SQL.PTools_exportSQLStats('$IO'))
Copy code to clipboard

The following example gathers performance statistics on the main module of a query (Action option 2) that was prepared by the current process, then uses the StatsSQLView query to display these statistics:

  DO ##class(%SYSTEM.SQL.PTools).clearSQLStatsALL()
  DO ##class(%SYSTEM.SQL.PTools).setSQLStatsFlagJob(2)
  SET myquery = "SELECT TOP 5 Name,DOB FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
GetStats
  SET qStatus = tStatement.%Prepare("SELECT * FROM %SYS_PTools.StatsSQLView")
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rsstats = tStatement.%Execute()
  DO rsstats.%Display()
  WRITE !!,"End of SQL Statistics"
Copy code to clipboard

The following example gathers performance statistics on all modules (Action option 3) of all queries in the USER namespace. When the statistics collection time expires after 1 minute, it re-sets to Action option 2 and the scope of collecting defaults to 15 (all statistics) on all namespaces:

  DO ##class(%SYSTEM.SQL.PTools).clearSQLStatsALL("USER")
  DO ##class(%SYSTEM.SQL.PTools).setSQLStatsFlagByNS("USER",3,,7,"M:1:2")
  SET myquery = "SELECT TOP 5 Name,DOB FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
GetStats
  SET qStatus = tStatement.%Prepare("SELECT * FROM %SYS_PTools.StatsSQLView")
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rsstats = tStatement.%Execute()
  DO rsstats.%Display()
  WRITE !!,"End of SQL Statistics",!
TerminateResetStats
  WRITE "returns:  ",##class(%SYSTEM.SQL.PTools).getSQLStatsFlag(),!
  HANG 100
  WRITE "reset to: ",##class(%SYSTEM.SQL.PTools).getSQLStatsFlag()
Copy code to clipboard
FeedbackOpens in a new window