New Query Transformation Rules in Azure SQLDB

The Saddest Place On Earth

I've had to do some work in Azure SQLDB recently, and one thing that I always do when I have an opportunity to work on it is to poke around for things that are new, or at least different between it and the on-prem SQL 2022 instance I use. It's often a useful way to find vNext stuff.

While poking, I saw an interesting new USE HINT called ABORT_QUERY_EXECUTION, which presumably allows you to set a time limit on queries generally. This will be nice for long running SELECTs in some cases.

There's also a new database scoped configuration called OPTIMIZED_SP_EXECUTESQL, but I have no idea what that does just yet. Many times these things are exposed but not actually implemented without knowing both the syntax and some new trace flag.

During my journey, I noticed some new optimizer rules:

• LOJToLASJ
• ROJToLASJ
• LogicalToPhysicalSort
• RedundantCseSpool

I'm not sure what the second two do yet (the fourth one, Redundant Common Sub Expression Spool, is also not clear to me), but the first two can be interpreted as Left Outer Join To Left Anti Semi Join, and Right Outer Join To Left Anti Semi Join. These rules implement an optimizer path that a lack of has long ground my gears to their very nubbins.

For background, see my post here: Why Not Exists Makes More Sense Than Left Joins For Performance

How It Works

Let's start with a couple tables and some easy data.

DROP TABLE IF EXISTS
    dbo.i,
    dbo.o;

CREATE TABLE
    dbo.i
(
    id bigint NOT NULL
);

CREATE TABLE
    dbo.o
(
    id bigint NOT NULL
);

INSERT
    dbo.i
WITH
    (TABLOCK)
(
    id
)
SELECT
    ROW_NUMBER() OVER
    (
        ORDER BY
            1/0
    )
FROM sys.messages AS m;

INSERT
    dbo.o
WITH
    (TABLOCK)
(
    id
)
SELECT
    ROW_NUMBER() OVER
    (
        ORDER BY
            1/0
    )
FROM sys.messages AS m;        

Here are the queries I'll be using. Keep in mind that the optimizer compatibility level 170 use hint has been around for a while, but seemingly dormant. My actual database is in compatibility level 160, which is the highest currently supported one.

SELECT
    c = COUNT_BIG(*)
FROM dbo.i AS i
LEFT JOIN dbo.o AS o
  ON i.id = o.id
WHERE o.id IS NULL;

SELECT
    c = COUNT_BIG(*)
FROM dbo.i AS i
LEFT JOIN dbo.o AS o
  ON i.id = o.id
WHERE o.id IS NULL
OPTION
(
    USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_170')
);        

Here are the query plans:

You can see the top plan does what these queries normally do: fully join both tables together, and filter out NULL values later.

The bottom plan does what the NOT EXISTS query form usually does, and uses an Anti Semi Join to filter non-matching rows at the join.

With clustered indexes created on both tables, the physical join type changes to Merge Join, which isn't totally unexpected.

The nice thing about this change is that it does not require any new operators, special handling, or enhancements beyond new heuristic rules to match when a query is sent to the optimizer.

More Like This Please

I do hope that more things like this start showing up instead of absolute clown nose features like DOP Feedback and some of the other unfortunates that have arrived in SQL Server as of late. A big one would be fixing joins involving OR clauses.

Thanks for reading!