T3.3: Uses PTools for Performance Analysis

Overview of PTools Metrics

The SQL Performance Analysis Toolkit (PTools) provides three primary performance metrics that distinguish different aspects of query execution behavior. Understanding these metrics is essential for diagnosing performance bottlenecks and optimizing SQL queries.

Time (Time Spent)

Time represents the overall performance metric, measured in seconds, indicating the total duration of query execution. This metric is collected at different levels depending on the Action option: Action option 2 records statistics for just the outer loop of the query (gathering statistics at the open and close of the query, which is the sum of total time to perform all invocations of OPEN, FETCH, and CLOSE), while Action option 3 records statistics for all module levels of the query. When modules are nested, the MAIN module statistics are inclusive numbers representing the overall results for the full query.

Global References (Global Refs)

Global References measures the number of global variable accesses made to the database during query execution. Since InterSystems IRIS stores persistent data in globals, this metric directly correlates to database I/O activity. A high number of global references may indicate inefficient data access patterns, missing indices, or poorly optimized query plans. This metric is particularly valuable for identifying queries that perform full table scans rather than index scans.

Commands (Commands Executed)

Commands tracks the number of ObjectScript code lines executed during query processing. This metric reflects the computational complexity of the query execution plan. Higher command counts may indicate complex query logic, multiple loop iterations, or inefficient execution paths. When combined with Time and Global Refs metrics, Commands helps distinguish between CPU-bound operations (high Commands, low Global Refs) and I/O-bound operations (high Global Refs, lower Commands relative to time).

Read Latency and Statistics Display

The PTools framework also provides Read Latency measured in milliseconds, which specifically tracks disk wait time and helps identify I/O bottlenecks. These statistics are displayed for Query Totals and for each Query plan module when using the Alternate Show Plans feature with the "Compare Show Plans with Stats" option. The Collect option parameter (default value 15 = 1 + 2 + 4 + 8) controls which specific performance statistics are gathered, allowing selective collection based on analysis requirements.

The %SYSTEM.SQL.PTools API

The %SYSTEM.SQL.PTools class provides the preferred API for invoking SQL Performance Analysis Toolkit functionality through ObjectScript. This interface regroups and reorganizes functionality implemented in methods of the %SYS.PTools base classes, providing a cleaner and more intuitive interface for developers and database administrators.

Collection Scope Methods

The toolkit offers multiple methods to initiate performance statistics collection at different scopes:

• setSQLStatsFlag(): Activates collection for the entire system, affecting all namespaces and processes
• setSQLStatsFlagByNS(): Enables statistics collection for a specific namespace, allowing targeted analysis without system-wide performance impact
• setSQLStatsFlagJob(): Activates collection for the current process or job, making it ideal for analyzing specific application workflows
• setSQLStatsFlagByPID(): Allows activation for a specified process or job by Process ID, with special handling: if the first parameter is unspecified, specified as $JOB, or specified as an empty string (""), it invokes setSQLStatsFlagJob() instead

Invocation Syntax

These methods can be invoked from ObjectScript or from SQL as stored procedures. From SQL, the syntax is: `SELECT %SYSTEM_SQL.PTools_setSQLStatsFlag(2,,8)`. From ObjectScript, the syntax is: `SET rtn=##class(%SYSTEM.SQL.PTools).setSQLStatsFlag(2,,8)`. All methods take an integer Action option parameter and return a colon-separated string, the first element of which is the prior Action option value. This return value enables checking previous settings before making changes.

Management Portal Interface

The Management Portal provides a graphical interface for activating performance statistics from the Settings tab on the SQL Runtime Statistics page, accessible via System Explorer > Tools > SQL Performance Tools > SQL Runtime Statistics, or System Explorer > SQL > Tools > SQL Runtime Statistics. This interface simplifies configuration for administrators who prefer GUI-based management over programmatic control.

Purpose of Action Options

The Action option parameter controls the scope and behavior of statistics gathering in the PTools framework. Understanding these options is critical for balancing performance analysis needs with system overhead.

System-Wide and Namespace Methods (setSQLStatsFlag, setSQLStatsFlagByNS)

For setSQLStatsFlag() and setSQLStatsFlagByNS() methods:

• Action 0: Turns off statistics code generation completely, disabling all performance tracking
• Action 1 (default): Turns on statistics code generation for all queries but does not gather statistics, preparing queries for potential analysis without incurring collection overhead
• Action 2: Records statistics for just the outer loop of the query, gathering statistics at the open and close of the query, which represents the sum of total time to perform all invocations of OPEN, FETCH, and CLOSE. This option provides overall query performance metrics with minimal overhead
• Action 3: Records statistics for all module levels of the query, including the main module and all subquery modules. When modules are nested, the MAIN module statistics are inclusive numbers representing overall results for the full query

Job-Specific Methods (setSQLStatsFlagJob, setSQLStatsFlagByPID)

For setSQLStatsFlagJob() and setSQLStatsFlagByPID() methods, the Action options differ slightly:

• Action -1: Turns off statistics for the specific job
• Action 0 (default): Uses the system setting value
• Actions 1, 2, and 3: Same as setSQLStatsFlag() and override the system setting

Transitioning Between Action Options

Transitioning between Action options requires understanding compilation requirements:

• 0 to 1: Requires compiling Routines and Classes that contain SQL to perform statistics code generation, and purging cached queries to force code regeneration when working with Dynamic SQL or database drivers
• 1 to 2: Simply requires changing the action option to begin gathering statistics, allowing SQL performance analysis to be enabled on running production environments with minimal disruption
• 1 to 3 (or 2 to 3): Requires compiling Routines and Classes again and purging cached queries. Action option 3 is commonly only used on identified poorly-performing queries in non-production environments due to significant overhead
• 1, 2, or 3 to 0: Does not require purging cached queries

When Collect Option Applies

When the Action option is set to 2 or 3, the Collect option parameter allows fine-grained control over which performance statistics are gathered. This capability is valuable for reducing overhead in production environments where only specific metrics are needed for analysis.

Bitmask Value System

The Collect option is specified by adding together integer values associated with each type of statistic to collect. The framework uses a bitmask approach where each statistic type is represented by a power of 2: value 1 represents one metric type, value 2 represents another, value 4 represents a third, and value 8 represents a fourth metric type. The default value of 15 (1 + 2 + 4 + 8) collects all available statistics, providing comprehensive performance data at the cost of maximum overhead.

Checking Current Settings

The setSQLStatsFlag family of methods return the prior value of the Collect option as the second colon-separated element in the return string. Administrators can determine current settings using the getSQLStatsFlag() or getSQLStatsFlagByPID() methods or stored procedures. When all statistics are collected (the default), these methods return 15 as the second element value. This return mechanism enables administrators to preserve and restore previous settings when making temporary configuration changes.

Balancing Overhead and Insights

By selecting specific Collect option values, database administrators can balance the need for performance insights with system overhead concerns. For example, collecting only Time and Global Refs metrics (omitting Commands and Read Latency) would use a smaller Collect value, reducing the computational cost of statistics gathering while still providing key performance indicators for identifying slow queries and excessive database I/O patterns. The %SYSTEM.SQL.PTools class reference documentation provides detailed information about specific integer values and their corresponding metric types.

Purpose of Keyword Profiling

The %PROFILE and %PROFILE_ALL keywords provide a targeted approach to performance analysis by enabling statistics collection for individual SQL statements without activating system-wide or namespace-wide profiling. This capability is essential for analyzing specific queries after preliminary investigation has identified potential performance issues.

%PROFILE vs %PROFILE_ALL

The %PROFILE keyword is equivalent to setSQLStatsFlagJob(2) and collects statistics for the main query module only. The syntax is straightforward: attach %PROFILE to a SELECT, INSERT, UPDATE, or DELETE statement. For example:

• `SELECT %PROFILE_ALL * FROM Sample.Person`
• `INSERT %PROFILE INTO TableName VALUES (...)`
• `UPDATE %PROFILE TableName SET ...`
• `DELETE %PROFILE FROM TableName WHERE ...`

The %PROFILE_ALL keyword is equivalent to setSQLStatsFlagJob(3) and collects statistics for the main query module and all of its subquery modules, providing comprehensive performance data for complex queries with nested components.

Advantages Over System-Wide Collection

This keyword-based approach avoids the substantial resource drain that accompanies gathering statistics across the entire system or a specific namespace. While system-wide collection provides breadth of information useful for identifying which queries need attention, keyword-based profiling provides depth of information for specific queries that have already been identified as problematic. The keyword approach is better used for concerted analysis of specific queries to examine particular queries once you have pinpointed the schemas or configurations that are not performing as well as expected.

Best Practices

It is important not to add the %PROFILE keyword to many queries in an attempt to collect statistics over a large section of the system. Doing so defeats the purpose of targeted profiling and can significantly impact overall system performance. Instead, the recommended workflow is: 1. Use SQL Runtime Statistics to identify problematic queries 2. Use %PROFILE or %PROFILE_ALL keywords on those specific queries for detailed analysis 3. Use the detailed statistics to guide optimization efforts such as index creation, query rewriting, or schema adjustments

The exportSQLStats() Method

After collecting performance statistics using PTools, the exportSQLStats() method provides the mechanism for exporting statistics data to a file or displaying it on the terminal for analysis. This method is part of the %SYSTEM.SQL.PTools class and supports invocation from both SQL and ObjectScript contexts.

Invocation Syntax and Format Options

From SQL, the method is invoked using the CALL statement: `CALL %SYSTEM_SQL.PTools_exportSQLStats('$IO')`. This syntax defaults to format H (HTML), which produces formatted output suitable for viewing in web browsers or HTML-capable viewers. From ObjectScript, the method is invoked as: `SET status=##class(%SYSTEM.SQL.PTools).exportSQLStats("$IO")`. This syntax defaults to format T (text), which produces plain text output suitable for terminal display or text file storage. The '$IO' parameter directs output to the current device, typically the terminal. To export to a file, specify a file path instead of '$IO'.

Alternative: StatsSQLView Interface

An alternative to exportSQLStats() is querying the %SYS_PTools.StatsSQLView interface, which presents collected statistics in a tabular SQL result set format. This approach enables using standard SQL tools and techniques to filter, sort, and analyze performance data. Example ObjectScript code: ```objectscript SET qStatus = tStatement.%Prepare("SELECT * FROM %SYS_PTools.StatsSQLView") SET rsstats = tStatement.%Execute() DO rsstats.%Display() ``` This approach is particularly useful for integrating performance analysis into automated monitoring scripts or custom reporting tools.

Typical Export Workflow

The typical workflow for using exportSQLStats() involves: 1. Clearing previous statistics using clearSQLStatsALL() 2. Activating statistics collection using setSQLStatsFlagJob() or setSQLStatsFlag() 3. Executing the query to be analyzed 4. Calling exportSQLStats() to view the collected statistics

The exported data includes all metrics configured by the Collect option, including Time, Global Refs, Commands, and Read Latency for each query module. This comprehensive data enables detailed diagnosis of performance bottlenecks and provides quantitative evidence for optimization decisions.

Default Behavior and Terminate Option Purpose

Statistics collection continues until explicitly terminated by default. However, the Terminate option provides automatic termination capabilities essential for production environments where indefinite statistics collection could impact performance or consume excessive storage.

Specifying Termination Parameters

By default, collection continues indefinitely until terminated by issuing another setSQLStatsFlag[nnn]() method. When the Action option is set to 1, 2, or 3, administrators can specify a Terminate option parameter consisting of either an elapsed period in minutes or a specified timestamp. The Terminate option also specifies the Action option to reset to 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.

Production Use Cases

This automatic termination capability is particularly valuable in production environments where enabling detailed statistics collection (Action 3) for troubleshooting should be time-limited to prevent ongoing performance impact:

• "M:60:2": Gather detailed module-level statistics for one hour, then automatically downgrade to Action 2 (outer loop only) for continued monitoring with lower overhead
• "M:1:2": Collect intensive statistics for just one minute before resetting, as demonstrated in the documentation examples

Return Value Format

The setSQLStatsFlag family of methods return the prior value of the Terminate option as the fifth colon-separated element in the return string, encoded as a value that represents the termination configuration. Administrators can examine current settings using the getSQLStatsFlag() or getSQLStatsFlagByPID() methods. The colon-separated return format is: `Action:Collect:Namespace1:Namespace2:Terminate:FlagType`, where the Terminate value encodes the termination settings for programmatic inspection and restoration.

Two Approaches to Performance Monitoring

InterSystems IRIS provides two distinct approaches to performance monitoring: SQL Runtime Statistics and the SQL Performance Analysis Toolkit (PTools). Understanding when to use each approach is essential for effective performance management.

SQL Runtime Statistics

SQL Runtime Statistics provides always-on general performance monitoring that cannot be turned off. These statistics are gathered when query operations are prepared and are viewable from INFORMATION_SCHEMA.STATEMENTS, STATEMENT_DAILY_STATS, and STATEMENT_HOURLY_STATS tables. Runtime statistics include:

• Avg Time: Average length of time the query takes in seconds
• Run Count: Number of times the query has been run
• Avg Rows: Average number of rows returned
• Avg Commands: Average number of commands executed

To ensure efficient statistics gathering, data is written only at set intervals, which may result in delays of up to 30 minutes before statistics appear. A system task automatically runs once per hour in all namespaces to aggregate process-specific statistics into global statistics.

SQL Performance Analysis Toolkit (PTools)

The SQL Performance Analysis Toolkit provides active profiling capabilities for specific SQL statements or groups of statements. PTools gathers detailed information about execution including Time, Global Refs, Commands, and Read Latency at the module level. However, active profiling can significantly increase the load on the server. Therefore, PTools is meant for concerted code analysis efforts after examining SQL Runtime Statistics to determine which specific queries need closer examination. It is not intended for continuous monitoring of executing code.

Recommended Combined Workflow

The recommended workflow combines both approaches: use SQL Runtime Statistics for continuous monitoring to identify queries with poor Avg Time or high Avg Commands, then use PTools (via setSQLStatsFlag methods or %PROFILE keywords) for detailed analysis of identified problematic queries. The Alternate Show Plans tool bridges both approaches by displaying estimated statistics normally, but actual runtime statistics when PTools gathering is activated. This combination provides comprehensive performance management: broad identification via Runtime Statistics, deep diagnosis via PTools, and minimal overhead during normal operations.