The Heap™ – Consultancy ©®

« first day (4998 days earlier) ← previous day next day → last day (25 days later) »

mustaccio

12:59 AM

> Benjamin Dicken

Give the man a break, he's an educator, not some kind of professional

Erik Darling

Hmm. Yes. Not much practice in the matter.

2 hours later…

Paul White

2:53 AM

Benji Dicken to his close friends

2 hours later…

Hannah Vernon

5:07 AM

Wordle 1,179 5/6*

⬛⬛⬛🟨🟨
⬛⬛🟨⬛🟨
🟨⬛🟨🟨⬛
🟩🟩⬛🟩🟩
🟩🟩🟩🟩🟩

Paul White

5:36 AM

Voting is open: Retail assertion and errors with TABLESAMPLE on a DML target

I can't quite believe MS were planning to add CoPilot to SSMS 21 with no option not to

Do they learn nothing from past experiences like Azure Data Studio?

I guess they can't comprehend anyone not having the Kool-Aid supplied intravenously

Vérace

5:53 AM

A chairde - Morning all!

Wordle 1,179 4/6*

⬛⬛⬛🟨🟨
🟩🟩⬛⬛⬛
🟩🟩⬛🟨⬛
🟩🟩🟩🟩🟩

Paul White

Wordle 1,179 5/6*

🟨⬛⬛⬛⬛
⬛🟨⬛⬛🟩
🟨⬛🟨⬛🟩
🟩🟩⬛🟩🟩
🟩🟩🟩🟩🟩

John K. N.

6:09 AM

Wordle 1,179 4/6*

⬛⬛⬛⬛🟨
⬛🟨🟨⬛⬛
⬛🟩🟨⬛🟨
🟩🟩🟩🟩🟩

John K. N.

6:34 AM

@ErikDarling took me a while 😂😂

4 hours later…

Paul White

10:33 AM

@ErikDarling Gr8 video. Enjoyed that. Funny there's always more to say about cursors

For example, your dynamic cursor isn't dynamic. That's not possible with a sort. You get a keyset cursor instead (TYPE_WARNING would print a message about that).

The Chk thing is also interesting. It's a storage engine checksum so (a) it computes its checksum at a lower level where it has direct access to the row; (b) surfacing all the columns to the query processor to do a CHECKSUM(*) has a cost; (c) the storage engine cheats massively.

The query processor computes the CHECKSUM(*) in part on the Body column, which involves an expensive full LOB read and checksum calculation over every character. It's always the Body column!

The storage engine cheats because it doesn't fetch the whole LOB at all. The name of the game is to detect changes, so it uses the LOB version number stored in the LOB root page. This is the massive cheat. Opening the LOB and reading the version number from the root page is cheap as.

The other way to avoid the Chk column, aside from making the cursor READ_ONLY (quite correctly) is to specify SCROLL_LOCKS as a cursor option. This means the row is held U locked while it is current, so there's no chance of concurrent changes and no need for a checksum.

You'll find your 'dynamic' (keyset) cursor is just as fast as the raw query if you use SCROLL_LOCKS.

You can also cheat, however. You know which columns your query uses, and hence which columns you care about detecting changes in. That's Id (which can't change), OwnerUserId, and Score. You don't need to checksum Body either!

SELECT TOP (1)
    P.OwnerUserId,
    CHECKSUM
    (
        P.OwnerUserId,
        P.Score
    )
FROM dbo.Posts AS P
ORDER BY
    P.Score DESC,
    P.Id DESC;

That's about 50% slower than the non-cursor query, but a big improvement over including Body in the checksum, along with everything else

~~~ ends ~~~

The Curse Of Cursor Options In SQL Server

►

Michael Green

11:25 AM

Good morning Heap.

Erik Darling

@PaulWhite thanks m8y. Lemme give some of this a spin.

Michael Green

Today I found some dynamic SQL with REPLACE() nested 28 deep. This is a record for me.

Paul White

Yikes

Use TRANSLATE?

For a moment, I read that as dynamic SQL nested 28 levels

One can only imagine the number of quote marks

Josh Darnell

@MichaelGreen Were you looking at the sp_WhoIsActive source? 😄

github.com/amachanic/sp_whoisactive/blob/master/…

Paul White

xml defence

It's kinda funny. Even if MS were to suddenly go nuts and provide a decent language, no one would use it for 20 years

So their scripts are back compat

Erik Darling

11:54 AM

oh like if they made dynamic sql better but then you'd need dynamic sql to figure out if you can use the new dynamic sql or not

Paul White

right

though they could add a language feature that only compiles the right code sections

Like #IF version >= 2016...

Madness, I know. No language provides that sort of feature

Except most of them

Erik Darling

how'd we get so lucky

Paul White

yeah

Erik Darling

12:10 PM

huh

why do you suppose only max compile memory is tracked and not any of the other compile stats

sys.query_store_query

> Kumeu River Village Pinot Noir 2020
Established in 1944, Kumeu River Village is a benchmark estate in New Zealand. Their balanced and fresh Pinot Noir has all the NZ Pinot notes of raspberry, cherry, and plums on its round palate, along with subtle acidity and a hit of chalky minerality that keeps it dry and refreshing. A perfect red for September weather. $19.99

tasting opportunity at our local wine shop

Michael Green

12:36 PM

@JoshDarnell Oh gosh, that's spookily similar. But no, it's template ETL code with tables, columns and predicates substituted at runtime.

@PaulWhite Synapse. Not supported. :sad-face-emoji:

@PaulWhite I did go two levels (or was it three?) once. Something to do with partitioned views automatically extending every x months in perpetuity. IIRC sp_oa and smo got involved too. Good times.

Erik Darling

1:28 PM

table partitioned on datetime2(2) with a where clause that uses getutcdate

sometimes compile times are 5+ seconds

shockering

Lamak

Wordle 1,179 3/6

⬜🟨⬜⬜🟨
⬜⬜🟨🟩🟨
🟩🟩🟩🟩🟩

4 hours later…

Erik Darling

5:14 PM

azure sqldb doesn't support openquery

i think it's the worst place on earth

Peter Vandivier

Sep 5 at 18:21, by Zikato

Azure users deserve to have problems

so i've heard

2 hours later…

Josh Darnell

7:18 PM

I just worked on a bug where someone assumed Task.Delay(int.MaxValue) meant a Task that waits indefinitely. And thus the thing they wanted to happen only once was happening every 24.8 days instead, causing all manner of inexplicable and confusing problems.

Zikato

I cannot imagine a larger number than max int

Erik Darling

7:34 PM

double it

Paul White

@JoshDarnell did you quote documentation at them

@ErikDarling well, that's inconvenient

Josh Darnell

@PaulWhite Oh, I'm about to.

@ErikDarling 😅

It's funny, being bitten by a bug because of good uptime.

@Zikato You're in good company!

Or...some kind of company, anyway.

Erik Darling

you should provide a chart of how many days that is on all the known planets

for completeness

Paul White

I suppose there's a good reason the task didn't just exit when it had done the thing that should only happen once

@MichaelGreen i can't decide if dba.stackexchange.com/a/340598/1192 is two levels of nesting or three

Erik Darling

Task.Delay(int.MaxValue)
Task.Kill()

Paul White

7:49 PM

It's a static so it applies to yourself

Josh Darnell

@PaulWhite No. It was an entirely unnecessary bit of overengineering, as far as I can tell.

Paul White

Not reading the documentation is an error, but suicide isn't the answer

@JoshDarnell gr8

Josh Darnell

There was a CancellationToken being passed into the Task.Delay, so that an external caller could release the task at some point. But it never happens in practice.

Paul White

If only it had happened after 24.7 days

It's a terrific repro though. Run this code then wait 24.8 days would be an epic instruction

Erik Darling

how many days would a bigint max be

or whatever you call it in a real language

Sean Gallardy

8:02 PM

@PaulWhite Oh, I see you've worked with Azure support too

Erik Darling

you're just mad that everyone knows how sqldiag works now

Sean Gallardy

It's no secret, it's also not even something good to copy off of

Paul White

8:25 PM

@JoshDarnell a superb bug fix would be to get the caller to send the cancellation token after a random small number of milliseconds less than the maximum time. Good luck to the next person investigating that race condition

Josh Darnell

@ErikDarling Like 292 million years.

Erik Darling

exponential back-in

Josh Darnell

@PaulWhite Hahaha

Paul White

Daily WTF material

Erik Darling

292,471,208

yw for the commas

Josh Darnell

8:27 PM

@PaulWhite It's funny because out ticketing system requires putting in QA instructions for our QA team. And that's essentially what I would need to put. "Make sure X doesn't happen after the app has been running for 25 days"

Paul White

Glorious

Josh Darnell

@ErikDarling ty

I always have to look up how to do that.

Erik Darling

DECLARE
    @max AS bigint = 9223372036854775807;

SELECT
    forever = FORMAT(@max / 1000 / 60 / 60 / 24 / 365, 'N0');

Paul White

Dr N0 is how I remember it

Adds thin space if you're French, I hear

Localised factors

I knew I'd regret checking my email today

Paul White

8:53 PM

Azure makes everything difficult and tedious

Sean Gallardy

9:21 PM

Man who hates apostrophes loves commas, today at 6.

Erik Darling

i love all punctuation in the correct place

Sean Gallardy

But not correct capitalization

Erik Darling

9:40 PM

Only when mobile and the AI does it for me.

Sean Gallardy

Glad we invested billions in AI, that ROI really showing :D

2 hours later…

Erik Darling

11:26 PM

K1 Acquires MariaDB, a Leading Database Software Company, and Appoints New CEO

Vérace

11:37 PM

@ErikDarling I wonder what sum Monty Widenius managed to gouge out of this K1 outfit. Sun paying 1Bn for an engine that didn't even have FKs as a default - gotta hand it to him though!

Anyone watching the debate tonight? Personally, I hope the prosecutor crucifies the felon!

« first day (4998 days earlier) ← previous day next day → last day (25 days later) »

all rooms

Transcript for

Sep9

Sep10

Sep11

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC