Evaluating Enterprise Edition's Performance Value

My company is considering upgrading from SQL Server 2000 Standard Edition to Enterprise Edition. I assume that Enterprise Edition features better performance, but I haven't found any benchmarks for it. Can you tell me what performance improvements I can expect?

You probably won't see any performance differences in upgrading from SQL Server 2000 Standard Edition to Enterprise Edition. Enterprise Edition is more scalable because it can use more than 2GB of memory and more than four processors, but the engine-level algorithms are basically the same for the two editions. In general, Enterprise Edition won't run faster than Standard Edition unless your hardware supports more than 2GB of memory and four processors, but there are three minor exceptions.

First, indexed views work on both Standard and Enterprise editions; however, when you reference the view indirectly, only Enterprise Edition knows how to use an indexed view instead of calling the base table. For example, say you have a table called MyTable with an indexed view called MyIndexedView. Enterprise Edition knows to use the view even when the query only references the base table. Although Standard Edition lets you name the indexed view directly in a query, the optimizer doesn't know how to use the view in a query plan if the query only references the base table. So, the performance benefits of using an indexed view in Standard or Enterprise editions of SQL Server are the same. However, Standard Edition requires that your users and application developers explicitly use the indexed view name in a query. Enterprise Edition might use the view even if you don't explicitly reference the view in your query. The optimizer is smart enough to know that the indexed view references base tables in this query and would be helpful for processing the current query. The optimizer will use it even though it wasn't directly mentioned in the query, which means a DBA can add a view to the database after the fact as a tuning aid without having to change the existing application. Existing queries would begin using the view if the optimizer decided the view was helpful.

Second, you might get a small performance improvement if you run Enterprise Edition on four processors with hyper-threading enabled. Standard Edition doesn't run on more than four processors and can't tell the difference between the logical or physical processors that hyper-threading creates. Hyper-threading on a four-CPU machine creates eight logical processors, and Enterprise Edition can use all eight; Standard Edition can use only four.

Third, Enterprise Edition supports the parallel creation of indexes, which means that SQL Server uses a CREATE INDEX statement to create indexes in several concurrent, parallel steps. Parallel index creation can save a lot of time when you're adding an index on a large data set. In Standard Edition, index creation is a serial process. Enterprise Edition also supports many important features not in Standard Edition, such as clustering and log shipping, but these features don't offer compelling performance reasons to upgrade.