SQL Server Performance Forum – Threads Archive

sql server memory issue

Can anyone help me on this memory issue?
memory configuration issue: one server box(win2003 EE sp1) with 8GB ram, win 2003 sp1 and sql sever 2000 EE sp 3 installed. I have done: – added /3GB switch and /PAE switch in boot.ini file
– has switched on the â€˜AWE#%92 switch by using sp_configure on sql server ( checked) Symption: Now server is extremely busy but memory usage is stable on 2.87G ( for more than one month), As I know sql server will try to use as much memory as possible but why in the task manager I can see the available memory more than 5GB? Have checked some reference here, but couldn#%92t find the answer, please help. thanks heaps!!

KBAs and articles for your information.
http://support.microsoft.com/?id=274750
http://support.microsoft.com/default.aspx?kbid=899761 http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=7773
http://www.sql-server-performance.com/awe_memory.asp Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

satya: I have checked all the posts you recommend and actually I have read them all before I post my issue. The thing is ; – the sql server 2000 I am using is sp3 not sp4, so it shouldn’t be caused by the sp4 bug
– the OS is windows 2003 EE sp1, so the OS shouldn’t have the problem of memory limit as widows 2000
standard edition
– double checked, the ‘AWE’, ‘3GB’ and ‘PAE’ switch are opened on sql server and OS ( and restarted
server)
– I have run the performance counter, the ‘target memory ‘ and ‘total memory’ of sql server 2000 are
both 2.8Gb, which doesn’t make sense if the switches are working. So my final question is why only 2.8GB memory is in use although I have 8GB physical memory. BTW, the server is very busy with 200 concurrent users and it keeps using 2.8Gb memory for a couple of weeks although we endured perforamnce issue during that period. ( request time out) Anyone else have idea please help!!!

Skip the /3GB, use /PAE only. Also have you set a max memory in sql server? You can’t use dynamic setting: sp_configure ‘max server memory’, 7000
RECONFIGURE
GO

Yes, I have set the maximum to 7585MB. and the minimum is 1200MB. I can change it to 7000Mb ( obviously one GB is taken by OS) but I don’t think there will be any difference

I have changed it to 7000Mb.
Still it doesn’t take the memory sql server needs to use.

SQL Server will take memory when it is required, check the usage of memory and other resources using PERFMON. BTW how big is the database and activity from application? Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

The database is pretty big, about 500,000 to 1million transactions/day. It got some performance issue last week but the memory usage stays same, which doesn’t make sense.

Have you rebooted or restarted the services after you made changes to the settings, any performance issue after these settings. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Yes, all rebooted, but still the memory usage is lower than 3GB although the server load is high

Can you paste
sp_configure out put here? -Johnson

See this relevanthttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11005 post and follow as suggested. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Johnson: the details are: affinity mask-2147483648214748364700
allow updates0100
awe enabled0111
c2 audit mode0100
cost threshold for parallelism03276777
Cross DB Ownership Chaining0100
cursor threshold-12147483647-1-1
default full-text language0214748364710331033
default language0999900
fill factor (%)010000
index create memory (KB)704214748364700
lightweight pooling0100
locks5000214748364700
max degree of parallelism03200
max server memory (MB)4214748364770007000
max text repl size (B)021474836476553665536
max worker threads3232767500255
media retention036500
min memory per query (KB)512214748364740964096
min server memory (MB)0214748364712191219
nested triggers0111
network packet size (B)5126553640964096
open objects0214748364700
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
scan for startup procs0100
set working set size0111
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276700

satya: I have checked that. but now my sql server is still only using 2.8Gb our of 8GB. regards,

hi, I think SQL is taking the memory what it required. Secondly, your SQL Server shows busy, may not be because of your RAM availability. It may be because of DISk I/O or any other factors. If SQL is not taking the memory from the available resources, it won’t go upto 2.8GB consumption. It will stick to 2GB only or less. That means, SQL Server is taking RAM what is required for it.
One observation is about the working set.
in your case, "set working set size" is enabled.
BOL Says:
—————————
SQL Server can lock memory as a working set. Because memory is locked, you can receive out of memory errors when running other applications. If out-of-memory errors occur, you may have too much memory assigned to SQL Server. The set working set size option (set with sp_configure or SQL Server Enterprise Manager) can disable the locking of memory as a working set. By default, set working set size is disabled.
————————— Second observation is about "priority boost":
If this server is dedicated to SQL Server (if no other application is running) you can go for this option , this will surly boost the performance. I have some doubt on the "parallelism threshold" from the details. What level of parallelism are you using? I think you are assigned more memory for per query. which may also hold resource from the memory.
Let it take the resource what it has. -Johnson

]]>