December 6, 2011 at 3:00 am
Hi All,
SQL Server 2005
From the perfmon, I can see that sql server is using 16GB of memory <perfmon, total server memory>.
From the below URLs, I have managed to find out the memory used by data (buffer) and query plans (cache).
Buffer:8.2GB
Cache: 1.5G
The above adds up to near 10GB. May i know what is utilizing the remaining 6GB? Anyway to find out?
Appreciate your help.
http://www.sqlteam.com/article/what-data-is-in-sql-server-memory
http://www.sqlteam.com/article/what-query-plans-are-in-sql-server-memory
thanks
December 6, 2011 at 3:30 am
December 7, 2011 at 12:56 am
Hi,
Can advise on how do I add up to the total server memory from the below?
thanks
Memory Manager KB
------------------------------ --------------------
VM Reserved 44366000
VM Committed 18374464
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
------------------------------ --------------------
VM Reserved 51840
VM Committed 6011016
AWE Allocated 0
MultiPage Allocator 50680
SinglePage Allocator 3045224
(5 row(s) affected)
Memory node Id = 1 KB
------------------------------ --------------------
VM Reserved 44308400
VM Committed 12357772
AWE Allocated 0
MultiPage Allocator 22864
SinglePage Allocator 3045224
(5 row(s) affected)
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 21776
MultiPage Allocator 4528
(7 row(s) affected)
MEMORYCLERK_SQLGENERAL (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 15672
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 37448
MultiPage Allocator 4544
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 37830656
VM Committed 11930264
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 24
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 5959936
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 37830656
VM Committed 17890200
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 24
(7 row(s) affected)
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 288
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLQUERYEXEC (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLQUERYEXEC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 296
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 864
MultiPage Allocator 128
(7 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 864
MultiPage Allocator 128
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 240
VM Committed 240
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 120
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 240
VM Committed 240
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 120
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 9408
VM Committed 9408
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2088
MultiPage Allocator 32
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2216
MultiPage Allocator 448
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 9408
VM Committed 9408
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4304
MultiPage Allocator 480
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1664
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1712
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3376
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 6313024
VM Committed 263292
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 392
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCLR (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1528
MultiPage Allocator 43032
(7 row(s) affected)
MEMORYCLERK_SQLCLR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 6313024
VM Committed 263292
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1920
MultiPage Allocator 43032
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 80
MultiPage Allocator 304
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 80
MultiPage Allocator 304
(7 row(s) affected)
MEMORYCLERK_SQLHTTP (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLHTTP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SNI (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 184
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_SNI (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 168
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_SNI (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 352
MultiPage Allocator 32
(7 row(s) affected)
MEMORYCLERK_FULLTEXT (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_FULLTEXT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLXP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_BHF (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 208
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_BHF (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 176
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_BHF (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 384
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1024
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLQERESERVATIONS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1024
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_HOST (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 48
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_HOST (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 48
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 18848
MultiPage Allocator 11896
(7 row(s) affected)
MEMORYCLERK_SOSNODE (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 19024
MultiPage Allocator 6368
(7 row(s) affected)
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 37872
MultiPage Allocator 18264
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 48
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 48
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_OBJCP (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 196336
MultiPage Allocator 4832
(7 row(s) affected)
CACHESTORE_OBJCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 196336
MultiPage Allocator 4832
(7 row(s) affected)
CACHESTORE_SQLCP (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2534288
MultiPage Allocator 568
(7 row(s) affected)
CACHESTORE_SQLCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2534288
MultiPage Allocator 568
(7 row(s) affected)
CACHESTORE_PHDR (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 31200
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_PHDR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 31200
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XPROC (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 104
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XPROC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 104
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 168
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_TEMPTABLES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 168
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_NOTIF (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_NOTIF (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_VIEWDEFINITIONS (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_VIEWDEFINITIONS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBTYPE (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBTYPE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBELEMENT (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBELEMENT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBATTRIBUTE (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBATTRIBUTE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_STACKFRAMES (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_STACKFRAMES (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 16
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 64
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERKEK (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERKEK (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERDSH (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERDSH (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERRSB (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERRSB (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERREADONLY (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERREADONLY (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTO (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTO (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_EVENTS (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_EVENTS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_CLRPROC (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_CLRPROC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2176
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2176
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 6664
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SCHEMAMGR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 6664
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_DBMETADATA (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 9800
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_DBMETADATA (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 9800
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 29776
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 29776
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_OBJPERM (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3616
MultiPage Allocator 32
(7 row(s) affected)
USERSTORE_OBJPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3616
MultiPage Allocator 32
(7 row(s) affected)
USERSTORE_SXC (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 808
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 776
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1584
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LBSS (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 480
(7 row(s) affected)
OBJECTSTORE_LBSS (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 528
(7 row(s) affected)
OBJECTSTORE_LBSS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 256
MultiPage Allocator 1008
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3024
MultiPage Allocator 48
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3312
MultiPage Allocator 48
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 6336
MultiPage Allocator 96
(7 row(s) affected)
OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 272
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_SERVICE_BROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 272
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 131072
VM Committed 131072
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 67760
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 67448
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 131072
VM Committed 131072
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 135208
MultiPage Allocator 0
(7 row(s) affected)
Buffer Distribution Buffers
------------------------------ -----------
Stolen 10902
Free 709626
Cached 369753
Database (clean) 1011473
Database (dirty) 91448
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
------------------------------ --------------------
Committed 2193203
Target 4076566
Hashed 1102922
Stolen Potential 4100605
External Reservation 126
Min Free 512
Visible 4076566
Available Paging File 8341347
(8 row(s) affected)
Procedure Cache Value
------------------------------ -----------
TotalProcs 36953
TotalPages 345916
InUsePages 260
(3 row(s) affected)
Global Memory Objects Buffers
------------------------------ --------------------
Resource 666
Locks 16904
XDES 246
SETLS 16
SE Dataset Allocators 32
SubpDesc Allocators 16
SE SchemaManager 831
SQLCache 4147
Replication 2
ServerGlobal 49
XP Global 2
SortTables 2
(12 row(s) affected)
Query Memory Objects Value
------------------------------ -----------
Grants 1
Waiting 0
Available (Buffers) 3050462
Maximum (Buffers) 3050590
Limit 3050590
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 3063390
(11 row(s) affected)
Small Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 12800
Maximum (Buffers) 12800
Limit 12800
(5 row(s) affected)
Optimization Queue Value
------------------------------ --------------------
Overall Memory 26768351232
Target Memory 22448848896
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway Value
------------------------------ --------------------
Configured Units 64
Available Units 64
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
(6 row(s) affected)
Medium Gateway Value
------------------------------ --------------------
Configured Units 16
Available Units 16
Acquires 0
Waiters 0
Threshold Factor 12
(5 row(s) affected)
Big Gateway Value
------------------------------ --------------------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
(5 row(s) affected)
MEMORYBROKER_FOR_CACHE Value
-------------------------------- --------------------
Allocations 369755
Rate -3
Target Allocations 3099204
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_STEAL Value
-------------------------------- --------------------
Allocations 10893
Rate -7
Target Allocations 2740338
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_RESERVE Value
-------------------------------- --------------------
Allocations 0
Rate -38
Target Allocations 3267621
Future Allocations 762647
Last Notification 1
(5 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
January 7, 2012 at 9:54 pm
Hi Guys,
I still have difficulty interpreting the output after reading up the dbcc memory status relevant documents. Can anyone help me with this? Appreciate your time on this.
dbcc memory status output attached.
thanks!
My question:
From the perfmon, I can see that sql server is using 16GB of memory <perfmon, total server memory>.
From the below URLs, I have managed to find out the memory used by data (buffer) and query plans (cache).
Buffer:8.2GB
Cache: 1.5G
The above adds up to near 10GB. May i know what is utilizing the remaining 6GB? Anyway to find out?
http://www.sqlteam.com/article/what-data-is-in-sql-server-memory
http://www.sqlteam.com/article/what-query-plans-are-in-sql-server-memory
January 9, 2012 at 6:09 am
1) why do you want to know the various internal memory consumption? There is almost nothing you can do about it, and the knowledge is VERY unlikely to help you with any issue you might be having on the machine.
2) If you REALLY want to know, buy one of the SQL Server Internals books. Probably your best resource.
3) If you are having a particular issue you are trying to troubleshoot, please post that and see if we can help you. The odds of it being a memory issue (Other than not having ENOUGH of it) is very slim.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 9, 2012 at 8:48 am
Hi Kevin,
Appreciate response on this.
You may ignore what I have posted above.
The actual problem here i'm facing here is with an instance with multiple databases.
From the total server memory (perfmon), i can see that sql server is utilizating alot of memory <making the memory utilization very high).
I also understand that sql server will use as much memory it needed to cache the data and the plan.
I'm tasked to identify the most memory utilized database so as to see if we can migrate it out to another very lowly utilized database server.
I executed this statement:
SELECT LEFT(CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END, 20) AS Database_Name,
count(*)AS Buffered_Page_Count,
count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Buffered_Page_Count DESC
However the total alot lesser than the total server memory. As such, I'm pondering where did the rest of the memory goes.
I tried adding the 4 perfmon counter together as well, but still very much lesser than the total server memory.
sqlserver:buffermanager:databasepages
sqlserver:plan cache:cachepages
sqlserver:memorymanager:connectionmemory
sqlserver:memorymanager:sqlcachememory
Would like to understand more on this so that i can better provide advise to my management.
thanks !
January 9, 2012 at 8:54 am
SQL will use as much memory as you allow it to use. If you feel that an instance is using too much, lower it's memory limit.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2012 at 8:59 am
Hi Gila,
thanks for ur response. Yes agreed.
But basically i wish to find out which of the db in the instance is utilizing the most memory. Anyway to do it?
thanks
January 9, 2012 at 9:05 am
SQL doesn't segregate memory per database (except in terms of the data cache). You can check which DB uses up the majority of the data cache, you might be able to get a rough idea of the databases that the various plans in the plan cache belong to, providing that they're object plans not ad-hoc SQL. Other than that though, not so much.
In addition to the data and plan cache, there are other caches, there's workspace memory, there's thread stacks and a whole lot of other stuff.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2012 at 10:08 am
Note that which database is taking up buffer pool space can change drastically from minute to minute. I can run one big report-style query and flush just about everything but that data out of the buffer pool.
There is MUCH more to getting good performance from a database than finding out what is using lots of RAM. You could very well wind up putting one of your databases on a different underutilized server and have it's performance be MUCH worse depending on the config, power and especially IO underpinnings of the new box.
I would recommend you get a quick performance review by a professional to assist you in making good decisions here. He/she can likely find LOTS of issues to remediate. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply