The Heap™ – Consultancy ©® - 2022-11-02

« first day (4320 days earlier) ← previous day next day → last day (765 days later) »

Paul White

00:00

Can you imagine the bugs in a Microsoft vaccine

Ironic

All things considered, if I had the choice, I'd prefer a hydrogen powered car I think

J.D.

lol

Erik Darling

00:20

I’m waiting for the Postgres version.

7 hours later…

Zikato

07:04

Morning

@PaulWhite Thank you, Paul. I probably made mistakes yesterday because the partitions were being eliminated in the end, but there were still so many rows that I didn't grab the actual execution plan.

John K. N.

07:43

Morning

Paul White

08:31

@Zikato I see. So it's all resolved now?

John K. N.

08:45

| vlf_begin_offset  | vlf_sequence_number | vlf_active | vlf_status |     vlf_first_lsn      |
|-------------------|---------------------|------------|------------|------------------------|
| 8192              | 71856               | 0          | 0          | 00000000:00000000:0000 |
| (107 res clipped) | NULL                | NULL       | NULL       | NULL                   |
| 840957952         | 71857               | 0          | 0          | 00000000:00000000:0000 |
| 899153920         | 71858               | 1          | 2          | 000118b2:00000010:0001 |

💚it

Paul White

08:59

very good

Paul White

09:25

@JohnK.N. thanks for the bounty

John K. N.

09:38

@PaulWhite You are welcome.

Zikato

09:52

@PaulWhite I think so. Thank you for your help.

3 hours later…

Zikato

12:24

Does Kusto/KQL belong on dba.stackexchange or main SO?

Paul White

Either. More likely to get answers on SO, I think

Sean Gallardy

13:22

@Zikato You have my deepest sympathies.

bbaird

Morning heap

Sean Gallardy

☀️

Zikato

@SeanGallardy So far it's not so bad. But I'm struggling with Top n Per bin(timestamp,x)

Paul White

Nothing's bad to begin with

Erik Darling

13:37

powershell

Paul White

Vérace perhaps

3

bbaird

MongoDB

Paul White

That's eventually consistently bad

bbaird

BASEically bad

Erik Darling

@SeanGallardy was the SLO stuff you were looking at the other day wrt hekaton

Paul White

13:48

Sean only works on HA/RD problemz

Sean Gallardy

@ErikDarling It was not, I was looking at exports of the SQL Server dll's to see what I coudl export hook :D

@PaulWhite I like OS/Hardware items, which is why I'm not a fan of QO/QE. I'd rather go with memory/cpu/scheduling/networking/disk structures.

HA just happens to fall into all those buckets

Paul White

Anything that keeps you off my lawn

Erik Darling

@SeanGallardy oh okay, i have a client with some interesting stuff going on that seems related to that and spgo

Sean Gallardy

@ErikDarling SPGO is binary optimization...

Feel free to hit me up on the issue

Paul White

What's SPGO?

Sean Gallardy

13:54

"Profile Guided Optimization" so you'd like it. youtube.com/watch?v=zEsdBcu4R00

>PGO is a runtime compiler optimization which leverages profile data collected from running important and performance centric user scenarios to build an optimized version of the application.

Paul White

thanks

Sean Gallardy

> PGO optimizations have some significant advantage over traditional static optimizations as they are based upon how the application is likely to perform in a production environment which allow the optimizer to optimize for speed for hotter code paths (common user scenarios) and optimize for size for colder code paths (not so common user scenarios) resulting in generating faster and smaller code for the application attributing to significant performance gains.

I'm not an authority on it

I know we use a lot of VTune as well: intel.com/content/www/us/en/develop/documentation/…

Once again optimizers were a mistake

Paul White

Yes, I just didn't recognise the acronym in context

Sean Gallardy

Is there such a thing? Optimistic Optimizers

Paul White

Didn't seem very Erik iykwim

Sean Gallardy

13:59

In Erik's blog video today, go to 9:59 into the video. Tell me that isn't a car crash that's happening outside his office.

@PaulWhite 4 sure

Paul White

Erik has a blog now?

Sean Gallardy

So I'm told by my email

Paul White

I hope it has subtitles

Sean Gallardy

No subs, but good maths

Erik Darling

@SeanGallardy i forwarded you the email, there are case numbers

it's pretty hysterical

at the intersection of hekaton and azure

Hannah Vernon

14:03

4u

Sean Gallardy

@ErikDarling Got it!

@ErikDarling Ok, yes I know of the hekaton issue, it's quite interesting actually.

That was a huge deal, like UGE deal and there was a lot of people working around the clock to fix that

Erik Darling

darn those summer interns

Hannah Vernon

essentially don't use hekaton because it will make you say hek a ton

3

Sean Gallardy

For the 2nd hekaton item, yeah that's a kind of "known issue" type of thing where the varheaps/lookaside lists aren't trimmed (optimization) and use all the free space.

There were a few different fixes checked in for that, not sure if/when they made it out

Paul White

varheaps have cropped up in recent CUs

Erik Darling

14:17

It’s always mourning in the varheaps

5

Hannah Vernon

nice one

Sean Gallardy

"The following is running slow <snip>... we don't want to change our query."
`OPTION (RECOMPILE, USE HINT ('FORCE_DEFAULT_CARDINALITY_ESTIMATION', 'DISABLE_OPTIMIZER_ROWGOAL'), NO_PERFORMANCE_SPOOL, HASH JOIN);`

Did Paul write that for these people?

Erik Darling

seems reasonable to me

Paul White

Performance spools are great, so no

Erik Darling

14:36

i went through like three years of them being terrible in query plans and ever since they've been useful

not sure what happened

Paul White

You grew

Erik Darling

oh you flirt

Paul White

😘

Sean Gallardy

A little diddy about Paul and Erik, two performance tuner kids doing the best that they can...

Paul White

You'll get the right result from an e-core eventually

Erik Darling

14:47

that's where the name comes from

eventual cores and prompt cores

Cade Roux

15:01

morning

Lamak

morning

2 hours later…

Cade Roux

16:46

How is it possible to run BEGIN TRANSACTION; INSERT blah; COMMIT TRANSACTION; and get an error that there is no corresponding begin transaction?

No constraints, no triggers

Cade Roux

17:03

No error in the INSERT statement

ypercubeᵀᴹ

is IMPLICIT_TRANSACTIONS set to OFF?

Cade Roux

Yes, but I can't reproduce the thing with that ON or OFF.

Erik Darling

does it only happen in one ssms window?

Cade Roux

It seemed to happen in SQLCMD during their install, so they stepped through the install in SSMS running individual blocks, and then they got to this one block like that and it failed, so I am trying to reproduce.

The INSERT is just literals

They lied, but still, just a system function

BEGIN Transaction
--Update version number
PRINT N'Insert version 8.0'
INSERT INTO [SchemaChangeLog]
       ([MajorReleaseNumber]
       ,[MinorReleaseNumber]
       ,[BuildReleaseNumber]
       ,[ScriptName]
       ,[ScriptID]
       ,[DateApplied]
       ,[Description])
VALUES('00','0000','0000','DatabaseUpgrade_8.0.sql','Upgrade8.0',SYSDATETIMEOFFSET(),NULL)
IF @@ERROR = 0
BEGIN
 COMMIT
 PRINT 'Success'
END
ELSE
BEGIN
 ROLLBACK
 PRINT 'Failure'
END
GO

So obviously it's possible that running multiple blocks in the same window and they stepped through things, earlier blocks may have left a transaction open or some other kind of state issue, but I don't see how this particular commit could have produced that error.

Charlieface

17:25

If it runs inside another procedure (or sp_executesql batch), and that procedure is called from inside a transaction then you will get that error dbfiddle.uk/9CI_jCtg

Which is why I always recommend not rolling your own error-handling, and instead just relying on SET XACT_ABORT ON

dbfiddle.uk/AK09rsct

Cade Roux

But you should see the other errors first, right?

Charlieface

Depends what the error is. Unless you give us a full repro we don't know. It could be only one of the errors are being returned, due to even more error-handling horribleness.

See also this post

13

A: What is the point of TRY CATCH block when XACT_ABORT is turned ON?

You are right that it is not necessary to catch errors you are not intending on handling. SET XACT_ABORT ON; ensures a rollback in all circumstances (except for a couple of very weird edge cases of uncatchable errors, which Erland Sommarskog says are basically unfixed bugs). Syntax errors from dy...

Cade Roux

And this isn't inside a proc, it's just a batch script

Charlieface

From SSMS? Are you sure there were no open transactions?

Cade Roux

I think there very well could have been open transactions from earlier blocks, but I can't get it to happen with more BEGIN TRANS, only with more COMMITs.

Charlieface

17:30

Perhaps you ran the block without the first line?

Anyway, sounds like you should write a proper question post.

Cade Roux

That's the only thing I can think when they were going through manually, yet the installation script doesn't have the ability to skip that, so I still need to get them to the point where they can reproduce it in the installer and fix the problem, whatever it is.

I need a real minimal reproducible case to post a question.

Cade Roux

18:01

I think this might be a red herring at the end of the script. What happens in the script is a BEGIN TRANSACTION; then a ton of database schema changes, then this insert and finally the COMMIT or ROLLBACK. I think somewhere, earlier we dropped out of the transaction with a ROLLBACK, and that's what we need to figure out. Because the schema changes are all rolled back but the log table entry is there, so somewhere (not in the script) a ROLLBACK is happening. But no procs are supposedly called.

So the real thing is to find the ROLLBACK that's not in the script.

Cade Roux

18:17

Which I would think is one of the schema changes is failing, causing a rollback and there is no message in the script.

Peter Vandivier

18:35

@Zikato CSL, Kusto is shortening of Cousteau ('cause you're exploring the metrics o_O). the full name is Cousteau Semantic Language though

oh FML there's an official KQL docs page now

nothing to see here dot gif

but also, yea, more likely to get hits on SO proper stackoverflow.com/questions/tagged/kql

Sean Gallardy

19:04

@CadeRoux This happens a ton with the SQL upgrade scripts as part of a CU/SP where an error occurs, the transaction is rolled back but the code flow allows for the execution to continue. We get weird stuff like this, it's really tracking back to find the real exception.

Cade Roux

The thing is there is no earlier error (probably due to SQLCMD invocation). So that makes it tough.

Sean Gallardy

On the server, capture all the error messages, see what shows up during the installation

Trace/XE

Cade Roux

The customer site has already progressed back to production after the upgrade was babysat manually and their downtime window passed, so trying to get permission to restore the pre-upgrade backup to a different server and test.

What I am thinking is that there was a batch-aborting error in the script, but in the manual run, they encountered it, evaluated it as a non-issue and proceeded, not realizing, and then got to the end and got a commit error.

The batch aborting would have caused the rollback and undoing of the entire script, but in their manual run, I think they were committing each part.

mustaccio

19:29

0

Q: Database backup strategy

I will like to seek some advice from the public regarding the backup of database strategy. My team is using mysql database for our application and I will to hear opinions on how we can perform database backup or is there any open source platform that can achieve it with an user interface? My idea...

> Nice to use strategy with a user interface

I'd like to have one too

Peter Vandivier

19:46

bareos.com

yw

:p

Zikato

20:12

@CadeRoux The error_reported event is in the default system_health XE session. Altough it doesn't collect several useful actions by default.

user image

♪ Rising up, back on the street
Did my time, took my chances ♫

Sean Gallardy

I did like the memes

I didn't see the latest one from chat on there though

Zikato

Those are chat exclusives

Sean Gallardy

I feel like I'm a celebrity now

getting exclusives

Paul White

Well, you're in a room with at least one celebrity

Sean Gallardy

Which one?

Paul White

20:23

Zikato

Sean Gallardy

I should have czheched his IMDB profile

Paul White

See under, "Man with the Memes"

v famous

Zikato

user image

Paul White

I suspect his long-term plan is to make sql memes subscription-only

Sean Gallardy

all about those subs

Paul White

20:27

cash or czech

Zikato

nice one

Sean Gallardy

He definitely doesn't seem like a czech or credit person

I'm thinking cache all the way

just like his databases, flush full of cache

Paul White

Literally could not be more Czech

Zikato

reminds me of the story when our country sent financial help to some other country. And to thanks us, they showed their and our flag together. Problem is, they've googled "check flag" and used the one that's used in formula racing

Paul White

oh dear 🤣

Zikato

20:30

pakistaniat.com/2007/05/11/…

found it

Paul White

Noun: chequered flag (plural chequered flags)

(motor racing) A black-and-white chequered flag which signifies the end of a race.
(motor racing) A victory in a race.

check please

The English language(s) is (are) worse than T-SQL error handling

Zikato

Buffalo^8

Sean Gallardy

chequered^h^h^h^h^h^hckered

Paul White

20:45

@Zikato you're going to have to explain that one to me

Lamak

Buffalo buffalo Buffalo buffalo buffalo buffalo Buffalo buffalo

"Buffalo buffalo Buffalo buffalo buffalo buffalo Buffalo buffalo" is a grammatically correct sentence in English, often presented as an example of how homonyms and homophones can be used to create complicated linguistic constructs through lexical ambiguity. It has been discussed in literature in various forms since 1967, when it appeared in Dmitri Borgmann's Beyond Language: Adventures in Word and Thought. The sentence employs three distinct meanings of the word buffalo: as an adjectival proper noun to refer to a specific place named Buffalo, the city of Buffalo, New York, being the most notable...

Paul White

huh

that may be the stupidest entry in Wikipedia

I read the explanations several times, and I still think it's stupid

Probably because the verb "buffalo" doesn't exist here

Lamak

yeah, I know....I just knew about it from somewhere

Paul White

We could argue about it being buffalo*8 instead of buffalo^8 as well

@Lamak yeah, thanks, I do appreciate you pointing that out

Sean Gallardy

That is... something else

Paul White

20:55

James while John had had had had had had had had had had had a better effect on the teacher

"James while John had had had had had had had had had had had a better effect on the teacher" is an English sentence used to demonstrate lexical ambiguity and the necessity of punctuation, which serves as a substitute for the intonation, stress, and pauses found in speech. In human information processing research, the sentence has been used to show how readers depend on punctuation to give sentences meaning, especially in the context of scanning across lines of text. The sentence is sometimes presented as a puzzle, where the solver must add the punctuation. == Meaning == The sentence refers to...

Zikato

wow, that one is even better/worse

Paul White

> Jedli na hoře bez holí, meaning either "they ate elderberries on a mountain using a stick" or "they ate on a mountain without any sticks" or "they ate elderberry using a stick to eat their sorrow away"; depending on the phrasing or a correct placement or punctuation, at least 7 meanings can be obtained. By replacing "na hoře" by "nahoře", one obtains 5 more meanings. If separating words using spaces is also permitted, the total number of known possible meanings rises to 58

Erik Darling

this is a dark timeline

Paul White

shadowy figures everywhere

Lamak

paul while zikato had had buffalo buffalo buffalo buffalo buffalo buffalo buffalo had had had had had had had had had had

I don't even know

Sean Gallardy

21:08

@PaulWhite bless you

Erik Darling

21:32

@Lamak twitter.com/DesertIsleSQL/status/…

the only true professional graphic designer around

Paul White

imagine asking Twitter a serious question

Erik Darling

really writing anything on twitter

Zikato

21:47

@PaulWhite I can't see the third meaning there.

Paul White

@Zikato Does this help web.archive.org/web/20140714234458/http://ja.kub.cz/…

Zikato

I've found that as well, but had to google synonyms for "sorrow". I've never heard it before in that sense

Erik Darling

7 hours ago, by Paul White

You grew

Zikato

My vocabulary grew 3 sizes that day

Erik Darling

after writing what should have been a few simple scripts recently i think i understand why developers hate databases

so many rules

Zikato

22:02

don't worry, rules will be removed in a future version of MS SQL

Source: learn.microsoft.com/en-us/sql/t-sql/statements/…

Erik Darling

=|

Paul White

22:31

@ErikDarling You shrunk

Erik Darling

you're slurring your Ds

not a good sign

Paul White

😀

J.D.

23:21

@Zikato 🤔

Sean Gallardy

23:44

@Zikato I remember those being on some MS tests in 2005 :/

« first day (4320 days earlier) ← previous day next day → last day (765 days later) »

Transcript for

Nov '222

The Heap™ – Consultancy ©®

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

site design / logo © 2024 Stack Exchange Inc; legal

mobile