The Heap™ – Consultancy ©®

McNets

6:53 AM

Morning

hot2use

Morning

ypercubeᵀᴹ

7:10 AM

m

hot2use

7:33 AM

@mmarie Seems like the creation of the master key comes first, before you can encrypt a database. So at some stage you must have created the master key (then probably forgot about it) and then turned on encryption for your database.

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

Disclaimer: This is just an assumption

hot2use

9:42 AM

@mmarie forget what I wrote. TDE on Azure is protected with a server certificate.

Paul White

m

sp_BlitzErik

10:31 AM

which bronski beat song is everyone listening to this morning?

Philᵀᴹ

2:22 PM

"LOL" Windows Bug: arstechnica.com/information-technology/2017/05/…

Pretty nasty one too

ypercubeᵀᴹ

2:43 PM

-1

A: Please, help me with this query - mysql

Bro please try inner join or right join for all table to get the data in one table.

sp_BlitzErik

do the dew bro

Joe Obbish

anyone got a way to generate integers to be inserted into an INT clustered column without a sort? can't use identity, a sequence, or TF 8795

sp_BlitzErik

i always read this again when i need to figure that out: sqlperformance.com/2014/10/t-sql-queries/…

Joe Obbish

that's the exact problem I had...

sp_BlitzErik

spooky, huh?

Joe Obbish

2:54 PM

no, this happens all the time

I think I have an interesting problem and Paul White already solved it in a blog post years ago

sp_BlitzErik

how does that make it less spooky?

he's literally changing the past

based on this chat

and other historic events

Joe Obbish

actually I don't see an answer in that blog post

although I didn't specify my problem exactly

I don't want any duplicate integers

sp_BlitzErik

hit f5, it will change based on what you say here

mmarie

3:28 PM

@hot2use Thanks. I got it sorted out. I basically just skipped that step and went on creating a database scoped credential and external data source and it worked.

swasheck

3:52 PM

had to work with our network team

The Lord tells me... (Stephen of Braveheart)

►

Paul White

4:02 PM

@JoeObbish So what is the requirement?

CLR TVF with the ORDER clause?

sp_BlitzErik

how often do you use CLR?

Paul White

Not as often as I would if it were easier, faster, and more capable

easier as in writing, testing, and deploying ideally within SQL Server

sp_BlitzErik

it seems like every time i work on a server where someone says "we use a lot of clr" i spend a lot of time looking at related memory dumps.

2

Paul White

faster as in data flow between the SQL Server execution engine and the CLR host

more capable as in hash operators, and ones that can read and return multiple rows

sp_BlitzErik

what's the last big problem clr solved for you?

swasheck

4:06 PM

@sp_BlitzErik yes. this. clr is great ... until it causes the server to completely crap the bed because the same idiot devs who can't write sql also cannot dispose of their objects

@sp_BlitzErik job security

Paul White

Yeah well people write carp .NET code just as easily as they write carp T-SQL

Joe Obbish

it did sound like a CLR thing, although I still haven't ever written one

sp_BlitzErik

carp codum

Joe Obbish

@PaulWhite requirement is to generate this data with no explicit sort at the end: dbfiddle.uk/…

can't use identity, a sequence, or TF 8795

it's a "for fun" thing

I suspect that it can't be done without CLR

sp_BlitzErik

@swasheck i cringe every time i see a clr regex function, because i know what's gonna happen when someone runs it on a max type column

Paul White

4:08 PM

@sp_BlitzErik ordered aggregation

think running totals but with very complex logic

sp_BlitzErik

you didn't just use a quirky update? ;)

swasheck

@sp_BlitzErik 💩 💩 💩 💩 💩 💩 💩 💩 💩 💩 💩

@PaulWhite sooooo ... Excel function?

(^^^ that was almost as bad as a kangaroo joke)

sp_BlitzErik

just use some powershell

i wrote a cmdlet that it only takes you 47 steps to import

1) download visual studio

2) update all the nugat packages

3) die while eating your own extremities

swasheck

@PaulWhite this sort of thing?

Ordered weighted averaging aggregation operator

In applied mathematics – specifically in fuzzy logic – the ordered weighted averaging (OWA) operators provide a parameterized class of mean type aggregation operators. They were introduced by Ronald R. Yager. Many notable mean operators such as the max, arithmetic average, median and min, are members of this class. They have been widely used in computational intelligence because of their ability to model linguistically expressed aggregation instructions. == Definition == Formally an OWA operator of dimension n {\displaystyle \ n} is a mapping...

Paul White

financial data

@JoeObbish dbfiddle.uk/… ?

Not really sure what we're aiming for here though

Joe Obbish

4:20 PM

@PaulWhite aiming to understand why that works

Paul White

The fastest way I found was SELECT INTO then creating the index afterward.

Joe Obbish

it seemed like the other sort was caused by the bigint to int conversion

and since ROW_NUMBER is always (?) bigint I thought I was stuck

I think I was just being stupid though

Paul White

@JoeObbish The introduced top (there are two) uses the default guess of 100 rows which means the optimizer decides against DMLRequestSort

It's pointless though. From a perf point of view we normally want minimal logging.

sp_BlitzErik

oh that's sneaky

i love it

Joe Obbish

oh I missed that

ok now I don't feel as bad

Paul White

4:22 PM

I doubt the CLR would be very fast because moving rows between .NET and the QP is so slow

You could do a similar thing with the TOP (@var)...OPTIMIZE FOR thing

Joe Obbish

I suppose if you have a recovery model of full there's no disadvantage?

sp_BlitzErik

full rec hoses ML

Paul White

@JoeObbish Bad page splits if the data arrives out of order

sp_BlitzErik

you could switch to bulk logged if you're in a vacuum

Joe Obbish

oh, and data might not arrive in order here

right

what I was after was trying to get data that sql server recognized as already sorted

just out of stubbornness

Paul White

4:24 PM

@sp_BlitzErik Yes, and not using one of the annoyingly large number of features that requires FULL

sp_BlitzErik

@PaulWhite that's why i'll never leave log shipping :)

Paul White

I spent some time a few years ago thinking about the optimal way to construct an ordered numbers table.

@sp_BlitzErik Quite right too!

It was fun for a while but I concluded I was wasting my time.

Joe Obbish

what if I need 100 billion numbers?

sp_BlitzErik

start counting

Joe Obbish

but yeah, it doesn't seem very practical

Paul White

4:27 PM

yeah we'll wait

Joe Obbish

ok

1

2

3

(that's what you wanted, right?)

sp_BlitzErik

yes

keep going

Joe Obbish

I suspect that I'll get banned

sp_BlitzErik

we'll index it later

Paul White

@JoeObbish Hack the compressed clustered columnstore bits together and DBCC WRITEPAGE them into the database directly.

Joe Obbish

4:28 PM

hmm

Paul White

I jest. Columnstore has no order :)

sp_BlitzErik

ah, writepage

swasheck

but using windbg a fool's errand

sp_BlitzErik

the quirkiest update

Joe Obbish

I heard writepage is pretty slow

I've never had the courage to try it

swasheck

4:29 PM

@JoeObbish Give It A Try!

sp_BlitzErik

it can crash your server pretty fast

Paul White

Point was you wouldn't need many pages

Maybe just one.

Joe Obbish

someone around here had a practical use for it

can't remember what it was for though

Paul White

One of these days they'll build a super-fast sequence generator into the engine itself

sp_BlitzErik

i found use for a billion row table once for some real stupid ms date math

swasheck

4:32 PM

@JoeObbish corruption recovery demo?

Joe Obbish

you mean a sequence? is that the joke?

sp_BlitzErik

nextvaluefor joke

Paul White

@JoeObbish no a generator function that's fast with no persistent storage at all

if there had been a joke there would have been ten pages of explanation

Joe Obbish

I like Itzik's version

except it causes too many issues on sql server 2016

@swasheck No, not that. it was to do something useful. we didn't roll it out to production though

can't find any emails about it

sp_BlitzErik

it was all a dream

Joe Obbish

4:36 PM

I could ask a guy

anyway, it's hard not to be annoyed at all of the sorting restrictions in sql server

by that I mean you take a sorted bigint column, convert it to int and now it's no longer sorted according to the optimizer

date manipulations are pretty annoying too

sp_BlitzErik

i still don't understand the lack of parallel backwards scans

Joe Obbish

add a day to your sorted date column? no longer sorted!

do you actually hit that limitation in practice?

sp_BlitzErik

i find it so much i have a warning for it in blitzcache

sad panda.

Joe Obbish

wow

ever create the same index but in reverse order?

sp_BlitzErik

if i need it, sure

Joe Obbish

4:38 PM

you're living the dream

Paul White

connect.microsoft.com/SQLServer/feedback/details/526431/…

On the ordering thing ^

swasheck

> Please take another look

Paul White

Re: backward || scans, it's not always obvious when you're suffering from it

Sure, you might see a serial backward scan followed by an exchange, but that's just luck

Other times, you'll just get a less good plan.

sp_BlitzErik

yep

i wish it were less intrusive to find serial zones in plans

Paul White

But then if there were no problems there'd be no need for awesome experts

sp_BlitzErik

4:43 PM

i also wish operators had a # of executions badge the way they have a parallel badge

Joe Obbish

it would be nice if you could see both a sort warning and a parallel badge at the same time

sp_BlitzErik

sure there would be. no one would wanna learn this stuff even if it were all easy.

Paul White

I wish there were parallel batch mode loops joins

sp_BlitzErik

AGs have a literal wizard and most people can be bothered to set up quorum correctly

Paul White

ha!

Joe Obbish

4:44 PM

@PaulWhite would that require batch mode for index seeks?

or is that more for parallel apply type of plans?

Paul White

@JoeObbish yes both

But really I want to be able to specify finished plans directly in the language of the execution engine.

SQL is so abstract :)

sp_BlitzErik

i was really surprised to see this one: connect.microsoft.com/SQLServer/feedback/details/757383/…

Paul White

Yeah Adam was quite cross about that

sp_BlitzErik

he does tend to get testy about deficiencies in parallel nested loops

though i suppose it'd be like if i found out my kid went to college and started wearing a fedora and a thumb ring

Paul White

an accurate analogy no doubt

Joe Obbish

4:49 PM

he is still cross about it as of March of this year

Paul White

yep

Joe Obbish

imo it didn't even fit in that well with the rest of the class...

sp_BlitzErik

consultants love airing product grievances

Paul White

Why do good answers on bounty questions always seem to come in at the last minute?

sp_BlitzErik

"look at this insane edge case i found that they won't fix!"

Paul White

4:51 PM

6

Q: SSRS and Managed Service Account - impossible to configure?

We are trying to configure Reporting Services to use a Managed Service Account. The environment is: Server: Windows 2008 R2 SP1 Reporting Services: SQL Server 2012 (version 11.0.6567.0) Currently, SSRS runs as a domain service account, but we would like to change to running as an MSA. The SQ...

back later

Joe Obbish

maybe, it seemed more like he wanted to point out issues with MS support/connect

which I guess is similar to what you said

sp_BlitzErik

i dunno, connect seems like it's improving, but i'm viewing that through the lenses of someone whose had a number of issues fixed recently

then again i'm kind of a bully about it

Joe Obbish

can that dark art be taught to others?

sp_BlitzErik

that's the secret agenda behind one of my pass abstracts

Joe Obbish

what did you submit?

sp_BlitzErik

5:03 PM

good question

rubs eyes

two perf tuning at different levels, one career-ish at a low level

"choosing hand and neck tattoos for success"

Joe Obbish

I'd go

what's your presentation style? accept questions during the talk?

sp_BlitzErik

depends on the talk but i'll always take questions

Joe Obbish

good, I will start planning my questions now

sp_BlitzErik

some are more 'fun' than others

i'll answer all your questions with memorized urls to paul's posts

Joe Obbish

that's a good strategy actually...

sp_BlitzErik

5:09 PM

i'll even use a nz accent if you bring drinks

Tom V

5:55 PM

@PaulWhite unfortunately I have no idea and it's too much work to set up a test env

Tom V

6:27 PM

@sp_BlitzErik I can help. python.org

meh mobile

sp_BlitzErik

oh, the 2017 integration?

Tom V

No a coworker was struggling with powershell until I pointed him to python, he's never looked back

sp_BlitzErik

lol

i'd rather write perl than powershell

Tom V

Says it all ^^

In Vim even

sp_BlitzErik

really, bless the people who can deal with it

i am not one of them

Tom V

6:32 PM

I see your 100+ line PS script and raise it, no rather replace it with a 10 line python script

sp_BlitzErik

heh seriously

Tom V

@sp_BlitzErik I'd rather read powershell than perl though

sp_BlitzErik

i get powershell for admin tasks, because it does hook into windows nicely

but the stuff people use it for outside of that is horrible

"look what we can do with sql"

"we can run a query"

Tom V

the coworker was trying to parse mhtml in powershell

It involved creating an internet explorer active x object and stuff

beautiful soup fixed it in a 10 liner

sp_BlitzErik

7:17 PM

oh good heavens

Joe Obbish

I use powershell to cook breakfast

Max Vernon

@seangallardy - that Win32App_1 ADS is Windows:

1

A: Alternate Data Stream "Win32App_1" attached to a large number of folders

Win32App_1 Alternate Data Stream is created by the "Storage Service" service that is part of the Windows Operating System. Versions of the service prior to Windows 10 do not appear to create these streams. If you use a Portable-Executable viewer, such as the dumpbin.exe tool available in Visu...