SQL Server 2017 CU17 Released

On October 8, 2019, Microsoft released SQL Server 2017 CU17, which is Build 14.0.3238.1. There are 34 public hot fixes in the cumulative update. Starting with SQL Server 2017, Microsoft is not using Service Packs as a servicing mechanism for SQL Server, only Cumulative Updates.

Microsoft has fixed hundreds of bugs since SQL Server 2017 RTM, and they have also added a significant number of product improvements and new features since the RTM release. I’ve already updated my blog post that highlights the more important hotfixes (in my opinion) for each SQL Server 2017 CU that has been released.

Performance and Stability Fixes in SQL Server 2017 CU Builds

You really are better off trying to stay as current as possible on your SQL Server builds. I still believe this despite some recent problems Microsoft has had with SQL Server Cumulative Updates.

Here is Microsoft’s official guidance:

Microsoft recommends ongoing, proactive installation of CUs as they become available

  • SQL Server CUs are certified to the same levels as Service Packs, and should be installed at the same level of confidence.
  • Historical data shows that a significant number of support cases involve an issue that has already been addressed in a released CU.
  • CUs may contain added value over and above hotfixes. This includes supportability, manageability, and reliability updates.

How to Uninstall Microsoft SQL Server 2017 Reporting Services

I had to uninstall SQL Server 2017 Reporting Services  (SSRS) for a client recently. It is not difficult to do, but the process is different than it was in older versions of SQL Server. Starting with SQL Server 2017, SSRS 2017 is a separate download from the rest of SQL Server that is not included on the SQL Server 2017 installation media. Because of this, you need to find Microsoft SQL Server Reporting Services under Uninstall or change a program in Control Panel. Then you simply right-click and choose uninstall.

This is different than how it used to be, and different from how Microsoft currently describes it in their documentation, which doesn’t appear to have been updated yet.

Uninstall Reporting Services

SSRS Uninstall 1

Figure 1: Microsoft SQL Server Reporting Services Entry

You will see a screen like this, which will let you do an Edition Upgrade, Repair, or an Uninstall.

SSRS Uninstall 2

Figure 2: Microsoft SQL Server 2017 Reporting Services Maintenance

Depending on your machine, the uninstall should go pretty quickly. It doesn’t typically require a reboot. It also doesn’t require a restart of the SQL Server Service, since it is a completely separate service.

SSRS Uninstall 3

Figure 3: Uninstall in Progress

It will look like this when it is done.

SSRS Uninstall 4

Figure 4: Completed Uninstall

Hopefully this will save you some time if you ever want to do this.

Avoiding SQL Server Upgrade Performance Issues


When you upgrade to a modern version of SQL Server, there are some critical things you should do to help avoid any SQL Server performance issues.

SQL Server 2008 and SQL Server 2008 R2 are rapidly approaching the end of Extended support from Microsoft on July 9, 2019.  SQL Server 2014  is also falling out of Mainstream support on July 9, 2019.  SQL Server 2012 fell out of Mainstream support on July 11, 2017. Because of this, I am seeing an increasing number of organizations that have been migrating to a modern version of SQL Server. I define a modern version of SQL Server as SQL Server 2016 or later.

I see this as a positive development overall, since SQL Server 2016 and newer actually have many useful new features that make them much better products than their predecessors. Migrating to a modern version of SQL Server also usually means using new, faster hardware and storage, running on a current version of Windows Server, which is also very beneficial, as long as you choose your new hardware and storage wisely.

SQL Server Upgrade Performance Issues

Despite all of this, I have seen a decent number of cases where organizations have migrated from a legacy version of SQL Server to a modern version of SQL Server on new hardware and a new operating system, and then be unpleasantly surprised by SQL Server performance issues once they are in Production. How can these performance regressions be occurring, and what steps can you take to help prevent them?

The main culprit in most of these performance regressions is a combination of lack of knowledge, planning, and adequate performance testing. Unlike legacy versions of SQL Server, modern versions of SQL Server have several important performance-related configuration options that you need to be aware of, understand, and actually test with your workload. Most people who run into performance regressions have done what I call a “blind migration” where they simply restore their databases from the older version to the new version of SQL Server, with no meaningful testing of the performance effects of these different configuration options.

Key Configuration Options

So, what are these key configuration options that you need to be concerned with from a performance perspective? The most important ones include your database compatibility level, the cardinality estimator version that you are using, your database-scoped configuration options, and what trace flags you are using. Since SQL Server 2014, the database compatibility level affects the default cardinality estimator that the query optimizer will use. Since SQL Server 2016, the database compatibility level also controls other performance related behavior by default. I have written more about this subject here:

The Importance of Database Compatibility Level in SQL Server

Compatibility Levels and Cardinality Estimation Primer

You have the ability override many of these database compatibility level-related changes with database scoped configuration options and query hints. There are actually a rather large number of different combinations of settings that you have to think about and test. So, what are you supposed to do?

Microsoft Database Experimentation Assistant

In my ideal scenario, you would use the free Microsoft Database Experimentation Assistant (DEA) to capture a relevant production workload. This involves taking a full production database backup, then capturing a production trace that covers representative high priority workloads. While this is going on, I would run some of my SQL Server Diagnostic Queries to get some baseline metrics from your legacy instance.

Once you have done that, you can then restore that backup to your new environment, and replay the production trace multiple times in your new environment. Each time you do this (which also includes a fresh restore from that original full production database backup), you will use a different combination of these key configuration settings. You have to make the database configuration/property changes after each restore, but before you replay the DEA trace.

Configuration Combinations to Test

The idea here is to see which combination of these configuration settings yields the best performance with your workload. Here are some relevant, likely combinations:

    • Use the default native database compatibility level of the new version
    • Use the default native database compatibility level of the new version and use the query optimizer hotfixes database-scoped configuration option
    • Use the default native database compatibility level of the new version and use the legacy cardinality estimator database-scoped configuration option
    • Use the default native database compatibility level of the new version and use the legacy cardinality estimator database-scoped configuration option and use the query optimizer hotfixes database-scoped configuration option
    • Use the existing database compatibility level of the old version
    • Use the existing database compatibility level of the old version and use the query optimizer hotfixes database-scoped configuration option

This level of DEA testing may not be practical if you have a large number of databases, but you should really try to do it on your most mission critical databases. Barring that, I would try to do as much testing of your most important stored procedures and queries as possible, using these different configuration settings.

Microsoft’s Recommended Upgrade Sequence

Finally, if no adequate testing is possible you can follow Microsoft’s recommended upgrade sequence (in your new production environment, after you go live), which is:

    • Upgrade to the latest SQL Server version and keep the source (legacy) database compatibility level
    • Enable Query Store, and let it collect a baseline of your workload
    • Change the database compatibility level to the native level for the new version of SQL Server
    • Use Query Store (and Automatic Plan Correction on SQL Server 2017 Enterprise Edition) to fix performance regressions by forcing the last known good plan

You also have all of the other new “knobs” of database-scoped configuration options, query-level hints, and trace flags available to you. You may have to do some additional work on some queries with USE HINT query hints. Ideally, you would have done enough testing so that you already have a pretty good idea of the “best” combination of these settings for your workload, but many organizations don’t actually do that.

Keep in mind that for each of the new QP features over the last two versions (Adaptive Query Processing in SQL Server 2017 and Intelligent Query Processing in SQL Server 2019), Microsoft exposes the ability to disable specific behavior at the database scoped configuration or query USE HINT scope.  Microsoft generally recommends that if you do find regressions related to a specific feature, try disabling it at lower granularities first, so you can still benefit from all of the rest of the improvements you get from the latest database compatibility level.

Query Tuning Assistant

Microsoft is shipping a new tool called Query Tuning Assistant (QTA) in SSMS 18.0. QTA can guide you through the recommended database compatibility level upgrade process in a wizard-fashion, collecting the baseline workload in Query Store, bumping up the database compatibility level, and then comparing performance with the post-upgrade workload collection. At the end of this process, if performance regressions are detected, rather than moving back to the previously known good plan, the QTA will actually suggest hint-based improvements that can be deployed for individual queries (using plan guides), without having to necessarily move back to the legacy CE. It will also gives you some ideas (indirectly) for how you can modify problematic queries that have CE-related regression issues, when you have that option.