The Heap™ – Consultancy ©®

Paul White

3:23 AM

@JoeObbish Well he needs the publicity

Joe Obbish

3:44 AM

@PaulWhite me too, thanks

Paul White

4:03 AM

Well you'll never get famous with a default avatar

Andriy M

5:55 AM

Morning

When creating a linked server, is it possible to specify a link to self in a generic way if it's a named instance?

I know that e.g. '.' would be a valid server reference if my instance was a default one, i.e. just SERVERNAME, but what if it's SERVERNAME\INSTANCENAME?

gbn

6:11 AM

@AndriyM do you want to do this programmatically?

Andriy M

@gbn I would like it to be a valid generic reference, like the .

If there is a way.

For now I'm specifying it as .\INSTANCENAME, which I'm happy to go with if there's no way to avoid specifying the instance name.

gbn

6:29 AM

EXEC sys.sp_addlinkedserver 'SELF', '', 'SQLNCLI', @@SERVERNAME
SELECT * FROM sys.servers

sp_BlitzErik

> Concurrency is hard; sorry about that

gbn

I'd use "SELF" as a valid generic reference for all servers. Linked servers are useful, sometimes, but I do dislike when they contain actual server or instance in the name. It breaks code deploys etc

@sp_BlitzErik Only the con Swiss Franc

Ignore that above. Correct SQL
EXEC sys.sp_addlinkedserver 'SELF', 'SQL Server'
EXEC sp_setnetname 'SELF', @@SERVERNAME
SELECT * FROM sys.servers

and if you want to re-link it

EXEC sp_setnetname 'SELF', 'foobar'
SELECT * FROM sys.servers

then

EXEC sp_setnetname 'SELF', @@SERVERNAME
SELECT * FROM sys.servers

@sp_BlitzErik Brooklyn or Oregon, still bloody early over there

sp_BlitzErik

@gbn the troubles of a man who finishes a bottle of wine before bed

Paul White

6:44 AM

@sp_BlitzErik I felt the need to apologise somehow

sp_BlitzErik

@PaulWhite wait until he finds out about RCSI

gbn

@sp_BlitzErik the bottle of wine finished you then...

sp_BlitzErik

7:01 AM

@gbn ⛲

McNets

7:21 AM

Bon dia !! (Catalan)

Tom V

Morning

Andriy M

@gbn Thanks, I've learnt something new.

What I'm a little concerned about is this note in the manual:

> Using sp_setnetname to point a linked server back to the local server is not supported. Servers that are referenced in this manner cannot participate in a distributed transaction.

gbn

@AndriyM Never had any problems (admittedly I rarely use them now, not for 10 years)

Do you need distributed transactions?

Andriy M

@gbn Not now, I don't think.

Andy K

7:43 AM

morning

congrats @McNets for the referendum

McNets

@AndyK thank you, but ~ 800 injured is not good news

Andy K

@McNets democracy is a rough game

Tom V

Yeah I'm not sure it was a huge success

Andy K

terrible

McNets

@AndyK yes

Tom V

7:46 AM

I'm not talking about the outcome, I have no opinion on that because I don't know enough about the situation

But those aren't circumstances we expect in a civilized part of the world

McNets

@TomV sure

Andy K

@McNets a friend of mine asked on her fb what kind of democracy Europe is promoting before remembering that EU started as an economical zone, not as a democratic one

McNets

@AndyK money rules

Andy K

@McNets I'm shared between the pessimistic answer of always or a more optimistic one of again

McNets

I hope this time something will change

Andy K

7:52 AM

@McNets it will. reverting back to autocraty or muting the voice of People are neither a decent options

gbn

@AndyK The last 20 years have demonstrated that the EU is quite autocratic. Just look at the national votes on treaties:you said "no"? Vote again until you get it right

It takes the shine off the good things: internal borderless customs, reduced mobile phone tariffs etc. The good things affect people more on a daily basis though, so this is what they think the EU is

Tom V

@AndyK It's really still mostly a monetary union and not a political one

Andy K

@TomV yes

Although Jean Monnet and Konrad Adenauer dreams were grand

Tom V

My personal opinion is that they should have worked towards a more integrated political union before expanding. Things would have been a lot easier I believe

Andy K

@TomV +1

gbn

8:08 AM

@TomV Common currency, not a monetary union

There are no regional transfers from "rich area" to "poor area" like within a country (Zurich, London, New York, California etc). No shared national debt. Etc

Tom V

@gbn I wikipedia'd that and it seems that's the correct translation indeed

gbn

although, I don't fully know the difference between "monetary" from "fiscal"

Tom V

Though with the tax and debt rules coming out of the EU it seems somewhere in between now

The EU reviews the state's balances and imposes some changes every now and then

gbn

@TomV Agree. Countries have a common currency after being a country. The EU started with a currency

@TomV Which are ignored if you are a big country

Tom V

I see it from a Belgian perspective. We don't really get to ignore them

gbn

8:14 AM

@TomV Itself a country almost split in 2. Although you managed very well without a government for, what, 400 days. Well played sir

Tom V

@gbn I keep trying to explain that, but the splitting thing doesn't really interest most of the population. It's just a politicians game. There are some rough edges that should be sorted but most of us realize that splitting the country would be more hassle than it would be worth

The Brussels situation would be nearly unsolvable

McNets

BTW, 8 days w/o smoke

gbn

@TomV Are you a Walloon or Flemish?

Paul White

@sp_BlitzErik He's already using it!

Tom V

8:30 AM

@gbn I'm Flemish

But I live so close to the French border almost everybody here is bi-lingual

Andy K

8:53 AM

@McNets nice

ypercubeᵀᴹ

10:48 AM

@ypercubeᵀᴹ, because of the application logic. — Boban P. 3 mins ago

The title of that q should not be "concurrency issue" but "How to create a concurrency issue"

Paul White

People always try to out-think themselves with these sorts of things

Or think they can make it simpler by making it more complex

Kinda odd

ypercubeᵀᴹ

@PaulWhite Yeah. DBMS developers had put a lot of effort into making auto incrementing columns working without transactions blocking one another. But people still think ("I'll put a lock and it will be ok")

sp_BlitzErik

12:13 PM

@PaulWhite i struggle to see how all his questions aren't duplicates of each other

Paul White

@sp_BlitzErik To some extent yes, but the alternative is the dreaded chameleon question. If it becomes disruptive we will deal with it.

sp_BlitzErik

eaml chameleon question

Paul White

435

Q: Exit strategies for "chameleon questions"

I'm not sure if there's already an existing term for this, so I'm inventing my own. (tl;dr: I call them "chameleon questions" because they change every time you submit or edit an answer. If you're already intimately familiar with the phenomenon, please skip past the first set of bullet points t...

I'm not certain that's exactly the right term for the situation, but I guess I'm saying I'd rather have n similar questions than one great big question that morphs over time with a mess of answers from different points in time, and endless comment/chat.

sp_BlitzErik

reading the description reminded me of something else

Paul White

12:29 PM

hm

Well the (deserved) downvotes on the most recent question mean he won't be asking any new ones for a while. That might produce a rethink.

sp_BlitzErik

perhaps

how many times am i going to forget to ask for an actual plan before running this query that takes 6 minutes?

Paul White

@sp_BlitzErik You gave a very good answer anyway.

Of course that will only encourage him/her.

@sp_BlitzErik Doesn't the new hotness allow one to grab a plan during execution? Or you specifically need the end-result plan?

But to answer directly: several more times than you would expect.

sp_BlitzErik

@PaulWhite they're very much estimated plans still

Paul White

@sp_BlitzErik Are they? I've not played with it.

It being Developers Choice: Query progress – anytime, anywhere

and more specifically sys.dm_exec_query_statistics_xml

sp_BlitzErik

yeah, we use that in blitzwho now when available

Paul White

12:39 PM

I would have tried it but I just don't trust trace flags

sp_BlitzErik

it seems to give you a snapshot of the plan as its in progress

so some operators may show final actuals, others may still be working on it

Paul White

It seems (from the docs) that the entry just disappears when the query finishes.

sp_BlitzErik

very much so

Paul White

Which is rather unhelpful in the case where one has simply forgotten to request a post-execution plan.

sp_BlitzErik

i have a client call starting in 15 minutes, but i really just wanna sit here listening to the chameleons and trying to break adaptive joins

life is v. unfair

Paul White

12:46 PM

Yes, yes it is. Then you die.

Well you got a bunch of "error has been repaired" messages, right? Run CHECKDB again. Does it still show errors? Run the repair again. Does it still show errors? Etc. etc. If you still have consistency errors, time to create a new database, migrate as much data as you can, and cut your losses. — Aaron Bertrand ♦ Feb 18 at 16:32

From February

sp_BlitzErik

i've always said we need a version of checkdb for people

2

Paul White

Ha. Have a star.

sp_BlitzErik

hooray for me

isn't it that remote queries run under serializable?

re: this question

Andriy M

1:02 PM

What's the implication of "live" in "live server/instance/DB"? That it's basically accessible online but not necessarily being accessed right now (by others)? Or that it's actually production or otherwise getting (perhaps many) connections? Or that connections are not strictly read-only?

ypercubeᵀᴹ

@AndriyM I read it as "production"

Andriy M

@ypercubeᵀᴹ Thanks, I think that would be my first guess too.

Paul White

@sp_BlitzErik I think you're thinking of the reverse process i.e. remote insert/update/delete.

Andriy M

Basically, "going live" for me is synonymous with deployment, I just wasn't sure whether I had that right or whether it's always the same meaning.

gbn

1:18 PM

Usually if the system is already live
I can deploy to a system then "make it live" later (eg blue/green web deploys or update DNS aliases or deploy a client update or change web.config etc)

Paul White

@sp_BlitzErik Ooo it might escalate to serializable for a distributed transaction. Ages since I thought about this stuff.

gbn

also... I can deploy many times to a live system, but it can be only "live" once. Or deploy to QA

@sp_BlitzErik I've met people who would fail the Turing test...

4

Turing test

The Turing test, developed by Alan Turing in 1950, is a test of a machine's ability to exhibit intelligent behavior equivalent to, or indistinguishable from, that of a human. Turing proposed that a human evaluator would judge natural language conversations between a human and a machine designed to generate human-like responses. The evaluator would be aware that one of the two partners in conversation is a machine, and all participants would be separated from one another. The conversation would be limited to a text-only channel such as a computer keyboard and screen so the result would not depend...

Transcript for

The Heap™ – Consultancy ©®