mibuso.com

A customer is working with SQL Server 2008 Standard Edition with a database of approx. 30GB (size in SQL), 1 disk for the OS (64-bit) and SQL, 2 disks for the database files and 1 disk for the log-file. (All mirrored)

To improve performance they added memory, from 32GB to 64GB (due to the fact that almost all of the memory was being used bij SQL). The SQL Standard Edition however doesn't use more than 32GB, Enterprise does but will need a re-install and quite an investment.

Besides being able to address more memory does the Enterprise Edition offer more (noticable) performance advantages over the Standard Edition?

TIA

OK, first thing is, that SQL Server does allways allocate as much memory as it could get!

With 32GB System-Memory Windows will grab ca. 1 to 2 GB for itself, the rest will be targeted by SQL Server (targeted is not necessarily used).
You should always set a "Max. Server Memory" for SQL Server; on a dedicated box you need to leave 1 or 2 GB for WIndows, if you have other services or applications running you have to subtrac those RAM requirements.
For example, in your case - dedicated NAV/SQL server presumed - the "Max. Server Memory" setting for SQL Server could be 28000 or 30000 (MB).

SQL Server 2008 R2 x64 Standard has a memory limit of 64GB - not 32GB!
I guess the problem in your case is, that you run a Windows Server 2008 R2 x64 Standard - which indeed has a limit of 32GB! So it's a Windows thingy, not SQL!

So it's probably not the SQL Server you need to upgrade, but the Windows Server! Luckily, the price difference here is not as big as with SQL licenses.

Then again: regardless how much RAM you add, sooner or later SQL will grab as much as possible. Hence, if SQL Server is using a lot of RAM it's not a bug but a feature
I actually doubt that with your tiny 30GB database so much RAM is really needed ...

Hello Jorg,

Thanks for your reply! I was under the impression that SQL Standard had the 32GB restriction. I'll have them look into upgrading Windows Standard to Windows Enterprise. As with many performance issues, this is one of the things we're investigating.

Out of interest, are there (for NAV relevant) advantages with SQL Enterprise over SQL Standard?

BTW, A limit was set to the memory for SQL...

The performance issue you are having is NOT because of RAM. You need to correctly analyze the whole system and fix what is broken.

Well, basically YES, but this usually applies to large systems with databases beyond 200GB and more than 100 Users ... of course, there's no general rule about when to use STD or EE, it always depends on the current business and transaction volume to process ...

Many differences between STD and EE are in context of "Business Itelligence", thus Analyis & Reporting; but that does not directly apply to NAV.

Regarding "High Availability" the "Asynchronous High Performance Mirroring" and "Database Snapshots" could be interessting EE features. Especially with SQL Server 2012 there are plenty of improvements regarding HA (HADRON), most of it EE features.

With large DB the EE feature of "Data Compression" could be pretty cool; see this article about details.

If the SQL Server is heavily used by non-NAV applications, too, e.g. interfaces, subsystems, webshops, etc. the "Resource Governor" could be feasible to define Resource Pools for applications or processes.

And much more, depending on the actual requiremenst. I suggest to browse the MS websites about details; here you'll find some "Compare Editions" feature where you could have a closer look at this.

Absolutely!

David:



Jorg, thanks for the additional info about STD vs. EE.

I just wanted to point out that the SQL Server 2008 Standard Edition supports the max. OS memory, and the SQL Server 2008 R2 Standard Edition is limited to 64GB, not 32GB.

http://msdn.microsoft.com/en-us/library/ms143685%28v=sql.105%29.aspx

Yes, that's true. Probably just a liitle "mix-up": the Windows Server 2008 R1/R2 x64 STANDARD has indeed a limit of 32GB!