Microsoft SQL Server Enterprise Edition has few additional internal features that can provide high performance database system for highly transactional and heavy workload systems such as ‘Payment Gateways’, Analytical and Reporting systems such as ‘Credit Reference Information System (CRIS) for banks and insurances that are not available in Microsoft SQL Server Standard Edition. Basically, Microsoft sales team totally talk and explain features such as HADR, Business Intelligence and Supported memory and CPU and license fee of SQL Server Enterprise edition compare to other editions, in the other hand due to customer’s lack of knowledge about the SQL Server product internal features, they are willing to go for Standard edition due to lower license fee. All of us know that Standard edition is much cheaper than Enterprise but in what cost? in the cost of medium to low performance during heavy workload and high usage of hardware resources?
Case Study: There is a company that provides Payment Gateway services and its production server is using SQL Server Standard Edition, to provide great service it needs to have high performance database server, which needs to handle multiple reports at same time whilst is accepting payment transactions into the databases. after a year of providing service, the company encounter few major issues regarding huge database size, low performance and the database system is not able to archive few million historical records within a second without downtime or any performance impact. By right all those issues raised by purchasing unsuitable SQL Server edition for their production server. This company could resolve this issues by getting SQL Server Expert consultant advice before purchasing licenses.
If SQL Server Enterprise Edition is Expensive, What about Other Editions!
Features that ONLY EXISTS in SQL Server Enterprise Edition.
1- Advance Scan (Merry Go Round Algorithm): This feature allows multiple tasks to share full table scans. If the execution plan of a Transact-SQL statement requires a scan of the data pages in a table and the Database Engine detects that the table is already being scanned for another execution plan, the Database Engine joins the second scan to the first, at the current location of the second scan. The Database Engine reads each page one time and passes the rows from each page to both execution plans. This continues until the end of the table is reached. Without advanced scanning, each user would have to compete for buffer space and cause disk arm contention. The same pages would then be read once for each user, instead of read one time and shared by multiple users, slowing down performance and taxing resources.
2- Fast Recovery: Fast Recovery allows a database to come online as soon as the REDO phase completes, before UNDO is run. This is made possible by lock logging, which records the locks that were applied by a transaction in the associated log record.
3- Prefetching: The leaf rows of a nonclustered index contain pointers to the data rows that contain each specific key value. As the storage engine reads through the leaf pages of the nonclustered index, it also starts scheduling asynchronous reads for the data rows whose pointers have already been retrieved.
4- Single Scatter Scan: The SQL Server storage engine scans the intermediate index page and builds a list of the leaf pages that must be read. The storage engine then schedules all the reads in key order. It also recognizes that for instance pages 308/465 and 488/489 are contiguous and performs a single scatter read to retrieve the adjacent pages in a single operation.
5- Automatic Matching: The Query Optimizer automatically matches the computed column definition to an existing scalar expression in a query.
6- Role Reversal: In Hash Join, after the build input is hashed, the second table, called the probe input, will be read and compared to the hash table. If rows are matched they will be returned. On the execution plan, the table at the top will be used as the build input, and the table at the bottom as the probe input. If the Query Optimizer is not able to correctly estimate which of the two inputs is smaller, the build and probe roles may be reversed at execution time, and this will not be shown on the execution plan.
7- Partitioning: You can transfer or access subsets of data quickly and efficiently, while maintaining the integrity of a data collection. For example, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes when the data is not partitioned. And it improve query performance, based on the types of queries you frequently run and on your hardware configuration. For example, the query optimizer can process equi-join queries between two or more partitioned tables faster when the partitioning columns in the tables are the same, because the partitions themselves can be joined.
8- Data Compression: This feature is to reduce the size of the database. In addition to saving space, data compression can help improve performance of I/O intensive workloads because the data is stored in fewer pages and queries need to read fewer pages from disk.
9- Deferred Transactions: A deferred transaction is a transaction that is uncommitted when the roll forward phase finishes and that has encountered an error that prevents it from being rolled back. Because the transaction cannot be rolled back, it is deferred. In other editions, a corrupted transaction causes database startup to fail.
10- Vardecimal Storage Formatting: This storage format can be enabled at a table-level granularity. When enabled, SQL Server stores decimal and numeric data in the variable portion of the row instead the fixed portion. You can use vardecimal storage format to reduce the size of your database if you have tables with decimal and numeric data types.
Updated on 27 October 2015.
I hope you realized why SQL Server Enterprise edition is much more expensive now!
Thanks for reading this article.
Author: Hamid Jabarpour Fard