SQL Server 2008 - High Memory Usage
hi ,
facing problem high memory usage sql server 2008. 64 bit , standard edition sp2 . 8 gb ram , 8 processors .
memory usage keep on growing ..sometimes reach 7.5 gb .... , not coming down ..untill restart service...
these details of dbcc memorystatus :: please suggest how overcome problem
memory manager kb
---------------------------------------- -----------
vm reserved 8484792
vm committed 4220368
locked pages allocated 0
reserved memory 1024
reserved memory in use 0
memory node id = 0 kb
---------------------------------------- -----------
vm reserved 8481144
vm committed 4216832
locked pages allocated 0
multipage allocator 36464
singlepage allocator 2687456
memory node id = 64 kb
---------------------------------------- -----------
vm reserved 2560
vm committed 2504
locked pages allocated 0
multipage allocator 2416
singlepage allocator 2687456
memoryclerk_sqlgeneral (node 0) kb
---------------------------------------- -----------
vm reserved 0
vm committed 0
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 3520
multipage allocator 3096
memoryclerk_sqlbufferpool (node 0) kb
---------------------------------------- -----------
vm reserved 8413184
vm committed 4150528
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 0
multipage allocator 400
memoryclerk_sqlqueryexec (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
memoryclerk_sqlstoreng (node 0) kb
---------------------------------------- -----------
vm reserved 3520
vm committed 3520
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 2592
multipage allocator 4416
memoryclerk_sqlconnectionpool (node 0) kb
---------------------------------------- -----------
vm reserved 0
vm committed 0
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 544
multipage allocator 0
memoryclerk_xe_buffer (node 0) kb
---------------------------------------- -----------
vm reserved 4224
vm committed 4224
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 0
multipage allocator 0
memoryclerk_sosnode (node 0) kb
---------------------------------------- -----------
vm reserved 0
vm committed 0
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 7480
multipage allocator 13384
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 2336
memoryclerk_sosnode (total) kb
---------------------------------------- -----------
vm reserved 0
vm committed 0
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 7488
multipage allocator 15720
memoryclerk_fulltext_shmem (node 0) kb
---------------------------------------- -----------
vm reserved 0
vm committed 0
locked pages allocated 0
sm reserved 192
sm committed 192
singlepage allocator 0
multipage allocator 0
memoryclerk_xe (node 0) kb
---------------------------------------- -----------
vm reserved 0
vm committed 0
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 88
multipage allocator 392
cachestore_objcp (node 0) kb
---------------------------------------- -----------
vm reserved 0
vm committed 0
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 97800
multipage allocator 3920
cachestore_sqlcp (node 0) kb
---------------------------------------- -----------
vm reserved 0
vm committed 0
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 2531360
multipage allocator 7912
cachestore_phdr (node 0) kb
---------------------------------------- -----------
vm reserved 0
vm committed 0
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 8480
multipage allocator 16
cachestore_xproc (node 0) kb
---------------------------------------- -----------
vm reserved 0
vm committed 0
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 120
multipage allocator 0
cachestore_temptables (node 0) kb
---------------------------------------- -----------
vm reserved 0
vm committed 0
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 384
multipage allocator 0
userstore_schemamgr (node 0) kb
---------------------------------------- -----------
vm reserved 0
vm committed 0
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 6080
multipage allocator 152
userstore_dbmetadata (node 0) kb
---------------------------------------- -----------
vm reserved 0
vm committed 0
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 6264
multipage allocator 0
objectstore_lock_manager (node 0) kb
---------------------------------------- -----------
vm reserved 16384
vm committed 16384
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 16336
multipage allocator 0
objectstore_lock_manager (total) kb
---------------------------------------- -----------
vm reserved 16384
vm committed 16384
locked pages allocated 0
sm reserved 0
sm committed 0
singlepage allocator 16360
multipage allocator 0
buffer pool value
---------------------------------------- -----------
committed 509696
target 806589
database 160915
dirty 5734
in io 0
latched 0
free 12849
stolen 335932
reserved 0
visible 806589
stolen potential 659008
limiting factor 13
last oom factor 0
page life expectancy 75735
process/system counts value
---------------------------------------- --------------------
available physical memory 2912673792
available virtual memory 8787090063360
available paging file 11828006912
working set 4389765120
percent of committed memory in ws 100
page faults 5448707
system physical memory high 1
system physical memory low 0
process physical memory low 0
process virtual memory low 0
procedure cache value
---------------------------------------- -----------
totalprocs 15595
totalpages 331201
inusepages 11081
global memory objects pages
---------------------------------------- -----------
resource 376
locks 2045
xdes 49
setls 8
se dataset allocators 16
subpdesc allocators 8
se schemamanager 364
se column metadata cache 414
sqlcache 2640
replication 2
serverglobal 50
xp global 2
sorttables 35
query memory objects (internal) value
---------------------------------------- -----------
grants 0
waiting 0
available 390625
current max 390625
future max 390625
physical max 593320
next request 0
waiting for 0
cost 0
timeout 0
wait time 0
small query memory objects (internal) value
---------------------------------------- -----------
grants 0
waiting 0
available 12800
current max 12800
future max 12800
optimization queue (internal) value
---------------------------------------- --------------------
overall memory 5296381952
target memory 2325504000
last notification 1
timeout 6
termination factor 5
small gateway (internal) value
---------------------------------------- -----------
configured units 32
available units 32
acquires 0
waiters 0
threshold factor 380000
threshold 380000
memory pool manager pages
---------------------------------------- -----------
reserved current 0
reserved limit 430327
memory pool (internal) pages
---------------------------------------- -----------
allocations 335932
predicted 484267
private target 0
private limit 0
total target 766259
total limit 766259
oom count 0
memorybroker_for_cache (internal) pages
---------------------------------------- -----------
allocations 334051
rate 3
target allocations 616046
future allocations 0
overall 646531
last notification 1
memorybroker_for_steal (internal) pages
---------------------------------------- -----------
allocations 1881
rate 2
target allocations 283875
future allocations 0
overall 646531
last notification 1
memorybroker_for_reserve (internal) pages
---------------------------------------- -----------
allocations 0
rate 0
target allocations 430322
future allocations 148330
overall 646531
last notification 1
-- reddy balaji c. ##mark answer if solves issue
hi ,
facing problem high memory usage sql server 2008. 64 bit , standard edition sp2 . 8 gb ram , 8 processors .
memory usage keep on growing ..sometimes reach 7.5 gb .... , not coming down ..untill restart service...
sql designed keep data in memory as possible avoid disk io.
limit memory max server memory setting.
balmukund lakhani | please mark solved if i've answered question
--------------------------------------------------------------------------------
posting provided "as is" no warranties, , confers no rights.
--------------------------------------------------------------------------------
blog: http://blogs.msdn.com/blakhani
team blog: http://blogs.msdn.com/sqlserverfaq
SQL Server > SQL Server Database Engine
Comments
Post a Comment