 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008SQL Server 2005 32 bit Standard and Windows 2003 64 bit Standard to use more then 4GB RAM
Hello folks, The issue I am having is we have SQL Server 32 bit installed on Windows 2003 64 bit server ,that has 8 cpus 2Ghz and 16GB RAM. Windows 2003 64 bit version can support high The issue is SQL Server is not using more then 3.7GB ram. Though I have defined in the SQL Manager to use minmum 10GB We have DBs in there that are huge and in constant use. How to resolve the issue. useing command DBCC MEMORYSTATUS gives VM Reserved 3647092 VM Committed 3623200 AWE Allocated 0 Reserved Memory 1024 Reserved Memory In Use 0 Where can i fix it so, SQL server see the large memory present in the system. Already tried AWE option, w/o any sucess. Any help will be greatly appreciated. Thanks Why are you using 32 bit SQL if you have x64bit OS? If you load the x64 bit version of SQL Server you can use all you want. If you keep the 32 bit you can only use 4GB if you set the /3GB. It doesn't cost more for the 64bit so why are you using the 32bit?  SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Hello folks, > [quoted text clipped - 32 lines] > > Thanks > Why are you using 32 bit SQL if you have x64bit OS? If you load the x64 bit > version of SQL Server you can use all you want. If you keep the 32 bit you [quoted text clipped - 37 lines] > > > > Thanks SQL Server according to MS can utilize "Operating system maximum RAM" here is the link for reference http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx > Why are you using 32 bit SQL if you have x64bit OS? If you load the x64 bit > version of SQL Server you can use all you want. If you keep the 32 bit you [quoted text clipped - 37 lines] > > > > Thanks JK, I am not sure I understand your point. But SQL Server Standard 2005 can utilize the OS maximum under the right conditions. If the OS is 32 bit then SQL Server 32 bit can use the OS max thru AWE. If the OS is x64 and SQL Server is x64 then it can use all memory as is. But to the best of my knowledge if the OS is x64 and SQL Server is 32bit you can use a mx of 4GB thru the /3GB switch because AWE is not valid in 64 bit. Why would you want to use more than 4GB of memory in x64 OS with a 32 bit SQL? It doesn't make sense to me, just use the x64 edition of SQL Server. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > SQL Server according to MS can utilize "Operating system maximum RAM" > [quoted text clipped - 46 lines] >> > >> > Thanks Andrew, according to the givin senerio i.e OS Win 2003 Standard 64 bit R2 SQL Server 2005 Standard 32 bit here is what MS has to say http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx SQL 2005 Standard Max. Procs = 4 RAM Requirements Minimum: 512 MB Recommended: 1 GB or more Maximum: 64 GB (32-bit) or 512 GB (64-bit) So according to given specs, SQL 2005 Standard 32 bit can use up to 64GB RAM. As for Windows 2003 Standard 64 bit R2 http://www.microsoft.com/technet/windowsserver/evaluate/features/compare.mspx It support 32GB And 4-way symmetric multiprocessing (SMP) support Now, the question is how those two pice can fit togather to harness avaliable RAM in the system. > JK, > [quoted text clipped - 57 lines] > >> > > >> > Thanks > Andrew, > [quoted text clipped - 14 lines] > Recommended: 1 GB or more > Maximum: 64 GB (32-bit) or 512 GB (64-bit) That may be true, (and I don't have much reason to doubt it) but I'm fairly certain that accessing that memory will not be nearly as fast as if you were running the 64-bit version of SQL Server. 32bit apps under WOW64 are slower in general in any case. > So according to given specs, SQL 2005 Standard 32 bit can use up to 64GB > RAM. [quoted text clipped - 7 lines] > Now, the question is how those two pice can fit togather to harness > avaliable RAM in the system. I suspect swinging a dead-chicken is involved. >> JK, >> [quoted text clipped - 64 lines] >> >> > >> >> > Thanks SignatureGreg Moore SQL Server DBA Consulting Remote and Onsite available! Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html Besides the obvious why are you using 32 bit sql server on 64 bit OS, I would guess that you have simply miss-configured or done something wrong with the AWE setting if SQL isn't using more than 3.7GB of RAM. Or perhaps you don't have much actual data on the server? SQL will only take ram initially if it needs it so if you have only 2GB of databases you won't see that much ram used for the buffer pool. SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net > Hello folks, > [quoted text clipped - 32 lines] > > Thanks Guys, i think i have identified the source of the issue When I look at the SQL error log, there is an entry after i recently rebooted the server. [310] 8 processor(s) and 4096 MB RAM detected Now, the question is why SQL server is not able to see the 16GB present in the system. Although the OS is showing 16GB fine. And the funny thing is, in SQL manager, and go to "General" TAB it shows 16383 MB there So, what am i missing here Thanks > Besides the obvious why are you using32bitsqlserveron64bitOS, I > would guess that you have simply miss-configured or done something wrong [quoted text clipped - 50 lines] > > - Show quoted text - Have you enabled AWE? Since SQL Server 32-bit is running under WOW64, you'll need to enable AWE and set max server memory just like you would do in a 32-bit world. Check out SQL Server MVP Ron Talmage's artice on this: http://www.code-magazine.com/Article.aspx?quickid=070193. SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ Guys, i think i have identified the source of the issue When I look at the SQL error log, there is an entry after i recently rebooted the server. [310] 8 processor(s) and 4096 MB RAM detected Now, the question is why SQL server is not able to see the 16GB present in the system. Although the OS is showing 16GB fine. And the funny thing is, in SQL manager, and go to "General" TAB it shows 16383 MB there So, what am i missing here Thanks On Mar 7, 1:29 pm, "TheSQLGuru" <kgbo...@earthlink.net> wrote: > Besides the obvious why are you using32bitsqlserveron64bitOS, I > would guess that you have simply miss-configured or done something wrong [quoted text clipped - 51 lines] > > - Show quoted text - Hi Dan! > Have you enabled AWE? Can Standard Edition 2005 use AWE? I thought for instance that SE cannot use "Lock Pages in Memory" and that this is a requirement for AWE? SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > Have you enabled AWE? Since SQL Server 32-bit is running under WOW64, you'll need to enable AWE > and set max server memory just like you would do in a 32-bit world. Check out SQL Server MVP Ron [quoted text clipped - 71 lines] >> >> - Show quoted text - Hi, Tibor. > Can Standard Edition 2005 use AWE? I thought for instance that SE cannot > use "Lock Pages in Memory" and that this is a requirement for AWE? You are correct that the lock pages in memory requirement for AWE. I'm not aware of a Standard Edition restriction on this and believe Standard can used lock pages in memory because AWE is supported under SE and required to use all memory available to the OS. I haven't personally run SE with AWE but I think it should work the same as EE. I should add that lock pages in memory is not granted by default so it is necessary to run gpedit.exe to assign the right to the SQL Server service account. This differs from SQL 2000 where the the lock pages right was assigned automatically during installation. SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ > Hi Dan! > [quoted text clipped - 80 lines] >>> >>> - Show quoted text - > You are correct that the lock pages in memory requirement for AWE. I'm not aware of a Standard > Edition restriction on this and believe Standard can used lock pages in memory because AWE is > supported under SE and required to use all memory available to the OS. Hmm, I definitely remember a discussion about SE on 64 bit not supporting Lock Pages in Memory. This I de remember because 65 bit can in some cases be unstable if paged out and we had a discussion how strange then that SE do not support what it takes for SQL Server to *not* be paged out (Lock Pages in Memory). I admit this is for 64 bit, but my reasoning is that if SE 64 but don't support lock pages in memory, then perhaps SE 32 bit don't either... SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > Hi, Tibor. > [quoted text clipped - 92 lines] >>>> >>>> - Show quoted text - I think that Tibor is right about this. On 64-bit systems Lock Pages in Memory is only supported in Enterprise Edition. http://support.microsoft.com/kb/918483 You can also check in the error log for the message "Using locked pages for buffer pool". http://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-pri vilege-for-local-system.aspx (I don't know about support for AWE.) Ola Hallengren http://ola.hallengren.com > > You are correct that the lock pages in memory requirement for AWE. I'm not aware of a Standard > > Edition restriction on this and believe Standard can used lock pages in memory because AWE is [quoted text clipped - 102 lines] > >>>> > >>>> - Show quoted text - >I think that Tibor is right about this. Or maybe not. The question in this thread is about *32* bit Standard Edition. > On 64-bit systems Lock Pages in > Memory is only supported in Enterprise Edition. So above implies that *32* bit Standard Edition SQL Server *can* use Lock Pages in Memory and hence use > 2 or 3 GB. SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi >I think that Tibor is right about this. On 64-bit systems Lock Pages in > Memory is only supported in Enterprise Edition. [quoted text clipped - 122 lines] >> >>>> >> >>>> - Show quoted text - In Books Online it says. "Support for AWE is available only in the SQL Server 2005 Enterprise, Standard, and Developer editions and only applies to 32-bit operating systems." http://technet.microsoft.com/en-us/library/ms179301.aspx This implies that you can't use AWE on a Windows x64, doesn't it? Ola Hallengren http://ola.hallengren.com > >I think that Tibor is right about this. > [quoted text clipped - 132 lines] > >> >>>> > >> >>>> - Show quoted text - > This implies that you can't use AWE on a Windows x64, doesn't it? Yes, but the question was if AWE is enabled on SE *32* bit or not... :-) SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > In Books Online it says. > [quoted text clipped - 156 lines] >> >> >>>> >> >> >>>> - Show quoted text - I'm sorry for misunderstanding, but I have to ask again. ;-) >SQL Server 2005 32-bit Standard and Windows 2003 64-bit Standard to use more than 4GB RAM. >I think SE 32-bit must use AWE to acquire and address memory over 4GB. >Support for AWE is available only in the SQL Server 2005 Enterprise, Standard, and Developer editions and only applies to 32-bit operating systems. As I understand it SQL Server 2005 32-bit Standard needs AWE to use memory over 4 GB. AWE does only work with 32-bit Windows. Ola Hallengren http://ola.hallengren.com > > This implies that you can't use AWE on a Windows x64, doesn't it? > [quoted text clipped - 161 lines] > >> >> >>>> > >> >> >>>> - Show quoted text - > in Memory). I admit this is for 64 bit, but my reasoning is that if SE 64 > but don't support lock pages in memory, then perhaps SE 32 bit don't > either... My understanding is that SE 64-bit doesn't need the lock pages in memory privilege because it doesn't use the AWE API (which requires the lock pages in memory right). EE 64-bit uses AWE API to acquire memory(http://blogs.msdn.com/slavao/archive/2005/11/15/493019.aspx) for performance reasons but the memory is still addressed natively using 64-bit pointers so AWE doesn't need to be turned on. I think SE 32-bit must use AWE to acquire and address memory over 4GB so I believe that both lock pages in memory must be granted and AWE memory must be turned on, even under WOW64. I haven't actually done this myself, though. SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ >> You are correct that the lock pages in memory requirement for AWE. I'm >> not aware of a Standard Edition restriction on this and believe Standard [quoted text clipped - 111 lines] >>>>> >>>>> - Show quoted text - > My understanding is that SE 64-bit doesn't need the lock pages in memory privilege because it > doesn't use the AWE API (which requires the lock pages in memory right). Yes, that is correct. But without utilizing Lock Pages in Memory, the process can be paged out by the OS and Slava mentioned in one of his blogs that 64 bit SQL server can in some cases become unstable if paged out. I.e., it owuld have been nice if 64 bit SE utilized Lock Pages in Memory since we then could make sure that OS doesn't page it to disk. > I think SE 32-bit must use AWE to acquire and address memory over 4GB We agree here. > so I believe that both lock pages in memory must be granted and AWE memory must be turned on Yes, but what if SE doesn't utilize Lock Pages in Memory. I.e., a programming restriction (SE doesn't use Lock Pages in Memory /AWE) mean that this Edition in 32 bit cannot use > 2 or 3 GB memory? You've might have seen that I've posted this in the MVP group and for the benefit of other readers, I'll post back my findings. Hopefully I'm on a wild goose chase and there is no problem for SE 32 bit 2005 to use > 3GB memory. (I.e., hopefullt the Lock Pages in Memory thing is a red herring...). SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi >> in Memory). I admit this is for 64 bit, but my reasoning is that if SE 64 but don't support lock >> pages in memory, then perhaps SE 32 bit don't either... [quoted text clipped - 115 lines] >>>>>> >>>>>> - Show quoted text - |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.