Duty Free!!

SQL Write-Ahead Overhead…

SQL Server Standard Edition vs Enterprise Edition

Many a times a Solution Architect would fall in a dilemma whether to go with  SQL Server 2005 Standard Edition or Enterprise Edition especially when the price difference is so high.  

The best place to head to before looking into the Budget is to head to BOL – check – ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/81f3e917-884a-4cc8-aca2-0a5fea89f355.htm  This link would list the features present and not but is a lengthy one. 

As a summary, if we have to work on huge DBs with loads of Binary/ Large Objects  then EE could be preferred because of advanced Indexing and pre-caching. SE is limited in terms of Cluster nodes[<2] and instances[<16] but these are rarely a limitation. if the environment is 32 bit and large memory support is implemented , EE is preferred as it Dynamically manages AWE. Remember this can be possible only on Win 2003 onwards.

Other possible maintenance tasks where EE is preferred would be Online Indexing and Parallel Indexing and DBCC . These features are useful in mission critical and round-the-clock used servers. Also the Online Page and secondary File restore would help in quick recovery.

Other important performance boosters in EE are Table and Indexed Partitioning and Enhanced Read-ahead and Scan.These are particularly useful when the Tables hold large number of records. Still, Query tuning cant be surrogated by upgrading to EE. The Query Plan needs to be well tuned prior to going for EE and make use of the Index Partitioning and parallel operations. It has be kept in mind, that when there are lots of concurrent users making heavy operations it would be sometimes better to have Parallellism disabled [Degree of Parallelism to 1].

Incase of BI and Analysis services, EE is most preferable for many of the purposes. The above listed BOL page and http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx would provide tabular list of features.

March 5, 2009 Posted by | Uncategorized | Leave a comment