The Heap™ – Consultancy ©® - 2024-10-17

« first day (5035 days earlier) ← previous day next day → last day (130 days later) »

Erik Reasonable Rates Darling

00:07

@J.D. people are surprisingly able to accept terrible conditions, historically

2 hours later…

J.D.

02:00

@SeanGallardy giggity

@SeanGallardy It's insane.

@ErikReasonableRatesDarling Yet complain about performance simultaneously and look for NoSQL to save the day because SQL isn't built for "big data".

4 hours later…

Hannah Vernon

05:38

Wordle 1,216 3/6*

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

Paul White

06:19

Wordle 1,216 5/6*

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

John K. N.

07:00

Wordle 1,216 4/6*

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

@ErikReasonableRatesDarling Your "Ola Stats Only Job.sql" script has a comment in the header stating Targets the master database but in the code you USE msdb.

4 hours later…

Erik Reasonable Rates Darling

11:20

I created an agent job in the gui and scripted it out. You should file an issue with Microsoft about that defect.

John K. N.

@ErikReasonableRatesDarling will do. Probably in 2026.

Erik Reasonable Rates Darling

11:50

timely as ever

@PaulWhite have you ever been able to repro the data movement error with a nolock hint?

Paul White

@ErikReasonableRatesDarling Not in recent times. They did stuff to make it a lot less likely

Erik Reasonable Rates Darling

hm okay

look at microsoft

practically condoning nolock hints

postgres doesn't have this problem

Paul White

12:06

well, the 'did stuff' involves giving up and moving to the next page so

and it's not like it's foolproof, just can't be bothered to try to repro these days

Erik Reasonable Rates Darling

12:17

are there any prerequisites wrt table size/indexing that you recall

this isn't for the isolation levels presentation, just a minor curiosity

Paul White

@ErikReasonableRatesDarling docs.microsoft.com/en-nz/archive/blogs/craigfr/…

Erik Reasonable Rates Darling

12:39

tys

Paul White

and as noted in KB815008 you may need to set the transaction isolation level instead of using a NOLOCK hint

I mention that because for a quick repro, people often rely on hints

all that said, most common cases have been ~~'addressed'~~ coped with by now

Erik Reasonable Rates Darling

u cot me

huh

this is not the plan shape i expected

SELECT
    d2.*
FROM dbo.do_it AS d
CROSS APPLY
(
    SELECT TOP (1)
        *,
        wtf = dbo.wait_for(d2.wait_for)
    FROM dbo.do_it AS d2
    WHERE d.id = d2.id
) AS d2;

user image

i figured the compute scalar would be in the outer part of the nlj

interesting

Paul White

make it non-deterministic?

anyway

> Performing a select statement on a database that exists on a CD-ROM disc may return the following error:

Error: 601, Severity: 12, State: 2
Could not continue scan with NOLOCK due to data movement.

> APPLIES TO Microsoft SQL Server 7.0 Standard Edition, when used with:
Microsoft Windows NT 4.0; Microsoft SQL Server 2000 Standard Edition

More SE problems

Erik Reasonable Rates Darling

coming soon to azure: PREEMPTIVE_CDROM_REQUEST waits

Paul White

no one needs that much I/O perf

Erik Reasonable Rates Darling

12:54

the function is non-deterministic, according to sql server. also not precise.

ah well

Paul White

@ErikReasonableRatesDarling try TF 9259 if you're curious enough

Erik Reasonable Rates Darling

i am. what does that do?

Paul White

stops compute scalars moving around so much

> FIX: Performance is significantly reduced when you set trace flag 9134 to prevent error message 601 in SQL Server 2000

🤣

Erik Reasonable Rates Darling

no change in the plan with it as a querytraceon or globally enabled

this compute scalar is a real damn barnacle

Paul White

ah well

Erik Reasonable Rates Darling

13:03

8 mins ago, by Erik Reasonable Rates Darling

ah well

Paul White

I might be interested enough to look into it if you provide a low-effort repro

but yeah that happens

Erik Reasonable Rates Darling

gist.github.com/erikdarlingdata/…

that's about as low effort as i can make it

Paul White

thx

@ErikReasonableRatesDarling seems to fail with an error

Erik Reasonable Rates Darling

any specific error

Paul White

> Msg 4121, Level 16, State 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.wait_for", or the name is ambiguous.

Erik Reasonable Rates Darling

13:15

did you get the function in the gist?

Paul White

God I h8 gist

Erik Reasonable Rates Darling

if you don't see it hit refresh, it's just below the first file

Paul White

thx again

have it now

Zikato

@PaulWhite It's intimidating at first but then you'll get the gist of it

Erik Reasonable Rates Darling

boooooooooo

John K. N.

13:53

user image

What's the new thing with the asterisk next to the number of close votes on questions?

Erik Reasonable Rates Darling

Oct 9 at 14:36, by Erik Reasonable Rates Darling

oh you get a little asterisk by the close link now if you've already voted to close something

John K. N.

ta

Erik Reasonable Rates Darling

this gets the computer scalar before the join, but uses a merge join which is annoying

SELECT
    d.*
FROM dbo.do_it AS d
WHERE EXISTS
(
    SELECT
        1/0
    FROM dbo.do_it AS d2
    WHERE d.id = d2.id
    AND d.wait_for = dbo.wait_for(d2.wait_for)
);

if i use the function on both columns, it's still a merge join and both functions are evaluated at the join

Paul White

14:09

@ErikReasonableRatesDarling What is it you'd like to achieve? CS before join or under top? Try an outer apply

Hannah Vernon

@J.D. because your CIO can blame someone else when that turd sandwich promised doesn't taste very good.

Which is a pretty shitty reason if you ask me

Paul White

@ErikReasonableRatesDarling Or try this:

SELECT
    d2.*
FROM dbo.do_it AS d
CROSS APPLY
(
    SELECT TOP (1)
        *,
        wtf = dbo.wait_for(d2.wait_for)
    FROM dbo.do_it AS d2
    WHERE d.id = d2.id
    ORDER BY
        wtf
) AS d2;

@Zikato Ho ho. Yeah, I just meant sharing code via gists is tedious and infuriating. Easy to miss supplemental scripts as I did and there's no really easy way to copy code to the clipboard.

user image

Erik Reasonable Rates Darling

14:57

@PaulWhite that's maddening, but ty

Paul White

yw

J.D.

15:26

@HannahVernon yea sounds like something a shitty CIO would do lol

1 hour later…

Paul White

16:51

> Msg 601, Level 12, State 3, Line 8
Could not continue scan with NOLOCK due to data movement.

@ErikReasonableRatesDarling repro'd on 2022 ^

Erik Reasonable Rates Darling

@PaulWhite did you use the example from craig's post or did you need something more involved?

Paul White

@ErikReasonableRatesDarling I think he says in the post that example was fixed in 2005

I created a new one

@ErikReasonableRatesDarling gist.github.com/SQLKiwi/069ae966c9831b06d71630aa954b33df

Erik Reasonable Rates Darling

17:07

groovy

i was going to try to write one where the scalar udf slowed down reads and writes

but mostly because i couldn't think of a very clever use for the function as a performance problem

perhaps i'll use it as a thought experiment

Paul White

But you're The Flash

Erik Reasonable Rates Darling

17:20

Only in that session

Paul White

Ah

Erik Reasonable Rates Darling

18:19

Thank you, Erik. I thought that this was simple, but merely reading the blogs was not enough. Once I studied them, the revelation blew me off of my chair. Your posts are like being covered in jet fuel. — J. Mini 55 mins ago

did i do 9/11?

« first day (5035 days earlier) ← previous day next day → last day (130 days later) »

Transcript for

Oct17

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 © 2025 Stack Exchange Inc; legal

mobile