June 22, 2011 at 9:21 am
Hi,
Can you please let me know what the issue was . why it was taking 96% of physical memory
OS : WINDOWS Server 2008 ENT 64
SQL SERVER : SQL Server 2008 r2 cu6
Lock pages in memory : enabled
cpu utilization < 10 %
Target server Memory = Total server Memory
Task Manager : Performance results
Physical memory(MB)
total : 98292
cached: 4892
free : 47
Kernel Memory (MB)
Total:831
Paged :266
NonPaged:564
System:
Handles : 42741
Threads: 2054
Processes:135
Uptime :362:05:20
Page file:94654M/101785
dbcc memorystatus results
Memory Manager KB
---------------------------------------- -----------
VM Reserved 101302248
VM Committed 562156
Locked Pages Allocated 91397904
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
---------------------------------------- -----------
VM Reserved 76928
VM Committed 76512
Locked Pages Allocated 0
MultiPage Allocator 9384
SinglePage Allocator 2943232
(5 row(s) affected)
Memory node Id = 1 KB
---------------------------------------- -----------
VM Reserved 101221608
VM Committed 482056
Locked Pages Allocated 91397904
MultiPage Allocator 81976
SinglePage Allocator 2943232
(5 row(s) affected)
Memory node Id = 64 KB
---------------------------------------- -----------
VM Reserved 2624
VM Committed 2552
Locked Pages Allocated 0
MultiPage Allocator 2472
SinglePage Allocator 2943232
(5 row(s) affected)
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 3312
MultiPage Allocator 6760
(7 row(s) affected)
MEMORYCLERK_SQLGENERAL (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 136
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 3448
MultiPage Allocator 6760
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 100810752
VM Committed 131072
Locked Pages Allocated 91347152
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 2200
(7 row(s) affected)
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 144
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 624
MultiPage Allocator 928
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- -----------
VM Reserved 360
VM Committed 360
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 144
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (Total) KB
---------------------------------------- -----------
VM Reserved 360
VM Committed 360
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 160
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- -----------
VM Reserved 50752
VM Committed 0
Locked Pages Allocated 50752
SM Reserved 0
SM Committed 0
SinglePage Allocator 5384
MultiPage Allocator 30440
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 672
MultiPage Allocator 4568
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------- -----------
VM Reserved 50752
VM Committed 0
Locked Pages Allocated 50752
SM Reserved 0
SM Committed 0
SinglePage Allocator 6056
MultiPage Allocator 35008
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 528
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 344
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 872
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 192
MultiPage Allocator 544
(7 row(s) affected)
MEMORYCLERK_SQLHTTP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SNI (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 40
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_SNI (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 40
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_SNI (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_SNI (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 80
MultiPage Allocator 48
(7 row(s) affected)
MEMORYCLERK_FULLTEXT (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_BHF (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 136
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_BHF (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_BHF (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 152
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 262960
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_XE_BUFFER (node 0) KB
---------------------------------------- -----------
VM Reserved 2816
VM Committed 2816
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_XE_BUFFER (node 1) KB
---------------------------------------- -----------
VM Reserved 1408
VM Committed 1408
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_XE_BUFFER (Total) KB
---------------------------------------- -----------
VM Reserved 4224
VM Committed 4224
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_HOST (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 96
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 13296
MultiPage Allocator 15360
(7 row(s) affected)
MEMORYCLERK_SOSNODE (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 10744
MultiPage Allocator 4280
(7 row(s) affected)
MEMORYCLERK_SOSNODE (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 2392
(7 row(s) affected)
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24048
MultiPage Allocator 22032
(7 row(s) affected)
MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 384
SM Committed 384
SinglePage Allocator 0
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 48
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_XE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 592
MultiPage Allocator 168
(7 row(s) affected)
CACHESTORE_OBJCP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 273784
MultiPage Allocator 16568
(7 row(s) affected)
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 426296
MultiPage Allocator 6800
(7 row(s) affected)
CACHESTORE_PHDR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 98968
MultiPage Allocator 136
(7 row(s) affected)
CACHESTORE_XPROC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 208
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 128
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_NOTIF (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_VIEWDEFINITIONS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBTYPE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBELEMENT (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBATTRIBUTE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_STACKFRAMES (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_STACKFRAMES (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_STACKFRAMES (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 24
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 736
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 744
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERKEK (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERDSH (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERRSB (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERREADONLY (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 80
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTO (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_EVENTS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4200
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 664
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4864
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_CONVPRI (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 144
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_CONVPRI (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_CONVPRI (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 160
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_FULLTEXTSTOPLIST (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 10736
MultiPage Allocator 1088
(7 row(s) affected)
USERSTORE_DBMETADATA (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 28592
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_DBMETADATA (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 768
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_DBMETADATA (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 29360
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1880
MultiPage Allocator 616
(7 row(s) affected)
USERSTORE_TOKENPERM (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 816
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2696
MultiPage Allocator 616
(7 row(s) affected)
USERSTORE_OBJPERM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2576
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_OBJPERM (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 272
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_OBJPERM (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2848
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 80
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 56
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 144
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LBSS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 128
MultiPage Allocator 288
(7 row(s) affected)
OBJECTSTORE_LBSS (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 128
MultiPage Allocator 256
(7 row(s) affected)
OBJECTSTORE_LBSS (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 256
MultiPage Allocator 544
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1144
MultiPage Allocator 56
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1064
MultiPage Allocator 56
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 56
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2208
MultiPage Allocator 168
(7 row(s) affected)
OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 488
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- -----------
VM Reserved 262144
VM Committed 262144
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1063736
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1053800
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- -----------
VM Reserved 262144
VM Committed 262144
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2117560
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_SECAUDIT_EVENT_BUFFER (node 0) KB
------------------------------------------ -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
Buffer Pool Value
---------------------------------------- -----------
Committed 11141120
Target 11141120
Database 10772880
Dirty 101378
In IO 0
Latched 4
Free 336
Stolen 367904
Reserved 32870
Visible 11141120
Stolen Potential 10183290
Limiting Factor 17
Last OOM Factor 0
Page Life Expectancy 766
(14 row(s) affected)
Process/System Counts Value
---------------------------------------- --------------------
Available Physical Memory 3944476672
Available Virtual Memory 8691610398720
Available Paging File 7407181824
Working Set 385658880
Percent of Committed Memory in WS 100
Page Faults 86101478
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0
(10 row(s) affected)
Procedure Cache Value
---------------------------------------- -----------
TotalProcs 7414
TotalPages 102845
InUsePages 1098
(3 row(s) affected)
Global Memory Objects Pages
---------------------------------------- -----------
Resource 574
Locks 264695
XDES 272
SETLS 16
SE Dataset Allocators 32
SubpDesc Allocators 16
SE SchemaManager 512
SE Column Metadata Cache 770
SQLCache 1033
Replication 2
ServerGlobal 54
XP Global 2
SortTables 3691
(13 row(s) affected)
Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 7546418
Current Max 7546418
Future Max 7546418
Physical Max 8282048
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
(11 row(s) affected)
Small Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 90112
Current Max 90112
Future Max 90112
(5 row(s) affected)
Query Memory Objects (default) Value
---------------------------------------- -----------
Grants 1
Waiting 0
Available 8249178
Current Max 8282048
Future Max 8282048
Physical Max 8282048
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
(11 row(s) affected)
Small Query Memory Objects (default) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 90112
Current Max 90112
Future Max 90112
(5 row(s) affected)
Optimization Queue (internal) Value
---------------------------------------- --------------------
Overall Memory 73157050368
Target Memory 66736431104
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway (internal) Value
---------------------------------------- -----------
Configured Units 64
Available Units 64
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
(6 row(s) affected)
Medium Gateway (internal) Value
---------------------------------------- -----------
Configured Units 16
Available Units 16
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
(6 row(s) affected)
Big Gateway (internal) Value
---------------------------------------- -----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
(6 row(s) affected)
Optimization Queue (default) Value
---------------------------------------- --------------------
Overall Memory 73157050368
Target Memory 66747170816
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway (default) Value
---------------------------------------- -----------
Configured Units 64
Available Units 64
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
(6 row(s) affected)
Medium Gateway (default) Value
---------------------------------------- -----------
Configured Units 16
Available Units 16
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
(6 row(s) affected)
Big Gateway (default) Value
---------------------------------------- -----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
(6 row(s) affected)
Memory Pool Manager Pages
---------------------------------------- -----------
Reserved Current 32870
Reserved Limit 10216160
(2 row(s) affected)
Memory Pool (internal) Pages
---------------------------------------- -----------
Allocations 4740
Predicted 4740
Private Target 0
Private Limit 0
Total Target 10584064
Total Limit 10584064
OOM Count 0
(7 row(s) affected)
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- -----------
Allocations 3847
Rate 0
Target Allocations 8149491
Future Allocations 0
Overall 8930304
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- -----------
Allocations 893
Rate 0
Target Allocations 8146537
Future Allocations 0
Overall 8930304
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- -----------
Allocations 0
Rate 0
Target Allocations 8145644
Future Allocations 0
Overall 8930304
Last Notification 1
(6 row(s) affected)
Memory Pool (default) Pages
---------------------------------------- -----------
Allocations 396034
Predicted 2433680
Private Target 0
Private Limit 0
Total Target 10584064
Total Limit 10584064
OOM Count 0
(7 row(s) affected)
MEMORYBROKER_FOR_CACHE (default) Pages
---------------------------------------- -----------
Allocations 360962
Rate 2
Target Allocations 8506608
Future Allocations 0
Overall 8930304
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_STEAL (default) Pages
---------------------------------------- -----------
Allocations 2202
Rate 2
Target Allocations 8147848
Future Allocations 0
Overall 8930304
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_RESERVE (default) Pages
---------------------------------------- -----------
Allocations 32870
Rate 0
Target Allocations 8930304
Future Allocations 2070512
Overall 8930304
Last Notification 1
Thank You,
June 22, 2011 at 9:25 am
have you set max memory in SQL Server?
---------------------------------------------------------------------
June 22, 2011 at 9:27 am
yes it was set to 10 gb less than physical memory
June 22, 2011 at 9:46 am
that setting covers just the buffer pool, there is also proc cache, locks, connections.
you need to monitor process:private bytes and working set for the SQL server process.
Remember SQL loves memory, if you allocate memory to it it will use it, it is designed that way.
---------------------------------------------------------------------
June 22, 2011 at 10:01 am
george sibbald (6/22/2011)
that setting covers just the buffer pool, there is also proc cache, locks, connections.
I'll add to the list of non-buffer-pool memory used by SQL Server: memory used by Linked Servers & SQLCLR, both of which can be huge sources of memory consumption depending on what the code is doing...there are others out there as well.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 22, 2011 at 10:03 am
Here are the requested readings
Process sqlservr
Private Bytes 82026496.000
Working Set 46809088.000
Are you referring to these readings.
Please let me know
Thank You,
June 22, 2011 at 10:07 am
opc.three (6/22/2011)
george sibbald (6/22/2011)
that setting covers just the buffer pool, there is also proc cache, locks, connections.I'll add to the list of non-buffer-pool memory used by SQL Server: memory used by Linked Servers & SQLCLR, both of which can be huge sources of memory consumption depending on what the code is doing...there are others out there as well.
yes, I should have added etc, thats all I could think of off the top of my head.
---------------------------------------------------------------------
June 22, 2011 at 10:09 am
sql2k8 (6/22/2011)
Here are the requested readingsProcess sqlservr
Private Bytes 82026496.000
Working Set 46809088.000
Are you referring to these readings.
Please let me know
Thank You,
those are the readings.
do you have an issue or are you just wanting to know why most of your memory was being used. SQL tends to take memory if its allocated, thats usually a good thing, saves IO.
---------------------------------------------------------------------
June 22, 2011 at 10:15 am
Can you please tell me where i can locate them.
June 22, 2011 at 10:18 am
I want to know why it was taking that much memory and why it was not dropping from that limit 96 % . It was constant from yesterday afternoon.
June 22, 2011 at 12:56 pm
96.44 mb of memory was used by the non buffer pool components
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply