sp_showcontrolinfo

Description

Displays information about thread pool assignments, bound client applications, logins, and stored procedures.

Considerations for process mode

When you configure Adaptive Server for process mode, sp_showcontrolinfo displays information about engine group assignments, bound client applications, logins, and stored procedures.

Syntax

sp_showcontrolinfo [object_type, object_name, spid ]

Parameters

object_type: one of:

AP for application

LG for login

PR for stored procedure

EG for thread pool (threaded mode) or engine group (process mode)

SV for service task

PS for process

DF for user-defined default execution class

If you do not specify an object_type or specify an object_type of null, sp_showcontrolinfo displays information about all types.

object_name: is the name of the application, login, stored procedure, or engine group. Do not specify an object_name if you specify PS or DF as the object_type. If you do not specify an object_name (or specify an object_name of null), sp_showcontrolinfo displays information about all object names.

spid: is the Adaptive Server process ID. Specify a spid only if you specify PS as the object_type. If you do not specify a spid (or specify a spid of null), sp_showcontrolinfo displays information for all spids. Use sp_who to see spids.

Examples

Example 1

Shows all user-assigned execution class-to-object bindings:

sp_showcontrolinfo

Example 2

Displays the execution class of the isql application:

sp_showcontrolinfo 'AP', 'isql'

Example 3

Displays the execution class for all processes assigned to thread pools:

sp_showcontrolinfo 'PS'

Example 4

Displays the execution class for spid 7:

sp_showcontrolinfo 'PS', null, 7

Usage

When used with no parameters, sp_showcontrolinfo displays information about all user-assigned thread pool assignments, bound client applications, logins, and stored procedures. When used with the object_type parameter, sp_showcontrolinfo provides information on an individual basis about application, login, or stored procedure bindings to an execution class, thread pool compositions, and session-level attribute bindings. See Chapter 4, “Distributing Engine Resources,” in the Performance and Tuning Series: Basics.
When run in process mode, sp_showcontrolinfo replaces thread_pool with the engine_group and engine columns.
Unless object_type is PR, execute sp_showcontrolinfo from the master database. If object_type is PR, execute sp_showcontrolinfo from the database in which the procedure resides.
If object_type is:
- null – sp_showcontrolinfo displays execution class information for objects that match the other parameters.
- DF – object_name and spid should be null, and sp_showcontrolinfo shows information about the user-defined default execution class.
If object_name is null, sp_showcontrolinfo displays the binding information for all applications, logins, and stored procedures.
If spid is null, sp_showcontrolinfo displays execution class information for objects that match the other parameters.

Permissions

Any user can execute sp_showcontrolinfo.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect