Sql 2005 Standard vs Enterprise
Werner van Zyl
a few performance issues at the moment, will it help upgrading them to
Enterprise edition or am I wasting my time?
Thanks in advance
Lawrence Garvin
news:AE2A2E90-2F67-4709...@microsoft.com...
>I have a client with a 123GB DB running on SQL 2005 Standard edition, we
>have
> a few performance issues at the moment, will it help upgrading them to
> Enterprise edition
It really depends on *why* you have performance issues. In a database
environment, there are four primary causes of performance bottlenecks, in
order of most likely:
1. Insufficient memory for buffering data (very common in larger database
environments)
2. Insufficient disk throughput
3. Insufficient processor resources
4. Insufficient network bandwidth.
Both Standard Edition and Enterprise Edition of SQL2005 support the same
memory limitations -- whatever the OS will support; however, Enteprise
Edition does support more than four CPUs. So unless you have a
dual-socket/quad-core systemboard with only 1 CPU, that's about the only
advantage I can see from upgrading the database server only.
You'll likely get much more benefit from upgrading the OS to Enterprise
Edition, or, given the size of your database, to 64-bit.
If you're already running a 64-bit Enterprise Edition OS, then some of the
more advanced performance-related capabilties of SQL Server Enterprise
Edition might be of benefit -- such as table partitioning and online index
maintenance. See the SQL2005 feature comparison for more details:
http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx
The first step, though is to isolate why you're having performance issues
and then apply the appropriate solution for the identified bottleneck:
1. For memory bottlenecks:
- add more memory
- convert to 64-bit so you can add more memory
- convert a 32-bit OS to Enterprise Edition so that you can recognize
more than 4GB
2. For disk throughput bottlenecks:
The minimal ideal SQL Server should have at least 8 disk spindles:
- 2 spindle RAID1 (Mirror) for OS/Paging
- 2 spindle RAID1 (Mirror) for Database Transaction Logs
- 1 spindle drive for TEMPDB
- 3 spindle RAID5 (Parity Stripe) for Databases
Additional enhancements beyond that would include additional spindles in a
RAID0 stripe for TEMPDB,
or additional spindles to convert the RAID5 to a RAID10 for the databases,
and possibly moving the paging file to a dedicated spindle.
--
Lawrence Garvin, M.S., MCITP:EA, MCDBA
Principal/CTO, Onsite Technology Solutions, Houston, Texas
Microsoft MVP - Software Distribution (2005-2009)
My Blog: http://onsitechsolutions.spaces.live.com
Microsoft WSUS Website: http://www.microsoft.com/wsus
My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin