@ErikDarling You'd have to really want to go to the Falkland Islands

SQL Server identifies the expressions 'v.s' and 's.s' as exactly equivalent when they are not (same expression label applied to both). It's a bug of course. Happens very early, before any simplification or optimization. Probably a parser bug.

@Zikato Your cast (and many other potential changes) prevents the two being incorrectly identified as the same expression.

@Zikato Of course not. Sean is outside his comfort zone here 😀

For example:

SELECT S.s, V.a
FROM (SELECT 1 AS s) AS S
OUTER APPLY dbo.temp_fn_testvariable(S.s) AS V;

LogOp_Project COL: Expr1000  COL: Expr1000
    LogOp_Apply (x_jtLeftOuter)
        LogOp_Project
            LogOp_ConstTableGet (1) [empty]
            AncOp_PrjList
                AncOp_PrjEl COL: Expr1000
                    ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)
        LogOp_ViewAnchor
            LogOp_Project
                LogOp_Select
                    LogOp_ConstTableGet (1) [empty]
                    ScaOp_Const TI(bit,ML=1) XVAR(bit,Not Owned,Value=0)
                AncOp_PrjList

Notice LogOp_Project COL: Expr1000 COL: Expr1000

The 'same' expression projected twice

Written as:

SELECT S.s, V.a
FROM (SELECT 1 AS s) AS S
OUTER APPLY (SELECT S.s AS a WHERE 1 <> 1) AS V;

LogOp_Project COL: Expr1000  COL: Expr1001
    LogOp_Apply (x_jtLeftOuter)
        LogOp_Project
            LogOp_ConstTableGet (1) [empty]
            AncOp_PrjList
                AncOp_PrjEl COL: Expr1000
                    ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)
        LogOp_Project
            LogOp_Select
                LogOp_ConstTableGet (1) [empty]
                ScaOp_Const TI(bit,ML=1) XVAR(bit,Not Owned,Value=0)
            AncOp_PrjList
                AncOp_PrjEl COL: Expr1001

Notice LogOp_Project COL: Expr1000 COL: Expr1001

Two different expressions

@PaulWhite That was my train of thought. I went to your Optimizer Deep Dive series to find the trace flags 3604 and 8605 and then find out what's different

Also, good morning

It still seems like a bug though

I wonder what type of query he was writing to encounter that issue

sorry, still sleeping

zzz

on brand

Anyway, thank you for investigating it. It was bugging me

Yes, it's quite interesting, in a disappointing sort of way

wow, a bounty

yes, it's a neat answer

the select with no columns?

yep

after seven years or so

heh yeah, it's good.

it doesn't explain all the details of why it happens but I guess I could edit it

;)

A chairde - Morning all!

@Charlieface It does have a USP - storage engines - lots of them - each as buggy and as brittle as the next (InnoDB excepted perhaps)! Here (and that's not the entire blog) is what one senior dev thinks of it! PostgreSQL is tentatively doing down the road of alternative storage engines - they call them table access methods.

There are some interesting projects moving in this area - neon.tech (rust based cloud storage) and orioledb (an Oracle/MySQL implementation of MVCC - updates wll no longer be a delete followed by an insert - no vacuum process for example! - and no txn id wraparound)

@PaulWhite if you'd like to go there might be an extra ticket

LMAO

the itinerary includes a layover in chile

you could see some legendary eyebrows

tempting

Yeah, I hate query optimization and execution, doesn't do it for me. I've seen enough weird things in later versions/compat, that's always my first go to (especially given I'd see 10+ issues a day that would only happen with the latest compat + version + inlining [even if it couldn't be inlined {like when stuff was being inlined that shouldn't}]). Always my first stop, period.

I remember when that feature first came out... the amount of support cases we had... good god.

From AV's to incorrect results. All over the place.

I had a case when applying a CU introduced a new restriction on Scalar UDF inlining and performance tanked.

@Zikato You should really avoid using scalar functions in general. I read a great blog series about it recently.

It's a perfect trap. Good developers love DRY so they encapsulate the logic in reusable modules. It's not their fault that it sucks

@Zikato DRY was a mistake

@ypercubeᵀᴹ Can you repeat that?

@Zikato DRY was a mistake. DRY was a mistake.

Got it. Thank you

@Zikato Exactly! That's why my #1 is "ok, let's try it on different versions and different settings"

Cool writeup

HADR_SYNC_COMMIT Wait Updates (In Progress)

You think that blog post should be considered "in progress" or is that somehow a natural part of the title?

@JoshDarnell I helped with that change :D

@SeanGallardy-MostlyRetired Nice!

but yeah, I wrote about the 2 second behavior and the XE to actually monitor

I read it as "In Postgres"

@SeanGallardy-MostlyRetired The thought of a HADR_SYNC_COMMIT wait hitting that 20 second ceiling 🤮

I really like the idea of the additional detail in the wait_resource.

I remember noticing that ASYNC_NETWORK_IO does a similar thing where the wait caps out at 2 seconds...I think.

Something L____ wrote.

Probably on Twitter.

The Twitter is not good documentation

@JoshDarnell My personal issue with this change, and the wait_resource information, is that it's still not actionable.

Ok, let's say you know which log block is having an issue... now what?

Unless there is a massive amount of tracing already in place, it's 0% helpful.

@JoshDarnell Can't say I've noticed this, I've seen some high waits.

Morning

> Note Because the ASYNC_NETWORK_IO wait is implemented, the wait_time against it will never exceed two seconds (2,000 ms). However, the wait type will remain in place indefinitely, and this situation will never unblock on its own. It requires you to kill the SQL Writer Service to unblock the situation.

Source

Twitter thread where I first heard about it: twitter.com/josh_the_coder/status/1272923360964620288

Can't believe Sean would imply that a KB would have incorrect information in it.

@SeanGallardy-MostlyRetired Haha well...that's a very good point.

@JoshDarnell Let's go 1 step further, you have massive amounts of tracing in place. You know which block is taking a long time. It's still non-trivial to understand why that is occurring. While this will help with RCA, there are only a few buckets where knowing this can help with currently occurring issues. Especially when it shows that it's a networking layer issue, which as you might guess, requires an imperial crap ton of other tracing.

I'm not saying improvements shouldn't be made. This is the first step in identification, which is the log block causing the issue, it's just that there is nothing good in terms of solving the problem in this problem space that it's mostly vestigial.

@JoshDarnell Interesting!

I read your blog, Sean. I trusted you!

@SeanGallardy-MostlyRetired Yikes. I was thinking that the LSN being present lets you know that an individual log block is taking forever, vs. having a bunch of "unrelated" HADR_SYNC_COMMIT waits that are comparatively shorter. I worked on a system a while back where that was the most prevalent wait, but it wasn't actually a problem (there were just lots of short instances of that wait - which is expected with sync AGs).

I guess that still doesn't really tell you much though, as my case was "there isn't a problem" and most folks looking at these waits, there probably is a very big problem 😁

@Zikato What Josh asked wasn't documented anywhere :)

Also: I am special

He rides the short bus as proof.

Luckily, I'm his seatmate.

@SeanGallardy-MostlyRetired I had to look up the meaning

@SeanGallardy-MostlyRetired 🤣

@SeanGallardy-MostlyRetired I think it’s time to take the Mostly Retired out. You answer more questions than I do.