« first day (2572 days earlier)      last day (2516 days later) » 

00:03
Real DBA's use TF 4000
anyone want to buy some call stacks
are they interesting?
I think so
did you resolve the callstack or is it still hex?
of course I resolved it
you act like I'm uncivilized
no idea where you got that idea!
well....... there was that one time...
Is the first one free?
they're free if you tell me what they mean
I make -5 promises
well
I got nothing to lose
00:16
so obviously this stems from batch processing with what looks like a hash join
although it makes me wonder how much memory and how many numa nodes this server has...
I don't think you're setting large pages
Looks like it's attempting to alloc some memory from a numa node, however it seems that the numa node is OOM
Server has 16 NUMA nodes, 20 DOP, and 256 megs of ram.
and it looks like there was a bunch of suspended processes
@EvanCarroll LOL SQL Server wouldn't startup on 256 megs of ram
2
Bloatware crap, I know.
=)
Postgres would
it'd start up on 640 k
Saved you some typing ;)
bwahaha, you're my favorite Microsoft drone EVER! high five
00:25
@EvanCarroll /HighFives
PostgreSQL also has better windows support.
you should mention that too.
Does SQL Server 2016 run on Windows 2000?
PostgreSQL 10 does!
Like Oracle Windows support?
@EvanCarroll It does not...
Yes, it does, afaik.
00:26
If anyone wanted to run anything on Windows 2000, I'd assume they were either gov't or healthcare... and quickly tell them no... then find a rule and smack their knuckles.
brb
Scratch that, PostgreSQL works on NT4.
BOO YA.
00:50
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
00:56
@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
00:59
@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
01:01
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
01:03
@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.
=)
01:06
@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
01:07
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
yeah, there are some advantages to learning all of this stuff over the past two weeks
01:08
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?
01:10
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
01:12
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
01:16
sure I was, but I don't have 10 TB of memory
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?
01:17
@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
01:19
damn
LMFAO
I'm still laughing
@JoeObbish Let's see what it looks like, now
🔥
@PaulWhite you did it wrong
🔥🔥🔥
Just lazy
01:21
@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?
01:22
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?
01:24
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
01:25
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
01:27
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
01:29
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.
01:34
@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
01:36
@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
01:40
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?
01:42
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...
01:43
@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?
01:47
@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
01:48
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?
01:50
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.
01:54
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.
01:57
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
01:57
😒
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?
01:59
I smell like I'm on my 4th serving of Willett rye

« first day (2572 days earlier)      last day (2516 days later) »