The Heap™ – Consultancy ©® - 2018-01-19 (page 1 of 4)

Scratch that, PostgreSQL works on NT4.

BOO YA.

wiki.postgresql.org/wiki/…

12:50 AM

I wouldn't call that a good thing

That says to me, "Hey, we haven't updated our codebase for any new API calls that are faster and more efficient! We're just using the same old stuff we did in 1996."

@SeanGallardy-Microsoft this week I worked with a code base written before DATEADD was a function and also not updated since then

@sp_BlitzErik Was it open source ;) lol j/k... but was the vendor still in business?

@SeanGallardy-Microsoft lots of concurrent hash builds from CCIs happening during this time

server has 4 physical numa nodes, 900 GB total memory

@SeanGallardy-Microsoft setting large pages removes this bottleneck entirely but we're using columnstore so we can't

@SeanGallardy-Microsoft whoa, that's new

out of memory as in the numa node within sql server doesn't have enough memory allocated to it for the request?

@JoeObbish is this still a VM?

affirmative

12:56 AM

@SeanGallardy-Microsoft Nothing another preprocessor macro can't fix.

@EvanCarroll /shakes head and walks away crying

#IFDEF REALLY_REALLY_OLD_NT4

gotta nest that 5 levels deep though

What version of SQL Server runs on MIPS?

QED.

#DEFINE MAKEAPICALLS(OSVER, API)##PGVER##STYLE(ADDSTYLECHAR)##TYPE

12:59 AM

@JoeObbish I know this is the kind of question you hate, but is the VM NUMA aware and how closely is the guest lined up to the host?

@EvanCarroll That's like asking what color of orange you'd like your strawberry shake

@SeanGallardy-Microsoft tricycle

@sp_BlitzErik Exactly.

"But it doesn't run on my IPAQ PDA" <--- not our target audience

Personally I think you should stop making standard edition, it attracts unserious people

LMFAO

1:01 AM

express???

Maybe just charge $5k a core for everyone

I will make a new edition called, "Heroin" edition

Microsoft SQL Server 2018 will have the --training-wheels-off flag.

ambiguity intended

@EvanCarroll we'll never remove that

@JoeObbish express is a gateway drug from mysql

1:03 AM

@JoeObbish I had a question asked to me.. "It says Express edition can't be a distributor, but the customer doesn't want to pay for standard edition - what can we do?"

Look you've got insert statements, and select statements and a cool setting with the term "parallelism", but that is totally undefined everywhere and everyone uses. You're a DBA!!!

Stored Procedures since forever... boom!

And that Postgres turd 🎸

@SeanGallardy-Microsoft Yea, well we'll have them too soon and then we can talk about how running functions outside of transactions is a feature or whatever.

=)

lol

1:06 AM

@SeanGallardy-Microsoft be nice, they just figured out partitioning

@SeanGallardy-Microsoft there are database platforms without stored procedures?

I had a joke about open source and making changes and compiling, stored procedures might take that away... but I don't have the energy to JEAGL it so... consider yourself both burned and amused.

@sp_BlitzErik I don't hate these types of questions

I'm just dumb

the VM reports 4 sockets in task manager and 4 numa nodes in resource monitor

@JoeObbish Just the crappy ones ;)

everything looks correct and lines up within sql server

the host is a 4 X 28 and cores per socket is set to 24

so everything is lined up

1:07 AM

it'll soft numa you at that point

confirmed there are no other guests

@SeanGallardy-Microsoft auto soft numa is off most of the time

You have the TF in the startup params?

no, there's an ALTER SERVER CONFIGURATION for it now

SET SOFTNUMA = ON/OFF

I'm so old

yeah, there are some advantages to learning all of this stuff over the past two weeks

1:08 AM

joke about trace flags went over everyone's head

@ypercubeᵀᴹ Of course

@SeanGallardy-Microsoft which one?

the TF in startup comment I made

following up on you all asking about TF's 1 or 2 days ago

omg JEAGLE

I still don't get it

you need to explain at even greater lengths

maybe think about it like this

I see you trolling... but no boat?

1:10 AM

if you just tell us what all of the trace flags do then we won't bother you any more about it

I told you, use TF4000

never heard of that one

because it's a secret

is that a serious suggestion? remember I'm the guy who would push a "this button will blow up your server" button

Hawaii doesn't forget

No, it wasn't serious

1:12 AM

if you keep up the phony trace flags you might fall to #2 PFE

fair warning

LOL

It's a real TF...

It's not in the retail build, though.

on a serious note, what's your DBCC MEMORYSTATUS look like?

so there's no harm in telling us what it does then

checkmate

@JoeObbish It like what you did to Hawaii, but in SQL Server.

oh yeah

that reminds me

when is that TF to disable all transaction logging going to start working again?

@JoeObbish No TF needed, use Hekaton with SCHEMA_ONLY

0 logging done

Weren't expecting THAT!

/drops server, walks away

1:16 AM

sure I was, but I don't have 10 TB of memory

You have 1...

less than that even

which would be the loneliest number - but compared to me it isn't

self-burn

and I have to reserve like 150 GB for the OS

because sql server will go over max server memory whenever the hell it feels like it

eh???

@JoeObbish Did you get it peanut butter and chocalte and tell it how much you love and miss it?

1:17 AM

@SeanGallardy-Microsoft I'm going to give it a beating with a baseball bat

you have your methods, I have mine

I don't think SQL Server is into BDSM...

@SeanGallardy-Microsoft Long and impenetrable as always

In other news, new batch of jerky in the oven

@PaulWhite LOL, good wordplay

@SeanGallardy-Microsoft oh, I missed this

uh, when would you want it? during the bad part of the workload?

Postgres doesn't run on Windows. It peaks at a fast walk.

10

1:19 AM

damn

LMFAO

I'm still laughing

@JoeObbish Let's see what it looks like, now

🔥

(sic)

@PaulWhite you did it wrong

🔥🔥🔥

Just lazy

1:21 AM

@SeanGallardy-Microsoft I asked for it. might take a while to get

I can't actually run T-SQL on the server. I need to ask someone to run all commands for it

I'm just interested to see how much foreign memory and whatnot you have

oh, it varies

usually not a lot

@JoeObbish Ahhh, you're management

< 1 GB per node most of the time?

we've looked at that quite a bit

what about away?

1:22 AM

Would it be fair to say that heavy memory allocation and VMs don't make natural bedfellows?

Due to drivers and whatnot

@PaulWhite I hope so

@PaulWhite It's more like... there are so many layers of indirection that is makes wrorkloads hate life

SQL Server is trying to pick a good layout for cpu and memory based off what the os tells it

That's the general vibe I was aiming for

the os is being told what that layout is by the hypervisor

the hypervisor then proceeds to do whatever the EF it wants

@SeanGallardy-Microsoft define layout? processor groups, numa nodes, etc? or something else?

1:24 AM

so SQL server says, windows please allocate memry on node 3, then windows does it and the hypervisor puts it on node 1

K-groups (processor groups) belong to the same numa node

then the hypervisor has to load all the stacks when it changes from cpu3 to cpu17

yeah but the numa nodes can move around, or something

now that local memory is foreign memory

Time to upgrade to CosmosDB

would TF 834 prevent that?

@JoeObbish Not using a hypervisor would prevent that

There is 0 SQL Server can do

when you're using VM

1:25 AM

let me elaborate

we don't see the issue with TF 834

because of the flat memory model

I'm open to the idea that the VM is causing problems

go on

@JoeObbish You can't be running the same workload with large pages

which makes me think it's a vm config issue

That's my feeling too

1:27 AM

I think the conversation is starting to go above my head

if there's something that I can check for then I will check for it

@JoeObbish Seriously, being 100000% not an a-hole right now, requisition bare metal and run your workload, see what happens

Aug 29 '17 at 13:36, by Joe Obbish

Don't take it personally. Paul tells me to hire a professional all the time.

I'm not saying it'll magically be better, but it'll take all the hypervisor crap out of it

if it runs great... you have your answer. If it runs crappy, it's probably not a vm config

Clippy: It sounds like you're trying to allocate some memory

LMFAO

@PaulWhite is on a roll tonight

1:29 AM

the memory bottlenecks can be inconsistent

one run can be much faster than the other

feels like there's some kind of memory layout within SQL Server that mitigates the issue

I'm sure configuring a VM for a high-end workload is extremely interesting

but we haven't been able to identify it, and even if we would, I doubt we could force it

does that fit with what you're saying?

@PaulWhite I'm not being snarky, but how many professionals do you know that can scale a columnstore ETL up to 96 cores?

I mean a VM tuning/configuration expert

@JoeObbish I'm assuming (possibly incorrectly) you've tried TF8015?

You can be as snarky as you like.

1:34 AM

@SeanGallardy-Microsoft if that's the one that I think it is it doesn't work over 64 cores

@PaulWhite oh. well, that's another story...

@SeanGallardy-Microsoft is this what you wanted?

Foreign Committed 4
Foreign Committed 93504
Foreign Committed 2028
Foreign Committed 37768

hat about "away"

yeah that's not alot

16 KB total

could just be at the hypervisor level then

the amount that we get for foreign varies during the workload, but usually stays pretty low

@JoeObbish It causes SQL Server to not use numa

1:36 AM

@SeanGallardy-Microsoft yeah, I'm pretty sure that one doesn't support over 64 cores

I don't have any servers with >64 cores to test, but I don't see that it checks anything in the code... could be though I'm just glancing at it

we used to recommend that one for some of our customers

So there is 839 and 8015

839 should treat it as a single large node

839 hasn't come up, I'll ask about it

Large Node is my rap name

1:40 AM

thanks

Not saying it's going to work or work well, just spitballing

I mean, I'll gladly help if you send me a server and some DAS equipment.. I can get you a shipping address

perfmon has no counter for away memory

this is outrageous

it's unfair!

@SeanGallardy-Microsoft we've tried like 100 things that don't work

Do you have premier or an EA? Might be worth getting the CAT team involved...

Are you suggesting he hire a professional?

2

google didn't tell me what EA is

is premier support opening tickets?

1:42 AM

We have an entire team dedicated to this kind of stuff... I can get details if he's interested, I don't know what "hire" would mean if anything

@SeanGallardy-Microsoft regarding TF 8015 from a good source: "8015 will only give SQL Server access to a single processor group’s worth of CPUs"

EA is enterprise agreement

ok, so a single k-group

@SeanGallardy-Microsoft It's a long-running joke in here to "hire a professional". I guess it could mean anything involving an exchange of money for services.

which for you would be... 28 cores

could be 56

yeah, not what we want...

1:43 AM

@PaulWhite what if I don't want money.... but something else...

Well what you do in private...

going to try 839 tomorrow

columnstore memory isn't numa node aware anyway

(it's easy to type things without fully understanding what they mean)

I don't see anywhere that 8015 tests for >64 cores though, it might just default ot the first k-group form windows and call it a day

I believe that we've complained to MSFT about it before

something like the code is old and it wasn't updated? that's probably speculation

What's the resistance to bare metal here?

1:47 AM

@PaulWhite pregnancy

if you keep asking questions like that I'm going to cry

@SeanGallardy-Microsoft wat?

condom joke... bare metal...

@PaulWhite when two numa nodes love each other very much...

you've obviously been married way too long

1:48 AM

you should enable TF 839

@SeanGallardy-Microsoft Oh OK that's new to me. Maybe a US thing.

it's a euphemism

in florida they use tin foil for condoms

"when two numa nodes *get drunk and see each other across the room *..."

fixed that for you

@JoeObbish They don't know what condoms are

that's why STDs are rampant here

that and spring break

and sex in swamps?

1:50 AM

What gator's and @sp_BlitzErik do on their own time...

can you think of any other odd configuration options around memory? I thought we tried them all already but we missed 839

I don't care how unsupported it is

There is a traceflag that says, "Treat max memory as a soft limit"...

you using that one?

Seriously, lose the hypervisor and confirm you have an actual SQL Server problem to solve.

Or try it on current code (2017).

835

I would test on bare metal and see, tbh

@sp_BlitzErik I clicked on one of your weekly links and got a BSOD.

It was coincidence, but I still feel you ought to know.

1:54 AM

Send me the workload and I'll run it on my laptop bare metal

@PaulWhite not computer enough to handle my links

@sp_BlitzErik You don't know how right you are :)

Still haven't downloaded the SO database!

@SeanGallardy-Microsoft no, I don't think the community knows that one

@PaulWhite not the mark of a Professional®

we don't get close to max server memory during the workload

usually one numa node gets close to max server memory

@PaulWhite I would if I could

@JoeObbish that's super weird.

1:57 AM

how so

I thought it was expected

Something about this server stinks

that's horribly unbalanced

oh, apologies

Server Smells

usually one numa node gets close to max server TARGET memory

so max server memory / 4

@sp_BlitzErik you smell

1:57 AM

😒

that looks like a confession to me

@JoeObbish What's preventing it?

I can tell you in an email if you'd like to know

Remote debugging this issue really isn't going to work here you know.

@PaulWhite What makes you say that?