SQL Memory usage query and cachestore_sqlcp

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.

What next?

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’ –

Read more

SQL Server Performance ,memory pressure and memory usage  

Sys.dm_os_memory_clerks and AWE memory allocation

SQL Server – Find high impact queries with sys.dm_exec_query_stats