September 23, 2009 at 12:41 am
I read that on the same hardware the SQL Enterprise Edition is "faster" than the corresponding Standard edition. Is this true? If so, why?
TIA,
barkingdog
September 23, 2009 at 1:07 am
I haven't experienced that. There are additional features, and you can do some things faster like index rebuilds. But your everyday queries shounld't be any faster..
CEWII
September 23, 2009 at 6:31 am
No, they use exactly the same engine - in fact I believe you can even change version by changing a registry key if this hasn't changed since 2005.
The only exceptions to this are as Elliot said the extra features that can provide performance enhancements if you choose to implement these specific features. For example table partitioning, materialised views, parallel index rebuild etc. and ones that can provide better Quality of Service e.g. Resource Governor.
It could also run ‘faster’ on Enterprise edition if the hardware has more than 4 physical CPU’s as Standard Edition will only utilise a maximum of 4
September 23, 2009 at 7:15 am
They are the exact same codebase, with different features/restrictions.
EE servers are usually larger, so it might appear to run faster, and there might be features that allow things to perform better, but outside of those, it's the same engine code.
September 24, 2009 at 9:08 am
One important performance enhancement that is present in Enterprise Edition and not other editions is EE makes better use of prefetch. According to the Microsoft article below:
"SQL Server Enterprise Edition uses more prefetching than other editions of SQL Server, allowing more pages to be read ahead."
http://technet.microsoft.com/en-us/library/ms191475(SQL.90).aspx
September 24, 2009 at 6:48 pm
Here's a nice summary of the perf-related features of Enterprise edition:
http://blogs.msdn.com/boduff/archive/2008/01/24/why-should-i-use-sql-enterprise-edition.aspx
And a detailed posting on the Large Page Extensions feature:
http://blogs.msdn.com/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx
BTW - The "Lock Pages In Memory" feature is no longer just Enterprise only - since SP1 CU2 the Standard edition also supports it.
So yes, a properly configured Enterprise box can be faster than Standard edition on the same hardware (assuming decent spec with >= 8GB RAM). The Large Page Extensions can reduce memory fragmentation, and the larger read-ahead will help when spooling sequential data from disk. And Parallel Index Operations and Merry-go-round Scans both help in a heavily contended environment when you have a decent number of CPU cores.
How much faster exactly? You'd have to measure in your environment 🙂
Regards,
Jacob
September 24, 2009 at 8:08 pm
Thanks to everyone for you great replies!
Barkingdog
September 25, 2009 at 6:42 am
Jacob is right. I was going to post much the same, but he got there 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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply