The Heap™ – Consultancy ©®

8:44 AM

Morning

Andy K

9:07 AM

evening and morning

Colin 't Hart

10:24 AM

G'day

McNets

11:01 AM

It seems that everybody is on holiday.

Andy K

I'm here

hot2use

We're here

"Please try turning the coffee machine off and on again. Contact Service when the error is still present afterwards"

(y)

rebooting didn't help

Colin 't Hart

11:27 AM

@TomV Hehe Notice the wording: Not if, but "... when the error reappears" :-)

Boot it as admin!!

@McNets Safe mode!

@Colin'tHart yep

@Colin'tHart Exactly

I looked for a manual detailing "F187" but couldn't find one

McNets

@TomV maybe activating some trace flag...

11:33 AM

> F187
Steam boiler sensor damage

Doesn't sound like something a reboot would fix

Colin 't Hart

Right, but everyone hopes the problems just go away...

Maybe that's why they explicitly state "when" and not if

ErrorNumber > 140 ? "when" : "if";

11:52 AM

it's a when with the meaning of if ;)

Why would steam need boiling?

@sp_BlitzErik Yep saw that, sighed, and moved on.

12:17 PM

@PaulWhite cu4 better be a long apology

Have any of you seen something like that before? I only knew about the cases where estimated number of rows to be read was higher

tiny rows

@sp_BlitzErik I had a bit of a revelation yesterday. It suddenly struck me that there's also no documentation for showplan elements. Think how long we had a single number for memory grant without even a unit of measure. I guess I'm being unfair; they're just following tradition.

@JamesL Please see the documentation.

@JamesL Seriously: no I haven't. Can you repro?

@PaulWhite there's no documentation for a lot of stuff, which makes building tooling difficult. afaik deadlock xml isn't documented anywhere.

@sp_BlitzErik It's not even valid xml is it? But yes, good point.

There's also this change where the width of arrows now seems to follow pre-residual filter rows read rather than actual rows read. I can see why that would be useful, but it's confusing at first. Also an undocumented change, AFAICT.

12:24 PM

yet it has the audacity to call my syntax invalid

@PaulWhite since when?

@Lamak I think it happened with CU3.

well...that's good to know

Hugo reported it here as a bug, but I think it is intentional.

Maybe it'll be in a Tiger Team post soon.

@PaulWhite how bizarre that they just changed it....it could be a toggle, maybe

12:30 PM

i wrote four blog posts introducing stuff from cu3 yesterday, and the whole time i felt like i was way more prone to being wrong than usual

@sp_BlitzErik run out of booze?

Even if it is wrong, it's likely to be right in some near-future CU ;)

@PaulWhite Ha, OK. I thought your first response was serious and was wondering what documentation I'd missed...

@Lamak this isn't total amateur hour

I can't reproduce it yet, maybe later today.

12:31 PM

@JamesL We (I) have been ranting about the lack of details on these things for a day or two now.

@PaulWhite bob ward confirmed something for me that i was accidentally right about since 2012

@sp_BlitzErik Subject area?

Or I can just wait to read your post.

not blogging about that one i don't think

for standard editions, query workspace memory also comes from "above the ram"

meaning if you have the max for the buffer pool and then additional, it'll use the additional rather than steal it from the buffer pool

@sp_BlitzErik Really? So SE can use more memory than licensed, just not for BP?

yeah

12:36 PM

> Maximum memory for buffer pool per instance of SQL Server Database Engine

It does say that. How interesting.

support.microsoft.com/en-us/help/2663912/…

I think I might have known that at some point.

blogs.msdn.microsoft.com/sql_server_team/…

> The rest of the caches in the SQL Server memory (procedure cache, thread stack, backup buffers etc) consumes memory outside buffer pool memory.

Yes, it just surprised me that memory grant e.g. sort/hash/exchange is also separate.

> [Bob Ward] You would be correct. Query workspace memory does not come from bpool.

12:39 PM

This is why they don't document things. I wouldn't read it (or remember it) ha ha.

i forwarded it to you

just in case you forget

Then again, who uses Standard Edition anyway.

@sp_BlitzErik Thanks, received, reading.

dezso

Why are you bound to MongoDB? Why is using a relational product not good enough? — dezso 13 hours ago

@sp_BlitzErik Bob's so cool. I want to be Bob.

i would get a bob tattoo

12:42 PM

Cowboys would seem appropriate for you all somehow :)

@dezso "without making my schema a relational one like the following document: ..."

Glad to see @ypercubeᵀᴹ back. The troll thing never seemed right.

@PaulWhite I filled the gap you (mods) had created :)

JINE

(Joke Intentionally Not Explained)

remains in the dark

Which is fine, I miss about 60% of the references in here anyway

dezso

@ypercubeᵀᴹ well, if they have the MongoDB hammer only in hand...

12:52 PM

3

dezso

so very creative

tss

how do you skip mungo jerry

in favor of a mango

especially with your vowel shifting ways

I'll settle for being first, rather than best

really microsofting it, pal

2

Now reversioning my CU3 ^^^

12:57 PM

breaking news

xkcd: xkcd.com/1400

i recently watched the disaster artist

I should mark that image Community Wiki now

i thought we were starting a company

what a letdown

Will never show a profit

1:04 PM

well yeah it'd all go to paying off mungo jerry for use of his image

but the meetings would be fun

I believe they would

@JamesL What tool is that capture from?

kinda looks like the html plan viewer in solarwinds

1:25 PM

Can't the charged particles on solar winds interfere with delicate electronic equipment?

that's the main reason i suggest people use a different monitoring tool.

I hesitate to refer to bit flipping in public of course

i wouldn't want to see you reduced to persona muy non grata

crikey no

2:03 PM

@PaulWhite First mover advantage ...

@PaulWhite Very much so. A lot of folks would fly core memory in satellites if you could still buy it.

You can still buy it. Here's an ambitious fellow trying to flog a board for $1800 AUD on Ebay.

ebay.co.uk/itm/…

2:33 PM

@ConcernedOfTunbridgeWells That's amazing

@PaulWhite A for ambition, if nothing else.

Well it's amazing for the asking price as well, but I was actually referring to the piece of equipment. I wonder how much storage is there.

I guess about 1K, although I can't be arsed counting.

well parallel deadlocks sure do make a mess of things in the xml

Actually, I think about 4k bits - 0.5k.

2:44 PM

I vaguely recall people claiming magnetic memory is making a comeback

Or is about to. Probably that has happened many times.

@PaulWhite At one point bubble memory was all the rage, but it died a death.

I still have no idea how it works.

Yeah I'd need to go read Wikipedia or something again

Imagine creating hardware or software for satellites. Quite the emphasis on economy, accuracy, quality, and efficiency, I would think.

it compiles? ship it!

Well quite

i bet they unit test

2:51 PM

@PaulWhite Aerospace software is quite tedious to write. Having said that, they used to let @gbn write software for radars.

They even paid him to do it.

Another reason I very rarely fly these days :)

My arms get really tired.

Silliness detected

Never. I'm always dead serious.

Is there an emoji for fingers crossed behind your back?

There ought to be

Q: Columnstore Index, speeding up queries even if QEP is not using it?

3:11 PM

would it be worth converting comments to a cw answer?

0

I have an strange behaviour in my queries, probably I'm missing something... I have a customer with very long queries that are being pre-builded by the Analysis Tool they are using... I was trying to optimize these queries creating new indexes. I managed to do that but some of the queries starte...

@sp_BlitzErik personally I think it should be closed

i agree

but my votes are often criticized

you have a good theory

1m to 4s seems like a bit too much for batch mode gains

could be a combination of things

only smart thing i'll say today

@PaulWhite Good question. It's from Plan Explorer and that might be the issue. SSMS 17.4 shows both having an estimated count of 1.

3:15 PM

@JamesL Right so I assume a nested loop is involved, and PE is showing one multiplied for loop executions, and not the other?

@PaulWhite Yes, that's it -- Plan Explorer is multiplying the numbers of EstimateIO, EstimateCPU, EstimatedRowsRead on the inner table etc by the number of EstimateRows on the outer table to get these figures.

Wait no, I said that wrong.

Estimated Rows is being multiplied by Estimated Number of Executions, but Estimated Rows to be Read is not, right?

PE does the math to make it easier to compare estimated and actual plans.

Anyway now I know it's PE I can look into it.

I expect they just need to extend the pre-existing logic.

@PaulWhite Yes, that's correct.

I can send you the plan XML if that helps you with something.

Please don't spend time on it on my behalf though.

3:31 PM

@JamesL No it's fine thank you.

I need to assess the new row goal thing for PE as well.

i need to assess the new row goal thing for blitzcache

is important

5:22 PM

HALLO EVERY 1.

7:28 PM

The wanted results looks like a variation on UPSERT, except there is always an INSERT - plus UPDATEs when there is a conflict. — ypercubeᵀᴹ 2 hours ago

The Best Sound for MS-DOS Games - Roland MT-32

7:47 PM

►

damn that's cool. I should be born 100 years earlier so I could play with that..

and observe the death of the buggywhip industry

Max Vernon

8:12 PM

@EvanCarroll don't worry, you'll live to see the death of the "car you drive" industry.

Yea I know it's only a matter of time before we kill of the car totally by accident or austerity.

Max Vernon

economy

Q: What exactly is microcode and how does it differ from firmware?

that's what I said, "austerity"

When you can't afford to drive them, buy them, insure them, or park them because other workers will otherwise work without them.

I'm a bigger fan in the top-down approach to killing off vehicles, it's just a lot more costly because actually transforming civil society isn't free.

23

Insofar as terminology, what exactly is "microcode" and if it can be updated, how does it differ from firmware? This question is not a duplicate of this question (as far as I can tell) which I've also asked about modifying microcode. Here I'm strictly wanting to know how to use these terms prope...

terminology firmware cpu

9:05 PM

@sp_BlitzErik How about this quote: "The applications or services that hold your registry file may not function properly afterwards. No user action is required."

what in tarnation

i have nothing to say about this

@sp_BlitzErik what's the problem

does it never go below 2?

there's no problem

i just don't know what to say about it

"this might be good or bad"

but why the tarnation

"check to see if it's bad"

that was in response to your error message

9:18 PM

oh

oh I get it

i'm not that rude

you don't know what to warn against

some of these checks feel masturbatory

can you get estimated number of executions for that node? I think that you can

yeah of course

9:19 PM

you could calculate number of rows scanned as a worst case

and for queries with lots (????) of logical reads suggest trying the disable row goal use hint

well, i can get rewinds and rebinds

-_-

that's what you want

yeah

so you could make everything come together

if you wanted to

9:24 PM

your test queries aren't bad enough

heh

@_@

i linked to two of your blog posts in things being published this week

TWO

look how important you are

valued community member

visitors = 12

> mfw

they haven't been published yet, dingbat

oh

hey you even said that

how about that

@JoeObbish why doesn't your dba profile link to your blog?

9:33 PM

@ypercubeᵀᴹ it does!

10th and 12th

Oh, sorry. Yes, it does.

But you could add a more visible link!

I prefer to be mysterious

@JoeObbish do you have a bad row goal query that doesn't take 3.5 hours to complete?

You know, @JoeObbish, if you spent half as much time causing query issue and twice as much time making delicious jerky... you'd have 10 times as much jerky and 1/4 the performance issue ;)

5

9:36 PM

@sp_BlitzErik Do you want it to be a risky query that does okay or a bad query that takes too long?

#CommonCoreMath

@SeanGallardy-Microsoft this guy thinks that he has any idea of the issues that I can cause

this isn't even my final form

LOL

It's over 9000!

@JoeObbish i suppose whichever one would provide numbers that would warrant alerting users to

orderbyselectnull.com/2017/10/19/a-row-goal-request

"All 1000 rows will be returned to the client, so one billion rows will be read from the BIG_HEAP table. This is indeed what happens and the query takes around 2 minutes to complete on my machine."

that one might be good

they can take as long as you want though

just need to change the number of rows in the outer table

the only gotcha that comes to mind is that sometimes you can get a spool that ruins the demo

9:40 PM

pretend that i'm lazy because i've been working on scripts all day

is 2 minutes a good completion time?

yes

then CNTL+F for that

bingo

needs an order by

@JoeObbish You should really have a stern talking to those damn spools

9:43 PM

@sp_BlitzErik what does

everything

negative numbers

@SeanGallardy-Microsoft there are a handful of plan types that I still don't understand

check out this practical joker

the spool gets introduced but it seems like a really bizarre strategy

@sp_BlitzErik nice to see you reading my posts... sniff

SNIFF HE SAYS

like i don't send him typo corrections at 5am

fyi i can make that smaller

9:48 PM

nevermind I'm dumb

nevermind i'm also dumb

how about: if estimated_rows_without_goal - estimated_executions <= estimated_rows then it was a silly choice

i'll get this one day

i hate word problems

@sp_BlitzErik that doesn't make sense to me

10:03 PM

i would love to hear what would make sense to you

in the context of plan cache only, right?

yeah

I think about it in terms of risk

if you assume estimated executions is correct, what's the worst thing that can happen?

can you tie it to logical reads somehow?

as in, is this analysis only done for the worst X plans?

yes, but the plan cache can be sorted by reads

hmmm

I'll think about it

ask me again later

needs some AI imo

10:13 PM

okay, i'm just going to let people know if a row goal is happening for now

if you think of some analysis you think would be valuable, lemme know

open a github issue or something

10:48 PM

@sp_BlitzErik has some magic numbers.... but

do something if (logical_reads_in_table * 100 > 0.5 * total_query_logical_reads) and estimated_executions > 100

where does logical reads in table come from?

it's flawed in so many ways, but I don't know what you can do without context...

NOT FROM THE PLAN CACHE

well

stop microsofting me

data pages?

won't work for CCis

it's unavoidable

if a table is one page then I doubt you care about a row goal

by definition you don't

yeh

i'm just gonna leave it as informational until i have a better idea of what makes sense to warn about

11:03 PM

I JUST GAVE YOU THAT

it's unusable

why

because i'd have to pull the database, table, and schema out of the query for whichever ones are involved in the row goal and then dynamically query net new dmvs to get page or row counts

11:21 PM

someday when you write your own stored procedure, you'll understand ;)

11:53 PM

@sp_BlitzErik hope you are ok