Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Differences between SQL 2K EE and SE?

Author  Topic 

CanadaDBA

583 Posts
Posted - 2005-01-10 : 12:53:26
We have SQL Server 2000 Enterprise edition in our development site while we have Standard Edition in our production. Should I suggest to upgrade our production?

Our production box has 4 CPU with 4GB RAM running Windows 2000 Server. Can the server utilize all its memory?

Canada DBA

Sitka
Aged Yak Warrior

571 Posts
Posted - 2005-01-10 : 13:19:35
Can the server utilize all its memory
Not for the SQL Service.

What is the specifications of the development box.
You should be able to test a nice range of real queries to get a baseline
for expected performance improvements.

Money Money Money, Depends on which licencing scheme is in place or are going to use on the production box, what has already been purchased.

Mix an observable set of metrics on the current machine with the user experience and see if it is being pushed to the point that folks are getting slowed down. http://www.sql-server-performance.com has some recommendations for the quantification of some of the performance counters.
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2005-01-10 : 13:28:36
If you want SQL Server to use more than 2GB of RAM, then you need EE.
With just 4GB's of RAM in the system, I'd not bother becuase the cost of the upgrade is pretty large.

If you had like 6GB+, I'd say move to EE and use PAE to allow SQL Server to use more than 2GB's of RAM

Michael


<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

Sitka
Aged Yak Warrior

571 Posts
Posted - 2005-01-10 : 13:45:02
Yeah good point MichaelP,

Factor in getting more ram with the upgrade to:
upgrading a busy 4 proc server to EE is going to make the costs of doubling (or tripling) the ram in the prod server seem like nothing.

I wish someone would start an Official XML Rant Thread.
Go to Top of Page

CanadaDBA

583 Posts
Posted - 2005-01-10 : 14:17:07
Thank you both. My dev box is almost same as the prod one. I think I'd keep the situation as is or down grade the dev server to SE, if higher management decide to sync servers from version and edition point of view.

Canada DBA
Go to Top of Page

Sitka
Aged Yak Warrior

571 Posts
Posted - 2005-01-10 : 15:26:24
Just curious,
Are you running into an incompatability? To me that would speak to some very sophisticated development.
Any performance issues with "test" ok, rollout "sucks", should be able to be addressed in the form "test" is blazing fast, and "rollout" is acceptable, The x-Factor

But in general let the production server usage and user experience decide. Utilization, got to keep those numbers up or the corporate entity is wasting money on unused resources. It's good to be able to provide some science as to how a production server upgrade would factor into a ROI arguement. I've come to see that any slow down for the user is really too much (inverse to utilization), as it is multiplied by every day they work, and takes away from their flow and cascades to all that rely on their efforts. "Work expands to fill time alloted" (Parkinson's(rough quote)). If the systems are sluggish the User will adjust their productivity downward and it will become the accepted baseline (they will stop calling). This can sit festering for years, Lack of productivity is expensive, and folks always amaze me with how much they can get done when the flow is just right, plus they are happier. So "User" calls and says, "my application is slow", pull some metrics, find out where the bottleneck is and address it. This can be as simple as how they apply filters.
Don't let others say we can do "this" or "that" cause bottlenecks are bottlenecks there is little other effort that can help performance when bottlenecked, It's great satisfaction when the bottleneck is eliminated and moves to the next one.

To quote a famous poster.

Go with the flow & have fun! Else fight the flow

Some say

Who Floz'it, Knoz'it

I like

CCR (Critical Constraining Resource)
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-01-11 : 01:47:18
Have you got any Indexed Views? EE will use them in query plan, SE won't. That might make a performance difference.

Kristen
Go to Top of Page

CanadaDBA

583 Posts
Posted - 2005-01-11 : 12:50:53
Well, we want to prevent falling into incompatibility. At this time we don't have any problem. Even the performance problems are not related to using EE or SE. But I was asked to sync all servers, either Test or Dev or Production.

Another request is that we have to upgrade our servers from Win2k to Win2k3 and I am going to post a title in this regard. Please read that and let me know your idea.
quote:
Originally posted by Sitka

Just curious,
Are you running into an incompatability? ...


Canada DBA
Go to Top of Page

CanadaDBA

583 Posts
Posted - 2005-01-11 : 12:59:14
Actually, I remember that some months ago, the developers created an application which was using Indexed Views. And at the time that they were developing and testing, everyting was fine. The application is loaded in production and is running now. Do you think that the application is working without using the Indexed View? And therefore, that specific part is slow?

quote:
Originally posted by Kristen

Have you got any Indexed Views? EE will use them in query plan, SE won't. That might make a performance difference.

Kristen


Canada DBA
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-01-11 : 13:51:26
"Do you think that the application is working without using the Indexed View?"

Yes - but I think the circumstances are quite limited. For example, a query on the underlying table that would benefit from the index which was ACTUALLY created against the View itself. I believe that only EE can do this - but its pretty much the only thing I can think of that could cause a significant performance difference on two otherwise identical machines, but where one had EE and the other SE.

I'll be blowed if I can find the relevant bit in BoL just now :-(

Ah ... sort of found something. "Features Supported by the Editions of SQL Server 2000" has a table and things which look relevant supported by EE, but not SE, include:

Enhanced Read-ahead and Scan
Indexed Views
plus a bunch of stuff in Analysis Services

Clearly "Indexed Views" are "supported" in SE, so I guess it is just that SE does not use them for performance purposes. It certainly uses them for Unique Constraints because we have an Indexed View to achieve that and that works on SE

CREATE VIEW MyView
AS
SELECT [MakeItUnique] = MyColumn
FROM MyTable
WHERE MyColumn IS NOT NULL

and then create a Unique Index on MyView.MakeItUnique :-)

The only other thing I can find is "Troubleshooting the Index Tuning Wizard"

Quote: "Add Indexed Views option is grayed out
Indexed views are available only on Microsoft SQL Server 2000 Enterprise Edition."

Oh good grief, there's more - but it all adds to the FUD:

"Designing an Indexed View
Note Indexed views can be created in any edition of SQL Server 2000. In SQL Server 2000 Enterprise Edition, the query optimizer will automatically consider the indexed view. To use an indexed view in all other editions, the NOEXPAND hint must be used."

"And therefore, that specific part is slow?"

Depends if the Query Cost Estimator would use that index and if there is enough data for the query to actually be significantly slower

We dropped an index, by mistake, when going from Test to Production once and it took us ages to spot that that was why the Production system was slower than anticipated :-(

Kristen
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site