Percentage of total: Performance Difference between SQL Server 2005 Standard Edition and Enterprise Edition

Dear All,

Our database does not use any features which are present in Enterprise
Edition and not present in Standard Edition. We want to know what are
the differences between Standard Edition and Enterprise Edition for
performance. Why should we go for Enterprise Edition when Standard
Edition has all the features required.

We are presently using evaluation versions of SQL Server 2005 Standard
and Enterprise Editions.

Thanks and regards,
Nishant Saini
http://www.simplyjava.comNishant Saini (nishant.saini@.gmail.com) writes:

Quote:

Originally Posted by

We have a database which contains many tables which have millions of
records. When We attach the database with MS SQL Server 2005 Standard
Edition Server and run some queries (having joins, filters etc.) then
they take very long time to execute while when We execute same queries
on Enterprise Edition then they run 10 times faster than on standard
edition.
>
Our database does not use any features which are present in Enterprise
Edition and not present in Standard Edition. We want to know what are
the differences between Standard Edition and Enterprise Edition for
performance. Why should we go for Enterprise Edition when Standard
Edition has all the features required.

There is a topic that covers this in Books Online. On the top of my head
I don't recall anything immediate, but I'm off to other things right now,
and don't want to look around.

Could you post one of your queries you've been testing with and the
query plan on Standard and Enterprise?

I presume that you are running the two editions on the same hardware?

Quote:

Originally Posted by

Dear All,
>
We have a database which contains many tables which have millions of
records. When We attach the database with MS SQL Server 2005 Standard
Edition Server and run some queries (having joins, filters etc.) then
they take very long time to execute while when We execute same queries
on Enterprise Edition then they run 10 times faster than on standard
edition.
>
Our database does not use any features which are present in Enterprise
Edition and not present in Standard Edition. We want to know what are
the differences between Standard Edition and Enterprise Edition for
performance. Why should we go for Enterprise Edition when Standard
Edition has all the features required.
>

The Enterprise Edition offers improvements over the Standard Edition,
especially on parallel operations and caching. If you have a
significant amount of data you will defnitely notice a better
performance with this edition.

Regards,
lucm|||Are there any indexed views in the database? From the Books On Line
(BOL):

"Indexed views can be created in any edition of SQL Server 2005. In
SQL Server 2005 Enterprise Edition, the query optimizer automatically
considers the indexed view. To use an indexed view in all other
editions, the NOEXPAND table hint must be used."

So if there were indexed views, but no references to the table hint,
performance could certainly be far better using the Enterprise
edition.

Roy Harvey
Beacon Falls, CT

On 15 Dec 2006 03:01:20 -0800, "Nishant Saini"
<nishant.saini@.gmail.comwrote:

Quote:

Originally Posted by

>Dear All,
>
>We have a database which contains many tables which have millions of
>records. When We attach the database with MS SQL Server 2005 Standard
>Edition Server and run some queries (having joins, filters etc.) then
>they take very long time to execute while when We execute same queries
>on Enterprise Edition then they run 10 times faster than on standard
>edition.
>
>Our database does not use any features which are present in Enterprise
>Edition and not present in Standard Edition. We want to know what are
>the differences between Standard Edition and Enterprise Edition for
>performance. Why should we go for Enterprise Edition when Standard
>Edition has all the features required.
>
>We are presently using evaluation versions of SQL Server 2005 Standard
>and Enterprise Editions.
>
>Thanks and regards,
>Nishant Saini
>http://www.simplyjava.com

|||Although Enterprise Edition is more aggresive when it comes to
read-aheads, and has some features that can improves performance in some
very specific situations (such a parallel index creation, Advanced
Scanning, etc.), if both systems have the same amount of memory, then I
would not expect a 10-fold performance difference.

How much memory does the system have, and have you assigned this memory
to SQL Server?

You could copy the "fast" database to the "slow" configuration with
detach/attach and see if that makes a difference. This way you can rule
out any database differences (such as one database with up-to-date
statistics and another with unusable or no statistics).

HTH,
Gert-Jan

Nishant Saini wrote:

Quote:

Originally Posted by

>
Dear All,
>
We have a database which contains many tables which have millions of
records. When We attach the database with MS SQL Server 2005 Standard
Edition Server and run some queries (having joins, filters etc.) then
they take very long time to execute while when We execute same queries
on Enterprise Edition then they run 10 times faster than on standard
edition.
>
Our database does not use any features which are present in Enterprise
Edition and not present in Standard Edition. We want to know what are
the differences between Standard Edition and Enterprise Edition for
performance. Why should we go for Enterprise Edition when Standard
Edition has all the features required.
>
We are presently using evaluation versions of SQL Server 2005 Standard
and Enterprise Editions.
>
Thanks and regards,
Nishant Saini
http://www.simplyjava.com

|||"Nishant Saini" <nishant.saini@.gmail.comwrote in message
news:1166180480.672397.223250@.73g2000cwn.googlegro ups.com...

Quote:

Originally Posted by

Assuming identical hardware, a 10x speed difference is usually the
difference between memory and disk access. Clear the procedure and memory
cache prior to side by side testing.

Quote:

Originally Posted by

>
Our database does not use any features which are present in Enterprise
Edition and not present in Standard Edition. We want to know what are
the differences between Standard Edition and Enterprise Edition for
performance. Why should we go for Enterprise Edition when Standard
Edition has all the features required.

I only consider EE if the hardware requires the use.

Quote:

Originally Posted by

>
We are presently using evaluation versions of SQL Server 2005 Standard
and Enterprise Editions.
>
Thanks and regards,
Nishant Saini
http://www.simplyjava.com
>

|||On Fri, 15 Dec 2006 15:54:07 -0600, "Russ Rose" <russrose@.hotmail.com>
wrote:

Quote:

Originally Posted by

>Assuming identical hardware, a 10x speed difference is usually the
>difference between memory and disk access.

My experience, for what it is worth, has been that extreme performance
differences - better or worse - are usually the result of different
execution plans.

Roy Harvey
Beacon Falls, CT|||Thanks for the responses...

Yes, The execution plans are different in both the databases.
Why the execution plans are so different in both the versions of SQL
Server 2005? Can we control the execution plans?

On Dec 16, 6:39 am, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

On Fri, 15 Dec 2006 15:54:07 -0600, "Russ Rose" <russr...@.hotmail.com>
wrote:
>

Quote:

Originally Posted by

Assuming identical hardware, a 10x speed difference is usually the
difference between memory and disk access.My experience, for what it is worth, has been that extreme performance

differences - better or worse - are usually the result of different
execution plans.
>
Roy Harvey
Beacon Falls, CT

If there are no indexed views in the database ans data is selected from
tables directly, then should there be any difference in performance?

Quote:

Originally Posted by

>"Indexed views can be created in any edition of SQL Server 2005. In
>SQL Server 2005 Enterprise Edition, the query optimizer automatically
>considers the indexed view. To use an indexed view in all other
>editions, the NOEXPAND table hint must be used."

Thanks and regards,
Nishant Saini
http://www.simplyjava.com|||Nishant Saini (nishant.saini@.gmail.com) writes:

Quote:

Originally Posted by

Thanks for the responses...
>
Yes, The execution plans are different in both the databases.
Why the execution plans are so different in both the versions of SQL
Server 2005?

There could be many reasons for that. Roy Harvey mentioned indexed views
for instance.

If you want a better answer, please post the query and the two plans,
so that we know what we are talking about.

Quote:

Originally Posted by

Can we control the execution plans?

Yes. SQL 2005 actually permit you to specify the plan exactly to using
plan guides. This is definitely an advance feature, and nothing you
should use at a whim.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||As Nishant said we are not using any special features of Enterprise
Edition. Indexed views are not being used and data is taken directly
from tables.

Regarding the execution plan, it may be different due to different
sizes of both databases. We are right now trying to check execution
plan for similar databases on both editions. We will post our findings
as soon as we get them.

Right now we need to know

1. Enterprise Edition has its own Query running mechanism ( which is
different and faster than Standard Edition mechanism) or NOT ?
2. If it is special, what kind of performance differences we will get
while using Std Edition.
3. Is there any specific DB size ( or table sizes ) for which Standard
Edition is fine and after that one should go for Enterprise.

Thanks in advance
Nitin Goyal

On Dec 16, 4:39 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Nishant Saini (nishant.sa...@.gmail.com) writes:

Quote:

Originally Posted by

Thanks for the responses...

Quote:

Originally Posted by

Yes, The execution plans are different in both the databases.
Why the execution plans are so different in both the versions of SQL
Server 2005?There could be many reasons for that. Roy Harvey mentioned indexed views

for instance.
>
If you want a better answer, please post the query and the two plans,
so that we know what we are talking about.
>

Quote:

Originally Posted by

Can we control the execution plans?Yes. SQL 2005 actually permit you to specify the plan exactly to using

plan guides. This is definitely an advance feature, and nothing you
should use at a whim.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||<nitin.goel@.daffodildb.comwrote in message
news:1166429585.200709.298530@.80g2000cwy.googlegro ups.com...

Quote:

Originally Posted by

As Nishant said we are not using any special features of Enterprise
Edition. Indexed views are not being used and data is taken directly
from tables.
>
Regarding the execution plan, it may be different due to different
sizes of both databases. We are right now trying to check execution
plan for similar databases on both editions. We will post our findings
as soon as we get them.
>
Right now we need to know
>
1. Enterprise Edition has its own Query running mechanism ( which is
different and faster than Standard Edition mechanism) or NOT ?

The query engine is the same.

Quote:

Originally Posted by

2. If it is special, what kind of performance differences we will get
while using Std Edition.
3. Is there any specific DB size ( or table sizes ) for which Standard
Edition is fine and after that one should go for Enterprise.

Not specifically.

However, Enterprise Edition supports more physical RAM which can make some
queries faster.

In addition, it can support things like parellelized query builds.

In general EE is more for specific features (like clustering beyond 2 nodes)
than pure size.

If you have a mission critical database that's 1 gig, but you want an N+2
architecture, you'll want EE.

If you have a datawarehouse that 500 gig that can be down for periods of
time during restores, etc, Standard Edition may be fine.

Check out MS's page on feature differences.

Quote:

Originally Posted by

>
Thanks in advance
Nitin Goyal
>
>
On Dec 16, 4:39 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

>Nishant Saini (nishant.sa...@.gmail.com) writes:

Quote:

Originally Posted by

Thanks for the responses...

Quote:

Originally Posted by

>for instance.
>>
>If you want a better answer, please post the query and the two plans,
>so that we know what we are talking about.
>>

Quote:

Originally Posted by

Can we control the execution plans?Yes. SQL 2005 actually permit you to
specify the plan exactly to using

>plan guides. This is definitely an advance feature, and nothing you
>should use at a whim.
>>
>--
>Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>>
>Books Online for SQL Server 2005
>athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
>Books Online for SQL Server 2000
>athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||

Quote:

Originally Posted by

Right now we need to know
>
1. Enterprise Edition has its own Query running mechanism ( which is
different and faster than Standard Edition mechanism) or NOT ?
2. If it is special, what kind of performance differences we will get
while using Std Edition.
3. Is there any specific DB size ( or table sizes ) for which Standard
Edition is fine and after that one should go for Enterprise.
>
Thanks in advance
Nitin Goyal

The engine is not the same. Have a look here:
http://www.microsoft.com/sql/prodin...e-features.mspx
especially in "Scalability and Performance" and "High Availability"
sections.

As for choosing Standard or Enterprise, you should think about
concurrent users and operations, not database size.

Regards,
lucm|||lucm wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Right now we need to know

Thanks in advance
Nitin Goyal

>
The engine is not the same. Have a look here:
http://www.microsoft.com/sql/prodin...e-features.mspx
especially in "Scalability and Performance" and "High Availability"
sections.
>
As for choosing Standard or Enterprise, you should think about
concurrent users and operations, not database size.
>
Regards,
lucm

Although the storage engine is different (or at least exposes more
features), the query optimizer is the same. So if you are not using
indexed views, you can live with 4 CPU's and you do not need the
advanced OLAP tools, then you can expect the same query performance. The
only documented exception is Advanced Scans which could improve
performance in OLAP type situations.

For a manageability and high availability point of view, there are many
reasons to choose EE, but IMO not from a performance point of view
(given the restrictions above).

If you disagree with me, then please mention a specific feature that
will cause EE to outperform SE (on regular DML statements).

Gert-Jan|||(nitin.goel@.daffodildb.com) writes:

Quote:

Originally Posted by

If you want to compare Standard and Enterprise, you must of course
use the same database on the same hardware. Else the test is meaningless.

Quote:

Originally Posted by

Rather than asking questions on the net, and hope that people will look
up the answers for you, why not look in this place in Books Online
instead:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/81f3e917-884a-4cc8-aca2-0a5fea89f355.htm

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Here is the H/W and database configuration in this case.

Parallel queries: Parallel Query processing runs only on multiple
processors. In our case only one processor is available to SQL Server.

RAM: it is an OS issue and not of SQL Server. There is no RAM support
specification in SQL Server (EE / SE). By the way, OS used by us is Win
2003 Server EE.

FYI, we have searched MS SQL Server books online + seen the differences
of SE and EE given by MS + asked our local vendor but he knows nothing.
And the queries run by us are select queries with joins on around 5 -1
0 tables. So the picture is still murky and Microsoft is not answering.

And Mr Sommarskog, we do not want anyone to search for us. We are
looking for very specific answers and we will be thankful if someone
can help us (we feel others have also faced same problems.)

Thanks
Nitin Goyal

On Dec 19, 3:14 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(nitin.g...@.daffodildb.com) writes:

Quote:

Originally Posted by

As Nishant said we are not using any special features of Enterprise
Edition. Indexed views are not being used and data is taken directly
from tables.

Quote:

Originally Posted by

Regarding the execution plan, it may be different due to different
sizes of both databases.If you want to compare Standard and Enterprise, you must of course

use the same database on the same hardware. Else the test is meaningless.
>

Quote:

Originally Posted by

up the answers for you, why not look in this place in Books Online
instead:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/81f3e917-884a-4cc8-aca2-0a5fea89f355.htm
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||

Quote:

Originally Posted by

System: - HP Proliant ML 370 G4 Server ( Intel Xeon 3.2 GH on Intel
7520 Chipset; Dual CPU capable )
RAM: - 4 GB DDR2 ECC
HDD: - 2 * 73 GB SCSI ( 15000 RPM ) RAID 0
DB size: - 20 GB
Downtime: - 2 hours a day i.e. morning 5 to 7 AM.
>
Parallel queries: Parallel Query processing runs only on multiple
processors. In our case only one processor is available to SQL Server.
>
RAM: it is an OS issue and not of SQL Server. There is no RAM support
specification in SQL Server (EE / SE). By the way, OS used by us is Win
2003 Server EE.
>

First of all, RAID-0 is a no-go. If this is a production server you
need at least a mirror (RAID-1).

Now, do you *need* EE or not? Without a load average it is difficult to
say. Since you purchased a server with a single CPU and a small amount
of RAM, you probably don't have too many concurrent users.

One of my customers has up to 100 concurrent users, he has a hardware
weaker than yours, a bigger database, and he is very happy with SE.
They make quite an extensive use of the server, and they can afford the
downtime when they restore lost data or rebuild indexes.

Regards,
lucm|||nitin.goel@.daffodildb.com wrote:

Quote:

Originally Posted by

>
Here is the H/W and database configuration in this case.
>
System: - HP Proliant ML 370 G4 Server ( Intel Xeon 3.2 GH on Intel
7520 Chipset; Dual CPU capable )
RAM: - 4 GB DDR2 ECC
HDD: - 2 * 73 GB SCSI ( 15000 RPM ) RAID 0
DB size: - 20 GB
Downtime: - 2 hours a day i.e. morning 5 to 7 AM.
[snip]

I am not sure you are aware of this, but the EE is approximately 4 times
as expensive as SE (when using CPU-based licensing). There is no way you
can make a business case that justifies spending an additional $15,000
on EE for performance reasons. If you were to spend another x dollars on
performance it would definitely be in the hardware, in whatever area is
limiting perfomance (in your case most likely in the I/O area). But if
you don't have to spend the money now, then you can postpone such
decision and see how it runs with the current hardware.

As for your maintenance window: it should be more than sufficient to
handle any problems with a database of just 20 GB, so from that point of
view you don't need EE either.

Gert-Jan|||(nitin.goel@.daffodildb.com) writes:

Quote:

Originally Posted by

Here is the H/W and database configuration in this case.
>
System: - HP Proliant ML 370 G4 Server ( Intel Xeon 3.2 GH on Intel
7520 Chipset; Dual CPU capable )
RAM: - 4 GB DDR2 ECC
HDD: - 2 * 73 GB SCSI ( 15000 RPM ) RAID 0
DB size: - 20 GB
Downtime: - 2 hours a day i.e. morning 5 to 7 AM.
>
Parallel queries: Parallel Query processing runs only on multiple
processors. In our case only one processor is available to SQL Server.

Does this mean that there are more CPUs in the box, but there are
other applications that gets to use those? Or is there only one CPU
in the box, and SQL Server is the only app on the machine?

The main reason I ask this, is that if there other apps on the machine,
this makes it even more difficult to assess your questions.

Quote:

Originally Posted by

And since you never post any of the queries, and nor the query plans,
you are not giving us any chances to straighten things out.

But I like to agree with what Gert-Jan said. If you have extrememe
performance requirement, you may want to cough up the extra money for
Enterprise to get whatever small fraction you can win. But if you
had those requirements, you would not let it suffice with a single
CPU box.

So go for Standard. Keep in mind that if your business expands and
you will want Enterprise one day, you can always upgrade at that point.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||<nitin.goel@.daffodildb.comwrote in message
news:1166597810.015478.291810@.a3g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

RED ALERT!!!

Switch to RAID 1 or add at least one more disk and go RAID 5 or kiss your
data (and job?) goodbye.|||Thank you for replies. Some more information from my side:

1. Sorry I have made a mistake, our DB size is 50 GB not 20 GB (typing
mistake).
2. There are around 100 concurrent users accessing this db from 30
locations.
3. There is only one CPU in the box and only SQL Server runs on it.
4. Regarding I/O performance, what more can be done in I/O area (
that's a very novice Q but I have never worked in this field ).
5. We will post some queries by Monday morning.

BTW I know price of both SE and EE but we will spend only if we see any
gain EE. And we are also ready to spend on I/O if it is good for us.

Thanks again
Nitin Goyal|||Thank you for replies. Some more information from my side:

BTW I know price of both SE and EE but we will spend only if we see any
gain EE. And we are also ready to spend on I/O if it is good for us.

Thanks again
Nitin Goyal|||Thank you for replies. Some more information from my side:

BTW I know price of both SE and EE but we will spend only if we see any
gain EE. And we are also ready to spend on I/O if it is good for us.

Thanks again
Nitin Goyal|||<nitin.goel@.daffodildb.comwrote in message
news:1166681115.492154.131080@.79g2000cws.googlegro ups.com...

Quote:

Originally Posted by

Thank you for replies. Some more information from my side:
>
1. Sorry I have made a mistake, our DB size is 50 GB not 20 GB (typing
mistake).
2. There are around 100 concurrent users accessing this db from 30
locations.

Again, the TYPE of query really matters. If each query takes a 100
milliseconds vs. 10 seconds.

Quote:

Originally Posted by

3. There is only one CPU in the box and only SQL Server runs on it.

Well you may or may not be CPU bound, again, depends a lot on what you're
doing her.e

Quote:

Originally Posted by

4. Regarding I/O performance, what more can be done in I/O area (
that's a very novice Q but I have never worked in this field ).

Add disks. Lots of disks. Fast disks.

You're better off with 10 40 gig disk tha 1 400 gig disk for example.

(of course that's ignoring RAID and doing stuff like putting your logs on
their own physical disk group.)

Just as a point of reference we had a system handling 14 million selects and
inserts a day running on a 6 yo 4-way Xeon machine up until recently. Just
recently moved this to a 2-way modern box AND added a bunch of other
queries... and if anything it's faster than before.

Quote:

Originally Posted by

5. We will post some queries by Monday morning.
>
BTW I know price of both SE and EE but we will spend only if we see any
gain EE. And we are also ready to spend on I/O if it is good for us.
>
>
Thanks again
Nitin Goyal
>

|||1. Sorry I have made a mistake, our DB size is 50 GB not 20 GB (typing

Quote:

Originally Posted by

mistake).
2. There are around 100 concurrent users accessing this db from 30
locations.
3. There is only one CPU in the box and only SQL Server runs on it.
4. Regarding I/O performance, what more can be done in I/O area (
that's a very novice Q but I have never worked in this field ).
BTW I know price of both SE and EE but we will spend only if we see any
gain EE. And we are also ready to spend on I/O if it is good for us.

Basic rules for the I/O on a database server: a RAID-10 array using as
many disks as possible (even number) is the best scenario for the
databases, while a 2-disk RAID-1 array is great for transaction logs.

If you use temporary tables, having a distinct 2-disk RAID-1 array for
tempbd is excellent (even better: a 4-disk RAID-10). As for the disks
themselves, the smaller and faster is the better; your 73 GB 15k are
excellent.

If you can afford it, having a RAID controller the databases (RAID-10)
and another one for the transaction logs and tempdb (RAID-1 or RAID-10)
is optimal.

Also don't be shy to add some RAM, as much as you can.

Regards,
lucm

Wednesday, March 28, 2012

Performance Difference between SQL Server 2005 Standard Edition and Enterprise Edition

No comments:

Post a Comment

Percentage of total

Blog Archive

About Me