In Adaptive Server Enterprise, you must set the parameters listed below:
To access the Database Object’s DDL text in Database Explorer, Object Extractor, and SQL Scanner:
sp_configure "select on syscomments.text", 1
To access the Estimated I/O Cost estimation in SQL Optimizer, Index Advisor, Configuration Analyzer, and Index Impact Analyzer:
sp_configure "allow resource limits", 1
To capture SQL statements with SQL Collector for Monitor Server:
sp_configure "event buffers per engine", 2000 sp_configure "max SQL text monitored", 4096 sp_configure "enable monitoring", 0
In Adaptive Server Enterprise 12.5.0.3, SQL Collector
for Monitor Server requires enable monitoring be
set to 0 (zero) and SQL Inspector and Performance Monitor requires
that enable monitoring be set to 1. This parameter
is not available in earlier versions of Adaptive Server. In Adaptive
Server Enterprise 12.5.1 and later, enable monitoring is
not required for SQL Collector for Monitor Server.
To save abstract plans in the SQL Optimizer module and use the Abstract Plan Manager module:
sp_configure "abstract plan cache", 0 sp_configure "abstract plan replace", 0
To retrieve SQL statements using QP Metrics in the SQL Inspector:
sp_configure "enable metrics capture", 1
To use the SQL Inspector and Performance Monitor modules:
sp_configure "enable monitoring", 1 sp_configure "sql text pipe active", 1 sp_configure "sql text pipe max messages", 100 sp_configure "plan text pipe active", 1 sp_configure "plan text pipe max messages", 100 sp_configure "statement pipe active", 1 sp_configure "statement pipe max messages", 5000 sp_configure "errorlog pipe active", 1 sp_configure "errorlog pipe max messages", 1024 sp_configure "deadlock pipe active", 1 sp_configure "deadlock pipe max messages", 1024 sp_configure "wait event timing", 1 sp_configure "process wait events", 1 sp_configure "object lockwait timing", 1 sp_configure "SQL batch capture", 1 sp_configure "statement statistics active", 1 sp_configure "per object statistics active", 1
The Adaptive Server monitoring tables are used by the Performance Monitor and SQL Inspector. For information about installing these tables, see the Adaptive Server Enterprise 15.0 Performance and Tuning: Monitoring and Analyzing guide.
