Category: SQL Server 2022

Back In Time

Feeling quite old while remembering the first time I ever read this, and this note at the end:

This is the SQL language IS DISTINCT FROM feature —implemented in the query processor, but not yet available in the T-SQL language.

Groovy. Since at least 2011, this has been in the Query Processor, and here in 2022 we finally get the linguistic support.

The thing is, it’s pretty underwhelming, and I’m going to show you why I think so.

First, in the docs for SQL Server, all the examples use a single literal value, like so:

SELECT * FROM #SampleTempTable WHERE id IS DISTINCT FROM 17;

I went looking for other docs examples from vendors who have had the syntax around for 10+ years, and there wasn’t anything all that much more interesting.

Mostly case expressions and whatnot.

Big deal.

Alignment

First, if I try to run either of these queries, I’ll get an error after about 6 seconds.

SELECT
    c = COUNT_BIG(*)
FROM dbo.Comments AS c
WHERE c.UserId IS DISTINCT FROM 
               (
                   SELECT
                       v.UserId
                   FROM dbo.Votes AS v
               );

SELECT
    c = COUNT_BIG(*)
FROM dbo.Comments AS c
WHERE c.UserId IS NOT DISTINCT FROM 
               (
                   SELECT
                       v.UserId
                   FROM dbo.Votes AS v
               );

Why does it take 6 seconds to get an error? Because a few parts of the query plan have to do some work, and then finally:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Sort of like how sometimes you try to SUM a column and after a while you get an error about arithmetic overflow.

This is a bit annoying, because that means we need a way to return a single value to evaluate.

So Yeah…

We can’t even rewrite the queries like this to get around the error, but I do want to show you the plans.

This is why we have to wait several seconds to get an error (unless you change it to IS DISTINCT FROM ALL/ANY):

SELECT
    c = COUNT_BIG(*)
FROM dbo.Comments AS c
WHERE c.UserId IS DISTINCT FROM 
               (
                   SELECT
                       v.UserId
                   FROM dbo.Votes AS v
                   WHERE v.UserId = c.UserId
               );

SELECT
    c = COUNT_BIG(*)
FROM dbo.Comments AS c
WHERE c.UserId IS NOT DISTINCT FROM 
               (
                   SELECT
                       v.UserId
                   FROM dbo.Votes AS v
                   WHERE v.UserId = c.UserId
               );

Adding a where clause inside the subquery doesn’t help.

But these query plans are total motherchucking disasters, anyway. We’ll get into indexing later, but right now they both have the same shape and operators, though slightly different semantics to deal with is/is not distinct.

Both plans run single threaded, and using Nested Loops as the physical join type, which stinks because we’re putting together two pretty big tables.

Not to mention that Eager Index Spool. What a filth.

Adding Indexes

We need these indexes to make things go any faster. Before we do anything else, let’s create these so we’re not just sitting around thumb-twiddling.

CREATE INDEX
    c
ON dbo.Comments
    (UserId)
WITH
(
    SORT_IN_TEMPDB = ON,
    DATA_COMPRESSION = PAGE
);

CREATE INDEX
    v
ON dbo.Votes
    (UserId)
WITH
(
    SORT_IN_TEMPDB = ON,
    DATA_COMPRESSION = PAGE
);

Thinking About It

Okay, so writing the query like we did up there isn’t going to get us anything. Perhaps my expectations are a bit too exotic.

Let’s try something a bit more austere:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Comments AS c
JOIN dbo.Votes AS v
    ON c.UserId IS DISTINCT FROM v.UserId;

SELECT
    c = COUNT_BIG(*)
FROM dbo.Comments AS c
JOIN dbo.Votes AS v
    ON c.UserId IS NOT DISTINCT FROM v.UserId;

The first thing to be aware of here is that the IS DISTINCT FROM is an inequality predicate, so you’re stuck with Nested Loops as the physical join type:

I ran out of care-juice waiting for this to finish, so all you’re getting is an estimated plan. The lack of an equality predicate here means you don’t have Hash or Merge join as an option.

Following up on bad ideas, the IS NOT DISTINCT FROM is an equality predicate, but the plan chosen is a serial Merge Join variety, which drags on 14 seconds too long:

Drop An Index

If we simulate not having any useful indexes on one table or the other by hinting the clustered index, the performance outlook does not improve.

SELECT
    c = COUNT_BIG(*)
FROM dbo.Comments AS c WITH(INDEX = 1)
JOIN dbo.Votes AS v
    ON c.UserId IS NOT DISTINCT FROM v.UserId;

SELECT
    c = COUNT_BIG(*)
FROM dbo.Comments AS c
JOIN dbo.Votes AS v WITH (INDEX = 1)
    ON c.UserId IS NOT DISTINCT FROM v.UserId;

No useful parts of the first query happen in Batch Mode, but the second query is rescued by two hash aggregates happening in batch mode.

An odd point to make on a blog focused on SQL Server performance tuning is that sometimes not having a useful index gets you a better plan.

Anyway, I’m going back to my vacation.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

New Baby

SQL Server 2022 CTP 2.1 dropped recently, and there’s some new stuff in it! What kind of new stuff?

Well, there’s some linguistic stuff:

• APPROX_PERCENTILE_DISC
• APPROX_PERCENTILE_CONT
• DATETRUNC
• IS [NOT] DISTINCT FROM
• Bit manipulation:
  • LEFT_SHIFT
  • RIGHT_SHIFT
  • BIT_COUNT
  • GET_BIT
  • SET_BIT

How useful any of that will be depends on how you use SQL Server. I’ve never heard anyone yearning for bit manipulation functions, but perhaps I travel in the wrong circles.

Inside You

As far as “unannounced” stuff, here’s some of what I noticed:

There’s a new database scoped configuration called LEDGER_DIGEST_STORAGE_ENDPOINT. The name seems fairly self-documenting, but I can’t picture myself using ledger enough to explore it further.

There are a bunch of new objects, too:

+------------------------------------------------------------+----------------------------------+
|                            name                            |            type_desc             |
+------------------------------------------------------------+----------------------------------+
| dm_db_xtp_hash_index_approx_stats                          | SQL_INLINE_TABLE_VALUED_FUNCTION |
| dm_tran_distributed_transaction_stats                      | VIEW                             |
| extgov_attribute_sync_objects_synchronizing                | VIEW                             |
| sp_change_feed_configure_parameters                        | SQL_STORED_PROCEDURE             |
| sp_dw_physical_manifest_file_table_insert                  | EXTENDED_STORED_PROCEDURE        |
| sp_dw_physical_upsert                                      | EXTENDED_STORED_PROCEDURE        |
| sp_help_change_feed_table                                  | SQL_STORED_PROCEDURE             |
| sp_manage_distributed_transaction                          | EXTENDED_STORED_PROCEDURE        |
| sp_md_discovery_stats_staleness_detection                  | EXTENDED_STORED_PROCEDURE        |
| sp_reset_dtc_log                                           | EXTENDED_STORED_PROCEDURE        |
| sp_trident_create_credential_to_access_internal_md_storage | EXTENDED_STORED_PROCEDURE        |
+------------------------------------------------------------+----------------------------------+

Again, their usefulness will depend on how you use the product.

Here are some new extended events:

• query_ce_feedback_telemetry
• query_feedback_analysis
• query_feedback_validation
• resumable_add_constraint_executed

I don’t think I’ll need to add any of these to sp_HumanEvents, but I’m sure you’ll see them in lots of Microsoft demos.

There are also some new wait stats, but I think I’ll be ignoring them.

• CDC_SCAN_FINISHED
• CORRUPTED_PAGE_PROCESS
• DW_DB
• DW_WS_DB_LIST
• NATIVE_SHUFFLE_WRITE_BUFFER_DEQUEUE
• PARQUET_INDEX_BUILD_MANIFEST_SYNC
• PURVIEW_POLICY_SDK_PREEMPTIVE_SCHEDULING
• PWAIT_S3_TEMP_CREDENTIAL
• RBAC_AAD_GROUP_INFO
• SPINLOCK_EXT
• WAIT_EXTGOV_PERMCACHE_DECISIONLOCK
• WAIT_EXTGOV_PERMCACHE_RESOURCELOCK

Nothing all that juicy this time around. Maybe next time!

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Overlooked

In my quest to tell you about new stuff in SQL Server 2022, I forgot to take a look at new things in sys.database_scoped_configurations.

It turns out that’s important, because some stuff has to be turned on if you want it to work. Here’s a list of new database scoped configurations for SQL Server 2022:

+------------------+-----------------------------------------------+-------+------------------+
| configuration_id |                     name                      | value | is_value_default |
+------------------+-----------------------------------------------+-------+------------------+
|               29 | ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY       |     0 |                1 |
|               31 | CE_FEEDBACK                                   |     1 |                1 |
|               37 | DOP_FEEDBACK                                  |     1 |                0 |
|               26 | DW_COMPATIBILITY_LEVEL                        |     0 |                1 |
|               27 | EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS         |     1 |                1 |
|               34 | MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT        |     1 |                1 |
|               33 | MEMORY_GRANT_FEEDBACK_PERSISTENCE             |     1 |                1 |
|               35 | OPTIMIZED_PLAN_FORCING                        |     1 |                0 |
|               28 | PARAMETER_SENSITIVE_PLAN_OPTIMIZATION         |     1 |                1 |
|               25 | PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES |  1440 |                1 |
+------------------+-----------------------------------------------+-------+------------------+

Some stuff is on by default, some stuff isn’t. You might notice DOP_FEEDBACK and OPTIMIZED_PLAN_FORCING are both off by default, here (even though I turned them on).

ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;

Invitation Only

You might think that Microsoft is so excited about new features (especially in CTP builds) that they want them to be easy to preview and test by people who care enough to do that stuff.

No so much, it turns out.

Heck, I’ve seen several presentations that said Query Store would be on by default in new databases created on SQL Server 2022 instances, but I haven’t had that happen yet and that’s not even a new feature.

Perhaps, like the daftly-hidden Azure Nonsense© in the installer, this will be changed in a future version, but no one tells me anything.

Anyway, not much here beyond this. Mind your database scoped database configurations when you’re testing out new features. Also make sure to check for extended events that help you track down if/when heuristics for those features kick in.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.