January 30, 2010 at 1:18 am
Hi,
In our client Place Server configuration is 16 GB RAM, 160 GB Hard Disk.
OS Windows 2003 server Enterprise edition
/3GB and /PAE enabled
SQL Server 2000 Server Enterprise Edition.
AWE enabled
If we configure memory utilization Dynamic (that is minimum 0 and maximum 4gb) No problem.
If we configure more than 4GB the result from sql server is dead slow.
Due to some other application if the memory utilisation goes above 4GB also the same problem(when checked in task manager, showed 4.7 GB)
So we are forced to fix maximum memory in sql server to 3 gb. Even though we have 16 gb of ram we are unable to use it.
Can once instance of SQL server 2000 use more than 4GB of RAM?
There are two other instances running on the server and we are forced to configure them to use maximum of 500 MB RAM.
January 30, 2010 at 1:00 pm
Yes SQL Server 2000 supports more than 4 GB of memory.
Do to the fact that you are running Enterprise Edition, have you tried to set the "Lock Pages In Memory" option? (http://support.microsoft.com/kb/921928)
Based on the parts of your post, I assume you are working on a 32-bit installation. I would suggest moving to 64-bit environment if possible.
January 31, 2010 at 6:03 pm
I agree, going with 64-bit for the OS and SQL is the best option if possible. 32-bit SQL can access more than 4GB of RAM with AWE enabled, but only for the buffer pool, so not all operations can take advantage of it.
It was unclear as to why the OS had to have the /3GB switch enabled though. What happens if you take that out and restart the server?
Joie Andrew
"Since 1982"
January 31, 2010 at 6:24 pm
I too would recommend the 64 bit upgrade. IN addition, I recommend moving the additional applications off the server onto a dedicated application server. You are likely running into a contention for the memory resources between SQL server and this other application.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 1, 2010 at 9:13 am
What is the build version of SQL 2000 that you are running?
If you go to SQL Query Analyzer and type in SELECT @@VERSION it should be in that string
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
February 2, 2010 at 3:10 am
You are on the limit of what will work if you have 16GB RAM and /3GB switch in boot.ini
By using the /3GB switch you are restricting Windows to use only 1GB RAM. If you have over 16GB memory then Windows absolutely must use more than 1GB RAM or it will fail. Depending on what is running on your box, Windows may need to use more than 1GB RAM for memory values less than 16GB. Many people have found that you need to add the /USERVA switch to boot.ini if you have more than 12GB memory. See KB316739 for more details of this.
My advice is to take out the /3GB switch and set SQL Server to use (say) 10GB RAM. You should see a good performance benefit and have a base for doing further tuning. If you want to get the maximum performance out of your machine then it will be a trial and error process of tweaking the /USERVA value to keep the right amount of System PTEs, etc, followed by adjusting the SQL max memory value.
You say you have multiple SQL instances running, if so you must take the memory these use into account when setting the max memory for your main instance. Also remember that Task Manager will only show your memory use up to the 4GB line. If you want to see what use SQL is making of memory above the 4GB line you must use the SQL AWE performance counters.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply