The Heap™ – Consultancy ©® - 2018-01-17 (page 2 of 2)

Evan Carroll

8:07 PM

Microsoft made another minor baby step in its pursuit to not-suck.

daniel.haxx.se/blog/2018/01/13/microsoft-curls-too

sp_BlitzErik

sean did that

Evan Carroll

I was really looking forward to a 4 letter acronym named "cmdlet" that reinvented curl but was broken in fresh and exciting ways and always required the QUOTED_IDENTIFIER flag.

sp_BlitzErik

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;

Evan Carroll

Now let's create a competing and confusing amount of "official" clients each with their own default settings that modify that.

sp_BlitzErik

done

what's next?

swasheck

8:14 PM

can we please simplify the in-memory troubleshooting memory pressure resolution path to cycle the service

sp_BlitzErik

done

what's next?

Evan Carroll

Providing an interface into C#'s geometry and geography types and calling that "Spatial Types for SQL Server." For fun, just say you comply with standards you haven't even looked at...

sp_BlitzErik

done

Evan Carroll

Create one repository for each piece of software, rather than understanding the technology, just market that you have multiple repositories because more is better!

sp_BlitzErik

i don't even know what that means

have you bought a mac yet?

Evan Carroll

8:20 PM

Then, call your stuff open source because it's written in Python. But just shim a bunch of proprietary .dlls. Python wrappers and binary blobs on github for everyone!

sp_BlitzErik

i hear photoshop is nice

Evan Carroll

"Microsoft user 2018"

sp_BlitzErik

did you photoshop that?

Evan Carroll

No I used Power BI. It can play movies too!

sp_BlitzErik

that's basically photoshop for it people

2

Evan Carroll

8:28 PM

EXACTLY

We agree! Now let's drink egg whites and inject each others asses with test.

swasheck

wait wat?

sp_BlitzErik

that's what i was doing today anyway

but i was injecting my ass with egg whites

which would explain a lot

swasheck

see? you have it all backwards

duhr

sp_BlitzErik

this is why my stomach is angry and i'm growing tail feathers

Sean Gallardy - Microsoft

lol

swasheck

8:32 PM

could also be your distiller of choice, to be honest

sp_BlitzErik

did something happen to lagavulin?

Tom V

I don't feel like reading back to see what this is about

I just had steak wellington and wine

Sean Gallardy - Microsoft

@EvanCarroll curl is just an alias for invoke-webrequest

sooo

glad I could make your day

swasheck

apparently not anymore

Evan Carroll

bhahahahhaha

swasheck

8:38 PM

looks like curl is legit(-ish) curl

assuming you "install" it

Sean Gallardy - Microsoft

lol

or I could use powershell which is already there and gives me pretty much the same thing

Tom V

Last few comments seem like something that could ruin the eat well feel well feeling

Sean Gallardy - Microsoft

but why do that when I could install open source software to do somethingI can already do...

swasheck

@EvanCarroll it looks like Microsoft's GitHub is a collection of actual things as well as a dumping ground for personal projects

this thing looks interesting

github.com/Microsoft/dbfs

Evan Carroll

Look at this awesome patch to add DAC to MSSQL-CLI github.com/dbcli/mssql-cli/pull/127/commits/…

Microsoft, they get it!

Tom V

8:42 PM

Binary file not shown

My sarcasm detector is at 11

3

swasheck

how's it going, @TomV?

Evan Carroll

You don't actually have to open up unless your git repository for the "client" has a 30 MB blob in it, github.com/dbcli/mssql-cli/blob/master/sqltoolsservice/…

Tom V

@swasheck good actually

swasheck

glad to hear it. you sound shocked

Tom V

I engaged in a challenge

I promised I won't complain (about anything) for a month

4 days of pretending everything is going well so far

@swasheck not complaining kind of got me out of my natural habitat

Sean Gallardy - Microsoft

8:56 PM

@TomV It's like you're in a relationship!

So, I have a plethora of notes written down on things to blog about... but I'm finding not everyone thinks they are as awesome as I do..

with that being said, what would you all like to see new posts about?

preferably the storage engine...

Tom V

@SeanGallardy-Microsoft I won't complain

Sean Gallardy - Microsoft

LOL

at least not for 26 more days ;)

Tom V

For at least 3 and a half weeks

Yah

Joe Obbish

@SeanGallardy-Microsoft problem is your platform

Tom V

@SeanGallardy-Microsoft I even pretend to be satisfied with Microsoft support

Joe Obbish

9:01 PM

we can't use T834 with CCIs but it seems to be the only fix for this bottleneck

Tom V

That was just kidding

Sean Gallardy - Microsoft

@JoeObbish obviously

Joe Obbish

well we'd had four versions without support for T834... hard not to be pessimistic about it

Sean Gallardy - Microsoft

If that's all it takes to get you down...

Joe Obbish

seeing "this doesn't work in versions SQL Server 2012 to 2017" sticks out in the documentation

swasheck

9:02 PM

@TomV nice! excellent thing to develop.

Tom V

@swasheck it's relaxing

Forcing yourself to either not react or at least react without sounding irritated somehow works

Joe Obbish

@SeanGallardy-Microsoft columnstore memory management

Tom V

9:21 PM

@swasheck I'm out for tonight. Hope you are progressing on the adoption track if that is still something you are aiming for

sp_BlitzErik

9:37 PM

@JoeObbish "management"

Joe Obbish

@sp_BlitzErik I know right

like this if you cry every time:

sp_BlitzErik

at least it's not a heap!

Joe Obbish

what

the entire problem is that it's a CCI

sp_BlitzErik

you seem to have worse luck with those

maybe if you provided less context that would be more clear

Joe Obbish

9:52 PM

I got a set of 40 nearly identical queries that should have the same plan

some of them do row mode hash joins instead of batch mode

probably related to plan timeouts?

even worse, the repartition streams is likely to put all of the rows on one thread

sp_BlitzErik

16 or 17?

Joe Obbish

16

sp_BlitzErik

would memory pressure change batch/row mode choice?

guessing trivial plans aren't the issue

Joe Obbish

memory pressure can affect query plan exploration

I think it can terminate early

sql_handle understands the mechanics pretty well

the funny thing is that only one join is affected

and it's the worst possible join to be affected

it's the join that happens first and that has the worst row distribution by hash

sp_BlitzErik

queryprocessor.com/query-plan-on-a-busy-server

no reference to column store, but you may find similar behavior

Joe Obbish

10:00 PM

do you know if query store has it?

if not I'll try to find the right XE

sp_BlitzErik

has what exactly?

Joe Obbish

query plan timeout reasons

or memory used during compilation

it does

sp_BlitzErik

SELECT *
FROM sys.query_store_query AS qsq

Joe Obbish

sys.query_store_query has last_compile_memory_kb among with a few others

sp_BlitzErik

you could also check for forced grants

SELECT  deqrs.forced_grant_count
FROM    sys.dm_exec_query_resource_semaphores AS deqrs;

Joe Obbish

10:10 PM

query store hates me :(

sp_BlitzErik

have you considered blitzing it?

Joe Obbish

database name should always be first imo

sp_BlitzErik

open a github issues

Joe Obbish

but that would be a breaking change

sp_BlitzErik

for who?

Joe Obbish

10:14 PM

everyone?

also, it hasn't finished after a minute

I'll just use XE

those have never let anyone down

sp_BlitzErik

check messages for status

use @skipxml if you don't care about analysis

Joe Obbish

currently on Gathering highest memory use plans

I guess we're giving it a work out

also there appears to be a wonderful extended event for this

sp_BlitzErik

la

dee

da

Joe Obbish

10:32 PM

@sp_BlitzErik I don't like it

it reminds me of all of the cool 2017 features I don't have

sp_BlitzErik

i suppose you could use blitzcache and sort by memory grant or avg memory grant

that will remind you of nothing futuristic

Joe Obbish

to be honest I wrote my own blitz-like procedure for this testing

please don't sue me

sp_BlitzErik

i don't care

what does it do?

Joe Obbish

check electronic mail

sp_BlitzErik

i'm so far in the future man

Evan Carroll

10:50 PM

from where I am standing, you're all cro-magnon

sp_BlitzErik

you wear flip flops

scram

Evan Carroll

That puts me on the same tier with Jesus, Moses, and Muhammad; and, just one sheet of plastic away from Socrates and a health code violation.

sp_BlitzErik

no one on reddit believes in those people

Joe Obbish

11:09 PM

I get a bunch of traditional optimizer timeouts

but no memory related ones, according to XE

hm

sp_BlitzErik

time for the trace flag

Joe Obbish

I'm already using FORCE ORDER

sp_BlitzErik

what's not happening that you want to happen?

Joe Obbish

Do you mean in general? or with respect to what?

sp_BlitzErik

clearly something has gotten out of control and you've resorted to the query tuning version of buying sweatpants

Joe Obbish

11:21 PM

oh man, if only you knew

sql server was picking join orders that were basically guaranteed to spill

I didn't like that

wasn't a CE issue

sp_BlitzErik

just enjoys spilling then

Charlie Brumbaugh

I have a question that I am not sure is on topic for the main site
I need to store the values of when a row was last updated and am unable to do the simple fix of adding a datetime column.

Right now my idea would be to use a trigger to store the primary keys of any updated rows in a separate table,
Does anyone have a better idea?

sp_BlitzErik

dude i don't even know what platform you're using

Charlie Brumbaugh

@sp_BlitzErik MariaDB

sp_BlitzErik

not sure, not my thing

not too many people in here seem to be users either, you're better off on the main site

Charlie Brumbaugh

11:34 PM

@sp_BlitzErik Thanks

sp_BlitzErik

yeah, sorry i couldn't just answer!

Transcript for

The Heap™ – Consultancy ©®