Error 701

Severity Level 19

Message Text

There is insufficient system memory to run this query.

Explanation

This error occurs when there is not enough procedure cache available to execute a stored procedure, trigger, or batch of one or more SQL statements. This can happen when trying to load a query plan into the procedure cache or when trying to resolve or compile a procedure, trigger, or batch. (For information about resolution, see "Resolution" in Chapter 21, "Configuration Issues.")

Because the amount of available procedure cache is based on SQL Server use, this error might occur intermittently as procedure cache is used and released. Because query plans vary greatly in size, one or more procedures can repeatedly fail with this error while others complete normally. Although there might not be sufficient space to load a large query plan, several smaller plans might fit.

Action

Increase the size of the procedure cache using one of the following methods:

• Use the memory configuration option of sp_configure to increase the total amount of memory requested by SQL Server.
• Use the procedure cache configuration option of sp_configure to increase the percentage of total cache space dedicated to procedure cache.
• Decrease the amount of memory required for other resources, for example, unnecessary user connections.

Increasing the memory option for SQL Server is the most straightforward method to resolve this error. Although it increases both the procedure and data caches, it can also waste memory. If your memory resources are limited, you can increase the procedure cache option for SQL Server without changing the value of the memory option. However, this shifts space from the data cache to the procedure cache and might result in performance degradation or other problems if not enough data cache remains.

You can also increase the size of the procedure cache without substantially changing the amount of data cache. This requires some combination of increasing both the memory and procedure cache parameters in such a way that the majority of the new memory goes to the procedure cache. Although this method requires more planning, it allows you to control where the additional memory goes. For more information about increasing the size of the procedure cache, see Chapter 21, "Configuration Issues."

To view current memory usage information in preparation for tuning memory configuration, use the DBCC MEMUSAGE statement, as described in Chapter 21, "Configuration Issues."