473,881 Members | 1,693 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Performance Difference between SQL Server 2005 Standard Edition and Enterprise Edition

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

Dec 15 '06
23 14057

<ni********@daf fodildb.comwrot e in message
news:11******** **************@ 80g2000cwy.goog legroups.com...
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.

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.
>
Thanks in advance
Nitin Goyal
On Dec 16, 4:39 pm, Erland Sommarskog <esq...@sommars kog.sewrote:
>Nishant Saini (nishant.sa...@ gmail.com) writes:
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.
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...@sommarsk og.se

Books Online for SQL Server 2005
athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000
athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

Dec 18 '06 #11
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/prodinf...-features.mspx
especially in "Scalabilit y 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

Dec 18 '06 #12
lucm wrote:
>
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/prodinf...-features.mspx
especially in "Scalabilit y 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
Dec 18 '06 #13
(ni********@daf fodildb.com) writes:
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.
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.
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.
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.ht m

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 18 '06 #14
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.

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...@sommars kog.sewrote:
(nitin.g...@daf fodildb.com) writes:
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.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.
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.Rath er 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.ht m

--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se

Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx
Dec 20 '06 #15

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

Dec 20 '06 #16
ni********@daff odildb.com wrote:
>
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
Dec 20 '06 #17
(ni********@daf fodildb.com) writes:
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.
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 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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 20 '06 #18

<ni********@daf fodildb.comwrot e in message
news:11******** **************@ a3g2000cwd.goog legroups.com...
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
RED ALERT!!!

Switch to RAID 1 or add at least one more disk and go RAID 5 or kiss your
data (and job?) goodbye.

Dec 21 '06 #19
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

Dec 21 '06 #20

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
1757
by: alex4groups | last post by:
Hi, I've been creating a db application using MS Access and MSDE. Only two of us are using the application, and the server and the app both run great on my laptop (1.6 GHz Pentium M, 2GB RAM, W2KPro). Only problem is when I take my laptop home, my coworker loses access to the server. We recently purchased a dedicated server to run the db on at the office. It's a 2.8 GHz Dual Xeon, 2GB RAM, running XPPro. We also bought SQL Server,...
22
3312
by: EP | last post by:
When running my asp.net hosting service (asp.net without IIS), on server 2003 with IIS not installed, I get the following when trying to process a request. "System.DllNotFoundException: Unable to load DLL (aspnet_isapi.dll)." Of course the dll is able to be found, it's still in the framework directory and for grins I even put it in my service's local directory. This is apparantly server 2003 not allowing asp.net to be run if IIS was not...
14
3052
by: Developer | last post by:
Hello All, i have recently installed VS2005 and was trying to install SQL sever 2000. I have Win XP' SP2. But when I tried installing, it only installed client tools and not the database. Can anyone please help me with this as I want to install SQL server and also wouold be grateful, if you can suggest me any workaround to dealwith this problem.(Like should I install any new OS etc). Any help would be appreciated.
9
5768
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just call it Express for simplicity). I have, to try to simplify things, put the exact same DB on two systems, one running MSDE and one running Express. Both have 2 Ghz processors (one Intel, one AMD), both have a decent amount of RAM (Intel system...
14
2669
by: Guillermo_Lopez | last post by:
Hello, Our company has developed several Access applications for our clients and we wish to expand to use a database server. We wish to use Access as the front end application and SQL Server 2005 as the back end Databes Server. Making a Server-Client application database. I'm very new to SQL Server so my concern is how the licensing works for a developer like us. I am aware that we can purchase the Developer edition for $49 USD. Our...
1
3599
by: nataraj2502 | last post by:
Hi, I have just configured my C# multi-threaded application from 32 bit to 64 bit platform for getting more memory support. But my application runs up to 40 - 50% slower as compared to 32 bit platform. The overall idea about the multithreaded application is, we do recalculation for server hundred thousand records available in the database. Fetch the records from the table and do the recalculation finally it will update it into the...
0
10718
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10816
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7953
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7110
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5781
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5977
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4597
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4196
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3225
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.