Question: There was SQL Server memory issue with the Error 701 There is insufficient system memory in resource pool 'internal' to run this query.
How can I troubleshoot the source of the error and complete some root cause analysis?
Answer: These out of memory errors can be tricky to nail , but there are some basics you can cover to start building a profile on the problem.
This sql server message usage query taps into the sys.dm_os_memory_clerks which Returns the set of all memory clerks that are currently active in the instance of SQL Server.
SELECT TOP(5) osmc.[type] AS [Memory Clerk], CAST((SUM(osmc.pages_kb)/1024) AS DECIMAL (15,2)) AS [Usage (MB)] FROM sys.dm_os_memory_clerks AS osmc GROUP BY osmc.[type] ORDER BY SUM(osmc.pages_kb) DESC OPTION (RECOMPILE);
Start from the top and if see CACHESTORE_SQLCP , then you have a good indicator Ad-hoc query plans are your largest consumer of memory.
1) Set or review max memory for SQL Server
2) Gather large adhoc sql statement sql statements, and understand why they are adhoc – any other problems etc. Read SQL Server - Error 701 - There is insufficient system memory for a query to gather ad-hoc. Also What is causing sql Cache Pressure for a more detailed breakdown
3) Enable ‘optimize for adhoc workloads’ –