Challenge 3 - Performance & Tuning

< Previous Challenge - Home - Next Challenge>

Introduction

Although SQL Server is easier than ever to deploy with Azure SQL Database, containers, and VMs, knowing how to properly use the monitoring and evaluation tools, like Query Store, can make a difference between an application that simply “performs ok” to one that “flies.” While SQL Server has evolved with features over the years, it’s a good idea to understand how these changes might impact an application.

Description

The purpose of this challenge is threefold:

Explore new features

To begin, download and open the Intelligent Query Processing notebook (either “Save link as…” or save the JSON text as a file) using Azure Data Studio or another tool of your choice that can work with a standard Jupyter Notebook. The Notebook will walk you through the test, comparing performance of an analytical query using compatibility mode 130 vs 150.

Follow the steps to create a Log Analytics workspace, a monitoring user, and an Azure Virtual Machine to enable Azure Monitor SQL Insights on your subscription, and start monitoring your databases.

While it’s clear the SQL Server 2019 version performs better, the challenge is to understand why. Leverage Query Store to evaluate the differences in performance and execution plans.

Understand key blockers

There’s a saying, “If you can’t measure it, you can’t improve it.” Perhaps for code optimizations, it’s more accurate to say, “To effectively improve something, you begin by measuring it.” Looking at the execution plan is a key way to understand how a query is parsed and executed so that changes can be intentional and evaluated. This part of the challenge involves understanding the indexing and reading the execution plan. While altering a table and its indexes to improve a query may impact other queries, the goal is to find the appropriate tradeoffs.

The WWI team powers a dashboard that uses a query similar to the below to track current invoices for a given day. Execute and evaluate its execution plan.

SELECT InvoiceId, CustomerId, TotalChillerItems, TotalDryItems, ConfirmedDeliveryTime
FROM Sales.Invoices
WHERE ConfirmedDeliveryTime BETWEEN '2016-01-07 00:00:00.000' AND '2016-01-07 23:59:59.998'
ORDER BY ConfirmedDeliveryTime DESC

Working with your team, improve the performance of the query. Provide any scripts needed that implement the changes your team suggests, and provide before/after results as documentation.

Success Criteria

Tips

Advanced Challenges (Optional)

Learning Resources