Rajendra Gupta

Database Upgrade using the Query Tuning Assistant wizard in SSMS 18

December 6, 2018 by

This article will cover managing a SQL Server database upgrade using new features in SQL Server Management Studio 18 including the query tuning assistant wizard, database upgrade feature, query store and more

With the release of each new SQL Server version, a Database administrator’s tasks are often to plan the next database upgrade. The most important part of the upgrade part is the query tuning. When we perform a database upgrade or move the database to a SQL Server running with a higher version, we need to upgrade the SQL Server compatibility level to use the latest enhancements in the query optimizer features and, in general, all the new functionality of the recent release. There might be some queries or workloads, which might not run properly under the new database compatibility model, which will require fine-tuning that query.

SQL Server 2016 introduced Query Store that helps to identify performance issue with the workload. We can do analysis of the execution plan change for any particular query and if required, we can revert to the previous execution plan to get a better performance. The Query store can always be helpful for any database level changes, SQL Server restart, database upgrade, index changes etc.

Suppose we want to upgrade ‘WideWorldImporters’ database from SQL Server 2017(Compatibility level 140) to SQL Server 2019 (Compatibility level 150). We can use the Query store as shown below.

Upgrade using the query store

  • SQL 2017 WideWorldImporters database – compatibility level 140
  • SQL 2019 WideWorldImporters database – compatibility level 140
  • SQL 2019 Create a baseline without changing the compatibility level
  • SQL 2019 Upgrade the compatibility level to 150
  • SQL 2019 View query store reports and force the ‘last known good plan’ if required

You can see here, first we captured the baseline before changing the compatibility mode to the recent version as SQL Server 2019 (150). We changed the compatibility level and viewed the built-in Query Store reports later, forcing the last known good plan if there is any change in the query performance.

In SQL Server 2017, we come across Understanding automatic tuning in SQL Server 2017. The Automatic tuning feature allows SQL Server to compare the execution plan before and after the change and then forced the plan automatically.

Once you have installed SSMS 18.0. you can find the SSMS properties from the help section from SSMS.

Connect to the SQL Server instance and go to Tasks -> Database upgrade.

SSMS 18 Database upgrade

  • SQL 2017 WideWorldImporters database – compatibility level 140
  • SQL 2019 WideWorldImporters database – compatibility level 140
  • SQL 2019 Create a baseline without changing the compatibility level
  • SQL 2019 Upgrade the compatibility level to 150
  • SQL 2019 Collect workload data for the upgraded database and get the recommendation
  • SQL 2019 Apply the recommendation and monitor performance. Roll back if required

For our example, we will be using the ‘WideWorldImporters’ having compatibility level 150.

Before we proceed, let me explain this ‘Database Upgrade’ feature in SSMS 18.5

In this database upgrade method, it starts the Query Tuning Assistant. The query tuning assistant contains below steps:

  • The configuration of the Query Store
  • Start Workload to collect baseline data of Query Store
  • Upgrade database compatibility
  • Collect the workload data again to perform the comparison and regression detection
  • Apply the recommendations and view the performance. If required, we can perform a rollback up as well

Let us start the Database upgrade for our sample database. The first screen is to set up the workload duration and the target database compatibility level.

Workload Duration: Workload duration should be in days. Let us try to put the duration for less than 1 day i.e. 0.10 days.

Target Database Compatibility Level: It shows the target database compatibility level. For our instance, it automatically selects the available compatibility level 150.

In the next screen, we can configure the Query Store so performance data can be captured. It gives the current state of the query store recommendation along with the recommendations. We can either use the recommendation or specify custom settings.

Now let go to the next step and review the setting. You can see the message written on this that it will not execute any workload instead you need to run the workload and monitor session actions from the Tasks -> Database upgrade menu options.

Click on the ‘Finish’ button to configure the session. Take a note of the error as shown below. It just says ‘Failed to create new action session. Most likely there is an existing active session. Please close or delete it before creating a new active session.’

This action does not give any particular error message. It is due to the number of days we specified on the first screen. A minimum number of days we can specify are 1 day. Go to the previous screen and specify the number of days as one.

Once we have configured the Query tuning new session, go to ‘Tasks -> Database Upgrade -> Monitor Sessions’

It opens the Query Tuning Assistant with five steps

  1. Setup
  2. Data Collection
  3. View Analysis
  4. View Findings
  5. Verification

Step 1: Setup: In the setup screen, it shows the sessions created along with its status, description and the time started.

Click on the particular session and then on the details.

Step 2. Data Collection

It comes to the baseline data collection. We can execute the workload against the database so that the Query store can store the baseline data.

Once the workload is finished, we can click on the ‘Done with workload run’.

This will upgrade the compatibility level of the desired database. You can give confirmation by clicking on ‘Yes’

You can see the confirmation message that the database compatibility level is upgraded successfully.

You can verify the database compatibility level from the database properties. Compatibility level is now changed to SQL Server 2019 (150).

Go to ‘next’ step and you can see the list of regressed queries. If the workload is running, you can ‘Refresh’ the data. By default, it shows 20 queries. We can change the metric and aggregation criteria from the drop-down values.

In the regressed queries, you can see queries ‘Tunable’ as True or False. Click on ‘Done with workload run’ and click on Next.

Step 3: View Analysis

In this step, you can select the queries which we want to tune. You cannot select the queries for which Tuneable is set to false.

You can click on ‘Yes’ to go ahead. As mentioned in the screenshot, you cannot return to this page.

Step 4: View Findings

In this step, we can deploy the recommendation generated in the previous step as plan guide. You can note that value in the last column ‘Can Deploy’ as true or false.

The status is ‘Initial’ and we can find the baseline metric, observed metric, and the percentage change.

In the ‘Query Option’ you can find the Microsoft docs link for the proposed hint which will improve the query performance. You can see the opened web page after clicking on a link.

Click on ‘Deploy’ and it moves to the last step.

Step 5: Verification

In the verification step, you can find the status of the queries after we clicked on ‘Deploy’. In the below image, we can see the status as ‘Deployed’ along with the % change as 100%.

We can still do a rollback. The value in the last column ‘Can Rollback’, true shows that we can roll back the changes in this query. It will remove the plan guide for the selected queries.

For our demo, let us select one query from the list and click on ‘Rollback’.

This action does not provide any message about the rollback performed. However, it removes that particular query from the list as shown here.

Click on ‘Sessions’ takes you to the initial screen where you can find a list of sessions.

If we can want to remove the session, we can click on ‘Delete Session’. However, please note that if we delete a session, it does not perform rollback for the changes performed previously. In addition, we cannot do roll back with the query tuning advisor after removing the particular session.

Conclusion

Database Upgrade is a helpful feature to upgrade the databases from lower version to higher version. We can easily capture the baseline, apply recommendations and monitor the performance after the upgrade. If required, we can rollback recommendations too.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. Use cases for Query Store in SQL Server
  2. SQL query performance tuning tips for non-production environments
  3. Using the SQL Execution Plan for Query Performance Tuning
  4. Performance troubleshooting when the query plan from the application is different than SSMS
  5. Force query execution plan using SQL Server 2016 Query store
168 Views