SQL analysis and profiling with Cache Monitor 0.54 PDF Print E-mail

The new main feature in Cache Monitor 0.54 is the profiling of sql statements. Specifically for SQL analysis InterSystems added in Caché 2008.1 the %SYSTEM.PTools.SQLStats class and measures lines of code,global references, times run and duration of run e.g.

 

 

These information are used in Caché Monitor 0.54 to implement the Profiling feature.

Here are some detail information about this new feature from the InterSystems Caché docs: This is an compile time option, so all dynamic queries are purged to re-generate them with statistics enabled. The stats gathered are:

  • Number of Global References
  • Number of Lines of Code Executed
  • Number of Times a Module is called
  • Total Time in a Module
  • Number of Rows returned by the query

The modules can be nested in one another. The outer module will display inclusive numbers, so the Module MAIN will be the overall results for the full Query Run. When you first enable SQLStats you should Purge Cached Query to force code regeneration.

When you turn off gather stats: do SetSQLStats^%apiSQL(0) you do not need to Purge Cached Queries, the couple extra lines of code should not change the performance. All of the data is stored in %SYS.PTools.SQLQuery and %SYS.PTools.SQLStats.

 

How does it work?

The Caché Monitor Profiler is easily accessible in the Server Navigators tree under the performance node. Before executing a Test sql, you have to enable the server side compile time option to re-generate all dynamic queries. You can enable this from the Tab PTools configuration. Press Enable PTools and the the server side compile time option is enabled.

 

 

sql_statistics

 

After executing some sql statements you see the statistics like on the sreenshot below. You can reload the statistic table by pressing reload in the upper right corner. If you select a row in the statistic table Caché Monitor displays the associated sql statement.

sql_statistics_statement

 

On Tab Execution plan Caché Monitor shows the Execution plan from the selected sql statement.

sql_statistics_execution_plan1

 

Like the Execution plan feature in Query Analyzer, information about indexes and column selectivity are available.

sql_statistics_execution_plan2

 

Detail change log from version 0.54:

  • Link added to Caches SQL docs in Query Analyzer
  • New Profile features: PTools are now implemented in profile window
  • Cached Queries are now listed under Performance node
  • TYPE_FORWARD_ONLY error in data view fixed
  • Detail Data View is refreshing after changing cell selection in the same row
  • Column control is on each gui table control visible

column_list