Tuesday, June 18, 2019

Server 2017 Standard vs Enterprise

In-memory OLTP and in-memory Columnstore are limited to the amount of memory specified by edition in the Scale Limits section. Columnstore and Real-time Operational Analytics are also limited to two degrees of parallelism (two cores/query) in SQL Server Standard edition. 


SQL Server 2017
Enterprise
Standard
The premium offering, SQL Server Enterprise edition delivers comprehensive high-end data-center capabilities with enabling high service levels for mission-critical workloads and end user access to data insights.
SQL Server Standard edition delivers basic data management and business intelligence database for departments and small organizations to run their applications and supports common development tools for on-premise and cloud - enabling effective database management with minimal IT resources.
It offers fast performance, an unlimited amount of virtualization, advanced analytics, maximum memory utilized per instance, adaptive query processing and end-to-end business intelligence.
The Standard Edition supports common tools for development of both on-site and virtual systems with basic reporting, basic analytics, end-to-end database security and enhanced memory performance.
It can provide the solution for businesses that demand high service levels for their workloads and also require end-user access to data insights.
It allows a maximum of 24 processor cores, and some of its defining features include 128 GB memory buffer pool.

An instance as an installation of SQL Server. A single installation of SQL can run any number of databases - big, small, OLTP, OLAP, doesn’t matter. But I’m Standard edition, the instance will not exceed 128GB of RAM whereas Enterprise edition has no limit - it will consume as much as the OS supports.
It should also technically be noted, that if you have two instances of standard, each would use up to 128GB, which would actually use up to 256GB on the system. Not that multiple instances on the same server is recommended.
The buffer pool is used by all databases (i.e. it is not per database). The max on standard is 128GB. So if you're server has 200GB of member then 72GB will not be used by SQL's buffer pool. That 72GB will be used by the OS for it's stuff including any running programs etc... so it might be over sized but not by a heck of a lot.
Note that the 128GB limit is only for the buffer pool. There are other caches/pools in Standard that get added on top of this. And memory for running queries is also added on top of this as well. So it's entirely possible for a Standard to consume allot more than 128GB of memory. Depending on your workload it might be warranted to allocate 200GB memory to a Standard edition.
One other thing that stinks about standard:
a. Columnstore indexes limited to 32GB total for the entire install (multiple databases all share the same cap)
b. Worse: index and compression both share the same CPU core, and are limited to a single core. so indexes take forever, if you add row/page/columnstore it makes that "Forever" taking even more forever time. ROW has minimal impact from my experience but page/columnstore add 3-5x to the run time.
Creating an index on standard with a few hundred million records often took 60-90 minutes. adding compression (row) added only a few minutes, but page/columnstore several hours.

Conclusion

Despite many similarities between the two editions for management tools such as RDBMS manageability, Development Tools, and Programmability but they provide a clear picture to meet in your business requirements that Enterprise delivers more functionality than the other editions of SQL Server, including the Standard edition.  In the one hand, a company that has a need for all the additional features and scalability of Microsoft SQL Enterprise, should opt for this edition but other hand whereas, smaller organizations may be able to utilize the foundation offered by SQL Server Standard edition.

No comments:

Post a Comment