December 18, 2007 at 10:17 am
Hello,
I have a problem about memory usage (w2003 + SQL2000 sp4 with 4 GB RAM).
I've looking processes and possible points of improvements using PSSDIAG. The results is that our SQL 2000 need more memory, but system (perfmon) view other think, for total of 4 GB is used 3.5 GB.
The sp_configure is:
NAME Minimun Maximunconfig_valuerun_value
affinity mask-2147483648214748364700
allow updates0100
awe enabled0100
c2 audit mode0100
cost threshold for parallelism03276755
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)4214748364721474836472147483647
max text repl size (B)021474836476553665536
max worker threads3232767255255
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364710241024
nested triggers0111
network packet size (B)5123276740964096
open objects0214748364700
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote login timeout (s)021474836471000010000
remote proc trans0100
remote query timeout (s)0214748364700
scan for startup procs0100
set working set size0111
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276700
I think that parameter max server memory (MB) config_value and run_value is not correct. Could some body help me.
Tks a lot
December 18, 2007 at 12:20 pm
Is this sql server 2000 standard edition (limited to 2GB)?
If you want sql server 2000 to use more memory you need the enterprise edition or higher.
Perhaps you can start the server with the /3GB boot.ini switch so applications can make use of 3GB and the OS 1GB vs 2GB applications & 2GB OS.
If you need more that 4 GB of memory, you need to enable AWE, apply a post SP4-hotfix (899761) ,manually set the memory limit of sql server and grant sql server the right to lock pages in memory.
More info:
http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/2644/
December 18, 2007 at 3:42 pm
Txs Jo,
The SQL is Enterprise edition, and boot.ini was configured with 3 GB.
Daniel
December 18, 2007 at 4:20 pm
What is your server OS? If you have Windows 2000 Advanced Server or greater, run sp_configure to set your max server memory to 3 GB. The /3GB switch only works on this level of OS and up. Just having the entry in your boot.ini file doesn't do it for you. You still need to tell SQL Server that it can have 3 GB. You'll need to add more memory and enable AWE to get any more than 3 GB.
December 19, 2007 at 2:40 am
txs John,
Is w2003 enterprise edt. SP2, + SQL 2000 SP4 enterprise edt.
Is possible that memory administration has faults caused by "set working set size parameter = 1" and min, max memory has been configured as dynamic.?
See parameters max min memory and set working set size.
Total amount of all data bases in this instance is about 250 GB. Specific database in wich we have problems has 90 GB.
I think that 4 GB of memory is enough (just) for this system. However Microsoft tell us that more than 4 GB RAM in a 32 bits system is not useful (is possible).
Daniel
December 19, 2007 at 3:47 am
What problems are you having with the 90GB db?
If there's nothing else running on the server, set the max memory to around 2.75GB and min to 2 GB
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2007 at 4:53 am
txs Gila,
The Data Base (90Gb) has a hard paging problems:
Memory (average):
Pages read: 169
Pages Input: 2068
I will trie to put set working set size=1
and min max memory to equal values, about 3 GB or 2,7 GB
Daniel
December 19, 2007 at 7:59 am
Have you had a look at the queries that are running against that DB? It could be that they are performing sub-optimally, doing more IOs than necessary, resulting in high paging levels.
You can add more memory over 4GB, providing you're using Enterprise edition, and enable AWE in SQL. It works OK. Not all portions of SQL's memory can take advantage of the windowing extensions, just the data cache iirc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2007 at 8:57 am
Also set the maximum/minimum memory to fixed. Likely dynamic is limited to 2GB.
December 19, 2007 at 12:39 pm
txs to all,
Dynamic memory is limited to 2 Gb?
Daniel
December 19, 2007 at 2:36 pm
As soon you use AWE you have to set the memory to fixed size. I don't have the enterprise edition to test it out. It could be it can allocate 3GB if you enabled the /3GB bootswitch without enabling AWE
found some nice links:
http://msdn2.microsoft.com/en-us/library/ms187499.aspx
http://msdn2.microsoft.com/en-us/library/aa175282(sql.80).aspx
http://sqljunkies.com/Tutorial/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.scuk
December 19, 2007 at 3:27 pm
Yes, Jo. You can use the /3GB switch and set the Max Server Memory to use 3 GB without enabling AWE.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply