The Heap™ – Consultancy ©®

sp_BlitzErik

14:07

SNEAKY

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

that wasn't in the original release notes

neither was this

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

Paul White

@sp_BlitzErik It is starting to drive me nuts

sp_BlitzErik

@PaulWhite makes you wonder what other secrets are in there

Paul White

14:36

@sp_BlitzErik The thing is, I knew this was coming, and checked for it. Disappointed when it wasn't there.

I'm talking about EstimateRowsWithoutRowGoal. cc: @JoeObbish

Can you get a plan with it in for SQL 17 CU3?

sp_BlitzErik

working on it

Paul White

Because it's not even in any of the showplan.xsd files I have

sp_BlitzErik

have you tried?

well then -_-

Paul White

Yes no luck so far

This is exactly the sort of amateurish update with no documentation that is starting to piss me off.

sp_BlitzErik

and here i was with my big mug of coffee getting ready to add it to blitzcache

it's enough to make a guy open a sandwich shop.

Paul White

14:40

damn right!

Pedro promised to blog about it, but hasn't AKAICT

But is that the release vehicle now?

sp_BlitzErik

blogs?

hahaha

Paul White

@sp_BlitzErik Found it in the plan cache

> <RelOp NodeId="4" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateRowsWithoutRowGoal="1204.82"

sp_BlitzErik

oh good, another trip to relop for me

@PaulWhite i have your and joe's row goals posts, what are some others you like?

wanna do a roundup when i blog about the new thing in the stuff

Paul White

14:58

@sp_BlitzErik I'm writing a new one

I was waiting for CU3

sp_BlitzErik

Sep 27 '17 at 13:12, by Paul White

At some point I'm going to have to face the fact I have already written everything I know.

Paul White

I'm guessing there will be an SSMS update required as well

sp_BlitzErik

ooh so it shows up in the xml but it's not exposed in the graphical plan

Paul White

@sp_BlitzErik Only in the cache, SSMS silently removes xml from the server it doesn't recognise

sp_BlitzErik

ssms is a idiot [sic]

Paul White

15:00

I'm sure the server sends it, so you could probably see it with a different tool

This should not be so hard

sp_BlitzErik

xml is hard

that's why query plans are moving to json

in cu3.5

(unannounced)

Paul White

ha

sp_BlitzErik

revisionist

Paul White

Incidentally, that's the first KB I've seen that mentions SET ROWCOUNT

I had that as a little nugget in my draft, which is less exciting now

sp_BlitzErik

isn't that on the verge of deprecation?

Paul White

15:04

Only for updates

Oh, on the verge, well who knows

And would it matter, deprecated stuff seems to be supported forever, if it's widely used enough

e.g. TOP x without parentheses

sp_BlitzErik

no

i suppose i was just curious what your angle was on it

Paul White

SET ROWCOUNT can be handy

It has some small differences from TOP/OFFSET that can be useful

sp_BlitzErik

you make my eyebrows raise at least once a day

i'd really love a switch in restore syntax to bump database compat level

Paul White

15:23

@sp_BlitzErik would that be really useful?

sp_BlitzErik

with the amount of stuff that's compat level dependent nowadays, yes

Paul White

@sp_BlitzErik I mean would it be a big improvement over updating it manually once the restore is complete?

A restore should restore the database to the state it was in when backed up

sp_BlitzErik

depends on the scale at which you're restoring databases

for one database, no

Paul White

I guess so

sp_BlitzErik

if you're doing a big migration, it would be nice to have the option rather than a separate script

Paul White

15:27

I bet it's all solved with PowerShell

heh

Have you looked at the UDF CPU & elapsed time thing yet?

I wonder if it aggregates all UDF invocations, at all levels

sp_BlitzErik

that's on my todo list

i'm trying to come up with workable row goal demos for bits

if you couldn't tell by my bottle and a half of wine email last night

Paul White

Oh is that what that was about

sp_BlitzErik

no, i thought you might like the exchange spills

Paul White

I did! I liked many aspects actually

Though scanning an index backward is a neater way to get non-parallel scans.
Not that you'd want to change the SO schema that much, I guess.

sp_BlitzErik

those things ran for 10 minutes a piece

Paul White

15:33

I can believe it

sp_BlitzErik

i was surprised that your trace flags didn't help

Paul White

@sp_BlitzErik my trace flags?

sp_BlitzErik

they're all yours

drop the farce

senior trace flag program manager paul white

Paul White

ha no seriously I am lacking context - which flag ought to have made a difference there?

sh Joe arrived

everyone act natural

sp_BlitzErik

they were trace flags you suggested when i hit a parallel deadlock, so i rolled with it

Joe Obbish

15:38

@PaulWhite I think I mentioned that a few weeks ago in chat

that XML information was only in the plan cache

it was available in previous versions too

sp_BlitzErik

of 2017, or 2016?

Joe Obbish

I believe I was testing on 2017 CU2

Paul White

Dec 16 '17 at 7:00, by Joe Obbish

@PaulWhite @sp_BlitzErik Either one of you notice EstimateRowsWithoutRowGoal="6.4516e+006" in the query plan cache yet?

sp_BlitzErik

ah

Paul White

Bound by NDA at that point

sp_BlitzErik

15:40

i've been lazy about 2017 testing lately

Joe Obbish

but yeah, it goes right to your point of poor documentation

Paul White

It's massively frustrating

Joe Obbish

if you're truly offended I can give you a TF to turn it off ;)

Paul White

@JoeObbish The feature switch for EstimateRowsWithoutRowGoal? Which is it?

sp_BlitzErik

USE HINT (‘DISABLE_OPTIMIZER_ROWGOAL’)

?

Joe Obbish

15:42

let it get it, it's on a different computer

Paul White

@sp_BlitzErik no

Joe Obbish

@PaulWhite 11029

I wish I was less lazy about blogging

I could have been one of the cool kids to blog about features before MSFT revealed them

Paul White

@JoeObbish ta

Joe Obbish

@PaulWhite Have you ever used UNION ALL to split a parallel heap up to get better row distribution on parallel threads?

sp_BlitzErik

15:58

i need to install better spyware on your computer

Joe Obbish

@sp_BlitzErik if you could write my blog posts for me that would be very helpful!

sp_BlitzErik

sure, just send me the scripts

and a blurb

@JoeObbish do you cook also or just bake?

Joe Obbish

@sp_BlitzErik I don't got a wife if that's what you mean

sp_BlitzErik

no, i was curious if you're also a professional chef

i suppose the row goal was to ask if you have an opinion on sous vide cooking

Joe Obbish

I'm not even a professional baker

I have a friend who does it sometimes

seems like an easy way to get good results

sp_BlitzErik

16:06

we just got one. i wish there were an easy way to send you a steak.

Joe Obbish

how do you finish them?

sp_BlitzErik

grill top

Joe Obbish

even now? that's dedication

I only cook on weekends, so at most it's just four meals

I haven't really tried to advance my skills there

Paul White

@JoeObbish what?

Joe Obbish

@PaulWhite well the other day you mentioned challenges of using heaps

I wonder if you'd ran into that one

let's say you have a heap with very skewed partitions. row counts are 1, 1, 1, 99999

if you run a MAXDOP 4 query that scans that heap you'll get 99999 rows on one thread

Paul White

16:12

oh so one union all query per partition?

probably

Joe Obbish

no, I'm scared to do that

feel like it might go badly if you have a lot of partitions

Paul White

ok so you're partitioning the union all query in some other way?

feel free to be explicit and precise

Joe Obbish

well... I plan to

but I need to create an algorithm to do it

the whole thing seems a bit ridiculous

if I were to ask a question, I suppose it would be do you know what the benefits of one partition per thread as an algorithm are?

Something to do with latch contention?

As in, why did MSFT change the way that they did it?

Paul White

@JoeObbish are you talking about technet.microsoft.com/en-us/library/…?

Joe Obbish

yes, sorry if that wasn't clear

sp_BlitzErik

16:21

> collocated

Paul White

@JoeObbish Well the pre-2008 implementation used an explicit apply model which ended up one thread per partition as a side effect. It was an easy implementation.

@sp_BlitzErik With two Ls

Rushed CUs always seem to lack symbols

sp_BlitzErik

debugging already?

Joe Obbish

hm

that's... regrettable

Paul White

@sp_BlitzErik I was curious about changes for Spectre, Meltdown etc.

Also waiting for SNAPSHOT_MATERIALIZATION

@Joe I think I've most often ended up with processing a single partition at a time, inside some looping construct.

sp_BlitzErik

ooh yeah

Paul White

16:30

One would think the ETL tools would have built-in support for looping over partitions

Sliding window maintenance etc.

Joe Obbish

@PaulWhite That's mostly what we do, although we try to process partitions concurrently

There's a scenario in which we combine partitions together though

Paul White

Sure, looping with some pool of workers

Joe Obbish

and process those as a unit

Paul White

Part of the reason I was messing with SSDT/SSIS the other day

sp_BlitzErik

would a worker table make sense for that?

Paul White

16:33

Certainly one implementation option

Joe Obbish

oh yeah, you'll be proud of me Erik

I successfully used XE to diagnose a problem yesterday

sp_BlitzErik

you've got one up on me :)

Joe Obbish

I even made it through all of the ridiculous typos in the event and field descriptions

Paul White

I successfully used XE to waste several hours of my time once. Does that count?

It produced xml

sp_BlitzErik

@JoeObbish you're a machine

Sean Gallardy - Microsoft

16:34

@PaulWhite Excellent, things are going as planned :evil laugh:

Paul White

I suspected

sp_BlitzErik

notice he doesn't comment on SNAPSHOT_MATERIALIZATION

nothing planned, apparently

Sean Gallardy - Microsoft

So I made 4 pounds of beef jerky last night, forgot I set an alarm and at 3 am I thought my place was burning down

Joe Obbish

@SeanGallardy-Microsoft I thought you work from home?

Paul White

Life is safer as a vegetarian

Sean Gallardy - Microsoft

16:36

@JoeObbish I work from wherever, sometimes from home but last year 40 weeks on the road

Joe Obbish

sounds like poor planning!

what do you use to make the jerky?

Sean Gallardy - Microsoft

@PaulWhite @sp_BlitzErik and I discussed last night, we eat vegetarians

Paul White

@SeanGallardy-Microsoft no that was vegans

Sean Gallardy - Microsoft

@JoeObbish 1) flesh from a dead cow. 2) an oven

@PaulWhite Their are #1 on the list, but vegetarians will do in a pinch

Joe Obbish

do you do it often? if so I would consider a dehydrator

that's what I use

has a timer so no ruined beef jerky or fires

Paul White

16:37

We lack B vitamins

Sean Gallardy - Microsoft

@JoeObbish I like the smokiness :)

@JoeObbish Preach, I was looking at some electric smokers for outdoor use...

Joe Obbish

try liquid smoke!

Sean Gallardy - Microsoft

@JoeObbish I have -500% luck with that

Joe Obbish

it really is such an odd product, right?

"liquid smoke"

Sean Gallardy - Microsoft

Like when I made my character I rolled +200% to being hit by vehicles and -500% to smoking meats

Paul White

16:38

Like smoked-flavour bacon vs. actually smoked bacon

sp_BlitzErik

salad eating homebody

Joe Obbish

@SeanGallardy-Microsoft "Florida Man starts huge wildfire due to beef jerky making error"

Sean Gallardy - Microsoft

Like "Dry Milk"

@JoeObbish Haha if you ever see that headline.. totally me :)

Send Jerky care packages

@PaulWhite It's bacon, I'm not complaining

Paul White

So dawn broke 15 minutes ago

sp_BlitzErik

you had it beat by years

Sean Gallardy - Microsoft

16:40

@PaulWhite Happy start of the new week!

Paul White

Smokers are v popular here. Flavoured wood chips.

@SeanGallardy-Microsoft Well it's 5:40am Sunday :)

Sean Gallardy - Microsoft

@PaulWhite Isn't sunday the start of the week?

Paul White

Monday

Sean Gallardy - Microsoft

or do you use those southern hemisphere calendars that start on Monday?

sp_BlitzErik

heathen

Joe Obbish

16:41

he's a foreigner

they don't understand weeks

Paul White

I have the power of correctness on my side

Joe Obbish

we should liberate them

Sean Gallardy - Microsoft

@PaulWhite Let me ask your wife about that...

Paul White

touche

Sean Gallardy - Microsoft

Also Re: parallelism, you're right I was thinking parallel branch operators

Paul White

16:42

No worries

I'm not a religious person, but isn't Sunday the traditional day of rest?

Sean Gallardy - Microsoft

and for Joe, there should be in the actual XML a <ThreadStat ..... > element

Paul White

Like everything created in 7 days and the last one was a rest day

Sean Gallardy - Microsoft

Depends on who you subscribe to

Tom V

To be fair the old continent usually considers Monday the first day of the week too

Paul White

It would seem odd to start the week on a rest day

sp_BlitzErik

16:43

@PaulWhite that's a terrible motivational poster

Paul White

@TomV They are blessed with the Power of Correctness

Sean Gallardy - Microsoft

"Hmmm, 6 days... that's too even. Let me just take a nap... OH CRAP IT'S DAY 7"

Joe Obbish

@SeanGallardy-Microsoft I know how to get the number of workers

I was asking for your preferred method because I thought it would be more convincing to you

Tom V

I was going to say something about sabbath and war but I won't

Sean Gallardy - Microsoft

@JoeObbish does you algorithm take into account undocumented workers?

Paul White

16:45

I guess my point is that a rest day usually comes after work is done, not before hand

Sean Gallardy - Microsoft

That was totally tongue in cheek btw

Joe Obbish

@SeanGallardy-Microsoft would you like a rant about how terrible parallel scans on partitioned heaps can be? :)

Sean Gallardy - Microsoft

@PaulWhite I thought Saturday the "sabbath" was the day of rest?

@JoeObbish Always!

Joe Obbish

@PaulWhite I rest early and often

Paul White

@SeanGallardy-Microsoft I'm no scholar but I think that is "lunch"

Sean Gallardy - Microsoft

16:46

LOL

Paul White

@JoeObbish It's the American way!

Sean Gallardy - Microsoft

you get lunch? Are you hiring?

Joe Obbish

@SeanGallardy-Microsoft it sucks

fix it

Sean Gallardy - Microsoft

@JoeObbish I'm totally underwhelmed by this rant.

sp_BlitzErik

@SeanGallardy-Microsoft hire joe and make him fix it

Sean Gallardy - Microsoft

16:47

I even made popcorn

kettlecorn to be exact

Joe Obbish

@SeanGallardy-Microsoft the problem is that you're too nice to rant to

Sean Gallardy - Microsoft

@sp_BlitzErik I like this idea :)

@JoeObbish Hahaha, awww shucks. thanks!

Joe Obbish

@PaulWhite in fact I'm resting right now

Sean Gallardy - Microsoft

Is talk of resting and not working in America considered pornographic?

or heretical?

@PaulWhite what are you doing up this early?

Paul White

@SeanGallardy-Microsoft Interacting with Americans in their own time zone

Sean Gallardy - Microsoft

16:50

@PaulWhite you have my deepest sympathies.

Paul White

Oh it's OK it's entirely my own choice

Sean Gallardy - Microsoft

It actually is lunch time here... just on Saturday

Paul White

I work when I feel inclined and sleep when I am tired

Sean Gallardy - Microsoft

@PaulWhite That'd be 10% work/90% sleep for me... I do like my ZZZZ's

Paul White

Sleep time is very unevenly partitioned for me

Nothing better than a good long sleep though

Sean Gallardy - Microsoft

16:52

Stop using SQL Server to partition it for you ;)

@sp_BlitzErik I am thinking chest today...

@JoeObbish My Jerky is kind of bland, just salt and pepper - I'm allergic to onion, garlic, and soy, among others...

sp_BlitzErik

8 hour chest day c'mon

Sean Gallardy - Microsoft

@sp_BlitzErik 5x5x5, gotta get strong

I'm just a weee tike

sp_BlitzErik

leave humanity behind

Joe Obbish

@SeanGallardy-Microsoft no wonder you need it to be smoky...

I wonder if anyone is allergic to smoke

Sean Gallardy - Microsoft

@JoeObbish My sister, actually :(

Joe Obbish

17:03

what do you guys eat for family meals? water and white rice?

Sean Gallardy - Microsoft

she's just smoke and tree nuts

I just recently developed mine, could everything my entire life

also rice is off the list for me.

Joe Obbish

wut

Sean Gallardy - Microsoft

Eggs and Milk, too

Joe Obbish

Sean Gallardy: ruining holiday meals since recently

Sean Gallardy - Microsoft

LMFAO

May I use that as my new slogan?

Joe Obbish

17:05

sure

Sean Gallardy - Microsoft

Updated ;)

Evan Carroll

18:50

HALLO MY PEOPLE.

McNets

19:05

Hi

Qualphey

19:25

Hey

Could anyone answer a few questions about SQL injection prevention

?

Qualphey

19:46

okay i asked the question

0

Q: Is it efficient to use plpgsql functions only to validate identifiers and values?

Will my queries be safe from SQL injections if I use plpgsql functions only to validate identifiers and values, instead of executing queries. Here are the example functions: CREATE OR REPLACE FUNCTION validate_identifier(identi TEXT) RETURNS TEXT AS $func$ BEGIN RETURN quote_ident(identi); ...

Paul White

21:12

@sp_BlitzErik Just heard from Pedro, UI support will be in the next SSMS release

Evan Carroll

@JoeObbish you still never got that answer from @SeanGallardy-Microsoft.

Just wanted to remind everyone

Maybe I'll answer it today. ;)

Paul White

yesterday, by Sean Gallardy - Microsoft

She should be back next week from here out of office, I'll put a reminder to bug her again

@EvanCarroll ^^^

sp_BlitzErik

@PaulWhite good morning to you too

Paul White

morning

Evan Carroll

ahh

sp_BlitzErik

21:19

that's good though. are they going to hire you to write less amateur documentation?

Paul White

@sp_BlitzErik No but there'll be several blog posts from the Tigger Team next week

sp_BlitzErik

we should start the sql eeyore team

Joe Obbish

@sp_BlitzErik am I invited?

sp_BlitzErik

only if you dress up as eeyore

Joe Obbish

but why

sp_BlitzErik

21:22

that's the uniform, ding dong

Joe Obbish

I've never seen Sunil or Pedro wearing a tiger outfit

sp_BlitzErik

a mostly parallel plan, to some, can look a lot like partly serial

Joe Obbish

is this a philosophical thing?

sp_BlitzErik

it's just a rip off of my favorite eeyore quote

a mostly sunny day, to some, can look a lot like partly grey

or something

Joe Obbish

I'm seeing blocking on a function definition

a function definition!

this is outrageous

sp_BlitzErik

21:25

lol

go on.

Joe Obbish

nevermind, I'm too dumb to read a lock graph

Paul White

@JoeObbish Sunil isn't actually a member of the Tiggers

Joe Obbish

What is he now?

oh man, there's a view involved

sp_BlitzErik

how gratifying

Joe Obbish

CXPACKET is for noobs

sp_BlitzErik

21:31

it's a jr dba wait

Evan Carroll

Why doesn't dba.stackexchange.com/users/322/frank-heikens have the gold PostgreSQL badge?

Paul White

@sp_BlitzErik Wait category: Other

Joe Obbish

wonder how many of my wait events are in other

sp_BlitzErik

@PaulWhite we'd love to have your contribution: github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/…

Paul White

I'm sure you would

sp_BlitzErik

21:38

:)

Joe Obbish

so sassy

sp_BlitzErik

> You must have a total score of 1000 in at least 200 non-community wiki answers to achieve this badge.

perhaps some of his score is from community wiki answers?

Paul White

Only 24 answers in that tag, right?

24 < 200

sp_BlitzErik

who can possibly know the answer to 200 questions

Paul White

I know it's ridiculous

Joe Obbish

21:49

you don't have to know the answer

you just have to post one

Paul White

That's not the attitude Mr. Obbish

sp_BlitzErik

that's some real liberal arts nonsense

coloringbook.stackexchange.com

Paul White

everyone gets a prize

Evan Carroll

hahah

Joe Obbish

@PaulWhite it's not?

why doesn't the badge require 200 ACCEPTED answers then?

that seems more in line with what Erik said

Paul White

21:57

sigh

sp_BlitzErik

> If you notice more than five spills within the same query plan's execution, contact your support professional.

Joe Obbish

@sp_BlitzErik source?

sp_BlitzErik

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

Joe Obbish

"Very rarely, multiple exchange spills can occur within the same execution plan"

ahahahahaha

sp_BlitzErik

> Rewrite the query to produce a different query execution plan.

Paul White

21:59

@JoeObbish Exchange spills are pretty rare, aren't they?

Joe Obbish

not to me!

sp_BlitzErik

have you considered rewriting the queries?

Joe Obbish

no

I'm contacting my support professional

sp_BlitzErik

does anyone have a better reference on exchange spills?

sp_BlitzErik

22:14

<RunTimeCountersPerThread Thread="6" ActualRows="2731"
<RunTimeCountersPerThread Thread="5" ActualRows="4112"
<RunTimeCountersPerThread Thread="4" ActualRows="2555"
<RunTimeCountersPerThread Thread="3" ActualRows="512933"
<RunTimeCountersPerThread Thread="2" ActualRows="1022"
<RunTimeCountersPerThread Thread="1" ActualRows="674"

oops

Joe Obbish

@sp_BlitzErik I blogged about it

what better reference could there possibly be?

sp_BlitzErik

link

Joe Obbish

@sp_BlitzErik ORDERBYSELECTNULL.COM

HTH

sp_BlitzErik

i'm getting an error

non-deterministic ip address

probably needs a sr dba

Joe Obbish

@sp_BlitzErik orderbyselectnull.com/2017/09/15/…

@sp_BlitzErik do you know any?

@sp_BlitzErik that's not even a real error

sp_BlitzErik

22:24

@JoeObbish no, they're all consultants now

@JoeObbish prove it

Joe Obbish

no u

sp_BlitzErik

22:36

the exchange spill xe is about the most useless thing i've ever seen

Joe Obbish

23:12

@sp_BlitzErik I know, right?

think there's a quota for XE too?

sp_BlitzErik

@JoeObbish oh like the 100gb one for tempdb? Not sure what 100gb worth of cxpacket buffers would look like.

Joe Obbish

@sp_BlitzErik I can't find it now, but I read once that there used to be a requirement to release "X new DMVs" with each major version of SQL Server

sp_BlitzErik

23:29

@JoeObbish there should be one that spellchecks xe session information.

Transcript for

The Heap™ – Consultancy ©®