> 2 GB Memory on SQL 2K Standard?

I've been reading posts and MS sites, and see tons of info on the 3GB Switch, AWE and PAE. I understand that Win2K Advanced or Data Center is necessary to use > 2GB, but I haven't found any definitive statements on SQL edition.

If I have Win2K AE and SQL2K Standard, can I use the 3GB boot.ini switch? If so, I would imagine that I could use a 4GB server, with 3GB used by SQL. The 3GB switch does NOT use AWE, which requires SQL2K EE, so it MIGHT work with SQL Standard.

The reason I ask is that we have Win2K Server and SQL2K standard, and we're looking for the cheapest route to allow us to jump to 4GB. Win2K is a relatively cheap upgrade, SQL is a VERY expensive one.

You need to have SQL Server Enterprise Ed to use more than 2 GB of memory.

Seehttp://www.sql-server-performance.com/awe_memory.asp for more details.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Thanks for the reply. I had already read that article VERY carefully, and it appears to be talking specifically about AWE enabled scenarios. Page 1 mentions that using only the /3GB flag turns off AWE, so a lot of the document doesn't appear to apply. It's possible that the DOC isn't specific enough to cover Standard Edition.

The Doc leads me to believe that the /3GB switch is at the OS level, and AWE is at the SQL level, and using only the /3GB switch will allow SQL to use up to 3GB regardless of Edition (It doesn't specifically state that tho). If you're 100% sure it won't work, I'll take your word, but the doc doesn't state that.

See ifhttp://support.microsoft.com/default.aspx?scid=kb;en-us;274750 helps.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Thanks for the effort, I do appreciate it. That doc doesn't talk about the Win2K Advanced / SQL2K Standard combination either tho.

Hmmmmm, that considerably cheaper combination is conspicuosly absent from all the documentation I've found. Think I'll give it a try, since I haven't heard anybody say that it won't work.

Sorry to bring up an old thread, but I'd rather post a similar question here than start a new thread.

My problem is, SQL is only using 1.6gigs at all times when I hoped it would be using the 2gigs that is available to it. This box has 4gigs of ram, Win2K (all up to date SP and patches), SQL Server Standard ed. (all up to date SP and patches as well). We have the /3GB switch in the boot.ini but from what I've read today, the /3GB switch is 'useless' when used with Win2K Server:

"When the /3GB switch is used with Windows 2000 Professional or with Windows 2000 Server, the kernel components are loaded into the memory space by using the 4 GB RAM Tuning feature in the same way as they load in Windows 2000 Advanced Server and in Windows 2000 Datacenter Server. This functionality lets device-driver developers test their drivers in this configuration without having to install Windows 2000 Advanced Server or Windows 2000 Datacenter Server. The user-mode memory space is still limited to 2 GB." http://support.microsoft.com/default.aspx?scid=kb;EN-US;291988

Is there something that I'm missing? Why isn't SQL using the full 2gigs? Note: Max mem useage in SQL is set to 4 gigs, even though it can't use that much.

Thanks!

Where are you seeing that it's only using 1.6GB ? Don't rely on the memory tab in Task manager, especially when you are using /3GB switch. Use perfmon and counters 'Total Server Memory' and 'Target Server Memory' under SQL Server:Memory Manager object.

For example, I have SQL Server EE on Windows 2K AS with /3GB switch and the task manger shows only 80KB for SQL server whereas perfmon shows the actual size I've configured i.e. 3GB.

In task manager it says 1.6gigs used by sql.exe.

In perfmon the Total Server Memory shows 1684456kb. Target Server Memory is almost the same, 1684176kb.

start>run>winver shows 4,062,760kb

Thanks for the reply.

No, we're not having any performance issues. Our program runs great but there's always room for improvement, even if its 300megs of ram.

S2K standard is limited to 2GB address space, which is not exactly the same as 2GB physical memory.
s2k also reserved 256M of address space for stuff other the data buffers,
most people don't use much of this 256M, so frequently a memory usage of 1.73GB seen in standard ed.
you may have changed some setting (do you startup with a -gxxx switch?) that increased the address space reserved for other stuff