Day 24: Query optimisation techniques

A. Lesson overview

Activity breakdown

Business scenario. With the right indexes in place, some queries are still slow: a report filters on YEAR(OrderDate), the loyalty-card search scans the customer table, and the customer order screen is fast for most customers but times out — sometimes — after the biggest account is opened. Today you find out why.

As on Days 22–23: row counts and results are exact; reads, timings and some plan choices depend on your machine — measure and record them.

B. Theory explained clearly

Part 1 — Sargable predicates

A predicate is sargable (Search ARGument-able) when SQL Server can use it to seek in an index: the column stands alone on one side of the comparison. Wrapping the column in a function or calculation forces SQL Server to compute the expression for every row first — a scan.

Two notes: CAST(datetime_column AS date) = @d is a special case SQL Server can seek on — but the explicit range is clearer and portable. And rewrites must return exactly the same rows: test with EXCEPT both ways, especially at boundaries.

Part 2 — Data types and implicit conversions

When two different types are compared, SQL Server converts the one with lower data type precedence to the higher one. int ranks above varchar; nvarchar ranks above varchar.

-- LoyaltyCardNo is varchar(12)
WHERE LoyaltyCardNo = 500012345      -- int literal: the COLUMN is converted to int for every row → scan
WHERE LoyaltyCardNo = '500012345'    -- same type: seek

The plan shows CONVERT_IMPLICIT in the predicate and a yellow warning on the SELECT operator (Type conversion in expression … may affect “SeekPlan”). Typical sources: application parameters typed as nvarchar against varchar columns, numbers stored as text, and joins between columns of different types. With a Windows collation (as RetailLab uses), an nvarchar value compared with a varchar column can often still seek through a converted range, but the warning remains and estimates may suffer. Match parameter and variable types to column types.

Part 3 — Plan reuse and parameter sniffing

Compiling a plan costs CPU, so SQL Server caches plans and reuses them. For a stored procedure (or a parameterised query from sp_executesql), the plan is built using the parameter values of the first call — the optimiser sniffs them — and reused for later calls with different values.

Usually that’s good. It goes wrong when the best plan depends strongly on the value — skewed data:

Which plan you get depends on who calls first after a restart or recompile — the classic “it’s slow sometimes” problem.

Remedies:

Parameter Sensitive Plan (PSP) optimisation. From SQL Server 2022 (compatibility level 160 and above, so on by default in RetailLab), the optimiser can detect a skewed equality predicate on a parameter and keep several plans — variants — chosen at run time by a dispatcher according to the parameter’s value. It doesn’t apply to every query, and when it doesn’t the classic problem remains. Today’s demonstration turns PSP off to show the classic problem clearly, then turns it back on.

Part 4 — Other common patterns

• OR across different columns (WHERE CustomerID = @c OR OrderDate = @d) often prevents seeks on either index. A UNION of two seekable queries can be much cheaper (use UNION, not UNION ALL, if a row could match both).
• Catch-all searches (WHERE (@c IS NULL OR CustomerID = @c) AND (@d IS NULL OR OrderDate >= @d)) get one cached plan that must work for every combination — usually a scan. Add OPTION (RECOMPILE) so each call is optimised for the parameters supplied, or build the query dynamically (Day 28). SQL Server 2025 also introduces optional parameter plan optimization for this pattern (see the references); as with PSP, check the plan rather than assuming it applies.
• Table variables have no statistics (SQL Server 2019+ uses the actual row count at first compile — deferred compilation); temporary tables have statistics. Use temp tables for large intermediate results.
• Scalar UDFs on many rows — check inlining (Day 18).
• SELECT * prevents covering indexes from covering and transfers unneeded data.

Part 5 — Hints

Hints override the optimiser. They fix today’s problem and may cause tomorrow’s when data changes. Use them last, document why, and revisit.

C. Dataset and starting state

Required indexes from Day 23: IX_PerfSalesOrder_CustomerID (CustomerID) INCLUDE (OrderDate, OrderStatus), IX_PerfSalesOrder_OrderDate (OrderDate) INCLUDE (ShippingCost), UX_PerfCustomer_Email. If you skipped Day 23, create them:

CREATE INDEX IX_PerfSalesOrder_CustomerID ON Perf.SalesOrder (CustomerID) INCLUDE (OrderDate, OrderStatus);
CREATE INDEX IX_PerfSalesOrder_OrderDate  ON Perf.SalesOrder (OrderDate)  INCLUDE (ShippingCost);
CREATE UNIQUE INDEX UX_PerfCustomer_Email ON Perf.Customer (Email) INCLUDE (CustomerName, Region);

Validation. Expected: 1000000, 1, 1, 1, 170.

SELECT  (SELECT COUNT(*) FROM Perf.SalesOrder) AS Orders,
        (SELECT COUNT(*) FROM sys.indexes WHERE name = 'IX_PerfSalesOrder_CustomerID') AS IxCustomer,
        (SELECT COUNT(*) FROM sys.indexes WHERE name = 'IX_PerfSalesOrder_OrderDate')  AS IxDate,
        (SELECT COUNT(*) FROM sys.indexes WHERE name = 'UX_PerfCustomer_Email')        AS UxEmail,
        (SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME())          AS CompatLevel;

D. Worked examples

Use Include Actual Execution Plan and SET STATISTICS IO ON throughout.

Worked example 1 — A function on the column

SET STATISTICS IO ON;

SELECT COUNT(*) AS Orders FROM Perf.SalesOrder WHERE YEAR(OrderDate) = 2025;

SELECT COUNT(*) AS Orders FROM Perf.SalesOrder
WHERE  OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01';

Expected result: both return 249826. The first plan scans IX_PerfSalesOrder_OrderDate (all 1,000,000 entries) and evaluates YEAR() on each; the second seeks the range and reads about a quarter of the index. Compare the logical reads. YEAR() returns the same answer, but hides the column from the index.

Worked example 2 — An implicit conversion

CREATE INDEX IX_PerfCustomer_LoyaltyCardNo ON Perf.Customer (LoyaltyCardNo);

SELECT CustomerID, LoyaltyCardNo FROM Perf.Customer WHERE LoyaltyCardNo = 500012345;     -- int
SELECT CustomerID, LoyaltyCardNo FROM Perf.Customer WHERE LoyaltyCardNo = '500012345';   -- varchar

Expected result: both return customer 12345. The first plan: an Index Scan with predicate CONVERT_IMPLICIT(int, [LoyaltyCardNo], 0) = (500012345) and a warning triangle on SELECT. The second: an Index Seek. The conversion was applied to the column (lower precedence), not to the literal. A side effect: if any card number contained a letter, the first query would fail with a conversion error.

Worked example 3 — Parameter sniffing, reproduced

-- For the demonstration only: turn off PSP so the classic behaviour is visible
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;
GO

CREATE OR ALTER PROCEDURE Perf.usp_OrdersForCustomer
    @CustomerID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, OrderDate, OrderStatus, SalesChannel, ShippingCost
    FROM   Perf.SalesOrder
    WHERE  CustomerID = @CustomerID;
END;
GO

SET STATISTICS IO ON;
EXEC Perf.usp_OrdersForCustomer @CustomerID = 2;     -- compiled for a small customer
EXEC Perf.usp_OrdersForCustomer @CustomerID = 1;     -- reuses that plan

EXEC sys.sp_recompile N'Perf.usp_OrdersForCustomer'; -- discard the cached plan
EXEC Perf.usp_OrdersForCustomer @CustomerID = 1;     -- compiled for the big customer
EXEC Perf.usp_OrdersForCustomer @CustomerID = 2;     -- reuses that plan
SET STATISTICS IO OFF;

Expected result: customer 2 returns 10 rows and customer 1 50,000 rows every time — only the work differs.

• Round 1: both calls use Index Seek + Key Lookup (SalesChannel and ShippingCost aren’t in the index). Customer 1’s call performs 50,000 lookups: in its actual plan, the Key Lookup shows Estimated Number of Rows ≈ 10 (from the sniffed value) and Actual 50,000; its logical reads are much higher than a table scan’s.
• Round 2: both calls use a Clustered Index Scan. Customer 2 now reads the whole table for 10 rows.

Record the logical reads of all four calls. Neither cached plan is good for everyone.

Worked example 4 — Three remedies

-- (a) Recompile the statement on every call
CREATE OR ALTER PROCEDURE Perf.usp_OrdersForCustomer
    @CustomerID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, OrderDate, OrderStatus, SalesChannel, ShippingCost
    FROM   Perf.SalesOrder
    WHERE  CustomerID = @CustomerID
    OPTION (RECOMPILE);
END;
GO
EXEC Perf.usp_OrdersForCustomer 2;     -- seek + lookups
EXEC Perf.usp_OrdersForCustomer 1;     -- scan
GO

-- (b) Let PSP handle it: remove the hint and turn PSP back on
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;
GO
CREATE OR ALTER PROCEDURE Perf.usp_OrdersForCustomer
    @CustomerID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, OrderDate, OrderStatus, SalesChannel, ShippingCost
    FROM   Perf.SalesOrder
    WHERE  CustomerID = @CustomerID;
END;
GO
EXEC Perf.usp_OrdersForCustomer 2;
EXEC Perf.usp_OrdersForCustomer 1;
GO

-- Did PSP create plan variants? (Query Store records them)
SELECT v.query_variant_query_id, v.parent_query_id, v.dispatcher_plan_id
FROM   sys.query_store_query_variant AS v;

Expected result: (a) each call gets the plan suited to its value — seek for customer 2, scan for customer 1 — at the cost of compiling every time. (b) If PSP decides the predicate is skewed enough (customer 1 is 5% of all orders; the others about 0.001%), the two calls use different plans without any hint, and sys.query_store_query_variant lists variant queries; the actual plan of a variant shows option (PLAN PER VALUE(…)) in its statement text. If PSP doesn’t engage on your system, the query returns no rows and you see the classic behaviour — then remedy (a) or (c) applies. (Query Store data can take a minute or so to appear.)

(c) For completeness: OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN)) plans for the average customer (≈ 10 rows) — the seek plan — for every call: excellent for 99,999 customers, poor for customer 1.

E. Guided practical lab — Make four predicates sargable, and prove it

For each pair: run the original, then your rewrite; compare plans and reads; then prove both return the same rows with EXCEPT in both directions.

Step 1 — A month

1. Do: Rewrite YEAR()/MONTH() as a range.
2. Run:

   USE RetailLab;
   GO
   SET STATISTICS IO ON;
   SELECT OrderID FROM Perf.SalesOrder WHERE YEAR(OrderDate) = 2025 AND MONTH(OrderDate) = 12;
   SELECT OrderID FROM Perf.SalesOrder WHERE OrderDate >= '2025-12-01' AND OrderDate < '2026-01-01';
   
   SELECT COUNT(*) AS OnlyInOriginal FROM (
       SELECT OrderID FROM Perf.SalesOrder WHERE YEAR(OrderDate) = 2025 AND MONTH(OrderDate) = 12
       EXCEPT
       SELECT OrderID FROM Perf.SalesOrder WHERE OrderDate >= '2025-12-01' AND OrderDate < '2026-01-01') AS x;
   SELECT COUNT(*) AS OnlyInRewrite FROM (
       SELECT OrderID FROM Perf.SalesOrder WHERE OrderDate >= '2025-12-01' AND OrderDate < '2026-01-01'
       EXCEPT
       SELECT OrderID FROM Perf.SalesOrder WHERE YEAR(OrderDate) = 2025 AND MONTH(OrderDate) = 12) AS x;

3. Observe: 21216 rows each; index scan vs index seek; 0 and 0.
4. Why it matters: The proof matters as much as the speed: a fast wrong answer is worse than a slow right one.
5. Confirm: both EXCEPT counts are 0.

Step 2 — A date formatted as text

1. Do: Replace the text comparison with a date comparison.
2. Run:

   SELECT COUNT(*) FROM Perf.SalesOrder WHERE CONVERT(char(10), OrderDate, 120) = '2025-12-24';
   SELECT COUNT(*) FROM Perf.SalesOrder WHERE OrderDate = '2025-12-24';

3. Observe: 684 both times; scan vs seek.
4. Why it matters: Formatting belongs in the presentation layer, not in WHERE.
5. Confirm: same count.

Step 3 — Arithmetic on the column

1. Do: Move the calculation to the constant side.
2. Run:

   SELECT COUNT(*) FROM Perf.SalesOrder WHERE DATEDIFF(DAY, OrderDate, '2025-12-31') <= 6;
   SELECT COUNT(*) FROM Perf.SalesOrder WHERE OrderDate >= DATEADD(DAY, -6, CAST('2025-12-31' AS date));

3. Observe: 4788 both times (25–31 December); scan vs seek.
4. Why it matters: “Within the last N days” is one of the most common non-sargable patterns in reports.
5. Confirm: same count. (DATEDIFF(DAY, …) counts day boundaries, so <= 6 means 25 December onwards.)

Step 4 — A string prefix

1. Do: Replace LEFT() with LIKE 'prefix%'.
2. Run:

   SELECT CustomerID, Email FROM Perf.Customer WHERE LEFT(Email, 14) = 'customer12345@';
   SELECT CustomerID, Email FROM Perf.Customer WHERE Email LIKE 'customer12345@%';

3. Observe: one row each — customer 12345; index scan of UX_PerfCustomer_Email vs index seek.
4. Why it matters: LIKE with a constant prefix and no leading wildcard is sargable.
5. Confirm: same row.

F. Independent practice

Task 1 — Implicit conversion from a parameter type

Search for customer 12345 by email using DECLARE @e nvarchar(100) = N'customer12345@example.com', then with varchar(100). Inspect both plans.

Success criteria: both return customer 12345. The nvarchar version shows CONVERT_IMPLICIT on Email and a warning on SELECT; with this database’s Windows collation it may still perform a seek (via a computed range) but with extra operators; the varchar version is a plain seek with no warning.

Task 2 — OR across columns

Count orders that are customer 12345’s or dated 2025-12-24. Compare the plan with a rewrite using UNION of two seekable queries.

Success criteria: 694 both ways (10 + 684, no overlap). The OR version typically scans; the UNION version seeks both indexes (or, depending on cost, SQL Server may itself turn the OR into an index union — check).

Task 3 — A catch-all search

Write Perf.usp_SearchOrders @CustomerID int = NULL, @FromDate date = NULL, @ToDate date = NULL, @Status varchar(10) = NULL with the (@p IS NULL OR column = @p) pattern. Call it for customer 12345, then for pending orders from 2025-12-24. Look at the plans; then add OPTION (RECOMPILE) and repeat.

Success criteria: customer 12345 → 10 rows; pending from 2025-12-24 → 5362 rows. Without RECOMPILE, the second call reuses the first call’s plan (or both scan). With it, the customer search seeks IX_PerfSalesOrder_CustomerID, and the date search seeks a date index.

Task 4 — Choose a remedy (challenging)

For Perf.usp_OrdersForCustomer, compare logical reads for customers 1 and 2 under: (a) OPTION (RECOMPILE), (b) OPTIMIZE FOR (@CustomerID UNKNOWN), (c) a covering index — INCLUDE (OrderDate, OrderStatus, SalesChannel, ShippingCost) — with no hint. Recommend one, giving reasons.

Success criteria: row counts 50,000 and 10 throughout. (c) gives an index seek for both customers with no lookups, so one plan suits both — usually the best fix when the index cost is acceptable. Your reads are your own.

Stretch challenge — A Query Store hint

Without changing the procedure’s code, attach OPTION (RECOMPILE) to its SELECT using sys.sp_query_store_set_hints, verify it, then remove it.

Success criteria: sys.query_store_query_hints shows your hint for the query; customer 1 and customer 2 each get a suitable plan; after sys.sp_query_store_clear_hints the view is empty for that query.

G. Solutions and explanations

DECLARE @n nvarchar(100) = N'customer12345@example.com';
DECLARE @v varchar(100)  = 'customer12345@example.com';

SELECT CustomerID FROM Perf.Customer WHERE Email = @n;
SELECT CustomerID FROM Perf.Customer WHERE Email = @v;

SELECT COUNT(*) FROM Perf.SalesOrder WHERE CustomerID = 12345 OR OrderDate = '2025-12-24';

SELECT COUNT(*) FROM (
    SELECT OrderID FROM Perf.SalesOrder WHERE CustomerID = 12345
    UNION
    SELECT OrderID FROM Perf.SalesOrder WHERE OrderDate = '2025-12-24'
) AS u;

CREATE OR ALTER PROCEDURE Perf.usp_SearchOrders
    @CustomerID int         = NULL,
    @FromDate   date        = NULL,
    @ToDate     date        = NULL,
    @Status     varchar(10) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, CustomerID, OrderDate, OrderStatus
    FROM   Perf.SalesOrder
    WHERE  (@CustomerID IS NULL OR CustomerID  =  @CustomerID)
      AND  (@FromDate   IS NULL OR OrderDate   >= @FromDate)
      AND  (@ToDate     IS NULL OR OrderDate   <  @ToDate)
      AND  (@Status     IS NULL OR OrderStatus =  @Status)
    OPTION (RECOMPILE);     -- remove this line to see the problem
END;
GO

EXEC Perf.usp_SearchOrders @CustomerID = 12345;
EXEC Perf.usp_SearchOrders @FromDate = '2025-12-24', @Status = 'Pending';

-- (a) RECOMPILE: see worked example 4 (a)
-- (b)
CREATE OR ALTER PROCEDURE Perf.usp_OrdersForCustomer @CustomerID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, OrderDate, OrderStatus, SalesChannel, ShippingCost
    FROM   Perf.SalesOrder WHERE CustomerID = @CustomerID
    OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));
END;
GO
-- (c) covering index, no hint
CREATE INDEX IX_PerfSalesOrder_CustomerID ON Perf.SalesOrder (CustomerID)
    INCLUDE (OrderDate, OrderStatus, SalesChannel, ShippingCost) WITH (DROP_EXISTING = ON);
CREATE OR ALTER PROCEDURE Perf.usp_OrdersForCustomer @CustomerID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, OrderDate, OrderStatus, SalesChannel, ShippingCost
    FROM   Perf.SalesOrder WHERE CustomerID = @CustomerID;
END;
GO
SET STATISTICS IO ON;
EXEC Perf.usp_OrdersForCustomer 2;
EXEC Perf.usp_OrdersForCustomer 1;
SET STATISTICS IO OFF;

CREATE INDEX IX_PerfSalesOrder_CustomerID ON Perf.SalesOrder (CustomerID)
    INCLUDE (OrderDate, OrderStatus) WITH (DROP_EXISTING = ON);

-- 1. Find the query_id of the procedure's SELECT
SELECT q.query_id, LEFT(qt.query_sql_text, 80) AS QueryText
FROM   sys.query_store_query      AS q
JOIN   sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE  q.object_id = OBJECT_ID(N'Perf.usp_OrdersForCustomer');

-- 2. Attach the hint (replace 42 with your query_id)
EXEC sys.sp_query_store_set_hints @query_id = 42, @query_hints = N'OPTION (RECOMPILE)';

SELECT query_id, query_hint_text FROM sys.query_store_query_hints;

EXEC Perf.usp_OrdersForCustomer 2;
EXEC Perf.usp_OrdersForCustomer 1;

-- 3. Remove it
EXEC sys.sp_query_store_clear_hints @query_id = 42;

H. Knowledge check

1. Understanding. Why does WHERE YEAR(OrderDate) = 2025 prevent an index seek on OrderDate, while the range version doesn’t?

2. Query interpretation. A plan has a warning: Type conversion in expression (CONVERT_IMPLICIT(int,[c].[LoyaltyCardNo],0)) may affect “SeekPlan”. What happened and how do you fix it?

3. Debugging. Users say the customer orders screen is fast “most days” but slow after maintenance windows. The procedure is simple and the table is large and skewed. What is the likely cause and what would you check?

4. Understanding. What does Parameter Sensitive Plan optimisation do, and why might you still see sniffing problems with it on?

5. Understanding. Why is OPTION (RECOMPILE) reasonable for a search screen but questionable for a procedure called 5,000 times a second?

I. Troubleshooting and cleanup

Cleanup. Make sure PSP is back on (the default):

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;

Keep IX_PerfCustomer_LoyaltyCardNo and the procedures (harmless), or drop them:

DROP PROCEDURE IF EXISTS Perf.usp_OrdersForCustomer, Perf.usp_SearchOrders;
DROP INDEX IF EXISTS IX_PerfCustomer_LoyaltyCardNo ON Perf.Customer;

Day 25 doesn’t depend on today’s objects.

J. Completion checklist, recap and references

Completion checklist

• YEAR() scanned, the range seeked; both returned 249826.
• You saw CONVERT_IMPLICIT in the loyalty-card plan and removed it.
• You reproduced both directions of parameter sniffing and recorded the reads.
• The four lab rewrites returned 21216, 684, 4788 and 1 row, with EXCEPT proofs of 0.
• PSP is back ON.

Recap

• Keep columns bare in predicates; move calculations to the other side; prove rewrites with EXCEPT.
• Match types; watch for CONVERT_IMPLICIT warnings.
• Cached plans are compiled for the first parameter values; skewed data turns that into “slow sometimes”.
• Remedies: RECOMPILE, OPTIMIZE FOR, a covering index, separate code paths, PSP (automatic, when it applies), Query Store hints.
• OR across columns and catch-all queries need rewriting or RECOMPILE.
• Hints are last resorts; document them.

Next: Day 25 — Partitioning: partition functions and schemes, boundaries, partition elimination and a sliding window on the order table.

References (Microsoft Learn)

• Query processing architecture guide
• Data type precedence (Transact-SQL)
• Parameter Sensitive Plan optimization
• Optional parameter plan optimization
• Query hints (Transact-SQL)
• Query Store hints
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
• sp_recompile (Transact-SQL)