CODEWITHSUNDEEP
sql-server
Oliver
Oliver

Reputation: 668

Are SQL Server's engines the same across all editions, within a particular version?

A contractor is trying to tell us that the Enterprise query optimizer is better/faster than the Standard optimizer. I have been searching/googling on this issue, and have not seen anything clearly saying so one way or the other.

So, are the various engines (eg query optimizer, storage engine, etc) the same in all editions (eg Standard, Enterprise), and simply have certain capabilities enabled or disabled? That's been my understanding. Hopefully someone can clarify and perhaps even point to a reference link.

Upvotes: 1

Views: 116

Answers (2)

john
john

Reputation: 581

SQL Enterprise can use more hardware, i.e. number of cpu/processor and memeory each edition can use.

Also can

  • Automatic use of indexed view by query optimizer
  • Star join query optimizations,
  • etc

see http://msdn.microsoft.com/en-us/library/cc645993.aspx

Upvotes: 4

anon
anon

Reputation:

The engines are the same, however there are certain - let's call them flags - that are set based on the SKU. These can lead to better performance; not necessarily an engine difference per se, but in a lot of cases just the ability of the engine to take advantage of something due to the presence of the feature (and the "flag").

Some examples:

  • Enterprise Edition will automatically match queries to indexed views. To get this behavior on lower editions, you need to use the NOEXPAND hint. This is probably the most well-known one because it is the only one that you can take advantage of in lower editions for free (aside from code maintenance time).
  • Hardware usage limits - depending on underlying OS, lower editions may be limited to 64GB RAM, 1GB RAM, 4 sockets/16 cores, etc.
  • The rest are not available in other editions:
    • Table partitioning
    • Data compression
    • Backup compression. In 2008 only in Enterprise; since then added to Standard as well.
    • Online index operations
    • High-performance mirroring
    • Database snapshots
    • Parallel index operations
    • Star join optimizations
    • Partitioned table parallelism
    • Resource governor (on lower editions can't control workloads)
    • Updateable distributed partitioned views
    • Change Data Capture (may have to use a more intrusive method)

I'm probably leaving some out but it's important to recognize that the engines themselves aren't different, but the performance difference will be substantial depending on which of these features you might use and what kind of impact they would have on your specific workload.

Upvotes: 2

Related Questions