Query plans and tuning

Keiko Tanabe's nightly sales report has a query that takes eleven minutes on the old StockLine server and nineteen on the test Managed Instance. Everyone expected Azure to be faster, so the result causes some alarm in the migration stand-up. Elena brings the query to the clinic instead of to a bigger service tier. "Before we buy more vCores," she says, "we read the plan." This session is about reading execution plans and changing queries and settings so the optimizer can do its job.

Estimated and actual plans

An execution plan is the optimizer's recipe for a query: which indexes to read, in what order to join tables, which algorithms to use for joins and aggregations, whether to sort, and how many threads to use. An estimated plan shows what the optimizer intends, based on statistics, without running the query. An actual plan is captured when the query runs and adds what really happened: actual row counts for each operator, the number of executions, warnings such as spills to tempdb, and runtime statistics. Live query statistics show the plan filling in while a long query runs. Query Store keeps estimated plans for each query over time, and since SQL Server 2019 the last actual plan can also be kept in the plan cache with lightweight profiling.

Plans are read from right to left and top to bottom: data flows from the operators on the right, which read tables and indexes, toward the SELECT on the left. Each arrow's thickness shows how many rows flow along it. The first thing Walt looks for is a big difference between estimated and actual row counts, because that is where the optimizer was surprised, and a surprised optimizer chooses badly.

What the report query was doing

The plan of Keiko's query shows the pattern in the figure. The query filters sale lines with WHERE CONVERT(date, SaleTime) = @ReportDate. Wrapping the column in a function means the optimizer cannot use the histogram on SaleTime or seek on the index; it scans the whole table and guesses that a fixed small fraction of rows match. It estimates 1,200 rows; 3,850,000 arrive. Because it expected few rows, it chose a nested loops join, which looks up each row in the product table one at a time, and a memory grant sized for 1,200 rows, so the final sort spilled to tempdb. On the old server, a warm buffer pool hid much of the cost; on the new instance, with colder caches during the replay, it did not.

The fix is to make the predicate sargable, meaning searchable by an index: compare the column itself with a range. With WHERE SaleTime >= @ReportDate AND SaleTime < DATEADD(day, 1, @ReportDate), the optimizer can seek on the index on SaleTime and use the histogram. The estimate becomes 3.9 million, the join becomes a hash join, the memory grant fits, and the query finishes in 2 minutes 40 seconds on the test instance.

-- Before: the function on the column hides it from the index and the histogram
SELECT p.Department, SUM(sl.Quantity * sl.UnitPrice) AS sales
FROM sales.SaleLine AS sl
JOIN product.Product AS p ON p.Sku = sl.Sku
WHERE CONVERT(date, sl.SaleTime) = @ReportDate
GROUP BY p.Department;

-- After: a range on the bare column
SELECT p.Department, SUM(sl.Quantity * sl.UnitPrice) AS sales
FROM sales.SaleLine AS sl
JOIN product.Product AS p ON p.Sku = sl.Sku
WHERE sl.SaleTime >= @ReportDate AND sl.SaleTime < DATEADD(day, 1, @ReportDate)
GROUP BY p.Department;

Query constructs that hurt

DP-300 asks candidates to recommend changes to query constructs based on resource use. A handful of patterns cause most problems. Functions applied to columns in WHERE or JOIN clauses, as above. Implicit conversions, where a column of one type is compared with a parameter of another: a varchar column compared with an nvarchar parameter forces a conversion of every row and shows as a warning on the plan. Patterns in LIKE that start with a wildcard, such as a search for every product name ending in paint, which cannot seek. SELECT * in queries that need three columns, which defeats covering indexes. Scalar user-defined functions called for every row, which older versions could not inline. Row-by-row loops and cursors where one set-based statement would do. And OR conditions across different columns, which can sometimes be rewritten as a UNION of two seeks.

Parameter sensitivity

Stored procedures and parameterized queries are compiled once and the plan is reused. The plan is optimized for the parameter values of the first call, which is called parameter sniffing. Usually that is good. It goes wrong when data is skewed: a procedure that lists redemptions for a store compiles first for store 47, a tiny store, and builds a plan with a nested loops join that is ideal for 30 rows; the next call, for store 12 with 5,400 rows, reuses that plan and runs slowly. Hana sees exactly this in the Finch Rewards test database.

There are several remedies. OPTION (RECOMPILE) compiles a fresh plan for each execution, which costs CPU but is fine for queries that run rarely. OPTIMIZE FOR a typical value, or OPTIMIZE FOR UNKNOWN, picks one plan deliberately. Splitting the procedure, so big and small stores take different paths, is clearer. And on SQL Server 2022, Managed Instance and Azure SQL Database at compatibility level 160 or higher, parameter sensitive plan optimization can keep several plans for one query, chosen by the parameter value's size range, automatically.

Intelligent query processing

Recent versions of the engine include a family of features, called intelligent query processing, that improve plans without code changes. They switch on through the database compatibility level, which is one reason to run at the current level after a migration. Adaptive joins decide between a nested loops and a hash join at run time, after seeing how many rows arrived. Memory grant feedback corrects grants that were too small, causing spills, or too large, wasting memory, on later executions. Degree of parallelism feedback lowers the number of threads for queries that do not benefit from more. Cardinality estimation feedback adjusts modelling assumptions for queries whose estimates were repeatedly wrong. Approximate count distinct gives fast, slightly inexact counts for huge tables. Scalar function inlining turns many scalar functions into inline expressions.

The StockLine test copy came from SQL Server 2016 at compatibility level 130. Walt raises it to 160 on the test instance only after capturing a week of Query Store data at 130, so that if a query gets worse at the new level he can see its old plan and force it, which is the next lesson's subject. This is Microsoft's recommended upgrade path: keep the old compatibility level at first, collect a baseline, raise the level, and compare.

Hints, Query Store hints and plan forcing

Sometimes the right fix cannot be made in the code, because the query comes from a vendor's application. Query hints such as OPTION (RECOMPILE), MAXDOP or USE HINT change behaviour for one statement but must be written into the query text. Query Store hints apply a hint to a query by its Query Store identifier, without touching the code, with sys.sp_query_store_set_hints. Plan forcing pins a query to a specific plan from Query Store with sp_query_store_force_plan. Both are powerful and both need tracking, because a forced plan or hint that made sense last year may be wrong after data grows. Walt keeps a list of every forced plan and hint with the reason and a review date.

-- Warehouse vendor query 4417 runs better without parallelism; apply a hint without changing its code
EXEC sys.sp_query_store_set_hints @query_id = 4417, @query_hints = N'OPTION (MAXDOP 1)';

-- Review every Query Store hint in the database
SELECT query_id, query_hint_text, last_query_hint_failure_reason_desc
FROM sys.query_store_query_hints;

Blocking, tempdb and other server settings

Not every slow query is the plan's fault. A query may be waiting for locks, as in lesson 8, or for tempdb, or for memory. On Managed Instance and SQL Server on VMs, server-level settings also matter: max degree of parallelism and cost threshold for parallelism control when and how widely queries go parallel, and on VMs, tempdb files on the local temporary disk and max server memory set below the VM's memory are the usual first steps. Database-scoped configurations, available on all three, set MAXDOP and several optimizer behaviours per database, which is how Azure SQL Database exposes most of these choices. DP-300 lists both kinds of setting.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;
SELECT name, value FROM sys.database_scoped_configurations
WHERE name IN ('MAXDOP', 'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION');