SQL Server Error 701: There is Insufficient System Memory to Run this Query
Today I am going to discuss SQL Server memory issues that your database Instance might face during bulk data load or during any memory intensive operations. You may also face insufficient memory issue when you try to execute numerous queries on large data sets at the same time in SQL Server. In such circumstances, SQL Server throws error 701 or error 802 with error text “There is insufficient system memory to run this query.” Full error descriptions for both error codes are given below.
Error 701, Severity: 17, State: 193.
There is insufficient system memory to run this query.
Or
Error 802, Severity: 17, State: 20
There is insufficient memory available in the buffer pool.
Error 701 or Error 802: Root Cause
If SQL Server is running out of memory and has no more memory available to allocate to its transactions then it will generate SQL Server memory error 701 or error 802. SQL Server Memory pressure or insufficient memory issue can be raised due to huge data load or other memory intensive operations.
We should check DBCC MEMORYSTATUS output to get latest snapshot of current memory status of the SQL Server. This command is very useful in troubleshooting memory issues in SQL Server. You might even have failed to run this DBCC command because system does not have enough memory. You need to reduce some memory pressure by stopping or killing some processes and then try to run this command to gather in depth information about SQL Server memory state.
The transaction that failed due to memory pressure and generates error 701 or error 802 might be or might not be the cause of this error. We should analyze the trends to reach on any conclusion.
Solution
There are mainly two reasons behind getting SQL Server memory issues for most of the SQL Server systems. Either your server has less memory to handle the current day to day workload or you are running some of the adhoc transactions that will require more memory for their execution like huge bulk data load, complex reports etc. If your SQL Server is running with insufficient memory during day to day activities then you should seriously evaluate the total workload and propose optimum memory size that can handle your current workload.
If you are planning to run any heavy ad hoc transaction then you should temporary increase the memory that can handle the transaction smoothly or you should run such transactions on a server that are built to handle such loads. You should also consider to reduce the batch size that is optimum for your system.
SQL Server is memory intensive application. We need to plan memory sizing for a server after proper evaluation of server state and overall load that will run on that SQL Server instance. But if you are facing memory issue on one of your SQL Server then first we need to troubleshoot it and find the root cause of this memory pressure. Here I am giving you step by step method to find the actual culprit that are causing the memory issue and possible solutions that can reduce some memory overheads.
First Make sure your SQL Server instance is running on dedicated server and not with other applications. If other applications are also using your database server then make sure they are not eating most of the memory. And, If most of the system memory is taken by other applications then you need to seriously think about migrating those application from this database server.
If you cannot migrate these applications to another server then workaround for such issues are to allocate the maximum memory value to your SQL Server instance. You can do it either by using sp_configure or using GUI. Read attached article to change max memory value for your SQL Server instance.
Next thing you should check is the server memory configuration. Check below SQL Server memory configuration parameters:
- max server memory
- min server memory
- min memory per query
Notice any unusual settings and fix them as necessary. All above three options have been covered in detain in above attached article.
Now, check the workload in terms of number of concurrent sessions, currently executing queries along with their memory grants. You should also focus on SQL Server transactions that are seeking huge memory values. Look at the transactions and gather a report for high memory intensive operations. Evaluate them and if possible optimize them so that they can take minimum memory.
You can also run below DBCC commands to free several SQL Server memory caches. This will also help you and make some relief for your current running transactions but in longer run it will not help if your system will need more memory to proceed the transactions.
- DBCC FREESYSTEMCACHE
- DBCC FREESESSIONCACHE
- DBCC FREEPROCCACHE
Another effective way to look in to memory status is by getting the output of DBCC MEMORYSTATUS command. You would get a good starting point to look in to culprit memory consumers.
We can also start collecting performance monitor counters for memory. Perfmon counters like the value of SQL Server: Buffer Manager\Buffer Cache Hit Ratio, SQL Server: Memory Manager and Page Life Expectancy will be very helpful in identifying memory pressure on SQL Server system. Analyze the trend for these counters and then decide whether issue is coming during some specific operations or system is continuously facing memory pressure.
Based on these analyses you can choose to either increase the system memory, increase the SQL Server memory allocation or reschedule some of the memory specific transactions to the off hours when load is minimum on the system. You can also reduce the batch size of the transaction to reduce the memory pressure. This will be certainly helpful in troubleshooting memory issues.
It is not necessary that your transactions are poorly designed always that is why SQL Server is taking lot of memory to process it. Sometimes even our systems on which SQL Server runs might have problem that end up with memory pressure. So you should consider all these points while fixing any performance issue.
I hope you like this article. Please follow us on our Facebook page and Twitter handle to get latest updates.
Read More:
- 4 Usages of DBCC SQLPERF in SQL Server
- Manage Transaction Log File during Bulk Data Load
- How to Improve Bulk Data Load Performance?
- Overview of SQL Server Performance Tuning
- Identify Disk Bottleneck in SQL Server using Perfmon Disk Counters
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023
