Sunday, 2 December 2012

SQL Server - Memory Pressure Issue



Sometimes DBA receives SQL Alert System -17. I have given below the detailed description and solution for this issue. Hope it will be helpful for DBAs.

Alert

SQL Server Alert System: '17 - Insufficient Resources' occurred on \\Server1
DESCRIPTION: There is insufficient system memory in resource pool 'internal' to run this query.

Issue Description

SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.

There is insufficient system memory in resource pool 'internal' to run this query.

There was a memory allocation failure during connection establishment. Reduce nonessential memory load, or increase system memory. The connection has been closed.

This may also due to Virtual Address Space pressure, specifically in a region of VAS that is reserved at initial startup of SQL Server known as MemToLeave.

Recommended Solution

1.   Most of the timings, while this issue occurs, we would not able to access the Database engine through SSMS. In this case, Connect Database Engine through command line prompts and identifies the blocking process and kills it with appropriate approvals.

2.   Try increasing the memory capacity if it is lower than threshold value.

3.   We will try upgrading the SQL Server Version to SQL Server 2008 R2 Service Pack 1 if it is SQL Server 2008 R2 RTM.  It is a Microsoft recommendation to avoid memory pressure. Reference links are given below:


4.   SQL Server Linked Server, process conflict, Power Shell to connect to SQL, executing SQL query using OSQL in command prompt and ad-hoc INSERTs cause a rapid memory leak which eventually causes the server to become non-responsive.

5.   Plan to schedule the database engine refresh once in a month for better server performance which may avoid memory pressure issue.

6.   High availability solution [ex. Clustering] can be implemented to keep database to be available online at all the scenarios irrespective of any issue occurs.