« first day (4998 days earlier)      last day (65 days later) » 

12:59 AM
> Benjamin Dicken
Give the man a break, he's an educator, not some kind of professional
Hmm. Yes. Not much practice in the matter.
 
2 hours later…
2:53 AM
Benji Dicken to his close friends
 
2 hours later…
5:07 AM
Wordle 1,179 5/6*

⬛⬛⬛🟨🟨
⬛⬛🟨⬛🟨
🟨⬛🟨🟨⬛
🟩🟩⬛🟩🟩
🟩🟩🟩🟩🟩
5:36 AM
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
5:53 AM
A chairde - Morning all!
Wordle 1,179 4/6*

⬛⬛⬛🟨🟨
🟩🟩⬛⬛⬛
🟩🟩⬛🟨⬛
🟩🟩🟩🟩🟩
Wordle 1,179 5/6*

🟨⬛⬛⬛⬛
⬛🟨⬛⬛🟩
🟨⬛🟨⬛🟩
🟩🟩⬛🟩🟩
🟩🟩🟩🟩🟩
6:09 AM
Wordle 1,179 4/6*

⬛⬛⬛⬛🟨
⬛🟨🟨⬛⬛
⬛🟩🟨⬛🟨
🟩🟩🟩🟩🟩
6:34 AM
@ErikDarling took me a while 😂😂
 
4 hours later…
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 ~~~
11:25 AM
Good morning Heap.
@PaulWhite thanks m8y. Lemme give some of this a spin.
Today I found some dynamic SQL with REPLACE() nested 28 deep. This is a record for me.
Yikes
Use TRANSLATE?
For a moment, I read that as dynamic SQL nested 28 levels
One can only imagine the number of quote marks
@MichaelGreen Were you looking at the sp_WhoIsActive source? 😄
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
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
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
how'd we get so lucky
12:10 PM
huh
why do you suppose only max compile memory is tracked and not any of the other compile stats
> 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
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.
1:28 PM
table partitioned on datetime2(2) with a where clause that uses getutcdate
sometimes compile times are 5+ seconds
shockering
Wordle 1,179 3/6

⬜🟨⬜⬜🟨
⬜⬜🟨🟩🟨
🟩🟩🟩🟩🟩
 
4 hours later…
5:14 PM
azure sqldb doesn't support openquery
i think it's the worst place on earth
Sep 5 at 18:21, by Zikato
Azure users deserve to have problems
so i've heard
 
2 hours later…
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.
I cannot imagine a larger number than max int
7:34 PM
double it
@JoshDarnell did you quote documentation at them
@ErikDarling well, that's inconvenient
@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.
you should provide a chart of how many days that is on all the known planets
for completeness
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
Task.Delay(int.MaxValue)
Task.Kill()
7:49 PM
It's a static so it applies to yourself
@PaulWhite No. It was an entirely unnecessary bit of overengineering, as far as I can tell.
Not reading the documentation is an error, but suicide isn't the answer
@JoshDarnell gr8
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.
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
how many days would a bigint max be
or whatever you call it in a real language
8:02 PM
@PaulWhite Oh, I see you've worked with Azure support too
you're just mad that everyone knows how sqldiag works now
It's no secret, it's also not even something good to copy off of
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
exponential back-in
@PaulWhite Hahaha
Daily WTF material
292,471,208
yw for the commas
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"
Glorious
@ErikDarling ty
I always have to look up how to do that.
DECLARE
    @max AS bigint = 9223372036854775807;

SELECT
    forever = FORMAT(@max / 1000 / 60 / 60 / 24 / 365, 'N0');
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
8:53 PM
Azure makes everything difficult and tedious
2
9:21 PM
Man who hates apostrophes loves commas, today at 6.
i love all punctuation in the correct place
But not correct capitalization
9:40 PM
Only when mobile and the AI does it for me.
Glad we invested billions in AI, that ROI really showing :D
 
2 hours later…
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)      last day (65 days later) »