@JoeObbish so 250mb?

I've never seen this "mb" in a memory grant

you're a turd

I wonder if even such a demo is possible

seems like pretty much everything can spill to tempdb

I mean, if it didn't then the query would fail

today was a pretty wild day

did it end with some cutting edge baking?

I don't do cutting edge stuff

rarely bake on fridays as well

no one else to eat it

going to end with some CONNECT ITEMS

what could be more exciting?

plural

I'll answer that, nothing

you read that correctly

that other one should have gone out on the twitters -- did it end up with many more votes?

thanks, appreciate it

not really

the unwashed masses of twitter can't appreciate my connect items

@JoeObbish can't you just engineer a plan with an arbitrary number of sorts/hashes?

I think that I have... five? that I could write right now

@PaulWhite I don't think so. It could just spill to tempdb.

But if that's a hint to try harder then I will do so. Is it?

not if the memory fractions work out right

i bet if you nested derived tables with a top/sort combo that started big and ended up small

hm

not clear to me how memory fractions matter?

so like top 10,000,000 > 5,000,000 > 1,000,000 > 100,000 > 10,000 > 1000 > 100

@JoeObbish I thought you were looking for a plan with some large amount of serial required memory? There is a minimum per sort

I was good with fractions in school

your entire demo isn't clear to me

which one?

@PaulWhite Oh, I see

whatever you're asking about now

will try that

I want a SELECT query with a required serial memory of 250000 KB or so

@sp_BlitzErik heh it is a fun game

how can I clarify?

it would take me like 15 minutes to explain the context of this

Merge union alls might be a way to go

I don't recall if parallelism affects the minimum

You could also try messing with the minimum query memory option

I don't like my chances here

sorry, typo

how's that?

You have to make sure the sorts all run concurrently so memory grant isn't reused

2560 is pretty good

want the script?

but I need 100X that

Which is what Erik meant by looking at the memory fractions

hmm

It will no doubt be a large plan

I have no idea how to make them run concurrently

Don't chain them

No blocking operators between

you'll have to pardon the non-specificity of my demo. i made this table to work for something else. feel free to adjust the columns and rows inserted to get a bigger serial dingdong.

(believe it or not I'm asking to solve a production problem)

oh yeah, forgot to try different data types

Hence my suggestion of merge union

C'MON DBFIDDLE

(sorry jack for what i'm doing rn)

just send me a pastebin

@JoeObbish dbfiddle.uk/…

@JoeObbish on phone

i had to prove to a client that sql estimates memory grants for var columns by estimating the data will be half the size of the column length no matter what's in there

heh

that's why the table is clumsily named

@PaulWhite meant Erik

I'd never speak to you that rudely

that plan gives me a serial required memory of 0 KB

oh, I'm being stupid

OIC

> mfw

is the trick to put the hash joins between the sorts?

seems to make the fractions weirder, yeah

whether it gets you what you need is another matter

i think it's a good start though, no?

@sp_BlitzErik you live in NYC

I bet I seem nice to you

everyone here thinks I'm mean

it's appreciated, yes

I suspect I'd get some kind of error if I tried to get up to 250000 KB

i'm a bad gauge of these things. people seem to think i'm being a dick no matter how nice i'm actually being.

blame the 'spergers, i guess

I know what you mean. Makes you not even want to try, right?

If people think you're being a dick no matter what might as well have some fun with it

^^^ not something that a dick would say

Or blame the asparagus

you and your exotic fauna

Paul, what made you suggest the merge union alls? The fact that they'll run concurrently?

@JoeObbish Is the instance in question not able to grant that much?

@PaulWhite it is, but isn't having a thousand joins in a query generally a bad thing?

@JoeObbish Just the first thing that came to mind

@JoeObbish I would say so yes. But I thought this was for a demo

is there a scientific way to do this? I assume that different operators have different memory minimums?

nope, it's to solve a production issue

Sort is 512kb if memory serves (ha!)

need that kind of creativity for the problem at hand!

Not sure about hash offhand

i suppose you could mess with the min memory per query at the instance level

@sp_BlitzErik doesn't help

don't even get me started on that

actually

it's too late

you might be thinking, why not use the MIN_GRANT_PERCENT hint?

after all

Nope

"The query is guaranteed to get MAX(required memory, min grant) because at least required memory is needed to start a query."

GUARANTEED it says

not guaranteed

never crossed my mind, no

@PaulWhite too disruptive

@PaulWhite such a trendsetter

Closes chat as duplicate

does anyone have a database full of stupid sql server facts? like the required memory grant per operator?

I'd love that

check the debugger

this email just ruined my night

@sp_BlitzErik I already am

SEE WHAT I DID THERE

Kimberley Tripp maybe

"previously signed up for"

yeah, no clue

you had some wild days on the internet, eh?

all of those 4chan references

so many open tabs

sometimes two irc clients

an aim name in another language

a bookmark with "w4r3z" in the title

boy howdy

hash join might be 1024 KB

Ha ha ha

!

snoozefest

who still uses irc?

Evan?

i don't know, i stopped being that sociable in 1999 or so

I don't know a person by that name

ok, good news

so far hash joins scales well

so I just need 250 hash joins in a query

brentozar.com/archive/2016/08/…

A post for all seasons

I wonder if 7470 affects the sort minimum

I do stupid shit like that all the time already

are we allowed to say that in here?

is that a van halen album

@JoeObbish apparently

You're still here

sometimes it takes a while to be punished for transgressions

And I see no flags, but then I wouldn't

you're also on mobile

This is true

i thought you were on the phone

wait does your house have dial up internet

HAHAHAHA

now if it didn't take 25 seconds to compile I'd be in business

@sp_BlitzErik dude

oi

@JoeObbish Is this a previously unstated requirement?

Shocked

@PaulWhite oh hush

"it depends"

my memory grant was bigger

@sp_BlitzErik no it wasn't

FORCE ORDER makes the compile faster, but then memory drops to almost nothing

-_-

it changes the memory fractions

@sp_BlitzErik how was it bigger?

Clinical fact: a lack of clarity is correlated with identicon usage

oh, required

@PaulWhite I mean what do you want

sure, it would be great if it executed in 0.1 seconds

but I didn't even know if it was possible at the time

@JoeObbish a quiet life, mostly

this is a "take what you can get and try to improve it" type of situation

i'm sure it will be fine once you get the plan in cache

@sp_BlitzErik it's part of another query

Said every consultant ever

and no, cache does not help

Try cash

your query probably clears it out

'~vsE908.xml' is too large to open with XML editor. The maximum file size is '10' MB.

although

I wonder how much of it is SSMS render time

you can increase that

yeah but I don't want to

query executes in 22 seconsd

Paul, does FORCE ORDER decrease compile time?

what about USE PLAN?

pay attention, this is as close as joe gets to being drunk

Sigh. Who users a chainsaw on an otherwise peaceful sunny afternoon

@JoeObbish yes

@JoeObbish no

I might have to use that weird join syntax that no one likes

Oh and now a weed whacker joins in

Chainsaw stopped. Net win

the bus got lost today

is that a legitimate complaint?

who lost it?

Did you speak to the driver?

joe was giving directions

I never give directions

E.g. try to explain where you wanted to go?

I get in the vehicle and it takes me where I need to go

how could I possibly give directions?

"we need to take the street, but it can't go that way"

@PaulWhite CUTE

Ta

@sp_BlitzErik heh

how does the weird () join syntax work again?

brentozar.com/archive/2015/05/forcing-join-order-without-hints

that one?

MY HERO

@sp_BlitzErik can you imagine meetings between Joe and Lonny?

@PaulWhite yes, joe is in here, lonny is on twitter, and they just check in verbally to voice their dismay every so often

"no one has heard of this"

😁

"adam machanic just blocked me"

It's like I'm actually there

it's funny how wrong you guys are

"that stupid diner that retweets everything i say with "hash" in it is the only response so far"

@JoeObbish we've made a career of it

and come on, "meetings"?

do we sound like people who schedule "meetings"?

anyway I'm lucky to work with Lonny

he's world class

Not that you'd both turn up on time for, no

You're both World Class

But he has a hat

And a face

could do without the hat

Every superhero needs a mask

I DID IT

254976 KB required

2 second run time

"yay"

good thing the optimizer is better at figuring out what you want than we are

both of you were helpful

I don't understand the problem

y'hear that?

anyway this is just a theory

need to try it now

if it works I'll tell you why I needed it

@sp_BlitzErik must be a typo

years from now we'll be excavated in a strange embrace like victims of pompei

A powerful image

noooooooooooooooooooooooooooooooooooooooooooooo

try a temp table

but shrink tempdb first

might be able to do it

There is insufficient system memory in resource pool 'default' to run this query

when running a kill

shutdown with nowait

just write some powershell

it'll be fine, eventually

got too greedy with memory

well with only 16gb

I don't have a hardware sugar daddy

oh yeah, my question

hate to ask this now... but it's topical

i'm here

so I have a desktop that I sometimes pretend is a server, right?

i'm following

I've noticed that when I run a load that should get to 100% CPU and stay there

CPU stays around 90% but falls pretty quickly to 40%

if I look at temps they increase to 100 C and stay there

it seems like my CPU gets throttled back due to the high temperatures

are you overclocking?

is that a reasonable theory?

@sp_BlitzErik no

yeah, you might need to put more paste on, or get more/better fans

sometimes there's a bios setting for that

with my stupid bling bling setup i can set temperature thresholds

did you ever talk to geoff about that his row goal connect item was that got marked as resolved?

i saw he commented on your post

no, we've only talked a few times through comments here or on the blog

also

it looks like the serial required thing actually works

and might be usable

did you just join to dbo.many()?

I actually can't find that code online anywhere anymore

I think that I spoke too soon

last test suggested that it didn't work

yeah it doesn't work

sigh

ah, maybe you need to partition it

So would you like to know what I'm trying to accomplish?

I suspect partitioning will help to a degree, but it isn't practical right now

according to my scotch you have several minutes left of me

go for it

I'm inserting into a CCI

I want to force it to not write to the delta store

that's it

WHY IS IT SO DIFFICULT?

blame it on the tuple mover

@JoeObbish Is this related to the memory grant thing and CPU temperature thing somehow?

@PaulWhite related to the memory grant, yes

temp no

when there's memory pressure on a server an insert into a CCI only waits 25 seconds for a memory grant

if it can't get one then it executes with a memory grant of 0 KB and writes to the delta store

even if you have 102400+ rows

I see.

the idea behind the required serial memory was to force SQL Server to give it that memory when it hits the 25 second limit

and it does, but it does not use it for the CCI build :(

See it didn't take 15 minutes to provide context

I was originally going to give a more detailed explanation

do you have any crazy ideas? I feel like it's impossible without some kind of TF

You can't run it in a dedicated workload group?

you mean run it in its own pool?

oh yeah, forgot about that one

no, unfortunately not practical for this. might have more than 64 queries for example

@JoeObbish Why is that important?

I believe there's a limit of 64 pools

So have one pool and serialize them.

Or a pool of x size and limit it to x concurrent.

the concurrency aspect of it is interesting. I know that I don't really understand anything about how memory is allocated

If you have finite resources, you can only do a finite amount of work at the same time.

we tried running 16 at once for example (on a server with more than enough memory) and 15/16 got a memory grant

we dropped it down to 15 queries and only 12/15 got a memory grant

well, yes

but it's frustrating that this follows its own set of rules. doesn't respect MIN_GRANT_PERCENT, timeouts, etc

my use case isn't typical. I'd rather have a query wait an hour for a memory grant than insert into a delta store (in this case)

@JoeObbish A workload group seems ideal then?

yeah, I get what you're saying

it's too bad that REQUEST_MAX_MEMORY_GRANT_PERCENT only accepts integers. still don't understand that

GROUP_MAX_REQUESTS might even be useful.

This is all just an idea, now I understand what you're trying to achieve.

right, I thought GROUP_MAX_REQUESTS was where you were going

BTW the behaviour of waiting to get desired memory, then proceeding with the minimum needed isn't special to columnstore.

I get what you're saying (there's another piece of this related to concurrent number of sessions that I don't want to talk about this), but who knows how the math works out

we tried MAX grant of 2% and it didn't work at all in the way we wanted it to

@PaulWhite right, but the 25 second rule is special?

I've even see people use application locks for this sort of thing.

@JoeObbish No idea.

Is normally a function of the query cost

it's definitely not the same as rowstore

I personally like application locks and feel that they're underused

right

MAX grant of 2% limited the memory of each query as expected but we couldn't get nearly 50 concurrent ones with memory grants

it was something like 20 or something

anyway, I think that I have what I need on this. in terms of knowing what needs to be done, as opposed to having what I want

RG calculations can be complex, but AFAIK they ultimately make sense

I'm probably misstating it, but Lonny has said that columnstore and rowstore memory can interact in weird ways

No that's true enough

At least for buffer pool

I got so much to blog about now

anyway too much excitement for me today. going to bed. thanks for the assist. I'll blog about my failure soon hopefully

k

...

> If your workload contain lot of ad-hoc batch then considering using “auto” capture mode as capturing detail for ad-hoc workload will not provide much benefit.

Does Brent Ozar spend any time here on weekends? Not that he needs to respond personally but I have questions about his post about Diff backups. Most of the issues seem mitigated by being aware of your backup strategies and testing your backups. I suggested that we try diffs to my other DBA and he basically barfed the internet at me for suggesting it. All backup strategies have pros and cons...

Are diffs that bad? Or is it more of a case of "Know what you are getting into". It would not be a big deal to make restore scripts at every backup so as to reduce the pain of pulling those together in a pinch. Since our backups are stored withing the network (another physical site) restore times are not that much of a concern. We only have 80gb worth of DB's anyway.

I'm new to DBA stuff but ticked off that the other DBA basically is calling me reckless. ( You guys cannot fix that)

@sp_BlitzErik I can't believe that you'd comment but not vote

I'm hurt

@JoeObbish I'm on a plane!

@PaulWhite yes, I still use IRC. I prefer it, actually.

planes are the worst

@JoeObbish they're okay when you're drinking.