sys.dm_os_memory_clerks Descriptions

Question

Post reply

sys.dm_os_memory_clerks Descriptions

Chris Kitchen

Hall of Fame

Points: 3090
More actions
April 24, 2013 at 8:49 am

#274519

Hi,
On one of our Sql Server 2008 R2 instances I'm executing the following query:
select top 20
left([name], 20) [Name]
,[type] [Type]
,sum([single_pages_kb]) [SinglePageMemoryAllocation_KB]
from sys.dm_os_memory_clerks
group by [type], left([name], 20)
order by sum([single_pages_kb]) desc;
I have been searching Google and other online sources without any luck trying to find descriptions for the output [Type] column from the query above. Ideally I'd like to find a list of all of the possible types so I can have a good read. Alternatively, just being able to find information on the following types would be a good starting point:
USERSTORE_SCHEMAMGR
MEMORYCLERK_SQLSTORENG
USERSTORE_OBJPERM
USERSTORE_DBMETADATA
USERSTORE_SCHEMAMGR
MEMORYCLERK_SQLSTORENG
USERSTORE_OBJPERM
OBJECTSTORE_SNI_PACKET
CACHESTORE_SYSTEMROWSET
MEMORYCLERK_SQLOPTIMIZER
MEMORYCLERK_SQLCONNECTIONPOOL
MEMORYCLERK_SQLCLR
MEMORYCLERK_XE
MEMORYCLERK_SQLQUERYEXEC
OBJECTSTORE_SERVICE_BROKER
MEMORYCLERK_HOST
Thanks in advance.
www.sqlAssociates.co.uk

Viewing 3 posts - 1 through 2 (of 2 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

If I understand your question correctly... try this link

http://msdn.microsoft.com/en-us/library/ms175019(v=sql.105).aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 2

H,

Thanks for your reply. Apologies, I will try and be a little more specific.

Using Sql Server 2008 SSMS if I run the query:

select distinct ([type]) from sys.dm_os_memory_clerks;

The following rows are returned:

MEMORYCLERK_SQLOPTIMIZER

CACHESTORE_PHDR

CACHESTORE_XMLDBTYPE

CACHESTORE_EVENTS

USERSTORE_OBJPERM

CACHESTORE_CLRPROC

USERSTORE_TOKENPERM

MEMORYCLERK_SQLSTORENG

CACHESTORE_FULLTEXTSTOPLIST

MEMORYCLERK_XE

CACHESTORE_XPROC

OBJECTSTORE_SNI_PACKET

CACHESTORE_BROKERRSB

OBJECTSTORE_SERVICE_BROKER

MEMORYCLERK_SQLSERVICEBROKERTRANSPORT

MEMORYCLERK_XE_BUFFER

MEMORYCLERK_SQLGENERAL

CACHESTORE_XMLDBATTRIBUTE

MEMORYCLERK_SQLHTTP

CACHESTORE_STACKFRAMES

MEMORYCLERK_SQLCONNECTIONPOOL

MEMORYCLERK_SQLSERVICEBROKER

CACHESTORE_NOTIF

CACHESTORE_XMLDBELEMENT

OBJECTSTORE_LOCK_MANAGER

MEMORYCLERK_SQLBUFFERPOOL

MEMORYCLERK_SQLSOAP

MEMORYCLERK_TRACE_EVTNOTIF

CACHESTORE_OBJCP

CACHESTORE_CONVPRI

MEMORYCLERK_QSRANGEPREFETCH

CACHESTORE_BROKERREADONLY

MEMORYCLERK_SQLCLRASSEMBLY

MEMORYCLERK_SOSNODE

MEMORYCLERK_SQLQUERYPLAN

OBJECTSTORE_SECAUDIT_EVENT_BUFFER

MEMORYCLERK_BHF

CACHESTORE_SQLCP

OBJECTSTORE_LBSS

CACHESTORE_SYSTEMROWSET

MEMORYCLERK_FULLTEXT

USERSTORE_SCHEMAMGR

MEMORYCLERK_SQLQUERYCOMPILE

CACHESTORE_TEMPTABLES

CACHESTORE_BROKERTBLACS

CACHESTORE_BROKERTO

CACHESTORE_BROKERKEK

MEMORYCLERK_SQLXML

USERSTORE_SXC

MEMORYCLERK_SNI

MEMORYCLERK_FULLTEXT_SHMEM

CACHESTORE_BROKERUSERCERTLOOKUP

CACHESTORE_BROKERDSH

MEMORYCLERK_SQLSOAPSESSIONSTORE

MEMORYCLERK_SQLQERESERVATIONS

MEMORYCLERK_HOST

MEMORYCLERK_SQLCLR

MEMORYCLERK_SQLXP

USERSTORE_DBMETADATA

MEMORYCLERK_SQLUTILITIES

CACHESTORE_VIEWDEFINITIONS

MEMORYCLERK_SQLQUERYEXEC

I am trying to find descriptions for each of these items but haven't been able to find descriptions for a large chunk of them.

I am just wondering if anyone knows where I could find this information online?

Thanks.

www.sqlAssociates.co.uk