The Heap™ – Consultancy ©®

Sean Gallardy

00:43

"Moderate"

Vérace

06:12

A chairde - Morning all!

Vérace

06:24

@HannahVernon No, not at all - that's what mods do! :-) You mentioned the word "gibberish" in your critique of my answer, which was in hindsight, harsh, esp. for relative newbies (OP 29 day member as of now, answer - New Contributor). I focused on that word but was blind to the bit at the top which you changed, which is fine by me - no probs with other bits either.

Too often I've seen words/phrases like "arrogant", "cliquish", "echo-chamber" & "inner-circle" when referring to (some of) the sites - I want this (these) site(s) to be welcoming to newbies (see here) . After all, nobody was born knowing SQL, well..., except perhaps for Erwin Brandstetter? I'll only use the words "gibberish" and "rubbish" to refer to comments here in future and not on the main site! :-)

John K. N.

06:39

Wordle 998 4/6*

⬛⬛🟨🟨⬛
🟨🟨⬛⬛⬛
⬛🟩🟩🟩🟩
🟩🟩🟩🟩🟩

Criggie

@HannahVernon "as little as possible" is also good.

Had a good one at work today - postgres DB had "lots of shit" turning up in it.

mad panic, "have we been compromised" etc etc

war room meetings, people in other timezones dragged out of bed

much kerfuffle, then one of the sysads notices that the traffic is coming from an internal IP. ....

Guess what piece of corporate-helpware was the cause....

Fricking Nessus Scanner

that is the definition of Self-Pwnage.

(If it sounds fake its cos I'm glossing over a lot of detail that is irrelevant like an elephant)

Paul White

10:31

Wordle 998 6/6*

⬜⬜🟨⬜⬜
⬜🟨⬜⬜⬜
⬜⬜🟨🟩⬜
⬜🟩⬜🟩🟩
⬜🟩🟩🟩🟩
🟩🟩🟩🟩🟩

Sean Gallardy

12:40

@Criggie Oddly common: seangallardy.com/…

Paul White

12:51

> ...maybe it’s time to rethink hitting your airplane with birds before it takes off to show how resilient it is to bird strikes. It’s stupid.

Sean Gallardy

Maybe it is? Maybe it isn't?

Paul White

At least consider throwing fewer birds less often

Erik Darling

> First of all, do not use the join predicate to filter. Filtering is the RESTRICTION operation in the relational algebrae and is not a JOIN...

another sql professional chimes in

Hannah Vernon

@Vérace awesome, thanks

Paul White

@ErikDarling seems to think SQL is a relational algebra

Sean Gallardy

13:02

Algebra gets around, lots of relations with her lately

Paul White

> Your query properly writted

John K. N.

@SeanGallardy Why is the main (default) font of the text body so small?

Paul White

From the same pro author

John K. N.

Did he Hit it first time?

Josh Darnell

@PaulWhite Properly irony'd.

Paul White

13:06

I do believe he's French

Sean Gallardy

@JohnK.N. 🤷‍♂️ I didn't write the CSS, but I can probably figure out how to make it larger.

Paul White

dba.stackexchange.com/users/199551/sqlpro?tab=profile

John K. N.

@ErikDarling a real pro, of course (intonation going down towards the end)

Erik Darling

@PaulWhite some think it's algebra, others think it's a paragraph

Paul White

Yes, French

Hannah Vernon

13:08

I was wondering what you were talking about, but now it makes complete sense

Paul White

@SeanGallardy I found it uncomfortably small to read as well. Never used to be like that, I believe

Sean Gallardy

Weird, ok let me check it out

Paul White

No

Hannah Vernon

Seens fine to me

Paul White

You're reading on mobile tho

Hannah Vernon

13:09

Yes I am

Paul White

We're not

Hannah Vernon

Maybe that's a clue for Sean

John K. N.

ugh

Chrome, non-mobile (aka desktop)

Paul White

Microfiche

Josh Darnell

Other posts have larger paragraph font. It looks like the font size was overridden in that one post.

Sean Gallardy

13:10

wow, yeah it's oddly small

7

let me go change it

Hannah Vernon

that's what she said

Sean Gallardy

good work everyone

@HannahVernon story of my life

Paul White

That got starred so quickly

John K. N.

🤣🤣

Sean Gallardy

Give the people what they want

Hannah Vernon

13:12

Moon Walker- Give The People What They Want (Official Visualizer)

►

Josh Darnell

By the way, on @Zikato's recommendation, I set up DBA Dash to monitor our SQL Server's at work. It has been pretty neat so far. So thanks, @Zikato!

Paul White

Great work, @Zikato

Josh Darnell

I guess saying @Zikato recommended it might be overstating things.

Feb 20, 2023 at 18:59, by Zikato

I don't, but I've heard good things about https://dbadash.com/

Sean Gallardy

@Zikato being @Zikato

Paul White

Sounds like a recommendation from @Zikato to me

Josh Darnell

13:16

Also, I can't believe that was a year ago.

Sean Gallardy

@JohnK.N. I changed it, would you hit f5 and see if it's better?

Erik Darling

@JoshDarnell was it annoying to set up?

Paul White

@SeanGallardy It's better for me, but I had to hard refresh to see it, even opening the link afresh

Sean Gallardy

@PaulWhite Thank you :)

Paul White

Don't thank me, thank @Zikato

Sean Gallardy

13:18

I wish I knew a software company that made a product or could make a product that would help people write documentation and keep it updated.

Hannah Vernon

@ErikDarling it was made by a wiseman and @Zikato recommended it, how bad can it be

Sean Gallardy

Paul White

TSG?

Sean Gallardy

TroubleShooting Guide

Paul White

Ridiculous acronym

Erik Darling

13:19

the query plan for the query the frenchman took offense at is quite funny

well something weird happened to chat

Paul White

Fred le Frenchie

Sean Gallardy

My chat is broken, I believe

Paul White

Could be CoPilot error

Erik Darling

Sean Gallardy

Not a very good CoPilot

Paul White

13:22

That's been happening quite frequently lately

I didn't see it this time around

Still don't see the missing messages

Sean Gallardy

I see doubles, even tried a force refresh

Paul White

Someone's using NOLOCK

Sean Gallardy

Also tried deleting, but then it just acts like it doesn't exist

@PaulWhite We're safe here, we only use READUNCOMMITTED and never use NOLOCK

Erik Darling

yes, i get page not found for the permalinks to both of my messages

John K. N.

@SeanGallardy uch etter

Paul White

13:25

It's a mystery

Erik Darling

well that's all amusing

Sean Gallardy

I eagerly await Erik sending chat invoices

Paul White

doubles all round

somewhere, an SO dev is wondering if anyone noticed what they just did

Josh Darnell

@ErikDarling No, it was fairly easy. There were some places where the setup guide could be improved, but nothing that would block being able to set it up.

John K. N.

Boeing should be doing more birds.

Paul White

13:28

So should Sean

2

Josh Darnell

Basically set the destination server (where the collected data will live), and the source servers (where you want to collect data from), and you're golden.

Sean Gallardy

I try to eat as many birds as possible

I've gotten through quite a few

Erik Darling

@JoshDarnell is it full of memes?

Josh Darnell

@ErikDarling I'm not an expert, but I don't think so. At least, they aren't prominently displayed. You can get to the memes if you want them. I just dug around and found PLE in one of the tabs, but it's sort of listing all the performance counters.

Sean Gallardy

MemeDB

Josh Darnell

13:36

Here's the "Performance Summary" tab for all the servers I'm currently monitoring.

Sean Gallardy

200ms read latency isn't highlighted

Josh Darnell

All of that seems like useful info. Except maybe IOPs.

@SeanGallardy And 10.1 is 🤨 Yeah, that's weird.

@ErikDarling if you're interested in seeing anything specific, let me know!

Vérace

@SeanGallardy Algebra - could I have her number? :-)

Sean Gallardy

@Vérace I'd give it to you but she can be a bit irrational

Vérace

Frédéric Brouard - his website is hilarious - "PostgreSQL ayatollahs"! I suppose that would make me a member of the Republican Guard at the very least...

Erik Darling

13:51

@JoshDarnell is there anything for query performance? blocking? deadlocks?

Vérace

@SeanGallardy If she "gets around", I don't care!

TylerH

Hi all, if I have a question about SSMS connections to SQL servers being blocked by firewall/network settings, is that something I can ask about troubleshooting here on DBA.SE? Or should that be asked on Server Fault or somewhere else?

John K. N.

1

Q: Unable connect to SQL Server on SSMS and Got error 10054

I have an issue with connecting to a server on SSMS SQL Server. I got this error 10054 when i tried to connect : SQL Server error 10054 =================================== Cannot connect to <IP>. =================================== A connection was successfully established with the server, but t...

I'd do a search first. Depending on your issue there might already be an answer.

If your situation is unique and contains enough information, then you can always ask on dba.stackexchange.com

Paul White

@TylerH It's on topic, but you'd have to write the question so it is immediately answerable without 1373878378247 back-and-forth comments. Those don't work well as Q & A. As John said, in other words.

TylerH

Thanks both--yeah, my situation is a little confusing. We have SQL servers configured that work fine (e.g. can be connected to) from Windows 10 machines. But new Windows 11 machines in our env are locked down and then opened up as needed via whitelisting policies (rather than the inverse for Win10 in our environment where we locked down specific things), and I can't connect to some SQL server environments from SSMS in Win11, with a connection error (MSSQL Server Error 5 - Access is denied).

I will definitely search the site before asking (not a stranger to stringent curation on SE ;-))--it's been frustrating as most threads on the issue cover scenarios where no machine can connect to the SQL Server due to it being new or incorrectly configured

Paul White

14:05

Be sure to include the SSMS version and full error messages (including state number)

And anything pertinent about the SQL Servers, including any listener setup

Sean Gallardy

@Vérace Well some of the moves in higher dimensions is quite amazing

Paul White

Someone will suggest running SSMS as an administrator

John K. N.

Firewall Rules can be a thing in a corporate environment on corporate clients. (Advanced Firewall Settings)

Connection strings are another issue. (FQDN)

Sean Gallardy

I would suggest, depending on the error, a packet capture from source and destination

Paul White

Access denied is rather generic

Sean Gallardy

14:12

I'll see you all later

Paul White

I had that today too, with a 3 minute estimate

John K. N.

Don't connect to just YourServer, but instead use YourServer.CompanyDomain.CompanyTLD (e.g. server.company.com)

Paul White

I dread updates now even more than before due to the chirpy Win 11 messages

TylerH

Yep, all of these things I have tried. I am also thinking packet inspection or traffic inspection via Fiddler or something might be the next necessary step

John K. N.

7

Q: What are valid connection strings for SSMS login box?

What are valid connection strings for SSMS login box? Every once in a while I will have to connect to an instance of SQL Server with some specific configuration setting or in a DMZ where no host names are available or I'll have to connect to the Dedicated Admin Connection (DAC) via SSMS or SQLCM...

sql-server ssms connectivity sqlcmd

shameless plug

TylerH

14:17

:-)

Josh Darnell

@ErikDarling There is query performance stuff - an object execution page that lists procedures and functions and their execution characteristics (duration, cpu, frequency)

And a "slow queries" page that splits out queries by duration and CPU and lets you drill into their details.

Paul White

UI design has really come a long way over the years eh

Josh Darnell

I'm surprised I don't see anything about memory grants in either of those pages.

There is something about blocking too, but not deadlocks as far as I can tell.

@PaulWhite GRIDS AS FAR AS THE EYE CAN SEE

Oh, memory grants are in the "Running Queries" page.

If you click on the rows here, it takes you to what is essentially the sp_WhoIsActive output for that time period.

Someone is not parameterizing their SQL.

Paul White

Ooo a pie chart

TylerH

When including the full connection error (including the state), should I include the full "program location" stack trace?

Don't want to throw in an extra 100 lines of code if it's just noise

Paul White

14:30

No

John K. N.

If possible in text form, instead of images.

Josh Darnell

@PaulWhite Can't ALL be grids I guess.

John K. N.

Any stack charts in there?

Paul White

There was one earlier for waits

John K. N.

Oooh, yesssss.

TylerH

14:36

OMG, I must need more coffee. In double checking the named pipes setting on the server, I saw it was disabled; enabling it and restarting the SQL Server service lets me connect on Win11 now. Though I'm curious why I was able to connect to it for years on Win10 without issue. I could have sworn it was enabled yesterday when I checked...

Paul White

rubber ducking ftw

Vérace

@TylerH Just as a matter of interest, why not Wireshark?

TylerH

indeed

@Vérace I'm not really a networking guy, so no reason. Fiddler is just the only thing I've used (and in an extremely limited capacity). I don't even know if Fiddler and Wireshark are complete overlaps in terms of functionality, to be honest, or if they do somewhat different things

Paul White

Neither checks named pipes on the server, as far as I know

Josh Darnell

That's wild, and now I'm confused. Why would named pipes matter in that scenario? I know pipes support remote communication, but I thought TCP got used by default when connecting to a remote SQL Server.

Paul White

14:47

Sounds like a question for Server Fault

Vérace

@TylerH Me neither - when I ask a question about it, it's "monkey is told, monkey does" - betimes I have vague glimpses through the mist of all that, but they are far too fleeting! (for a deep understanding)...

Josh Darnell

@PaulWhite -_______________-

Paul White

HtH

John K. N.

@TylerH I never use Named Pipes and on checking a recently installed SQL Server 2022 instance I found that the protocol is deactivated in the SQL Server Network Configuration settings for the Network Protocols.

@TylerH It could be that the SQL Server Management Studio is using a fallback protocol (Named Pipes) on the Win11 client, because it is unable to connect via the TCP protocol. (hunch)

This could turn out to be quite an interesting question.

Paul White

15:17

> Generally, TCP/IP is preferred in a slow LAN, WAN, or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, ease of use, and configuration options.

docs.microsoft.com/en-us/previous-versions/sql/sql-server-2016/…

John K. N.

You crafty little you, you.

Lamak

Wordle 998 5/6

🟨⬜🟨⬜⬜
⬜🟨⬜⬜🟨
⬜🟩🟩🟩🟩
⬜🟩🟩🟩🟩
🟩🟩🟩🟩🟩

Sean Gallardy

15:32

@JoshDarnell depends on client protocol configured order

Paul White

I suppose it's a personal thing, but I've always found named pipes to be less trouble

Sean Gallardy

Imagine Anonymous Pipes

Hannah Vernon

Someone asked me yesterday if we had monitoring for our SQL Servers. I had to then tell them about how incredibly amazing and wonderful the SolarWinds monitoring that we use is. They laughed.

you can't really hear the sarcasm in my voice when I said that, but they did.

Paul White

It's a funny name for a company

Hannah Vernon

funny I was just thinking that too

Paul White

15:43

A thing that could wipe out all the electronics on earth one day

Hannah Vernon

Better than Uranus Winds I suppose

2

Paul White

Yes

Hannah Vernon

I wonder how many people actually use column sets

I've never seen it in the wild

Paul White

xml addicts only

TylerH

@JohnK.N. I thought it could be a TCP issue, but I added a firewall policy to allow all TCP/UDP traffic (first it was port 1433 and 1434 for TCC/UDP specifically, then all ports for the SSMS application). But... not being a networking/security guy, I don't know if that actually worked/was applied correctly

of course, when I test it in PowerShell with test-netconnection, the TCP connection attempt does fail, so maybe you're right

except it works on the Win10 machine, and the TCP connection attempt fails there, too... or, at least I thought it did

Erik Darling

15:56

solarwinds123

Paul White

ha ha ha

that's a blast from the past

Hannah Vernon

no kidding

Erik Darling

i was considering doing a run of solarwinds123 t-shirts and sticking a box of them by their booth at pass

i bet a bunch would get handed out before anyone caught on

Paul White

you're such a jerk

Hannah Vernon

LOL I would have loved to have seen that

Erik Darling

15:57

marketing people being the ultimate muppets

or something like "solarwinds, easy as 123"

Paul White

subtle

Sean Gallardy

I don't get it

(the solarwinds123 joke)

Tell me it wasn't a password

Paul White

@SeanGallardy It was

Erik Darling

you don't remember their huge security breach from a few years ago

Sean Gallardy

I remember there was one, but I didn't look into it

@PaulWhite ... I ugh... wow.

Paul White

16:07

> Another interesting development in the search for how attackers could introduce the supply chain compromise was the discovery of a compromised SolarWinds password existing on a private Github repository from June 2018 to November 2019. An intern working for SolarWinds had set the password solarwinds123 on an account that was interestingly granted access to the company’s update server.

specopssoft.com/blog/…

> It has not been made evident whether the breached password played a part in the supply-chain attack affecting some 18,000 businesses with the compromised version of the Orion security platform. However, it is difficult to dismiss the possibility of the compromised password playing into the supply chain attack at some point.

Erik Darling

that intern is definitely an extern now

4

Paul White

Ultimately, it doesn't even matter exactly how the breach happened
Everyone thinks it was the "solarwinds123" password

Except Sean, anyway

Sean Gallardy

I don't even know what to say

Time for us to make a better monitoring system with a much stronger password, something such as P@ssw0rd1

Erik Darling

password can only be 8 characters long

Sean Gallardy

Take the 1 off then

Erik Darling

16:12

password may not contain any special characters

Sean Gallardy

password it is!

Erik Darling

password may only contain the word password

Sean Gallardy

VSecure could be another option

Erik Darling

see above

Sean Gallardy

wompwomp

Erik Darling

16:17

password may only contain the word password

Paul White

Except in France, where "lepassword" is acceptable

Erik Darling

it is writted

mustaccio

16:46

it is wrotten

Hannah Vernon

17:28

ok, folks, I need a list of awesome songs to code by. Any suggestions?

Erik Darling

probably just a shitload of depeche mode

Hannah Vernon

17:44

I feel like that is not a bad suggestion

might get old pretty quick though.

Erik Darling

it hasn't gotten old in 40 years, why would that change now

i'm taking a stab at sampling perfmon counters, if anyone wants to run it and yell at me about what's wrong

TylerH

18:12

@JohnK.N. Looks like your hunch is correct. This answer suggests it is specified to work that way: dba.stackexchange.com/questions/318855/…

Given that that question and this question both exist (with answers), and the nature of my problem was due to TCP port 1433 being blocked but enabling Named Pipes being a workaround, I'm not sure whether there's any value in posting mine--it could just get closed as a duplicate of those two together

Hannah Vernon

18:38

@SeanGallardy - I don't mind asking this on main but perhaps you could confirm what I'm thinking? I had a colleague ask about suspending data between the primary of a Distributed AG, and the forwarder, for a period of a day or so. I think the only way to "suspend" a Distributed AG is to set the role for current primary node of the Distributed AG to "secondary", then when you want to resume data movement, you need to do a FORCE_FAILOVER_ALLOW_DATA_LOSS. Something like this:

    /* suspend data movement, typically in preparation for a failover */
    ALTER AVAILABILITY GROUP [SQLDAG]
    SET (ROLE = SECONDARY);


    /* "undo" the preparation for failover */
    ALTER AVAILABILITY GROUP [SQLDAG]
    FORCE_FAILOVER_ALLOW_DATA_LOSS;

I tested it, naturally, and it seems fine. I just want the tiniest confirmation that I'm not missing something obvious.

FORCE_FAILOVER_ALLOW_DATA_LOSS seems drastic, but I don't think it is since there is not really any data loss.

Sean Gallardy

19:07

@HannahVernon I can't say I've ever had someone ask me about this or figure a need to do it

Why not go to the forwarder and suspend db movement on the databases?

I'm not sure if that'd even work

Hannah Vernon

19:40

its a curious one for sure

I've never wanted to do it before either. My colleague that is doing it is trying to figure out a way to comply with stupidity from mgmt.

Imma test it over a day to see how badly it kills my Distributed AG

Erik Darling

if only there were a way to sneaky apply differential backups to stupid ags to help catch them up

log shipping once again supreme

Sean Gallardy

The role change is internal to that instance, it may affect repl (CDC included) and potentially things like log reuse

I've never really thought about it, trying to think what other items might interact with that specific check

I can tell you it 1000000% was not tested

Erik Darling

isn't that just assumed

Sean Gallardy

shots fired

Zikato

20:23

Did someone mention me?

Hannah Vernon

@SeanGallardy I can also tell you that it takes the primary AG databases into a state where they can't be read, so that's a bit of a deal breaker

its an effective way to disable access to your databases :-)

Stressed Out

►

I mentioned @Zikato

ooops I did it again @Zikato

Imagine Dragons - Whatever It Takes (Official Music Video)

►

Josh Darnell

21:56

@PaulWhite This was an interesting read, thanks!

@Zikato 🙋‍♂️

J.D.

In case anyone wants to tell me why my thought process sucks: A question on queue tables and race conditions. :)

Hannah Vernon

for the record, this is the error I got when attempting to pause the Distributed AG:

> The target database, 'XXX', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

@J.D. you got lmgtfy'd by nbk

also, I smell a queue

J.D.

@HannahVernon lol it doesn't even answer the question.

@HannahVernon Yup, and we all know what that means. :D

Hannah Vernon

@J.D. naturally, it's a comment, lol

J.D.

Yuppers lol

Paul White

22:05

Was

J.D.

@PaulWhite heh, thanks.

Paul White

@HannahVernon Everything about AGs is designed to reduce availability

J.D.

I guess the 5-second version of what I'm trying to ask, is if my adds and removes are atomic, and the data is self-contained in the instance of the process that's working on those rows, is it still possible to have race conditions with concurrency.

Hannah Vernon

@PaulWhite so far so good then

> application process crashes in an unhandled way, leaving SerialIds stuck in the queue table

this seems wrong. Shouldn't the application just handle whatever is next in the queue table when it starts up?

I mean it is a queue after all

that way you could run multiple apps and it would be no problem. If one crashes, just start another and it does the work remaining in the queue

Paul White

I always imagined you listened to Another One Bites The Dust by Queen on loop

As you destroy SQL Servers

Hannah Vernon

22:14

hahahahahah

Hannah, Destroyer of SQL Servers

I like it

Paul White

I haven't read the question, but being atomic has nothing to do with being serialisable.

Maybe I'll read it later

I don't think I have ever answered a question on mobile

Hannah Vernon

I recently read something about using OUTPUT like that for a queue table. If I remember correctly, it can have concurrency problems, but I'm probably imagining it

presses the destroy button

Paul White

They should probably disallow answers from mobile devices

Hannah Vernon

answering on mobile is purely terrifying

Paul White

Remus Rusanu used to have a long rant about queue tables and all the ways people tried to be clever with them

I think he used to recommend DELETE with an OUTPUT clause but I might be misremembering

Josh Darnell

22:24

Another good day in 2-core server land.

Yano_of_Queenscastle

@JoshDarnell Numbers go up, that's good, right?

Paul White

@J.D. @HannahVernon rusanu.com/2010/03/26/using-tables-as-queues

Hannah Vernon

@PaulWhite perhaps this, which does indeed show that

hah

Josh Darnell

@Yano_of_Queenscastle I like it. That's good spin. "We're experiencing consistent growth."

Paul White

Could you put that data in a grid instead?

Failing that, a pie chart?

Josh Darnell

22:29

Sad to say no

Best I can do is a line chart.

Paul White

Something for a future enhancement, perhaps

Josh Darnell

I'll file an issue.

Apparently there is an existing one for the lack of deadlock details: github.com/trimble-oss/dba-dash/issues/67

Erik Darling

23:39

Building Reusable Queues In SQL Server Part 2

Deadlocks seem like monitoring tool v1 territory

Weird

Transcript for

The Heap™ – Consultancy ©®