SQL Performance Analysis Toolkit
InterSystems IRIS provides analysis tools that can be used to actively profile specific SQL statements. These tools gather detailed information about the execution of these SQL statements and are useful for pinpointing specific problems within a query plan. Using this information, developers can take steps to improve the performance of inefficient SQL statements. However, the active profiling can significantly increase the load on the server. Therefore, the SQL Performance Analysis Toolkit is meant for a concerted code analysis effort after examining the SQL Runtime Statistics and determining which specific queries need a closer look. It is not intended for continuous monitoring of executing code.
%SYSTEM.SQL.PToolsOpens in a new tab class methods are the preferred APIs for invoking this functionality through ObjectScript. The %SYSTEM.SQL.PToolsOpens in a new tab methods interface regroups and reorganizes functionality implemented in methods of the %SYS.PTools base classes.
Activate SQL Performance Statistics
The SQL Performance Analysis Toolkit offers support specialists the ability to profile specific SQL statements or groups of statements. By using these tools during the execution of specific SQL statements, you can gather performance statistics that can be used to analyze problematic statements in isolation or across a live workload. These tools can be executed as stored procedures in SQL or through direct method calls.
Using methods in the %SYSTEM.SQL.PToolsOpens in a new tab class, you can initiate the collection of advanced performance statistics. The following methods are provided to gather such statistics at different scales, including:
-
The entire system: setSQLStatsFlag()Opens in a new tab
-
A specific namespace: setSQLStatsFlagByNS()Opens in a new tab
-
The current process or job: setSQLStatsFlagJob()Opens in a new tab
-
A specified process or job: setSQLStatsFlagByPID()Opens in a new tab. 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 action option. You can determine the current settings using the getSQLStatsFlag() or getSQLStatsFlagByPID() method.
You can invoke these methods from ObjectScript or from SQL as shown in the following examples:
-
from SQL: SELECT %SYSTEM_SQL.PTools_setSQLStatsFlag(2,,8)
-
from ObjectScript: SET rtn=##class(%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 performance statistics, 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 action option, Routines and Classes that contain SQL will need to be compiled to perform statistics code generation. You must purge cached queries to force code regeneration when working with Dynamic SQL or a database driver.
-
To go from 1 to 2: you simply change the action 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 action option, Routines and Classes that contain SQL will need to be compiled to record statistics for all module levels. When working in Dynamic SQL or with a database driver, 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() methods or stored procedures. 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 resets 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.
Activating Performance Statistics in the Management Portal
You can set the action option for collecting performance statistics in the Management Portal from the Settings tab on the SQL Runtime Statistics page by following either of the following paths:
-
System Explorer > Tools > SQL Performance Tools > SQL Runtime Statistics
-
System Explorer > SQL > Tools > SQL Runtime Statistics
Use Keywords to Profile Queries
If you would like to examine the performance of a specific query, attach the %PROFILE (equivalent to setSQLStatsFlagJob(2)) or %PROFILE_ALL (equivalent to setSQLStatsFlagJob(3)) keywords to the end of a SELECT, INSERT, UPDATE, or DELETE statement to gather performance statistics for just that query.
Gathering statistics this way avoids the substantial resource drain that comes with gathering statistics across the system or a specific namespace, but cannot provide the same breadth of information. This option is better used for a concerted analysis of specific queries to examine specific queries once you have pinpointed the schemas or configurations that are not performing as well as expected. Do not add the %PROFILE keyword to many queries in an attempt to collect statistics over a large section of the system.
Get Statistics Settings
The various setSQLStatsFlag[nnn]() methods return the prior statistics settings as a colon-separated value. You can determine the current settings using the getSQLStatsFlag()Opens in a new tab or getSQLStatsFlagByPID()Opens in a new tab methods or stored procedures.
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.
Export Query Performance Statistics
You can export query performance statistics to a file using the exportSQLStats()Opens in a new tab method of %SYSTEM.SQL.PToolsOpens in a new tab. This method is used to export statistics data from %SYSTEM.SQL.PToolsOpens in a new tab classes to a file.
You can invoke exportSQLStats() as shown in the following examples:
-
from SQL: CALL %SYSTEM_SQL.PTools_exportSQLStats('$IO') (defaults to format H).
-
from ObjectScript: SET status=##class(%SYSTEM.SQL.PTools).exportSQLStats("$IO") (defaults to format T).
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_exportSQLStats('$IO',,0,1,0)
-
Query Plan: CALL %SYSTEM_SQL.PTools_exportSQLStats('$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()Opens in a new tab.
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 or 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.
SQL statistics report the aggregate counter values for all components of the query being run. Such components include all partitions of a parallel query or all shards of a sharded query.
Delete Query Performance Statistics
You can use the clearSQLStatsALL()Opens in a new tab method to delete performance statistics. By default, it deletes statistics gathered for all routines in the current namespace. You can either specify a different namespace or limit deletion to a specific routine or both.
Additionally, you can use the Purge Stats button from the SQL Runtime Statistics page in the Management Portal to delete all of the accumulated statistics for all queries in the current namespace. If successful, a message indicates the number of stats purged. If there were no stats, the Nothing to purge message is displayed. If the purge was unsuccessful, an error message is displayed.
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}
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'))
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 StatsSQLViewOpens in a new tab 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"
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 resets 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()