SQL Memory usage query and cachestore_sqlcp

15 February,2017 by Jack Vamvas

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

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Memory usage query and cachestore_sqlcp


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer