August 16, 2007 at 3:51 pm
We are running a 24-hour production server with SQL Server 2000 Enterprise SP4 running on a Windows Server 2003 Enterprise box with 8GB of RAM. As per articles on the topic, we have implemented the /3GB and /PAE switches in boot.ini, we have also deployed the "Fix: Not All Memory is Available When AWE is Enabled on a Computer Running 32-bit Version of SQL Server 2000 SP4 (899761)" patch and have set the AWE configuration to 1 and the Max server memory configuration to 7GB. The server seems to start paging early at around 3.5 GB while 4.5 GB of RAM is still available. I am sure there must be something simple we are missing.
August 16, 2007 at 3:54 pm
If you are running SQL Server on any account other than the system account, make sure that you've granted that user the 'lock pages in memory' right.
August 16, 2007 at 8:03 pm
Thanks. Sounds promising, as it's using a domain account.
Can't run an RSoP myself so I floated it by an NA.
He'll get around to looking into it, eventually.
August 18, 2007 at 6:17 am
/3gb and allocated memory to 7gb out of 8gb is not a good idea. You will need to check available memory but I'd say 6gb would be top. You can check sysperfinfo to see how much memory is being used - your error log will tell you if awe is working or not. I'd also advise applying 2187 rollup.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 18, 2007 at 8:48 am
Thanks.
I'm a little new to this end of things and if you could explain a couple of the points you brought up I would appreciate it.
Why cap it at 6gb?
It is pretty much only used for SQL Server and some disk-to-disk copying for backup and log shipping.
What does the 2187 rollup cover (besides the patch I mentioned above)?
Last we checked, the AWE counters in Perfmon were coming up empty.
What are some other issues I should be aware of?
August 20, 2007 at 6:53 am
awe actually has a memory overhead due to paging to get the extended addresses and if you enable the 3gb switch you are restricting the o/s and all out of process to just 1 GB which in my view is not enough. For instance if you're using microsoft log shipping and maint plans then these run out of sql server process memory, e.g. in your 1gb with the o/s, as do extended procedure calls and probvably your disk copy routines and any monitoring agents. Check sysperfinfo this will tell you how much memory you're using, forget awe counters.
In certain cirumstances I might take the memory to 6.5gb, but I can't see your system and I don't know what it does so 6gb should be a safe value. If I was using the 3gb switch I'd not take memory beyond 6gb, and even then it still might give trouble.
There were a number of regressions in sp4 as I understand and 2187 both fixes them and adds a few more fixes.
make sure your boot ini switch has the /PAE switch. make sure you enable awe in sql.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 21, 2007 at 2:46 am
Another factor to add to Colin's list. When SQL starts and wants to allocate AWE memory, the amount you ask for must be availabe. If you ask for too much, the AWE request fails and you just use normal memory.
Our standard for a server of that size is to set SQL max memory to 80% of server memory. For us this is safe, but as Colin says each shop will have to work out themselves what is safe for them.
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
September 5, 2007 at 2:19 pm
What fix level are you on? If you are using SQL 2000 SP4, get the latest Hotfix rollup. SP4 introduced a new bug that prevents SQL using more than half the memory on the box. The hotfix rollup cures this.
You are also at the limit of what you can do with the /3GB switch. You may find you need to remove it, but get the Hotfix applied first.
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
September 6, 2007 at 4:45 am
Use EM, right-click on the instance and select Properties. If you see Product Version 8.00.2187 (SP4) you know you have the Hotfix Rollup level 2187 applied on top of SP4. If the product version is 8.00.2039 you just have SP4 and do not have the AWE memory fix on your box.
According to a person I talked to in PSS, the 2187 Hotfix Rollup is likely to be the last fix for SQL 2000 that is made generally available. Maintenance for SQL 2000 has been stabilised, and only serious issues are now being fixed. Following the release of 2187 Rollup, further fixes have been issued on a by-request basis. KB 894905 gives a list of these, with the current highest build level being 2249.
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply