Which SQL version or edition should we use out of SQL server 2017std/ent or 2016std/ent in terms of better performance gain when you are dealing with SP's (approx 1000 or more)in single database which are equipped with complex joins over tables, views, udfs and also having SORT and ORDERBY clauses in many of the SPs. Thanks

  • 3
    You're looking at this the wrong way. SPs, UDFs, etc don't just get "slower" or "faster" with a version change. There are some things that could affect them out of the box, specifically the optimizer, but this isn't because they are procs or UDFs, it's the underlying code and DDLs. If I'm standing up a new SQL Server, I'm going with the latest version possible so I can minimize the number of upgrades / patching I need to do. – scsimon Oct 14 '19 at 13:36

There is not much different between 2016 and 2017 especially in terms of performance (except following points). I believe, the main aim of SQL 2017 is to cover cross platform support (Windows, Linux and Docker).

  1. Adaptive Query Processing
  2. Automatic database tuning

Of course always newer versions would be having additional benefit in general, you need to consider other aspects while deciding on version/edition, hope this blog post would be useful

| improve this answer | |

For version, 2017. Later versions have additional features and improvements, so for performance always prefer the latest version.

Enterprise Edition can scale up to larger machines: enter image description here

and has more sophisticated High Availability and Disaster Recovery features. But they have the same query optimizer and support the same programability features.

In general large databases, and databases with very demanding throughput requirements may perform better on Enterprise Edition, but smaller databases on smaller servers will perform the same.

See generally Editions and supported features for all of the differences.

| improve this answer | |
  • Thanks for the reply! But i am more concern about the features available in 2017 enterprise like Automatic Tuning, Batch Mode Adaptive Joins, Interleaved Execution for Multi-Statement Table Valued Functions etc. Are these features really impressive in practicality? – Sunny Oct 14 '19 at 13:44
  • 1
    They are, but it sounds like you have a particular application. And so you should test whether they have an impact on your applicaiton. – David Browne - Microsoft Oct 14 '19 at 13:48

If you need to check your code for any game-breaking issues, you can use the Database Migration Assistant against your current database.

INFO: https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15

DOWNLOAD: https://www.microsoft.com/en-us/download/details.aspx?id=53595

You can then see if going to either one will be impossible due to features you are using. Off the top of my head, I don't know of any that changed between 2016-2017. It is possible that your current version won't work on either though, so you should check.

Be aware that the new cardinality estimator can also cause you problems, so you should evalaute that as well.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.