When a regular query (excluding update index statistics) generates a 701 error, it indicates that Adaptive Server has exhausted the procedure cache space. If you are running with the default procedure cache size, increase procedure cache and try again. The general guideline for version 15.0 and later is to use a procedure cache that is 2 – 6 times the size of your 12.5.x procedure cache. In some cases, especially while using the optimization goal allrows_dss, your procedure cache may need to be even larger.
If increasing the procedure cache does not resolve the 701 error and you cannot isolate the problem, set up a configurable shared memory dump that includes the procedure cache pages:
sp_configure 'dump on conditions', 1 go
sp_shmdumpconfig 'add', 'error', 701, 1, 'my_dump_directory',null,include_proc go
sp_shmdumpconfig adds the error 701 condition to initiate a memory dump.The fourth parameter (1 in the examples above) indicates the number of memory dumps to capture. Adaptive Server does not capture additional memory dumps on this condition until Adaptive Server is restarted or until you manually reset the counter.
The parameter my_dump_directory is the name of a directory to hold the memory dump. The file system on which the directory resides should have enough free space to hold the memory dump file, which can be large. Verify the dump conditions currently defined by running sp_shmdumpconfig without any parameters. This also shows an estimated size of the memory dump to be captured.
The parameter include_proc enables procedure cache information to be included in the configurable shared memory dump.
A file name that includes the date and time of the memory dump is automatically generated. Once the memory dump has been captured, reset the system using:
sp_shmdumpconfig 'drop', 'error', 701 go
By default, Adaptive Server sends the 701 error message to the client. You may also have this message reported in the error log by running:
sp_altermessage 701,'with_log',true
To stop all configurable shared memory dumps, set dump on conditions to 0. Once the memory dump has been captured, open a case with Technical Support and upload the memory dump to the FTP site. Include the output from the SQL statements below which use the monitoring tables within Adaptive Server:
select * from master..monProcedureCacheMemoryUsage select * from master..monProcedureCacheModuleUsage go
The monitoring tables are automatically set up during execution of the installmaster script in Adaptive Server 15.0.2 or later. The installation process for earlier versions of Adaptive Server execute the installmontables script. See Adaptive Server 15.0 documentation for more details on configuring the monitoring tables.
