473,702 Members | 2,325 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 #1
23 14031
Nishant Saini (ni***********@ gmail.com) writes:
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?
--
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 15 '06 #2

Nishant Saini wrote:
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

Dec 15 '06 #3
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"
<ni***********@ gmail.comwrote:
>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 #4
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:
>
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 #5

"Nishant Saini" <ni***********@ gmail.comwrote in message
news:11******** **************@ 73g2000cwn.goog legroups.com...
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.
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.
>
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.
>
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 #6
On Fri, 15 Dec 2006 15:54:07 -0600, "Russ Rose" <ru******@hotma il.com>
wrote:
>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
Dec 16 '06 #7
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...@sne t.netwrote:
On Fri, 15 Dec 2006 15:54:07 -0600, "Russ Rose" <russr...@hotma il.com>
wrote:
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?
>"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

Dec 16 '06 #8
Nishant Saini (ni***********@ 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, 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 16 '06 #9
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...@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 #10

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

Similar topics

15
1744
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
3295
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
3032
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
5759
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
2651
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
3583
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
8652
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9234
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9089
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...
0
8940
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7831
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6575
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
5907
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();...
2
2402
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2036
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.