December 4, 2007 at 8:59 am
I've got a standalone Microsoft SQL Server 2005 - 9.00.3042.00 (X64) running on windows server 2003 x64 SP2 with 4 dual core 3Ghz CPU and 8GB of RAM.
SQL setup to use all 8GB of memory and
We have a database with (sql 2000 compatibility level) which is used by 3rd party application.
The server memory goes up to 5 GB and stays there and the server starts to slow down and eventually becomes unresponsive at which time we have to restart the server.
Also i read about Grant 'Lock Pages In memory' right to the service account but i'm not sure where to do that.
Any help is appreciated.
Here is the dbcc memorystatus and sp_configure
Memory Manager KB
------------------------------ --------------------
VM Reserved 8501472
VM Committed 3158272
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
Memory node Id = 0 KB
------------------------------ --------------------
VM Reserved 8495712
VM Committed 3152600
AWE Allocated 0
MultiPage Allocator 62936
SinglePage Allocator 2649272
MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 49824
MultiPage Allocator 4504
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 8413184
VM Committed 3070720
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8
MEMORYCLERK_SQLOPTIMIZER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 600
MultiPage Allocator 128
MEMORYCLERK_SQLUTILITIES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 480
VM Committed 480
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 88
MultiPage Allocator 0
MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 960
VM Committed 960
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1416
MultiPage Allocator 304
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 760
MultiPage Allocator 0
MEMORYCLERK_SQLCLR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
MEMORYCLERK_SQLSERVICEBROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 320
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
MEMORYCLERK_SNI (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 80
MultiPage Allocator 16
MEMORYCLERK_FULLTEXT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 160
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
MEMORYCLERK_HOST (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 17088
MultiPage Allocator 13456
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
CACHESTORE_OBJCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 41664
MultiPage Allocator 416
CACHESTORE_SQLCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2501808
MultiPage Allocator 42440
CACHESTORE_PHDR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 17728
MultiPage Allocator 64
CACHESTORE_XPROC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 256
MultiPage Allocator 0
CACHESTORE_TEMPTABLES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 56
MultiPage Allocator 0
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
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
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
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
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
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8
CACHESTORE_BROKERTBLACS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 456
MultiPage Allocator 0
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
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
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
CACHESTORE_BROKERRSB (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
CACHESTORE_BROKERREADONLY (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 104
MultiPage Allocator 0
CACHESTORE_BROKERTO (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
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
CACHESTORE_SYSTEMROWSET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1328
MultiPage Allocator 0
USERSTORE_SCHEMAMGR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2880
MultiPage Allocator 0
USERSTORE_DBMETADATA (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3056
MultiPage Allocator 0
USERSTORE_TOKENPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 280
MultiPage Allocator 0
USERSTORE_OBJPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 104
MultiPage Allocator 0
USERSTORE_SXC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 152
MultiPage Allocator 0
OBJECTSTORE_LBSS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 256
MultiPage Allocator 880
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2216
MultiPage Allocator 48
OBJECTSTORE_SERVICE_BROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 288
MultiPage Allocator 0
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 16384
VM Committed 16384
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 6392
MultiPage Allocator 0
Buffer Distribution Buffers
------------------------------ -----------
Stolen 8766
Free 2159
Cached 322393
Database (clean) 39882
Database (dirty) 3632
I/O 0
Latched 0
Buffer Counts Buffers
------------------------------ --------------------
Committed 376832
Target 759153
Hashed 43514
Stolen Potential 629486
External Reservation 0
Min Free 512
Visible 759153
Available Paging File 634374
Procedure Cache Value
------------------------------ -----------
TotalProcs 51726
TotalPages 325547
InUsePages 3218
Global Memory Objects Buffers
------------------------------ --------------------
Resource 258
Locks 802
XDES 56
SETLS 16
SE Dataset Allocators 32
SubpDesc Allocators 16
SE SchemaManager 359
SQLCache 6125
Replication 2
ServerGlobal 48
XP Global 2
SortTables 2
Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 352870
Maximum (Buffers) 352870
Limit 352855
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 365655
Small Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 12800
Maximum (Buffers) 12800
Limit 12800
Optimization Queue Value
------------------------------ --------------------
Overall Memory 4984823808
Target Memory 2124873728
Last Notification 1
Timeout 6
Early Termination Factor 5
Small Gateway Value
------------------------------ --------------------
Configured Units 64
Available Units 64
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
Medium Gateway Value
------------------------------ --------------------
Configured Units 16
Available Units 16
Acquires 0
Waiters 0
Threshold Factor 12
Big Gateway Value
------------------------------ --------------------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
MEMORYBROKER_FOR_CACHE Value
-------------------------------- --------------------
Allocations 322395
Rate 0
Target Allocations 573019
Future Allocations 0
Last Notification 1
MEMORYBROKER_FOR_STEAL Value
-------------------------------- --------------------
Allocations 8760
Rate 0
Target Allocations 259384
Future Allocations 0
Last Notification 1
MEMORYBROKER_FOR_RESERVE Value
-------------------------------- --------------------
Allocations 0
Rate 0
Target Allocations 390040
Future Allocations 139416
Last Notification 1
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
NameMinimumMaximumConfig_valueRunning_value
Ad Hoc Distributed Queries0100
affinity I/O mask-2147483648214748364700
affinity mask-2147483648214748364700
affinity64 I/O mask-2147483648214748364700
affinity64 mask-2147483648214748364700
Agent XPs0111
allow updates0100
awe enabled0100
blocked process threshold08640000
c2 audit mode0100
clr enabled0111
cost threshold for parallelism03276755
cross db ownership chaining0100
cursor threshold-12147483647-1-1
Database Mail XPs0111
default full-text language0214748364710331033
default language0999900
default trace enabled0111
disallow results from triggers0100
fill factor (%)010000
ft crawl bandwidth (max)032767100100
ft crawl bandwidth (min)03276700
ft notify bandwidth (max)032767100100
ft notify bandwidth (min)03276700
index create memory (KB)704214748364700
in-doubt xact resolution0200
lightweight pooling0100
locks5000214748364700
max degree of parallelism06400
max full-text crawl range025644
max server memory (MB)16214748364721474836472147483647
max text repl size (B)021474836476553665536
max worker threads1283276700
media retention036500
min memory per query (KB)512214748364720482048
min server memory (MB)02147483647016
nested triggers0111
network packet size (B)5123276740964096
Ole Automation Procedures0100
open objects0214748364700
PH timeout (s)136006060
precompute rank0100
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote admin connections0100
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
Replication XPs0100
scan for startup procs0100
server trigger recursion0111
set working set size0100
show advanced options0111
SMO and DMO XPs0111
SQL Mail XPs0111
transform noise words0100
two digit year cutoff1753999920492049
user connections03276700
user options03276700
Web Assistant Procedures0100
xp_cmdshell 0111
December 4, 2007 at 12:59 pm
check this out http://msdn2.microsoft.com/en-us/library/ms190730.aspx
December 4, 2007 at 1:51 pm
local security policy on the server in the user rights assignment
add the account that SQL is running under
December 4, 2007 at 2:35 pm
We run a similar configuration here and Micorosft PSS has recommended to set max. server memory to 6GB.
December 5, 2007 at 7:58 pm
Thank you all for helping me out
bbychkov looks like sql server is flushing the memory properly after setting the max to 6GB.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply