Occasionally, a query or procedure causes Adaptive Server Monitor to stop responding. Users who have the system administrator role can configure Adaptive Server to grant Adaptive Server Monitor access to the text of the currently executing SQL batch. Viewing the SQL text of long-running batches may help you debug “stuck” processes, or fine-tune long statements that are heavy resource consumers.
You must configure Adaptive Server to collect the SQL batch text and write it to shared memory, where the text can be read by Adaptive Server Monitor Server (the server component of Adaptive Server Monitor). The client requests might come from Monitor Viewer, which is a plug-in to Sybase Central, or other Adaptive Server Monitor Server applications.
Configuring Adaptive Server to save SQL batch text also allows you to view the current query plan in showplan format (as you would see after setting showplan on). You can view the current query plan from within Adaptive Server; see “Viewing the query plan of a SQL statement”. SQL batches are viewable only through Adaptive Server Monitor Server. See the Adaptive Server Monitor Server documentation for more information about displaying the batch text.
Because the query or procedure you are viewing may be nested within a batch of SQL text, the sysprocesses table includes columns for the line number, statement number, and spid the statement that has stopped responding, so its query plan can be analyzed.
By default, Adaptive Server does not save SQL batch text, so you must configure Adaptive Server to allocate memory for this feature. Adaptive Server Monitor access to SQL has no effect on performance if you have not configured any memory to save SQL batches.
