In Adaptive Server® Enterprise 15.x, the stored procedure sp_monitor has been enhanced to support deadlock and procstack monitoring types. For syntax information about these parameters, see “System changes for sp_monitor”.
To summarize these enhancements:
enable and disable commands are granular; they turn individual monitoring types on or off.
A new command, list, displays the currently active monitoring type, the currently enabled configuration options, and any required configuration options that are not yet turned on.
archive and report commands save monitoring data to an archive, and let you create reports from the archived data.
The help command is enhanced to display detailed usage information, where possible with examples.
The enable/disable options in sp_monitor have been enhanced to provide a greater level of granularity, including an argument that allows you to selectively enable or disable a specific monitoring type, and thus to set or unset the configuration options required by the specific monitoring type.
The default behavior is that all the monitoring types supported by sp_monitor are turned either on or off. Most configuration options required to support the monitoring types are also enabled or disabled.
If you do use enable or disable to specify an entity type, monitoring is enabled or disabled for only that particular entity, and the configuration options required for only that entity are turned ON or OFF.
sp_monitor enhancements can fetch data from the monDeadLock table and present it to the user in a human-readable format. Sites that can neither turn on the configuration option print deadlock information, nor alter their applications to turn the deadlock traces on, can capture deadlock events directly from the monitoring table. Users can instead monitor deadlocks using sp_monitor, and use the results to tune and troubleshoot the areas of their applications that cause deadlocks.
You can use these features of this enhancement to:
Create a default collection of all deadlock information from the monDeadLock table and printing out a block of output for each deadlock instance.
Print out deadlock tracing only for a given deadlock ID.
Print out deadlock tracing for deadlocks resolved on a specified day.
Archive the deadlock event data from monDeadLock to a user-supplied archive table name:
Use select into to create the archive table, if it does not already exist
Insert new rows into the archive table.
Ensure that duplicate deadlock events are not reinserted to the archive table.
Process deadlock event data from an archive, and generating deadlock tracing for all deadlock events, or for specified deadlock IDs, or for deadlocks resolved on a specified day.
Provide output modes that summarize the frequency of deadlock events, whether grouped by date of event, application name, or table name.
Support verbose output mode, to generate such details of the deadlock data as names of users, applications, or details about a procedure’s identity.
These features are supported whether the deadlock analysis
is performed directly on the monitoring table or on data archived
in an archive table.
sp_monitor procstack examines the execution of a task within a nested stored procedure execution. The procedures executed are extracted by retrieving data from the monProcessProcedures table at runtime.
sp_monitor procstack lets you monitor connections by generating a SQL text fragment for each executing procedure, and a backtrace for a task that might, for example, be waiting for a lock while executing a deeply nested stored procedure sequence.
procstack generates a summary for the sequence of stored procedures executed, and calls sp_helptext to extract a SQL context block around the line number of each procedure’s execution frame. If possible, the execution plan of the currently executed statement in the procedure is generated.
sp_monitor procstack can help you identify, during application development, errors that lead to blocking; during production, this interface regenerates inefficient SQL code at the moment performance slows down, or tasks are blocked.
list provides a snapshot of the monitoring enabled in the server, and of the configuration options required for the enabled monitoring entities. Any configuration options that are missing, but required by the enabled monitoring entities, are also listed. Configuration options that are already enabled, through either sp_configure or the configuration file, are also noted in the list output.
archive and report commands display monitoring data for a specified monitoring type. These commands specify the location of the archive table.
For example:
sp_monitor archive, 'deadlock'
sp_monitor report, 'deadlock'
sp_monitor 'archive [using prefix=<string>]', {'<monitoring type>'}
sp_monitor 'report [using prefix=<string>]', '<monitoring type>' [,<options supported for monitoring_type>]
To report deadlock event data from a default archive table, monDeadLock, in the current database, mondb, enter:
sp_monitor report, 'deadlock'
sp_monitor 'archive [using prefix=<string>]', {'<monitoring type>'}
Periodically archive events.
The monDeadLock pipe can overflow its size; Sybase recommends that you frequently archive deadlock events when they occur rapidly.
If the pipe is not full, or has not rolled over, archiving stores only new event information.
select into must be turned ON in the database where information is archived.
Currently, archiving and reporting are supported only
for deadlock monitoring data from monDeadLock.
This command produces enhanced command-specific help and usage information for sp_monitor.
