November 7, 2009 at 2:45 pm
Dear all,
We have just been given a 64bits server for our db and web site with 4 processors and 8Gbs mem!
Amazing stuff to us...
However, I just noticed that the sql server installed is 32 bits and does not use AWE to allocate memory.
On a 32bits OS, this would limit our sql server to a 2Gbs footprint (unless we boot with the /3Gbs flag).
What about a 64bits OS??? Are we still limited to that 2Gbs footprint?
Or are we limited to 3 or 4Gbs unless we use AWE?
Looking into Task Manager, it looks like our Sql Server process uses 3 700 000Mbs...
Obviously, I'd like to use more memory. Is it simply a case of setting up the Max Server Memory to something like 5500 (since we share the box with the web server) and switching the AWE flag on?
Thanks
Eric
November 7, 2009 at 4:16 pm
Since AWE is enabled through the OS, it is possible that you may be able to increase the size of the max memory value and see SQL increase the amount of memory that it uses.
I think that the bigger issue that you may see is that in 32-bit versions of SQL the amount of memory that you set is for the buffer pool, not every function used by SQL Server. So certain functions, like execution of stored procedures use a different portion of memory. Just something you may want to watch out for. If you get errors in your error log saying that executions failed because of insufficient memory, then you will want to add the -g switch to your startup parameters.
Is it not possible to get the 64-bit SQL Server and migrate the databases over?
Joie Andrew
"Since 1982"
November 7, 2009 at 11:39 pm
Eric Mamet (11/7/2009)
Is it simply a case of setting up the Max Server Memory to something like 5500 (since we share the box with the web server) and switching the AWE flag on?
Hey Eric,
Yes. Change the max server memory setting and enable AWE. Ensure that the Windows account SQL Server starts under has been granted the 'lock pages in memory' privilege (see http://technet.microsoft.com/en-us/library/ms190730(SQL.90).aspx) and restart the SQL Server service.
Simple as. 🙂
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2009 at 12:35 am
Paul,
The article stated this about locked pages in memory and AWe:
Locking pages in memory is not required on 64-bit operating systems.
AWE is something that is required on 32-bit OSes, enabled through applications. Do you think that since SQL is running in WOW32 that it is still required?
Joie Andrew
"Since 1982"
November 8, 2009 at 1:29 am
32-bit always behaves like 32-bit... even on 64-bit.
So, the same rules apply for every 32-bit software.
If you can, install 64-bit software to overcome all 32-bit related problems.
32-bit uses WOW (windows on windows) "emulation" on a 64-bit box !!
The remark on lock pages in memory not being needed on 64-bit, only goes if you are actually using 64-bit software !!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 8, 2009 at 1:35 am
unixbomer (11/8/2009)
Paul, the article stated this about locked pages in memory and AWe:Locking pages in memory is not required on 64-bit operating systems.
AWE is something that is required on 32-bit OSes, enabled through applications. Do you think that since SQL is running in WOW32 that it is still required?
Yes. Both SQL Server and the host OS need to be 64-bit for that to apply.
32-bit SQL Server running under WOW can access 4GB of VAS, but aside from that things remain pretty much the same.
The OP doesn't make it entirely clear that the host OS (Windows 2003/2008?) is in fact the 64-bit edition, so I just tried to give simple advice that should work whatever the exact scenario is.
http://blogs.msdn.com/slavao/archive/2006/04/12/575152.aspx
edit: fixed link
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2009 at 1:40 am
ALZDBA (11/8/2009)
...all good stuff...
Thanks ALZDBA! I was replying at the same time as you it seems 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2009 at 1:48 am
So there must be some truth in it :w00t:
As always in fora, it's nice to get something confirmed, if possible even more than once.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 8, 2009 at 1:58 am
ALZDBA (11/8/2009)
it's nice to get something confirmed, if possible even more than once.
I can confirm that. 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2009 at 8:18 am
Sorry for the late reply chaps...
I was busy loosing yet another football match with my Kids team!
Lost 7-5 last week, 6-4 this week against the same team. This is starting to P... me off!
Ok, back to business.
I am told I could have a sql 2008 64bits instead of 2005 32 bits...
Based on your various comments, I think I'll take that offer.
😀
Many thanks for your help
Eric
November 8, 2009 at 10:02 am
first of all, comfort the kid ! "Next time, no more luck, perseverance in the sports for the team will do the trick" 😉
I would take the 64-bit offer ! Prepare your migration to SQL2008, so your setup starts with implementing best practices.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 8, 2009 at 1:28 pm
Thanks Paul and ALZDBA for clarifying that for me. I wasn't exacly quite sure how it would work, and I have not been able to find a lot of information on SQL Server in WOW environments.
Good stuff!
Joie Andrew
"Since 1982"
November 8, 2009 at 2:03 pm
Joie, you're welcome. SQL Server on WOW is a bit of an odd duck - the only time you'd really choose it I think is if you somehow ended up with a 32-bit copy of SQL Server and a 64-bit OS, with no budget to upgrade...?
Eric, 64-bit SQL Server on a 64-bit OS is definitely the better option. 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 9, 2009 at 1:36 am
That was pretty much our case... No budget, sql 32 bits and os 64...
But now, we'll definitely go for sql 2008 64.
A little bit of testing... and here we go!
November 9, 2009 at 7:22 am
After you change all these items, I suggest you run BPA against your system to find out what else may be hampering your system for memory.
Just down load BPA and run it.
There might be some additional tweeting and patches needed for your system.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply