The Heap™ – Consultancy ©® - 2025-02-18

« first day (5159 days earlier) ← previous day next day → last day (5 days later) »

Paul White

05:31

> PERCENT
Indicates that the query returns only the first expression percent of rows from the result set. Fractional values are rounded up to the next integer value.

TOP (Transact-SQL)

Where did that nonsense come from? Fractional values for PERCENT haven't been rounded up since at least SQL Server 2000

Oh, I think it means fractions of a row

1 hour later…

Zikato

07:12

Could be phrased better

Paul White

Indeed

user image

documentation.red-gate.com/sp/getting-started/…

Paul White

07:32

Grok 3 is available

2 hours later…

Erik Reasonable Rates Darling

09:45

What an exciting day

2 hours later…

John K. N.

11:22

Wordle 1,340 4/6*

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

1 hour later…

Paul White

12:27

Wordle 1,340 5/6*

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

Hannah Vernon

13:21

Wordle 1,340 4/6*

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

Erik Reasonable Rates Darling

14:16

ha ha guys everything is fine paul just forgot to self-answer his questions

Paul White

I didn't forget. I'm giving a chance to anyone else who wants to have a go

It's a situation we don't have a good current Q & A for, as far as I can see

Zikato

Is Paul a lawyer? Never ask a question to which you do not already know the answer

Erik Reasonable Rates Darling

i finally have a half-decent answer for something and paul decides to drop this whopper to distract the voting ring

Paul White

6

A: IndexOptimize and Empty Statistics

weird You're in sort of a weird situation. It's not entirely clear if auto-update stats is turned off because the synchronous stats updates are painful, or if the default sampling that is used by them is insufficient. If you're looking to avoid one or the other, you may want to think about some c...

transcript context

Erik Reasonable Rates Darling

everyone has seen my shame already

this is a great way of simplifying something i usually have a lousy time explaining to people. i hate to have to credit mongodb though.

The ESR (Equality, Sort, Range) Rule

why does top percent allow (9e) and normal top not

Zikato

14:28

Documentation

> The numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if you specify PERCENT. Otherwise, expression is converted to bigint.

@PaulWhite github.com/MicrosoftDocs/sql-docs/pull/10019/files

Paul White

@ErikReasonableRatesDarling TOP PERCENT expects a float. TOP requires a (big) integer (4u)

@Zikato Much better. I forgot how to make edit suggestions to Docs

Zikato

I don't blame you. It's GitHub

Paul White

I mean, I think there used to be an edit symbol or link on Docs pages

The GH stuff is a pain, but separate

And now I look for it, I see the edit pencil again

Erik Reasonable Rates Darling

oh

a question update

eager table spool

so it's a HP type thing

🤖 phase separators activate 🤖

you might even say there's

> Something odd about the Sort

Paul White

14:48

user image

Erik Reasonable Rates Darling

i wonder what paul is trying to tell me

does anyone have a meme decipherer

Paul White

Have you tried Thinking Like The Engine™

Take the left fork to find the correct answer
Take the right fork to go down the HP rabbit hole (on a select!)

Sean Gallardy

It's my turn to pedal the bike that keeps Azure alive today

2

This must be what Hell is like

Paul White

Is it a bit like babysitting Replication

Erik Reasonable Rates Darling

if you're getting a plan with either a redundant sort or an eager table spool it is a HP type thing though not explicitly HP. i realize we're not worried about seeing the same rows twice, but it is somewhat odd to see the estimated rows without row goal attribute on the sort and spool

Paul White

14:58

Ok

As usual, you are not 100% wrong

Erik Reasonable Rates Darling

so i'm guessing it's doing something to "preserve" the row count to meet the goal of the top percent

Paul White

You're very close

Erik Reasonable Rates Darling

well i did have a banana for breakfast

Paul White

Oct 21, 2024 at 12:37, by Paul White

user image

Zikato

ok, which madman is actually pinging the Erik health czech?

Sean Gallardy

15:02

How's that beta decay working for you?

Paul White

@Zikato Hello

@ErikReasonableRatesDarling If that bothers you, add OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'))

Sean Gallardy

// No comment

Paul White

You'll never be a real SQL writer

-- No comment
/* No comment */

Sean Gallardy

fake it till I make it

Zikato

Valid in KQL though

Sean Gallardy

15:06

I always use OPTION (USE HINT ('RUN_LIKE_PAUL'))

Paul White

@Zikato Please see my previous remark

Erik Reasonable Rates Darling

@PaulWhite it doesn't bother me necessarily, it just seems like a clue maybe

Zikato

@PaulWhite technically he's correct as that is no comment

Paul White

@ErikReasonableRatesDarling Or enable TF 11029 (global only) to keep the row goaling behaviour, but remove the attribute from showplan

@Zikato Yes, but the essential irony is lost

Erik Reasonable Rates Darling

there's probably some invisible compute scalar that's only visible 87 debugger breakpoints deep that shows a count/sum arrangement

Paul White

15:11

@SeanGallardy Kids made fun of me at school for the way I ran. Didn't stop me being the fastest sprinter though

Erik Reasonable Rates Darling

or starting that shrimp boat empire

Zikato

@PaulWhite like Emil Zátopek

Paul White

Cat on the keyboard again?

Zikato

> People laughed at Emil Zátopek's running style because it looked extremely unorthodox and awkward. He had a grimacing face, flailing arms, and a jerky, uncoordinated motion, which made it seem like he was in constant agony. Some even joked that he looked like he was "wrestling with an invisible enemy."

Despite his unconventional style, Zátopek was one of the greatest long-distance runners of all time. He won four Olympic gold medals, including an unprecedented triple victory in the 1952 Helsinki Olympics (5,000m, 10,000m, and marathon—all in the same Games). His awkward form masked an in…

Sean Gallardy

@PaulWhite Always nice to get the last laugh from the top podium

Paul White

15:20

The funny thing is, I wasn't even especially fast, objectively speaking.
I guess that just means the others were *slow*. which does make sense in retrospect

Erik Reasonable Rates Darling

user image

Paul White

@ErikReasonableRatesDarling Do you often see count or sum in a compute scalar?

Erik Reasonable Rates Darling

on average? no.

Paul White

I heard you had 25/20 vision

Erik Reasonable Rates Darling

division was never my strong point

i'm a uniter paul

Paul White

15:23

@ErikReasonableRatesDarling So, you have seen one calculate an average based on a sum and count performed elsewhere...?

Sean Gallardy

@ErikReasonableRatesDarling Updated

Paul White

36.2k 3 47 87

sql-server availability-groups clustering sql-server-2016 sql-server-2019 sql-server-2014 sql-server-2012

Chat profile refreshed as a service

Erik Reasonable Rates Darling

@PaulWhite

ScalarOperator ScalarString="CASE WHEN [globalagg1003]=(0) THEN NULL ELSE [globalagg1005]/CONVERT_IMPLICIT(int,[globalagg1003],0) END

Paul White

@ErikReasonableRatesDarling Exactly

But the CS itself doesn't perform the aggregate

Erik Reasonable Rates Darling

nope

but coming back to a recent conversation

SELECT
    AVG(p.Score)
FROM dbo.Posts AS p
GROUP BY
GROUPING SETS
(
    (),
    ()
);

that do get spools

[sic]

Paul White

15:31

Indeed, but there's still a stream (or hash) agg to perform the sum/count

The spool just saves and replays those values

I had a sudden urge to eat toast

SELECT DISTINCT
    AVG(p.Score)
FROM dbo.Posts AS p
GROUP BY
GROUPING SETS
(
    (),
    ()
);

That's still funny to me

Erik Reasonable Rates Darling

yes, i admit i'm a bit stuck on the lack of an explicit operator that shows any maths, and the plan xml doesn't seem particularly helpful

which is why i have a horrible feeling about some invisible element like prefetch

???

Paul White

There's no invisible OP

I wonder when we'll get an AI powered toaster that can actually stop toasting when the required even shade has been achieved

Erik Reasonable Rates Darling

okay well there's something happening at some point that's not expressed in the plan

Paul White

I think the question to ask oneself is, how does this plan work?

In principle

Erik Reasonable Rates Darling

the top says "i have a row goal" and then says "hey sort, how many rows did you get?" and the sort is like "100" and then the top is like "gimme the first 9e percent" and the sort is like "okay that's just 9"

but i realize it's pipelined/cursory so that probably happens 9 times

you'll have to forgive my lack of enthusiasm for typing that nine times

Paul White

15:48

There you go

So, why is there a sort or a spool in the plan?

Erik Reasonable Rates Darling

to stop all the rows from showing up at once

Paul White

No, rows only ever move one at a time (outside batch mode)

Erik Reasonable Rates Darling

yeah but without a blocking operator that would just continue

the top needs some cache to ask for things from until it's happy

Paul White

Why does it need that 'cache' though?

Or, to ask the same question a slightly different way, "that would just continue" ... until what?

Erik Reasonable Rates Darling

one reason might be that the number of rows after the read might change if it weren't there

it provides stability so the plan doesn't run forever if you were to keep inserting rows

Paul White

15:56

you're so close to getting this it's slightly maddening 😆

Erik Reasonable Rates Darling

typical e-core

Paul White

Let's say (though it's not important) that there is zero concurrent activity of any kind

Just your query running in glorious total isolation

You said, the top is like "gimme the first 9e percent" and the sort is like "okay that's just 9"

Erik Reasonable Rates Darling

i do have some regrets about the wording

Paul White

There's clearly some mathing going on isn't there.

Erik Reasonable Rates Darling

yes

Paul White

16:03

What's the basis of that mathing?

Erik Reasonable Rates Darling

it may be something more like "i need the top 9 percent, how many rows is that?" and the sort is like "i got 100 from the scan, so that's 9. lemme give those to you in order."

Paul White

There you have it

(details aside)

So, what's the crucial function of the sort or spool in that plan?

Erik Reasonable Rates Darling

the rows don't need to leave the scan in order, they just need to get to the top in order, and they need to be limited to the 9 percent requested

Paul White

correct

Erik Reasonable Rates Darling

look there's a reason my rates are reasonable

3

Paul White

16:04

55 secs ago, by Paul White

So, what's the crucial function of the sort or spool in that plan?

(the unimportant detail is that the mathing happens at the Top)

Erik Reasonable Rates Darling

to receive all the rows from the scan, figure out what the total number of rows is, and feed them in order to the top until the percent is happy

Paul White

to count the rows yes!

Erik Reasonable Rates Darling

didn't i say that already

Paul White

No, you danced all around it

You knew the answer, I just had to get you to express it

The rest follows easily after that one key observation

e.g. Why is the index scan unordered in the sort case, but ordered for the spool?

Erik Reasonable Rates Darling

only eager index spools order data

table spools are heapy

Paul White

16:11

Sort of. Table spools can preserve order though.

Erik Reasonable Rates Darling

yeah like exchanges

but they don't do the ordering

i am unaware of an eager table spool property that shows it preserving order though

unless it's just implicit when the child seek or scan is ordered

Paul White

People would explain the same thing differently here, but I would probably start with the Top. All it knows is that it needs to return 9% of the potential full result to its client. The immediate question is, "9% of what, exactly?"

How dare chat maintenance interrupt

Erik Reasonable Rates Darling

top is quite the economist

Paul White

Except it's accurate

Erik Reasonable Rates Darling

something something cost model

Paul White

16:20

There are several ways the Top could get the row count so it can do its mathing, but whichever way it did it, something would have to 'see' all the rows. You just can't count them otherwise. We can't use a normal aggregate because that would destroy the stream. You wouldn't have the individual rows any more, just the count. I suppose you could do a COUNT_BIG(*) OVER () or something.

@ErikReasonableRatesDarling Yes, a sort costs cheaper than a spool up to a certain size

Erik Reasonable Rates Darling

okay

i am going to write my e-core answer

pls refrain from dunking for a moment

Paul White

Thank you

publishes draft answer

Erik Reasonable Rates Darling

16:40

okay i'm done

dunk away

Paul White

1

A: Unnecessary sort with TOP PERCENT?

the e-core answer I will eventually be Dunked On™️ by the gentleman from New Zealand (or perhaps a gentleman from Rugby), but I won't let that stop me from spinning at top e-core speed while the p-cores are busy making toast and tea. sort v. spool Sorts and Eager Table Spools both act as blocking...

Erik Reasonable Rates Darling

how dare you mark that as the answer

Paul White

will there be a revolution

Erik Reasonable Rates Darling

there will be a resolution at minimum

> Became Hot Network Question occurred 4 mins ago

Paul White

16:57

the power of an answer

Sean Gallardy

user image

Paul White

🤣

Erik Reasonable Rates Darling

17:13

@SeanGallardy just once ?

Paul White

I earned a Nice Question badge
Now I need to find space to hang it up

Sean Gallardy

@ErikReasonableRatesDarling Going to hit up the nursing homes for more votes

Erik Reasonable Rates Darling

@SeanGallardy don't forget the graveyards

Paul White

I hear the 150+ year olds are also a good source

Sean Gallardy

Absolutely, like I live in Philly!

Paul White

17:19

Transcript tidied

Sean Gallardy

YTB

Erik Reasonable Rates Darling

youth tiger balm?

Paul White

yachts? they're boats

you tidy bastard

yes that's better

yellow tied bibs

yanks think bestest

3

Sean Gallardy

You're The Best

Erik Reasonable Rates Darling

17:35

that doesn't make any sense

Paul White

looks wrong

Erik Reasonable Rates Darling

yarbles testes bollocks

Sean Gallardy

Stop giving out my password

Paul White

Currently the 7th hottest Q & A on the network

Erik Reasonable Rates Darling

i have socialed it

Paul White

17:44

influence++;

Erik Reasonable Rates Darling

we shall see!

Paul White

especially well regarded in the mongo world

Erik Reasonable Rates Darling

i've never met a mongo

Paul White

so this seems to have been a gr8 day

I might just leave it there

Though I am tempted to move the Q & A to meta

Or delete Erik's answer and substitute my own

Erik Reasonable Rates Darling

anything is possible when you've got diamonds to spare

Paul White

17:49

I just think we're a dash of tyranny away from a perfect ending to the day

Erik Reasonable Rates Darling

it would be especially cruel if you waited for it to get one more upvote

Paul White

Enlightened is awarded pretty quickly

I'd have to act fast

Or unaccept

Erik Reasonable Rates Darling

ha ha

well it would also give me my first 200 on the day in quite a while

one step closer to whatever that aaron-only badge is

or maybe ronaldo also has it

i forget

Paul White

Legendary - still only Aaron

I suppose I could cast a spam flag on it and cost you 100 rep

Erik Reasonable Rates Darling

ouch my face

oh not to distract you from your dastardly plans but is there any attribute in the plan xml that tells you if a trigger plan is one or multi row

Paul White

17:53

In the plan xml? No, I don't think so

There's a clue in the estimated number of rows read from the pseudo tables though

Erik Reasonable Rates Darling

yeah

probably good enough

Paul White

not exactly handy

unless you're the sort of weirdo that enjoys XQuery

Erik Reasonable Rates Darling

i do have that going for me

but it would only be worth pursuing if there were an attribute or smth and not looking at estimates

Paul White

sounds like you don't truly love xquery

Erik Reasonable Rates Darling

i was hoping for dm_exec_plan_attributes since it has a bunch of cursor stuff in it

Paul White

17:58

there is a trigger 1 row cache key but you're looking in plan xml?

Erik Reasonable Rates Darling

that would be good enough

Paul White

it's part of the SET options flags

> TriggerOneRow

docs.microsoft.com/en-us/sql/relational-databases/…

Why does that page claim not to exist for SQL Server, only Azure SQL Database?

Erik Reasonable Rates Darling

> UPON

Indicates that the database option PARAMETERIZATION was set to FORCED when the plan was compiled.

well-named that

Paul White

Universal Parameterization

Erik Reasonable Rates Darling

even more well-named that

Paul White

18:03

Everything in SQL Server has at least three names

Curiously, being simple parameterized is also a cache key but it's hidden away in Status flags

Erik Reasonable Rates Darling

you mean auto-parameterized

Paul White

I do

> APON

> SPON

Erik Reasonable Rates Darling

it's funny what is and what is not surfaced directly in query store

Paul White

It does have parameterization type at least

Erik Reasonable Rates Darling

this network question isn't hot enough

Paul White

18:13

Not really ideal clickbait

Also, the network is dying a slow death

Erik Reasonable Rates Darling

aren't we all

Paul White

I should have titled it Crazy SQL Server Optimizer Bug Caused My Divorce

Or...One Weird Trick to get on HNQ

Wouldn't it be nice if COUNT_BIG(*) OVER () could get the total row count like that

Erik Reasonable Rates Darling

i have my reservations about count over

but sure

Zikato

18:56

@PaulWhite Then you'll see, that it is not the SPON that bends, it is only yourself.

@PaulWhite Disqualified for being an employee

5 hours later…

Charlieface

23:58

Is PasteThePlan down?

« first day (5159 days earlier) ← previous day next day → last day (5 days later) »

Transcript for

Feb18

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