|
"select on syscomments.text",1 |
Database Explorer and SQL Scanner |
Retrieve the database objects' SQL text. |
You must have privilege to access the system catalog table, syscomments, therefore the "select on syscomments.text" configuration parameter must be turned on. |
|
sa_role
Adaptive Server 15 or later
"event buffers per engine", 2000
"max SQL text monitored", 4096 |
SQL Collector for Monitor Server |
Capture currently running SQL statements |
The SQL Monitor is not available if you do not have sa_role privileges. In addition, the following Adaptive Server parameters need to be set to use the Adaptive Server Enterprise Monitor Server. They are: |
|
sa_role |
SQL Optimizer |
Abort Run Result for SQL statements with result set |
If you do not have sa_role privileges, you will not be able to abort the Run Result operation.
Note: The sa_role is only required for Run Result operation. You can terminate SQL statements during a Batch Run without the sa_role.
For SQL statements without a result set (for example, INSERT, UPDATE, DELETE and SELECT with INTO clause) can be terminated without having the sa_role. |
|
sa_role
Adaptive Server 15.0 or later
"allow resource limits", 1
|
SQL Optimizer,
Migration Analyzer,
Configuration Analyzer,
Index Impact Analyzer,
Index Advisor
|
Retrieve Estimated I/O Cost |
The estimated I/O cost is not available for SQL statements during optimization under the following conditions:
a. Your logon account does not have sa_role privilege.
b. You are using Adaptive Server 15.0 or later and "allow resource limits" configuration parameter is turned off.
If you do not have the privilege to retrieve the estimated I/O cost, then "N/A" is shown for the cost. |
|
sa_role
Turn on dbcc traceon (3604,302,310) option under the Database Settings tab of the Preferences setting.
|
SQL Information Panel |
Trace on information |
The “Trace on” feature shows the reason why the Adaptive Server optimizer has chosen a particular way to execute the source or alternative SQL statement and displaying the reasons for index and table joins selection. This information is located in the SQL Optimizer window or the SQL Scanner window. “Trace on” details are not available if you do not have sa_role privilege. In addition, the dbcc traceon option needs to be selected from the Preferences settings.
a. Select Preferences from the File menu.
b. Select the Database Settings tab page.
c. Check ON the dbcc traceon (3604,302,310) option.
|
|
Create Procedure |
SQL Optimizer |
Optimize for cursor |
The CREATE PROCEDURE privilege is required to optimize a SQL statement that runs inside a cursor.
A procedure is created when the SQL for Cursor checkbox is selected in the SQL Optimizer window. The stored procedure is created to retrieve the query plan and run time. The stored procedure is executed and then dropped. |
|
Create/Drop Index |
Index Adviser and Index Impact Analyzer
|
Retrieve query plan and in Index Advisor to retrieve the run time. |
You must have the privilege to create and drop indexes when retrieving run time (Index Adviser only) and query plan. |
|
"abstract plan cache",0
"abstract plan replace",0
Adaptive Server 15 and later |
SQL Optimizer |
Abstract Plans |
Required for ASE 15.0 or later to generate and retrieve abstract plans. |
|
mon_role
Adaptive Server 15.0 or later
“enable monitoring”, 1
”wait event timing”, 1
”process wait events”, 1
”max SQL text monitored”, 4096
”sql text pipe active”, 1
”sql text pipe max messages”, 1000
”statement pipe active”, 1
”statement pipe max messages”, 5000
”SQL batch capture”, 1
|
SQL Inspector |
Retrieve SQL information from the Performance tables |
You must have privilege to access the Adaptive Server monitoring tables, monSysSQLText and monSysStatement, therefore the mon_role is required.
You must also have several configuration parameters set to enable the monitoring
|
|
mon_role
Adaptive Server 15.0 or later
“enable monitoring", 1
"sql text pipe active", 1
"sql text pipe max messages", 100
"plan text pipe active", 1
"plan text pipe max messages", 100
"statement pipe active", 1
"statement pipe max messages", 5000
"errorlog pipe active", 1
"errorlog pipe max messages", 1024
"deadlock pipe active", 1
"deadlock pipe max messages", 1024
"wait event timing", 1
"process wait events", 1
"object lockwait timing", 1
"SQL batch capture", 1
"statement statistics active", 1
"per object statistics active", 1
|
Performance Monitor |
Retrieve information from the Performance tables |
You must have privilege to access the several of the Adaptive Server monitoring tables, therefore the mon_role is required.
You must also have several configuration parameters set to enable the monitoring.
|
