The Heap™ – Consultancy ©®

Hannah Vernon

5:03 AM

Wordle 1,040 4/6*

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

Paul White

7:03 AM

Wordle 1,040 4/6*

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

John K. N.

7:31 AM

Wordle 1,040 5/6*

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

Zikato

8:54 AM

Good morning

Erik Darling

9:54 AM

good morning

Erik Darling

11:37 AM

curse you

it's interesting that a case expression causes a late filter and seems to prevent an index scan with a predicate here: dbfiddle.uk/4fDw2L9X

Erik Darling

11:48 AM

... i'm also not sure how that create table statement doesn't throw an error

huh

Zikato

12:07 PM

Which one? In your fiddle or are you quoting some question on the site?

Erik Darling

did you look at the fiddle?

Zikato

Just glanced

Josh Darnell

@ErikDarling Because of the trailing comma?

This is a weird error message (from System.Data.SqlClient):

> BeginExecuteNonQuery requires an open and available Connection. The connection's current state is open.

Erik Darling

@JoshDarnell yes, i've never made that mistake before

or maybe i have

and it just never threw an error

and i never noticed

@JoshDarnell open, but not available

sort of like going into a patek dealer

Josh Darnell

@ErikDarling Yeah, weird. I could have sworn that SSMS has yelled at me about that before, but maybe I'm just thinking of SELECT statements.

@ErikDarling Haha right. I guess I don't know what it means for the connection to not be available. I got this right before that error, so the connection was, indeed, busted. The messaging is just weird.

> A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)

Erik Darling

12:22 PM

sounds like you should run checkdb

Paul White

@ErikDarling It's an historical oddity preserved for back-compat. Not tolerated for the (more recent) table variables

@ErikDarling Things don't pushed past a CASE because that's the recommended solution for a whole class of order-of-evaluation transient errors

You can also avoid the issue in this case with TF 9259

Josh Darnell

@ErikDarling For real?!

Erik Darling

i feel like i've written a million case statements that things got pushed past

it gets pushed with an index keyed on account value

Paul White

Yeah, it all rather depends. I was talking about your specific demo

Erik Darling

it does all rather depend

Paul White

12:27 PM

Dr No on the trailing comma: github.com/MicrosoftDocs/sql-docs/issues/…

Erik Darling

crud

Paul White

@ErikDarling You also often need a sort

There are a million things to consider. Weird edge cases when variable assignment occurs etc.

It's just one of those irritations you have to code around sometimes

Erik Darling

upsetti spaghetti

Paul White

It's certainly not ideal, but as I say, complicated

Things fight sometimes, and it depends what happens in what order

For example, scalar computations are pushed down to enable computed column and index matching. Filters are also pushed down. They can't both be at the bottom.

Of course, it could do both and cost the choice, but these things happen v early, before CBO (which may not happen at all for simple queries)

Erik Darling

a race to the bottom

Paul White

12:34 PM

I wouldn't argue that your demo shows a sub-optimal outcome

Erik Darling

i didn't notice until i was testing repeatable read and serializable and getting deadlocks, and thought the late filter may have been involved (it wasn't, of course)

Paul White

SQL Server relies too much on post-optimization rewrites to fix up heaps of little issues

Asking that to push a filter past a compute scalar and a sort is apparently a step too far

The optimizer itself always produces a separate filter, but it's normally close enough to the GET to be pushed down, post-opt

Erik Darling

i'm sure it would be better in rust

Paul White

Would still affect someone's spacebar heating

Also, you missed a gr8 opportunity to use CHOOSE

Erik Darling

i rarely think of choose or iif

Paul White

12:41 PM

I like IIF for its conciseness

Erik Darling

i've noticed

Paul White

And because the intrinsic has always been IIF

Also, for conciseness, use a $1000 literal instead of the CONVERT

HtH

Erik Darling

eh

a bit imprecise that £1000 and $1000 return the same results

sql server should check exchange rates before filtering those results

Paul White

dbfiddle.uk/k0ptsEjn

Erik Darling

chat seemed to have a bit of a mishap

@PaulWhite quite verbose

Paul White

12:50 PM

The main site was throwing 500s as well

currency type symbols you can use

> Currency or monetary data does not need to be enclosed in single quotation marks ( ' ). It is important to remember that while you can specify monetary values preceded by a currency symbol, SQL Server does not store any currency information associated with the symbol, it only stores the numeric value.

Such helpful documentation

@ErikDarling There may be a better way, but I can't think of it

Erik Darling

i wonder how many people have been caught off guard by that

it is amusing

though i wasn't serious about the current exchange rates being respected

Paul White

People are hardly ever serious in here

Erik Darling

except when it's inappropriate

our irish friends have mastered that

Paul White

Weird list of symbols. Looks like they gave up maintaining it long ago

French francs and drachma!

Currency sign (generic)

The currency sign ¤ is a character used to denote an unspecified currency. It can be described as a circle the size of a lowercase character with four short radiating arms at 45° (NE), 135° (SE), 225° (SW) and 315° (NW). It is raised slightly above the baseline. The character is sometimes called scarab.: 5 == History == The symbol was first encoded for computers in 1972, as a placeholder for national currency symbols such as the dollar sign, in national variants (ISO 646) of ASCII and the International Reference Variant. It was proposed by Italy as an alternative (to the dollar sign) at 0x24...

Someone attempted to standardise

Imagine I pasted the normal xkcd

There is also a little comedy value in taking people seriously when they weren't

Erik Darling

master comedians only

sorry, main comedians only

Paul White

1:01 PM

🙄

the list of problematic words grows without end

Erik Darling

🚬

i smuggled a pack of gauloises home with me

don't tell customs

Paul White

your lakh of respect for tax authorities is alarming

Erik Darling

i'll send them a cheque

Paul White

czech?

Erik Darling

the exchange rate on those is dismal

i'd need about 20 of them

and who can find 20 czechs

Paul White

1:10 PM

did you stride confidently down the Nothing To Declare customs channel?

Erik Darling

well all i had to declare was that i had a nice time exploring business opportunities abroad

customs agents don't really care to hear that

Paul White

very considerate

I wonder if Josh went off to run DBCC CHECKDB

Erik Darling

oh yeah i never answered him

Paul White

48 mins ago, by Josh Darnell

@ErikDarling For real?!

Erik Darling

hopefully checkdb is done by now

Paul White

1:14 PM

he's probably on to his fifth server by now

one does not simply reject expensive consultancy advice

or maybe he's still writing that one line of powershell

Erik Darling

the best advice is often free

Paul White

cheapest, anyway

which is one valid measure of 'best'

Erik Darling

it's often the best available

Josh Darnell

@ErikDarling It's a DB at a customer site, so getting access is convoluted. Should be able to czech it out later today.

Erik Darling

oh i don't actually think you need to run it

SELECT
    m.*
FROM sys.messages AS m
WHERE m.language_id = 1033
AND m.severity = 19;

even if it were a sql server error, it wouldn't indicate corruption

Paul White

1:20 PM

> The transaction timestamps ran out. Restart the server.

Work of art

Erik Darling

SELECT
    m.*
FROM sys.messages AS m
WHERE m.language_id = 1033
AND m.severity > 19
AND m.text LIKE N'%[c-C]orrupt%'
ORDER BY
    m.severity

those are a bit more fun

@PaulWhite nearly a frog

Paul White

That range c-C bugs me

Erik Darling

poor kwah

Paul White

You know you can write [cC], yes?

Erik Darling

did not

Paul White

1:26 PM

Yep, one of a list of characters

With a range, you're into collation and sorting

Thank you for coming to Code Review

Erik Darling

i think i took that from some text search where the predicate was like >[c-C] and <= [g-G] or something

Zikato

Invoice is on the way

Erik Darling

(in the query plan, not the query text)

Paul White

Your cheque has arrived

Erik Darling

hope it lays flat

Paul White

1:29 PM

It's a tiny bit Czechia than the last one

Zikato

I'd have to lose some weight to lay flat again

Erik Darling

get some ozempic

Paul White

A problem easily solved with a board and a big pile of bricks

Just keep adding bricks until the required degree of flatness is obtained

Sean Gallardy

Big transcript today (preemptive 4 me)

Paul White

Have a mop on standby in case you overdo it

Sean Gallardy

1:31 PM

I'm hiring Erik as my Editor if I write a book

If I can afford the invoice

Paul White

Just pay him in illegal tobacco

Erik Darling

pay me in sql server source code

Sean Gallardy

Speaking of which, I've been trying to find time to play with AIDA64

Paul White

Sean's cloud experience enables him to easily throttle transcript throughput

Sean Gallardy

@PaulWhite Hello, thank you for your ticket. We've reviewed your data and found that you need to update your SLO to get more throughput. Thank you.

Erik Darling

1:47 PM

woah woah woah

it hasn't even been 800 days yet

Sean Gallardy

We've heard your feedback and have put in place improvement efforts to get faster turn around when informing you that you need to pay us more money.

you'll be receiving a survey about our interaction today, please let me know how I can receive a 5.

If you don't choose 5, you'll be called and hounded by multiple levels of management.

Erik Darling

2:08 PM

@SeanGallardy this seems like the best way to get attention drawn to your issue

Sean Gallardy

I mean it's lopsided for sure. They'll spend 1 hour looking into your issue and 20 hours fighting you to give them 5 stars.

Don't need to solve issues when you can make the stats look like you do.

Erik Darling

i have a friend who is dealing with a very negative support issue in managed instance

there was an outage because of tempdb system table metadata contention (and of course in memory tempdb isn't available there on mi)

and support is trying to blame it on them having "too many connections"

like yes it's our biggest sales day of the year, thanks for noticing

Sean Gallardy

I might know that one... were they also creating 3k new connections/sec?

Erik Darling

don't know the exact number

but you'll probably recognize one of the names on the email thread, at least

Sean Gallardy

There was one that came in recently that we got called into and they basically DOS'd themselves with connections. Their logic was if it didn't immediately work, create a new connection and do the same thing.

but yes TempDB not having in memory options is the cost of running in the cloud

I'm sure an intern will migrate the functionality

Erik Darling

2:21 PM

based on what the cloud costs it should be an option

unless it's a thoroughly broken feature that microsoft doesn't want to support in their hosted solutions

which deserves a call out if so

Sean Gallardy

Probably a little of A JOIN B

Yeah I think that was a 128 vcore instance

The cost of buying a small house per month

Erik Darling

2:53 PM

Gotta love these flagship features

Sean Gallardy

Speaking of which, I just gave a thoughtfully worded survey response internally.

Erik Darling

3:04 PM

Without me editing it?

Sean Gallardy

3:57 PM

I really should have sent it to you first, this is true

What am I allowed to send you for editing in this contract?

Sean Gallardy

4:15 PM

More reasons not to mess with Florida: tampabay.com/news/bizarre/2024/04/23/…

Erik Darling

4:29 PM

Words in English

Erik Darling

6:34 PM

I found dinner.

Sean Gallardy

Black Angus?

Erik Darling

Not sure what their names are

They weren’t very talkative

Sean Gallardy

Anti-social bunch

Erik Darling

7:47 PM

Probably busy playing wordle or something

Hannah Vernon

8:15 PM

> Unfortunately, common ways of shutting down SQL Server 2012 do not currently automatically checkpoint databases (this contradicts the documentation, so it should be fixed at some point in the future).

I knew I wasn't imagining that.

Sep 16, 2022 at 18:53, by Hannah Vernon

also I feel about 95% certain that some version of SQL Server (prior to 2014 perhaps) didn't issue automatic checkpoints on service shutdown.

Transcript for

The Heap™ – Consultancy ©®