After installation, you must decide the maximum amount of SQL text that can be copied to shared memory. Consider the following to help you determine how much memory to allocate per user:
SQL batches exceeding the allocated amount of memory are truncated without warning. If you do not allocate enough memory for the batch statements, the text you are interested in viewing might be the section of the batch that is truncated, as illustrated in Figure 5-2.
Figure 5-2: How SQL text is truncated if not enough memory is configured
For example, if you configure Adaptive Server to save the amount of text designated by bracket A in the illustration, but the statement that is running occurs in the text designated by bracket B, Adaptive Server will not display the statement that is running.
The more memory you allocate for SQL text from shared memory, the less chance the problem statement will be truncated from the batch copied to shared memory. However, Adaptive Server immediately rejects very large values because they do not leave enough memory for data and procedure caches.
Sybase recommends an initial value of 1024 bytes per user connection.
Use sp_configure with the max SQL text monitored configuration parameter to allocate shared memory, where bytes_per_connection (the maximum number of bytes saved for each client connection) is between 0 (the default) and 2,147,483,647 (the theoretical limit):
sp_configure "max SQL text monitored", bytes_per_connection
Since memory for SQL text is allocated by Adaptive Server at start-up, you must restart Adaptive Server for this parameter to take effect.
The total memory allocated for the SQL text from shared memory is the product of bytes_per_connection multiplied by the number of user connections.
