Selecting a SQL Server Database Version for Your Company

SQL Server database and SQL

Choosing the right version of SQL Server is important for the performance you desire. If you’re installing an older one because your organization’s management prefers an older build or the vendor is unable to support newer versions, it is important to let them know which version your company needs, and why.

For this reason, we will discuss some popular versions of SQL Server from older to newer and mention their advantages in this blog.

Which SQL Server Version Works Best with SQL Performance Tuning?

Knowing the versions that support this task is extremely important because it will give you the ability to improve the SQL Server database and SQL performance.

To that effect, we will discuss the SQL Server 2016, 2017, and 2019 versions here.

SQL Server 2016

This version was chosen by a lot of independent software vendors or ISVs for one reason – 2016’s Service Pack 1 edition came with Enterprise features in Standard mode. These helped create a single application version that worked simultaneously for both Standard as well as Enterprise clients.

Advantages of Choosing this Version:

  • It is easy to find support material online as this version is quite popular and numerous database professionals are well-versed with this version’s tools.
  • Standard Edition users may find this version appealing since it supports 128GB RAM and additional space for internal functions such as query plans.
  • Support for this version ends after 2026 – longer than the older versions (2012/2014).
  • Newer applications that have additional compliance requirements will benefit from features in this version such as Always Encrypted, temporal tables, and Dynamic Data Masking. These will make it somewhat easier to protect and monitor sensitive information.
  • You can have both row store and column store indexes in this version, unlike the earlier ones that only had row store indexes.
  • If you need query plan monitoring to help with SQL performance tuning, you can use the Query Store’s features provided in SQL Server 2016 for this purpose.

SQL Server 2017

Being a newer release, it is one of the most regularly updated versions with patches coming in almost every other month. These patches are important because they resolve significant problems. It also comes with a minimum commit replica configuration to ensure commits are accepted by several replicas.

Advantages of Choosing this Version:

  • The upgrades are easier to get from this version onward due to a Distributed Availability Group that contains multiple SQL Server versions in it. Before this, we had AG version upgrades that were not as convenient, leading most users to construct a new cluster and migrate to it rather than opt for an upgrade.
  • This version contains batch mode execution plans, which gives those who require high-performance column store statements an advantage.
  • If you must run your SQL Server on Linux, you may consider SQL Server 2017 as several bugs have been resolved in the Cumulative Updates.
  • It’s a newer version so support will last longer than that of its predecessor.

SQL Server 2019

Released on November 4, 2019, this version is the latest in the SQL Server series. Naturally, it comes with the longest support lifespan, i.e. it will be supported until 2030. This version also receives regular patch updates to fix many significant issues in the form of Cumulative Updates.

Changes and Features in this Version:

  • Patch contents aren’t documented anymore. Moreover, you are likely to receive updates with undocumented new features – something to consider in case you require it for mission-critical production environments.
  • There is a bit of a learning curve thanks to some cutting-edge features in this version, so be prepared to perform some experimentation as you learn.
  • Some of the best performance features are included in the 2019 compatibility mode. However, you will have to keep a close eye on all SQL Server databases and SQL queries – even the ones running fast at present – as these will alter your current execution plans. In other words, you will have to test both slow and fast queries to make sure the slow ones speed up and the fast ones don’t fall behind in performance.
  • Table variables have gotten better in this version along with user-defined functions.
  • Additional features to watch out for including Big Data Clusters, Java support, and high container availability, so you may want to explore this version if you’re looking for perks like these in the SQL Server you want.

In Conclusion

At this point, SQL Server 2017 might seem like the best version to go with, thanks to a balance of features, stability, and support lifespan. Furthermore, you’ll receive plenty of help with SQL performance tuning – a lifesaver for overworked professionals who may not have the time or resources to upgrade every server every year.

Query Performance Tuning: Making an SQL Monitor Report

Creating a SQL Monitor Report plays an important role in database optimization as it helps the user observe other occurrences during the execution of long-running statements. 

In this post, we’ll discuss how to create one such report that may help you during query performance tuning

SQL Monitor Report: Bringing DBAs One Step Closer to Database Query Optimization

To begin with, you need to make sure your database has the tuning and diagnostic pack. Otherwise, Oracle will not authorize the creation of SQL Monitor Reports.

Also, such reports can be made after an adequate amount of time has passed. The wait is to allow query bottlenecks to reveal themselves. This is typically done for seemingly endless queries that run for long periods of time. However, in general, creating SQL monitor reports is recommended for completed queries.  

Let’s look at an example: A DBA has a simple plan with a hash join involving two big tables. Suppose one of these tables takes two seconds to undergo a complete table scan, whereas the second one takes nine seconds. 

Although only around two seconds out of a total of eleven seconds are sent on the first table, it will appear as though a hundred percent of the query time is being spent on it if you create a SQL Monitor report during the first two seconds.

Creating Reports for Excessively Long Execution Plans

Really long execution plans – those that exceed three hundred lines – don’t have a SQL monitor report generated for them by default. This gets cumbersome because long execution plans are where these reports are needed the most! 

In such cases, there are two things you can do to make the database generate a report. These are – 

  1. Prior to issuing the query in question, generate the following in the session operating the query:

alter session set “_sqlmon_max_planlines” = 800;

2. Apply the following hint while executing the query: 

/* + monitor */ 

How to Create an HTML Version of the Monitor Report 

The HTML version of a SQL Monitor report offers some more details as compared to its text report. This is why it is often recommended by database professionals, with the help of the following query: 

Select dbms_sqltune.report_sql_monitor(

sql_id => ‘&v_sql_id.’,

Session_id => ‘&v_session_id.’,

Session_serial => ‘&v_serial.’,

Type => ‘HTML’,

Report_level => ‘ALL’,

Inst_num => ‘&v_instance.’ )report

from dual;

Not every variable needs to be plugged in – you just require variables sufficient to enable Oracle to recognize the particular SQL\session combination. And if there is only a single session executing the statement on the entire database, only the sql_id is enough.

Creating a Text Monitor Report Instead

In case you’d rather make a text report – whether if it’s due to some problems with an HTML report, or simply preference – here’s how to do it – 

Select dbms_sqltune.report_sql_monitor(

sql_id => ‘&v_sql_id.’,

Session_id => ‘&v_session_id.’,

Session_serial => ‘&v_serial.’,

Type => ‘TEXT’,

Report_level => ‘ALL’,

Inst_num => ‘&v_instance.’ )report

from dual;