April 30, 2020 at 12:27 am
Hello !!
First, let me say im not a DBA expert, and im facing a hard decision
We have a DB on SQL 2008 Enterprise. Since SQL 2008 is out of support from MS we decided to move to a newer version (2019)
My main question is:
Is there any performace difference between Standard and Enterprise? (i know the memory limitation)
I mean, if I go for standard, that version not includes features that will improve the performance of the DB?.
For example. If I have the same hardware (with 128GB of ram) the enterprise will be "faster" than standard version.
I know some of you may think, "what this guy is asking!!!" Sorry for that, im not very familiar with DB's
Im not interestend in other features only a database engine to storage data (dont care datawarehouse features, replication, cluster, etc)
If only i will storage data, there something that i will miss if I go for standard?
Hope you can help me. Diference of $$$ between two versions is high, and in 3rd world countries, that is a lot of money!
thanks a lot in advance!
April 30, 2020 at 12:46 am
have a look at https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15
There are a few differences that can affect performance - you may or not suffer from them as it will depend on the type of usage you have - or it may be that you do get affected but with negligible differences - always hard to know.
for example standard lacks "Read-ahead" - this for a pure OLTP may not be that important - for batch/OLAP it can make a big difference, but even when it does maybe you can live with the difference vs cost.
April 30, 2020 at 1:11 am
Thanks a lot. I will read about "Read-ahead"
This kind of info I need to make a wise choice!
April 30, 2020 at 7:26 am
Also don't forget the changes with the cardinality estimator that where introduced in SQL2014, you will need to go over and test your queries, procedures, processed etc etc to ensure they are not affected by this change.
Some queries will be faster, others will stay the same, others will be worse due to the way that statistics and cardinality changed from 2014 onwards.
You may want to look at traceflags/settings to enable the legacy estimator, enable query store, then enable the new estimator and see the changes.
This will be one of the biggest gotchas (if not the biggest) from upgrading from anything prior to 2014 to anything 2014 or above.
April 30, 2020 at 9:54 am
All good advice above. Some other things to be aware of:
(1) You can't downgrade from Enterprise to Standard. You'd need to uninstall and then reinstall. If you have the SQL Server 2019 Standard and Enterprise installation media available, I would recommend upgrading to 2019 first and then doing the reinstall. If you were to do it before the version upgrade, you'd need to make sure you have the service packs available to bring your installation to exactly the same SP and CU level that it's at now.
(2) If your 2008 installation is 32 bit then you will need to reinstall whatever happens, since you can't upgrade from 32 to 64 bit. You'd need to do that before the version upgrade, since 2019 isn't available in 32 bit. (The good news about that, though, is that you should see an instant performance improvement, since 64-bit will allow you to use a much bigger chunk of the memory on your server.)
(3) Make sure that the Windows version on your server supports SQL Server 2008 and 2019. If it doesn't, you may need to upgrade Windows first. If there is no Windows version that supports both SQL Server versions, you will need to upgrade to an intermediate SQL Server version, upgrade Windows and then upgrade to SQL Server 2019.
(4) Make sure before you start your upgrades, downgrades and/or reinstalls, that you aren't using any Enterprise-only features in your databases. If you don't do that, you may find that you can't recover your databases when you restore or reattach them.
John
April 30, 2020 at 10:17 am
Following on from John's comments, above, you can see if you are using any of those enterprise-only features by spinning up an instance of SQL Server 2019 enterprise edition (or developer edition may work) , and restore a copy of the database into it. Then run a full functionality test of the database and applications against that server (which I hope would be part of your upgrade project anyway).
When that's done, have a look in each application database's sys.dm_db_persisted_sku_features DMV; this will tell you what features are being used that won't be supported if you downgrade (sorry, reinstall) to a lower edition of SQL Server.
Thomas Rushton
blog: https://thelonedba.wordpress.com
April 30, 2020 at 11:22 am
Thanks for mentioning sys.dm_db_persisted_sku_features, didn't know it existed
April 30, 2020 at 3:29 pm
Not sure with the same hardware you'll see better performance. In addition to memory, there are CPU limits. 24 cores for standard. Also memory for columnstore and hekaton are different (and SSRS). If those matter to you.
Good comments above on other items
Edition differences - https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-ver15
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply