stop the merry-go-round - 2018-08-17 (page 1 of 2)

/*
Votes is upper input, hash agg that spills, nested loops
runs about 50 seconds
*/
SELECT COUNT(*)
FROM dbo.Posts AS p
JOIN dbo.Votes AS v
ON p.Id = v.PostId;

/*
Posts it upper input, hash join no spill
*/
SELECT COUNT(*)
FROM dbo.Posts AS p
JOIN dbo.Votes AS v
ON p.Id = v.PostId
OPTION(FORCE ORDER);

Paul White

@sp_BlitzErik probably; people say a lot of things

Joe Obbish

1:46 PM

I was rightfully afraid

> "upper input"

sp_BlitzErik

anyway, weird

Paul White

@JoeObbish remain?

Joe Obbish

ahem

@PaulWhite hm?

Paul White

3 mins ago, by Joe Obbish

and will request all chatters to remain from using it in my presence

shouldn't that be "refrain"?

Joe Obbish

yes

sp_BlitzErik

1:47 PM

what's wrong with upper input?

Paul White

@sp_BlitzErik is there a columnstore index of any sort on either table?

sp_BlitzErik

if i say inner or outer half the people will misunderstand

no, this database is column store free

Paul White

perhaps you're seeing memory grant feedback?

idk just guessing

Joe Obbish

oh... well you have a point then

sp_BlitzErik

thought of that

Joe Obbish

1:48 PM

@sp_BlitzErik login plz

I will fix

Paul White

mystery.gif

Joe Obbish

you got the old and new plans?

sp_BlitzErik

not anymore

trying some stuff

Joe Obbish

query store? plan cache?

sp_BlitzErik

> using query store

Paul White

1:51 PM

compat level 150?

sp_BlitzErik

@PaulWhite doesn't exist

Paul White

@sp_BlitzErik does on Azure

sp_BlitzErik

never heard of it

Paul White

well it can't be that then

Joe Obbish

Mr. Plan Cache isn't using the plan cache?

Paul White

1:52 PM

perhaps your database has become self-aware

sp_BlitzErik

the plan cache was cleared in an attempt to get the old plan back

@PaulWhite "local factors" he says

the plan has changed yet again

brentozar.com/pastetheplan/?id=rkl5QLVUQ

Paul White

what is the local time?

sp_BlitzErik

9:56am

Joe Obbish

Erik's going to eat lunch soon

Paul White

last stats update: 17/08/2018 9:52 AM

Joe Obbish

1:56 PM

> being this new

Paul White

sp_BlitzErik

right

nothing happened to trigger a stats update

this database is entirely static

^_^

Joe Obbish

is it in a VM?

Paul White

someone give me a ghost gif

sp_BlitzErik

Jul 27 at 1:09, by sp_BlitzErik

this is some nonsense

jadarnel27

2:00 PM

Paul White

@jadarnel27 cool!

jadarnel27

@PaulWhite right?!

This ghost has really brightened my morning.

Paul White

the name is a little odd, however

jadarnel27

Gyfcat is like that.

sp_BlitzErik

2:18 PM

what a weird morning

Joe Obbish

so I converted tens of thousands of columns to BIGINT

to get better support for bitmap filters

BUT THERE AREN'T BITMAPS IN THE PLAN

Paul White

Alanis Morissette - Ironic (OFFICIAL VIDEO)

►

Sean Gallardy

lively crowd this Friday (or for Paul, Saturday) AM

Paul White

the weekend is off to a spooky start for sure

Sean Gallardy

The reason Paul is so good is he's in the future so he knows what's going to happen

Paul White

2:22 PM

also I have learned much from the first few pages of Erik's book

sp_BlitzErik

@JoeObbish are they nullable?

HHHTTTHHH
TTTHHHTTT
HHHTTTHHH

Joe Obbish

wait

Paul White

does joe look like the sort of person that would allow nulls

Joe Obbish

WAIT

Sean Gallardy

Dear Room: does anyone use the GDR patches?

Joe Obbish

2:23 PM

with a NUMERIC(18,0) data type

sp_BlitzErik

@SeanGallardy no

Joe Obbish

I get the crappy bitmap

sp_BlitzErik

@PaulWhite only in heaps

Joe Obbish

with BIGINT

I get NOTHING?

NOTHING??????????????????????????????

Paul White

@SeanGallardy no, latest scare not scary enough

@SeanGallardy also too many trace flags

Sean Gallardy

2:24 PM

Ok, well just in case you know someone that does - tell them to wait a few days before downloading it.

sp_BlitzErik

you mean

the patch that windows update just forced on me?

^_^

yep that one

for SQL

lol

I'd give it a day or two to cool off the newly minted press

sp_BlitzErik

2:25 PM

well i don't patch windows

Paul White

I hear it does frooky things with statistics

sp_BlitzErik

do i look like a sysadmin

Sean Gallardy

@sp_BlitzErik Windows as a Service means you aren't in CONTROL

Paul White

and bitmaps

sp_BlitzErik

Joe Obbish

2:26 PM

I want to punch something

sp_BlitzErik

i believe this is what you'd call

use the ghost

some real bullshit

ah a technical term

I see Windows patching is still stupid

glad they give us control over our personal devices... oh wait

Paul White

2:26 PM

I paused Windows PatchDates

sp_BlitzErik

if only there were a team dedicated ot that

Joe Obbish

Sean should be glad right about now that we don't do phone calls

Sean Gallardy

I'll get on the phone with you

or discord

Paul White

discord

Noun: discord (countable and uncountable, plural discords)

Lack of concord, agreement or harmony.
Bible, Proverbs vi. 19
A false witness that speaketh lies, and he that soweth discord among brethren.
Burke
Peace to arise out of universal discord fomented in all parts of the empire.

(5 more not shown…)

seems about right

Joe Obbish

it would just be ten minutes of me cursing MSFT

sp_BlitzErik

2:28 PM

you'd be preaching to a pew

Sean Gallardy

@JoeObbish Preach

Paul White

anyone remember the days when updates made the product better?

Joe Obbish

@PaulWhite will you not be attending the CU10 release party?

sp_BlitzErik

what were those things called

they provided service in packs

cervix thwacks?

Paul White

sounds right

Sean Gallardy

2:30 PM

@sp_BlitzErik Both are about equally painful

Paul White

@JoeObbish will there be cheese rolls?

Sean Gallardy

Depends, are you a fan of cheese?

Paul White

yep

Sean Gallardy

Then.... sure

Paul White

I'm also a fan of sleep. Night all!

Sean Gallardy

2:31 PM

Haha, night

Paul White

swooshes out

Sean Gallardy

Ghost_Paul_Swoosh.gif

sp_BlitzErik

adios

Joe Obbish

man

this might be the angriest repro I've ever done

I will pour all of my rage into it

sp_BlitzErik

COLUMN1024

Joe Obbish

2:40 PM

guys I have an important update

it turns out that I'm really dumb

4

sp_BlitzErik

dumb and angry

Joe Obbish

I spent three days converting these columns

Sean Gallardy

2:55 PM

@JoeObbish Go on...

Joe Obbish

are default schemas not applied to table references in views?

sp_BlitzErik

well

what are you seeing?

Joe Obbish

3:21 PM

I assume that the view's schema overrides the login's default schema

that seemed to be what was happening

I ended up just creating copies of all of the views instead of truly figuring it out

sp_BlitzErik

3:31 PM

quite the dba

Joe Obbish

I've had about enough of scripting things out

sp_BlitzErik

i hear it's quite easy with powershell

0

Q: Script SQL Server Agent jobs to file

I am busy making a backup of all Agent Jobs by scripting them all to file.`How would i rewrite the below to use BCP instead BEGIN SET @EchoCommand = 'ECHO ' + @EchoField + ' >> ' + @Path + @JobName + REPLACE(REPLACE(CONVERT(VARCHAR(200), GETDATE()), ' ', ...

sql-server sql sql-server-2012 sql-server-2008

Sean Gallardy

3:48 PM

@JoeObbish Huh?

Joe Obbish

@SeanGallardy view was in dbo

view referenced tables without a schema

I ran a query against the view with a default schema that wasn't dbo

the tables used in the plan were in dbo instead of my default schema

Sean Gallardy

Ah, ok that makes more sense. Then it would check the user's default schema first, sys, then dbo I believe

assuming the user had access to other said schemas

sp_BlitzErik

dba

alert

jadarnel27

4:07 PM

When does CU10 come out, next week?

sp_BlitzErik

WHEN IT'S PROBABLY READY

jadarnel27

Your caps, man. They got me all messed up.

I haven't run since Monday.

Everything is terrible.

sp_BlitzErik

you had plenty of time yesterday

;)

jadarnel27

I was busy helping #sqlfamily.

Would anyone like to buy a not-so-gently used achilles tendon?

sp_BlitzErik

i'll trade you for a mangled piriformis

jadarnel27

4:13 PM

I guess I probably don't want that either.

Sean Gallardy

@jadarnel27 I can chase you with a blunt object yelling threats if you'd like?

jadarnel27

@SeanGallardy TIA

Sean Gallardy

Just trying to motivate you!

jadarnel27

Thanks =P

I'm pretty sure I strained my achilles doing speed interval training on a treadmill.

Sean Gallardy

I don't even know what you said... but you special..

jadarnel27

4:22 PM

Josh run fast. Josh hurt leg. Josh bad at run.

Sean Gallardy

Ahahahahaha <3

sp_BlitzErik

critique my new routine

Day 1: Squats 3x5,  OHP 3x8, Cardio

Day 2: Deadlifts 3x5, Bench 3x5, Cardio

Day 3: Chins 3xF, Dips 3xF, Cardio

Day 4: OHP 3x5, Squats 3x8, Cardio

Day 5: Deadlifts 1x5, Bench 3x8, Cardio

jadarnel27

What's 3xF?

sp_BlitzErik

failure

jadarnel27

Ohhh.

Not enough box jumps and one-footed kettlebell romanian deadlifts.

sp_BlitzErik

4:36 PM

everyone i see doing box jumps also seems to do other exercises with terrible form

connection?

jadarnel27

For real though, you know way more about weightlifting than I do. It looks good to me =)

Do you ever vary where the bar is positioned when you do squats?

sp_BlitzErik

no, i don't care much for high bar

jadarnel27

Ah, okay. In CrossFit they do front vs back, but I wasn't sure if it really makes much of a difference.

I just had to Google high bar. Weird body positioning.

Hey, @Sean - is the likely reason for this error that a failover happened during?

Joe Obbish

@sp_BlitzErik me too

Sean Gallardy

4:52 PM

@sp_BlitzErik It says "Cardio" That should be removed.

@jadarnel27 It's what happens when the primary thinks the secondary is offline and synchronous

Friends don't let friends use Vormetric

jadarnel27

I was also surprised to see cardio. Maybe that's just the walk back to his car.

@SeanGallardy Thanks!

Sean Gallardy

@jadarnel27 I think I win the "lowest amount of cardio" award for this room - @sp_BlitzErik were in a challenge to see who could do the least

@jadarnel27 Or for whatever other reason the transaction sequence couldn't be sent or wasn't acknowledged by the secondary

Joe Obbish

@sp_BlitzErik where are your muscle ups?

sp_BlitzErik

@SeanGallardy i need to lose 20lbs and have my doctor stop trying to put me on bp meds

jadarnel27

@SeanGallardy Congratulations :P

sp_BlitzErik

5:01 PM

@JoeObbish on venice beach

jadarnel27

@SeanGallardy Gotcha - so it's really all about connectivity / communication with the secondary. Would Kin's suggestion (go async) resolve the issue you think?

Sean Gallardy

5:16 PM

@sp_BlitzErik Sorry to hear :(

@jadarnel27 Would it resolve the error? Probably. Would it fix the issue? No.

jadarnel27

6:19 PM

On a scale of 1-10, how new would I be considered to be if I didn't know what "at the cost of a many-to-many merge join" meant?

sp_BlitzErik

many infinite scales

brentozar.com/archive/2018/04/many-mysteries-merge-joins

jadarnel27

> Simple as a pimple

Thanks!

@sp_BlitzErik That was really helpful. Have you blogged about everything? How do you even remember?

sp_BlitzErik

i don't

i've written several dupes

:D

jadarnel27

Haha that seems reasonable, considering the number of posts.

I guess this one is pretty fresh, which is why you were so quick to remember it?

Also

> being this new (contributor)

+41, -13

sp_BlitzErik

Sean Gallardy

7:41 PM

> Waiting on Joe

Joe Obbish

@SeanGallardy wut

Sean Gallardy

I dunno seemed like something to say

… annnnnnnnnnnnyway

Joe Obbish

I think it's a good feature

as long as we can vote to apply the "I'm new" destination to existing users

Sean Gallardy

<--- gets one

Joe Obbish

yeah

I'm surprised you don't post answers as questions, tbh

Sean Gallardy

7:50 PM

ORLY?

Everyone once and a while I do, just 160 or so votes shy of the gold badge.

jadarnel27

8:03 PM

I was going to post an answer to that AG question earlier, but didn't want to get dunked on by Sean so soon after being dunked on by Paul.

Sean Gallardy

lol - now I want to dunk...

If only I were taller

sp_BlitzErik

8:33 PM

there should be a badge for that

dafuq

github.com/Microsoft/FASTER

Sean Gallardy

8:49 PM

Where do you find this stuff?

sp_BlitzErik

in the immoral words of paul

"i go looking"

[sic]

sp_BlitzErik

9:15 PM

@jadarnel27 @Forrest in the spirit of being welcoming and inclusive® to everyone in chat, if you'd like a free copy of my stupid book, send me your address. i have a few extra copies kicking around.

Forrest

I would actually really appreciate that, thanks! I'll do a deadlift or two in your honor.

jadarnel27

@sp_BlitzErik dude, awesome! I'll email you.

I will not do any deadlifts.

sp_BlitzErik

HIH

Joe Obbish

9:38 PM

@sp_BlitzErik does the olved steries guy have one?

Sean Gallardy

@JoeObbish No :( not yet

I asked for the signed copy

I'm going to hoard my query bucks in the book, fyi

sp_BlitzErik

10:09 PM

I told him I'd send one after he moved!

This is blatant character assassination

Sean Gallardy

:)

I was saying that it took longer to get one with your signature

The "graffiti" edition, as it were

sp_BlitzErik

🤔

Joe Obbish

10:37 PM

you better have written a unique, personal message just for me!

Paul White

40

Q: Come Take a Look at our New Contributor Indicator!

We've been doing quite a bit of research into ways that we could help new users have better experiences that ultimately lead them to becoming increasingly valuable, long-term contributors to our sites. While this is still ongoing, it's starting to blossom in the form of small but hopefully very e...