@ErikDarling Figures because of the hack, they're not doing well generally. Noone feelse sorry for them because of their aggressive sales tactics.

@PaulWhite it’s like guessing trace flags

@Charlieface I’ve never looked at their financials, but I have hated them as a company for a long time. Their DPA monitoring tool is total garbage.

@ErikDarling You mean you never did that?

Not with any satisfaction

Stupid word

@J.D. one might say it was merdé

Morning

Two cm of snow overnight again, but it's thawing now

An unseasonable polar blast arrived here about 2pm this afternoon. Dropped from 18C to 12C in about an hour. No snow here though there has been some at elevation on the South Island

morning

Interesting grid shape

robbed of a three

@PaulWhite congrats

I'm still waiting for Erik to pick up Wordle

Have you met Erik at all. You'll be asking him to participate in math puzzles next

and chess puzzles

yeah

Bah

I spend enough time being puzzled

I've just discovered a new trick that I don't think I've seen anywhere. LEAD(CAST(NULL AS bit), 1, 1) OVER... gives you what, and why would it be useful rather than the more obvious solution?

Perfect timing

For anyone desiring context

@HannahVernon mierde indeed.

@PaulWhite Thanks for the meta link, didn't realize database recommendation questions were an exception. I've seen plenty of other ones close, e.g. this one but I'm assuming that's more so due to the quality of the question. Though the Closed Reason is incorrect on that one then.

Everything has exceptions. Some have been discussed on meta, some haven't.

Yea, good to know, thanks.

Close reasons don't matter much except as a hint to the poster about potential improvements.

I don't often bother reopening and closing again just to choose a better reason.

Sorry, Reason.

Worddd

quicker this morning

@Charlieface You might prefer not to use the optional default for LAG/LEAD

ISNULL
(
    LAST_VALUE(0) OVER (
        PARTITION BY D.DeviceId
        ORDER BY D.Time ASC
        ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING),
    1
)

@Lamak Starting with two blank lines is a popular choice this morning

was an artistic decision

More messy methinks, and LEAD compiles down to LAST_VALUE anyway.

But the point of it, in case anyone hasn't got it, was to get a 1 for the last row in the window, without using row-number on the opposing sort.

@Charlieface Yes, it does. But using the default adds an extra count aggregate. And using the more explicit syntax makes it more obvious you're getting an unoptimized window function with a FOLLOWING element. That part is preference. I dislike hidden details.

But developers tend to like such things and not worry about obfuscation.

@JoshDarnell Not quite joining the 'two leading blank lines' club today, but close

@PaulWhite Curious: is FOLLOWING less efficient than PRECEDING? Or LEAD less efficient than LAG?

@PaulWhite Haha yeah, close enough 😀

@Charlieface Not really; they're often equivalent and rewritten by the optimizer. I was referring to the optimization available for ROWS UNBOUNDED PRECEDING.

> The good news is that when the frame starts with UNBOUNDED PRECEDING, SQL Server identifies the case as a fast track case, in which it simply takes the previous row’s running total and adds the current row’s value to compute the current row’s running total, resulting in linear scaling. In this fast track mode, the plan writes only two rows to the spool per input row—one with the aggregate, and one with the detail.

sqlperformance.com/2019/08/sql-performance/…

Further details in Itzik's Window Functions book

@PaulWhite ah Itzik the god of window functions.

So ROWS UNBOUNDED PRECEDING is more efficient, I was always unsure.

He's probably mentioned the drawback of the LAG/LEAD defaults somewhere too, but I don't recall.

Eg sounds like it's more efficient to do SUM(val) OVER (ORDER BY x ROWS UNBOUNDED PRECEDING) - val than to do SUM(val) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

Yes

And these are some of the non-obvious gotchas I dislike syntax sugar for

I had a suspicion that was the case, had to do a one of those wacky in/out inventory-over-time calculations recently.

As far as LEAD default concerned: really daft that LEAD can't realize it's got the last row without having to do a COUNT

shrug

Kinda like SUM needs to do COUNT just to get you the null (even if you don#t want)

Don't shrug you don't work for MS.

Very similar yeah

Well, there are very many questionable implementation decisions with window functions

That one doesn't annoy me the most, and I don't know offhand if it's even possible to do something else there

Hence, shrug

Like, it annoys me the prior query computes the same row number twice

But there we are

Considering window functions are serial plans, you just need to keep in hand the previously read row and whether you've read a row before (or following for LEAD).

Window functions are serial plans?

Serial zone, at least per partitioning value(s)

MS finally realized the whole thing was a mistake, so they brought in batch mode and did it properly (sort of). Then had the gall to try and charge extra for it.

It's basic for loops 101, it wasn't hard.

🤣

properly huh?

Or wasn't hard? trying to work out if you're taking the mickey or not

> basic for loops 101

The multi-pass batch mode implementation is actually pretty tricky

chat was a mistake

most things were

I'll get my coat

I'm not really as Standard Edition sort of person, but you can get batch mode there, just not as conveniently as with batch mode on rowstore in Enterprise.

Most obviously by creating a columnstore index

I don't know what the situation is with the cloud versions

In some ways, the Window Aggregate is worse than the Window Spool. It hides a tremendous amount of detail.

@PaulWhite I know, the dummy CCI table. I was thinking of without any tricks.

I know it hides detail, who cares, it's faster.

Spoken like a true developer 🙂

This still isn't fixed after 6 years feedback.azure.com/d365community/idea/…

Not a huge deal in itself, but speaks to lack of attention to detail

The original row mode window functions were implemented the way they were for lack of time (shipping deadline).

The point about hiding detail is mostly about understanding and debugging. A black box with little exposed detail is hopeless if it doesn't perform as well as hoped. What to do to improve things?

The two things aren't necessarily opposed either. It's perfectly possible to have a very efficient internal implementation, but still expose detail. No one would mind if several operators in a plan with useful properties were implemented as a single complex operator in the execution engine. Not being able to see the number of passes, frame cache size, and other details makes it tough to refine.

They have made some effort on occasion, like with the locally aggregated rows but too many important optimizations are completely hidden from the query tuner, especially in batch mode and on columnstore.

None of it matters because SQL Server 2019 is slower than 2016 anyway

Oh that's been confirmed now has it

Luckily, I hear 2022 is nothing but good news

Also, people talk even less about 2017 than 2014

Yep, 5-10%

2017 didn’t need to happen

Service Pack

What was even in 2017 anyway

Adaptive joins

Interleaved execution

Linux!

Memory grant feedback too

Resumable online index builds

Graph (lol)

Actually, that's quite a lot

I wonder how much of the slow down is due to the proliferation of cloud editions

Debugging 2022 code is a real journey. So many pointless calls to cloud edition specific things

And even things like ADR checks turn up at low levels that must get called a lot

it's like they're trying to keep you out

Then they completely changed the floating point implementation, which gets used in heaps more places than you'd think. Not to mention security related stuff like routine stack space checks and mitigations for Intel bugs

this is what i was referring to in case you hadn't seen already: Is SQL Server 2019 More CPU-Intensive Than SQL Server 2016?

Thanks, I was going to ask eventually

Oh, and the explosion in feature flags and cloud-only metrics, diagnostics, whatnot

> In this demo case, 2019 compat level actually works beautifully, dropping the CPU time down by about 1/3, and I wish the client’s case was that easy.

sounds like they should have hired a better consultant

does

I can't be bothered to walk over to the machine with 2016, but I did run it quickly on 130 and 150 compat 2019:

 SQL Server Execution Times:
   CPU time = 3859 ms,  elapsed time = 4001 ms.
   CPU time = 3891 ms,  elapsed time = 3907 ms.
   CPU time = 3937 ms,  elapsed time = 3967 ms.

 SQL Server Execution Times:
   CPU time = 2375 ms,  elapsed time = 2439 ms.
   CPU time = 2375 ms,  elapsed time = 2404 ms.
   CPU time = 2391 ms,  elapsed time = 2403 ms.

Not unexpected seeing as the 150 execution gets batch mode on row store.

It was inevitable that future versions would be slower anyway.

2016 came with that "It Just Runs Faster!" guarantee

Anyway, being the professional he is, I'm sure Brent ran the same workload on 2016 & 2019 and compared the average worker times.

Probably ran Windows Performance Recorder too.

loves WPR

can't keep him away from a good heatmap

I knew it

That post does read like the tests were run on different physical hardware though

sounds like a vm problem

I mean it probably is a SQL Server thing (see above) but there are so many possibilities like even different microcode versions on the physical processors

Also, benchmarking is a EULA violation

He'll be getting a knock on the door

yeah i don't doubt it, but i'm also not doing the work

same

here comes the cookie monster

om nom nom

A cynical person might wonder if Brent's hoping that will hit HN and Reddit

Lucky we don't allow that sort in here

a really cynical person might wonder if he's trying to get people to do free work for an engagement he got paid for

shocking, i know

bounce yourself out right now

assuming you can still lift that much

ha ha

is that a fat joke or a weak joke

@ErikDarling yes

maybe instead of wine i should start sending you whey protein

whey to go

sir

please reconsider

I don't even know what whey protein is tbh

Sounds like a body building thing but who would know

Maybe I could feed it to my 2019 instance to make it go faster?

usually you put it in a cup so i'm sure putting it in a cpu would also be fine

SQL Server 2019 was a compiler change, I'm going to guess there was a function that didn't get inlined or optimized the same way.

wat.bmp

SQL 2019 changed which version of the msvc++ compiler was used to generate the executables

different versions of compilers behave differently, even with the same options

oic

i thought you were talking about something else

So one compiler might have aggressively inlined functions that another one doesn't. Additionally, there might be code changes in the functions which now do more work.

There's also differences in the profiled execution stuff, but I'm not sure what is used there or not

In any case, a wpr would be helpful

I'm betting 2022 also has the same issue, which might be worse than 2019 given the new additions in features and code

I wish I had time to setup and test it :/

I can test it if you tell me what you want from wpr

Do you want both in one file or separate files

2 different

4u

Was there a blog post about the compiler change? I feel like I remember reading about that.

But maybe it was stuff Sean mentioned in chat.

@ErikDarling Ok, I setup a new VM and tested it... I'm getting like ~3% diff max

we're talking 10's of ms

Not sure how that translates to 5-10%

not really interesting anymore either

--- 2016 ---
Beginning execution loop
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 2765 ms,  elapsed time = 2862 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 2828 ms,  elapsed time = 2847 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row affected)

 SQL Server Execution Times:

--- 2019 ---
Beginning execution loop
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 2781 ms,  elapsed time = 2844 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 2937 ms,  elapsed time = 2943 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row affected)

 SQL Server Execution Times:

Lol

I haven’t gotten to it yet

Is it just me, or is it unreasonably difficult to see your raised-flags summary at dba.stackexchange.com/users/flag-summary/xxxx where xxxx is your user id