1. Monitors SQL processes using the SQL Process view (2022.2+)
Key Points
- SQL Runtime Statistics: Always-on monitoring of SQL query performance and execution
- INFORMATION_SCHEMA tables: STATEMENTS, STATEMENT_DAILY_STATS, STATEMENT_HOURLY_STATS, and CURRENT_STATEMENTS
- Query Test button: Available from SQL Statement Details to display runtime statistics
- Management Portal SQL interface: Provides comprehensive SQL statement tracking and analysis
- Statistics aggregation: System task runs hourly to aggregate process-specific statistics into global statistics
Detailed Notes
Overview
InterSystems IRIS provides comprehensive SQL process monitoring capabilities through the SQL Runtime Statistics system.
- Always enabled: Cannot be turned off
- Monitoring scope: Query runtime, commands executed, rows returned, run counts for SELECT, DDL, and DML statements
INFORMATION_SCHEMA Tables
SQL runtime statistics are viewable from several INFORMATION_SCHEMA tables:
- STATEMENTS: Cached queries
- STATEMENT_DAILY_STATS / STATEMENT_HOURLY_STATS: Aggregated statistics
- STATEMENT_PARAMETER_STATS: Parameter sampling (if enabled)
- CURRENT_STATEMENTS: Currently running statements
Statistics Collection Timing
- Statistics gathered when a query operation is prepared
- Written at set intervals for efficiency
- Delay: May take up to 30 minutes to see newly gathered runtime statistics
Management Portal Interface
The SQL interface provides multiple views for analyzing SQL Statements:
SQL Statements tab:
- Lists all statements in the namespace
- Sortable columns: Table/View/Procedure Names, Plan State, Location, SQL Statement Text, performance metrics (Average Time)
SQL Statement Details view:
- Query Test button displays SQL Runtime Statistics page
Statistics Aggregation
- System task runs automatically once per hour in all namespaces
- Aggregates process-specific SQL query statistics into global statistics
- Note: Global statistics may not reflect statistics gathered within current hour
Statistics Management
- Runtime statistics can be explicitly purged
- Dropping a table or view automatically deletes related SQL runtime statistics
Documentation References
2. Gathers and maintains table statistics using TUNE TABLE
Key Points
- TUNE TABLE command: Gathers statistics based on representative data in the table
- Statistics collected: Map size, extent size, and field selectivity for query optimization
- Automatic recompilation: Recompiles cached queries when statistics change significantly
- Sampling options: %SAMPLE_PERCENT allows sampling percentage specification (default uses entire extent for tables under 1000 rows)
- Privilege requirements: Requires %ALTER_TABLE administrative privilege and %ALTER privilege on the specific table
Detailed Notes
Overview
The TUNE TABLE command is a critical tool for maintaining optimal query performance by gathering table statistics based on representative data currently in the table.
Statistics Collected
TUNE TABLE calculates and sets three key statistics:
- Map size: Storage organization
- Extent size: Number of rows
- Selectivity for each field: Data distribution
All are essential for the query optimizer to generate efficient execution plans.
Definition Updates
- Updates both the SQL table definition and commonly the corresponding persistent class definition
- Statistics used by query optimizer without requiring class compilation
- For deployed classes: Only updates SQL table definition (optimizer uses statistics from table definition indirectly)
Cached Query Behavior
- When statistics change: Automatically recompiles all existing cached queries referencing the table
- When statistics unchanged: Cached queries not recompiled, avoiding unnecessary overhead
TUNE TABLE Options
- %SAMPLE_PERCENT: Specify percentage of rows to sample (useful when outlier values not evenly distributed)
- %CLEAR_VALUES: Clear existing statistics
- %RECOMPILE_CQ: For compatibility
Note: For tables with extent size less than 1000 rows, entire extent is always used regardless of sampling percentage
Execution Methods
TUNE TABLE can be executed via:
- SQL command
- Management Portal SQL interface Actions drop-down (for single tables or entire schemas)
- Programmatically using `$SYSTEM.SQL.Stats.Table.GatherTableStats()` method
Privilege Requirements
- %ALTER_TABLE: Administrative privilege (namespace-specific)
- %ALTER: Privilege on the specific table
Documentation References
3. Uses Statement Index runtime statistics to identify optimization opportunities
Key Points
- Statement Index: Catalog of all SQL statements with Query Plans and runtime metrics
- Performance metrics: Average time, run count, average rows returned, average commands executed
- Alternate Show Plans: Compare multiple execution plan options with cost analysis and statistics
- Filter and sorting: Narrow statements by schema, table, routine, or text substring; sort by any column
- Frozen vs. Unfrozen plans: Control query plan stability through plan state management
Detailed Notes
Overview
The SQL Statement Index provides a comprehensive catalog for analyzing and optimizing SQL query performance.
- Scope: Tracks all SQL statements executed in the namespace
- Content: Associated Query Plans and runtime statistics
- Entry creation: When a query is prepared for the first time
Key Performance Metrics
Each SQL Statement entry includes:
- Average Time: Average query execution duration in seconds
- Run Count: Number of times executed
- Average Rows: Average rows returned
- Average Commands: Average commands executed
Management Portal Access
The Statement Index is accessible through Management Portal SQL interface via SQL Statements tab:
- Lists statements in collation sequence by schema and table/view name
- Displays only statements for which current user has privileges
Analysis Capabilities
Column sorting:
- Table/View/Procedure Names
- Plan State
- Location
- SQL Statement Text
- Performance metrics
Filtering options:
- Schema names
- Table names
- Routine locations
- Text substrings
Optimization Identification
The Statement Index enables identification of optimization opportunities:
- Slow queries (sortable by Average Time)
- Frequently executed statements (sortable by Run Count)
- Queries with inefficient plan states
Alternate Show Plans Tool
Provides advanced optimization analysis:
- Displays multiple possible execution plans for a query
- Each plan shows Cost value for relative comparison
- Detailed statistics: Time, Global Refs, Commands, Read Latency for each module
- Compare Show Plans with Stats: Side-by-side comparison with actual or estimated statistics
Plan States
SQL Statements can have different Plan States:
- Frozen: Locked execution plan (provides stability for production queries)
- Unfrozen: Can be optimized
- Unfrozen/Parallel: For %PARALLEL queries
Documentation References
Exam Preparation Summary
Critical Concepts to Master:
- SQL Runtime Statistics: Understand always-on monitoring, INFORMATION_SCHEMA tables, and statistics aggregation timing
- TUNE TABLE Operations: Know when to run TUNE TABLE, sampling options, and privilege requirements
- Statement Index Analysis: Master filtering, sorting, and identifying optimization opportunities from runtime metrics
- Query Plan Management: Understand frozen vs. unfrozen plans and when to use each
- Performance Metrics: Memorize key metrics (Average Time, Run Count, Average Rows, Average Commands)
Common Exam Scenarios:
- Identifying slow queries using SQL Statement Index sorting and filtering
- Determining when to run TUNE TABLE on tables with changing data distributions
- Using INFORMATION_SCHEMA.CURRENT_STATEMENTS to monitor active SQL processes
- Comparing alternate execution plans to select optimal query performance
- Understanding statistics collection intervals and aggregation timing
Hands-On Practice Recommendations:
- Query INFORMATION_SCHEMA tables (STATEMENTS, CURRENT_STATEMENTS, STATEMENT_DAILY_STATS)
- Execute TUNE TABLE with different sampling percentages
- Use Management Portal SQL Statements tab to filter and sort statements
- Compare Show Plans with Stats for queries with multiple possible execution plans
- Monitor SQL runtime statistics and identify queries needing optimization