Check Memory Used by Queries

You can check the memory that a query uses by observing the high water mark for temporary memory usage. The high water mark represents the largest amount of in-use temporary space used since the high water mark was initialized or reset.

Complete the following tasks:

1. Use the ttIsql dssize command to check TEMP_IN_USE_SIZE and TEMP_IN_USE_HIGH_WATER. Alternatively, you can query the SYS.SYSTEMSTATS or the SYS.MONITOR table for these values.
2. Call the ttMonitorHighWaterReset procedure to reset the TEMP_IN_USE_HIGH_WATER to the current value for TEMP_IN_USE_SIZE.
3. Execute a query.
4. Use dssize to check TEMP_IN_USE_HIGH_WATER for peak memory usage for the query.