Understanding Memory Clerks

Question

Understanding Memory Clerks

SQLSACT

SSC-Insane

Points: 21838
More actions
November 7, 2012 at 4:20 am

#269684

Hello All
When investigating memory using sys.dm_os_memory_clerks, why is it that CACHESTORE_SQLCP is always among the top consumers?
I would imagine that the bulk of SQL's buffer pool gets allocated to the area where data/index pages are brought in from disk for queries.
In any average SQL Server, shouldn't MEMORYCLERK_SQLBUFFERPOOL be on top of the list?
Am I on track here or is my understanding wrong?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

KylesWay Old Hand Points: 369 More actions · Answer 1

These are cached SQL statements or batches that aren't in stored procedures, functions and triggers. This includes any dynamic SQL or raw SELECT statements sent to the server

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

kylesway (11/7/2012)
CACHESTORE_SQLCP
These are cached SQL statements or batches that aren't in stored procedures, functions and triggers. This includes any dynamic SQL or raw SELECT statements sent to the server

What I'm trying to figure out is why it's on the top of the list? Which cache counter type would represent my buffer pool?

Thanks

George M Parker SSCertifiable Points: 5412 More actions · Answer 3

SQLSACT (11/7/2012)
kylesway (11/7/2012)
CACHESTORE_SQLCP
These are cached SQL statements or batches that aren't in stored procedures, functions and triggers. This includes any dynamic SQL or raw SELECT statements sent to the server
What I'm trying to figure out is why it's on the top of the list? Which cache counter type would represent my buffer pool?
Thanks

You've posted this in the SQL Server 2008 forum, but please confirm that you're running SQL Server 2008. I recall that there were some issues in SQL 2005 where ad hoc querys could bloat the plan cache leading to this type of behavior.

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

George M Parker (11/7/2012)
SQLSACT (11/7/2012)
kylesway (11/7/2012)
CACHESTORE_SQLCP
These are cached SQL statements or batches that aren't in stored procedures, functions and triggers. This includes any dynamic SQL or raw SELECT statements sent to the server
What I'm trying to figure out is why it's on the top of the list? Which cache counter type would represent my buffer pool?
Thanks
You've posted this in the SQL Server 2008 forum, but please confirm that you're running SQL Server 2008. I recall that there were some issues in SQL 2005 where ad hoc querys could bloat the plan cache leading to this type of behavior.

2008 Sp3

Is there a script that would give me the amount of memory allocated to SQL Server, show me how the memory is allocated between the different areas of memory (Plan Cache, Buffer Pool) etc.

Thanks

George M Parker SSCertifiable Points: 5412 More actions · Answer 5

Check out the following: http://www.sqlservercentral.com/Forums/Topic1340744-391-1.aspx

If you order by Virtual_memory_committed_kb, you should see MEMORYCLERK_SQLBUFFERPOOL as the largest consumer of memory.

select * FROM sys.dm_os_memory_clerks

order by Virtual_memory_committed_kb desc

I'm still trying to find a definitive reference that adequately defines sys.dm_os_memory_clerks and what all of the columns mean.

SQLSACT SSC-Insane Points: 21838 More actions · Answer 6

George M Parker (11/7/2012)
Check out the following: http://www.sqlservercentral.com/Forums/Topic1340744-391-1.aspx
If you order by Virtual_memory_committed_kb, you should see MEMORYCLERK_SQLBUFFERPOOL as the largest consumer of memory.
select * FROM sys.dm_os_memory_clerks
order by Virtual_memory_committed_kb desc
I'm still trying to find a definitive reference that adequately defines sys.dm_os_memory_clerks and what all of the columns mean.

Thanks for this

Do you know of a way to check if my an instance is experiecing a plan cache bloat? What I mean is, how can I check what the allocated size of my plan cache is and how much of it has been used.

Thanks

George M Parker SSCertifiable Points: 5412 More actions · Answer 7

Thanks for this
Do you know of a way to check if my an instance is experiecing a plan cache bloat? What I mean is, how can I check what the allocated size of my plan cache is and how much of it has been used.
Thanks

Check out the following blog by Kimberly Tripp: http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx.

SELECT objtype AS [CacheType]

, count_big(*) AS [Total Plans]

, sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]

, avg(usecounts) AS [Avg Use Count]

, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]

, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]

FROM sys.dm_exec_cached_plans

GROUP BY objtype

ORDER BY [Total MBs - USE Count 1] DESC

go

SQLSACT SSC-Insane Points: 21838 More actions · Answer 8

George M Parker (11/7/2012)
Thanks for this
Do you know of a way to check if my an instance is experiecing a plan cache bloat? What I mean is, how can I check what the allocated size of my plan cache is and how much of it has been used.
Thanks
Check out the following blog by Kimberly Tripp: http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx.
SELECT objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
go

Thanks

I've used that script before.

I'm trying to find a way to get the total MB's SQL has allocated to the plan cache and how much of it has been used up already

Thanks