SQL Server Performance Forum – Threads Archive

Memory is limiting to 4GB

Hi There
I am after a bit of help with Memory Usage on SQL 2000
The configuration
SQL 2000 EE SP4
Windows 2003 R2 EE SP2
SQL is configured on Active/Passive Failover
Active Server has 8GB RAM No matter what options I select SQL will not use more than 4GB of RAM
It starts up with exactly 4Gb, but it will not use anymore no matter what the load. AWE is enabled
/PAE /3GB are on (even tried without /3GB)
Perf counters show it also uses exaclty 4GB and it wont change.
Have played with many options for Max and Min Memory. SQL Service Account has Lock Pages in Memory I have read all the articles here and in many other places to make this work, but why won’t it use more than 4GB RAM
Is there a bug that I am not aware of?
This server is dedicated for SQL.
I am really getting stuck here, any help appreciated Thankyou in advance Dean
affinity mask-2147483648214748364700
allow updates0100
awe enabled0111
c2 audit mode0100
cost threshold for parallelism03276755
Cross DB Ownership Chaining0111
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)4214748364775087508
max text repl size (B)021474836476553665536
max worker threads3232767255255
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364766206620
nested triggers0111
network packet size (B)5123276740964096
open objects0214748364700
priority boost0111
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 size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276700

---

Have you collected the stats using PERFMON counters for memory? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

the relevent counters for this are below
you did not mention what specific counter you were citing at 4G
hence we cannot tell you anything meaningful
SQLServer:Buffer Manager -> Database pages
SQLServer:Buffer Manager -> Total pages SQLServer:Memory Manager -> Total Server Memory (KB)

---

Thankyou for your reply
Yes, I have collected the stats
At Service Startup and throughout SQL usage – both Target Server Memory and Total Server Memory run at 4GB (4166160 kb) they stay at this figure when running with a few kb of this) Something else that I have noticed is in SQL Enterprise Manager, Server Properties, Memory. It is set to dynamically assign memory with matching min and max figures from sp_configure as expected. But if you look at the greyed out section below for Use a fixed memory size (MB) it is set to 4095, why is it doing this and not going all the way up to 8190 at the top of the scale?
I have also tried setting this option to around 7000 and it still doesn’t make any different. regards
Dean

---

you should run exec master..xp_msver ‘PhysicalMemory’

---

Thankyou for your reply, I have been logging the counters for the past few hours this morning as users are starting. SQLServer:Buffer Manager -> Database pages
2258 – 76650 and rising (with a peak at 204717), more users are connecting this morning after lasts nights changes/testing SQLServer:Buffer Manager -> Total pages
519824 SQLServer:Memory Manager -> Total Server Memory (KB)
4165840-4166568 – This figure has not moved since service startup (but why not higher at initial startup) exec master..xp_msver ‘PhysicalMemory’
8190 (8587497472) Hope this helps. Dean

---

i find it very unusual you peak database pages is 200K but total pages 512K what does your db do?
what is the size of data?
does your app not use stored proc? what are the other counter vallues (for pages)
SQLServer:Buffer Manager -> ie, procedure cache pages, reserved pages, stolen pages

---

I appreciate your assistance, but the issue I am having is related to the memory not being available to SQL from startup even though I am telling it to use it, not the performance of the server. Procedure Cache Pages
27919 Reserved Pages
270 Stolen Pages
29664 At the time of the above checks – Database Pages 142658 There is 12GB of DBs at the moment and growing.
DBs are used for many different things.
Some DB’s used stored procedures. Dean

---

i don’t give a damn about your performance
i am wondering why the number are strange
something must be screwed up but for you immediate issue
it is almost as if you did not have EE installed
if AWE is not active, there is no way SQL could be using 4GB
it would be 2 or 3 GB unless you have Windows 64-bit installed

---

ugh!
are you on build 2039? get a later hotfix
this was very widely discussed when it came out
but most of us probably assumed everyone had seen it by now

---

Yes You need to apply 2040 Hotfix which boost your memory by 50 % even you enabled AWE option.
Please find below MS KB.
http://support.microsoft.com/kb/899761/

---

Not sure why you are referring to apply that hotfix as the issue seems a different one.

---

]]>