« first day (4892 days earlier)      last day (25 days later) » 

12:00 AM
Wordle 1,073 3/6

12:29 AM
Is a duck
@ErikDarling I's prefer to be a mantis shrimp!
4 hours later…
5:01 AM
Wordle 1,073 2/6*

5:11 AM
Kermit ? No, too long
2 hours later…
7:04 AM
Wordle 1,073 4/6*

7:15 AM
8:01 AM
2 hours later…
9:36 AM
Evening Morning
A chairde - Morning all!
The scent of repression helpful moderation lingers in the air
10:04 AM
@ErikDarling Care to explain that one at great length?
The past is too uncertain
@ErikDarling You're probably right about friendo with the hash computed column but I do wonder about that row goal on the inner side of the NLJ. You might want to mention disabling that in a query hint. Optimization time outs being what they are these days.
Or a FORCESEEK hint on the Catalog_Items table, you know, normal routine.
It's also noteworthy that the DatabaseContextSettingsId values are different between the ad hoc and stored procedure executions. Possibly interfering with the use of an index keyed on a computed column. Bearing in mind the SET options stored with the procedure ANSI_NULLS and QUOTED_IDENTIFIER.
10:28 AM
I had wondered a bit about the DatabaseContextSettingsId
I bet that's it actually
> The optimizer ignores an index on a computed column for any SELECT statement executed by a connection that doesn't have these same option settings.
There were a number of odd things that I didn’t have time to address fully before I had to leave the house
Have parents here for the long weekend and they’re staying in the Darling Data corporate suite
My money's on the stored procedure having QUOTED_IDENTIFIER set to OFF
If you don't have time or inclination, I'm willing to write a short answer along those lines
10:32 AM
I thought the ANSI settings were the same across both
@PaulWhite I can get to it a bit later
@ErikDarling The setting stored with the procedure won't show up there in the plan and that section's not reliable in any case
I ignore it completely
    IsAnsiNullsOn = OBJECTPROPERTY(OBJECT_ID(N'dbo.P', 'P'), 'IsAnsiNullsOn'),
    IsQuotedIdentOn = OBJECTPROPERTY(OBJECT_ID(N'dbo.P', 'P'), 'IsQuotedIdentOn');
Josh Darn It
Why are you taking @JoshDarnell’s name in vain on Memorial Day
He arrived in the room
Probably going for a Jogh
ha ha
If you've made that gag b4, I've not seen it
10:46 AM
Just occurred to me now, don’t worry
After all these years, still finding new ways to abuse his name
Your vigilance remains in tact
It's been years since I've seen a computed column index not used due to SET settings. Was all the rage pointing that out when the feature first became available. Perhaps someone should reblog or vlog it.
Indexing a hash code is a classic example. People also used to imagine using it with xml
I can’t recall ever seeing it for a stored procedure
@PaulWhite and also with you
Wordle 1,073 6/6*

10:58 AM
ORMs are another story
@ErikDarling Probably true
People love to demo with ad hoc SQL
I wish people would stop adding hocs to things
Erik has a sub hoc kink?
Unexpected news
I wonder which command people prone to saying "unalive" use when they need to terminate a SQL Server session
My apologies to anyone traumatised by the word, "terminate" there
Insincere apologies, but take what you can get
Unquery maybe
11:15 AM
DISCONNECT would work, I guess
2 hours later…
1:10 PM
@PaulWhite I like to think they've been given the option to live on in a parallel universe.
One where queries run either infinitely quickly or they're blocked forever
1:55 PM
Thanks for the answer @HannahVernon . I came to a similar conclusion during my investigation. I'm looking for some form/source of documentation.
2:41 PM
3:01 PM
@PaulWhite updated
@ErikDarling Had any successful Turdles today?
a couple
3:42 PM
@Zikato clever clogs
2 hours later…
5:52 PM
Another interesting paper, this time by Edsger W. Dijkstra - The Humble Programmer - not a trait that I would normally associate with that profession, no more than DBAs! :-)
@ErikDarling thanks
@Zikato nice! Although you'd think auto drop would be the default
Would have been the default before 2022 you mean?
@PaulWhite nethng4u
Wow I wouldnt have thought I would have gotten the legend himself to respond! — anakata 1 min ago
6:09 PM
Their settings must be right normally, else they would not have been able to create the computed column and index in the first place.
I do find things like this somewhat odd to track down
I think there's a scenario, or used to be, when this was easy to do when generating a script of the stored procedure
It’s hard to know what’s wrong but not to know where it’s wrong
A: Strange behaviours with SET options in SSMS

Andriy MAmong the SET statements in your script, SET QUOTED_IDENTIFIER is special in that it is processed at parse time rather than at execute time. From SET Statements (Transact-SQL) (emphasis mine): Considerations When You Use the SET Statements All SET statements are implemented at execut...

SQL Server is designed to trap you
It’s quite annoying that chat doesn’t seem to let you open links in a new tab from mobile
6:18 PM

Dan GuzmanModern SQL Server client APIs set both ANSI_NULLS ON and QUOTED_IDENTIFIER ON by default. So the options will be on by default and take precedence over database SET option settings and you don't need to include the SET statements in DDL scripts. However, once gotcha is the SQLCMD utility, which ...

I suppose many databases will have at least a few modules with the wrong setting due to various past behaviours
I see a lot of vendor application databases that seem intent on defying the laws of ANSI
Cold Fusion has unchangeable settings around them. Thankfully it’s rare to see.
They must have very good reasons
6:35 PM
It’s almost like every conceivable star aligned to keep me employed
6:51 PM
this is me being a b*tch
> yeah nah we don't give out sysadmin to anyone for any purpose
Only to run PowerShell scripts we find on the internet
I hear it solves all token store problems
7:16 PM
It scooby dooby does
7:27 PM
I'm willing to compromise on the slightly inferior CONTROL SERVER permission
What’s the difference
8:14 PM
@PaulWhite the best is when they say no CONTROL SERVER is not good enough
must have sysadmin
the person who asked me said they'd need it "just for a minute or two" like that was going to appease me
one minute or eternity. may as well be the same thing when you're talking about sysadmin
1 hour later…
9:17 PM
@ErikDarling various things e.g. dba.stackexchange.com/a/65192/1192
I don't know offhand if having CONTROL SERVER skips all permission checks, or just ensures they succeed
In general, the older something is in SQL Server, the more likely there is to be an explicit check for membership of sysadmin
I’m glad I’ll never have to deal with all that
Oh, and apparently DENY is checked for CONTROL SERVER
So that answers the skipping checks question
9:36 PM
Jeez what’s the point
9:51 PM
I suppose you might have a need to grant permissions to everything except e.g. one particularly sensitive database or something
Of course, they could just grant themselves sysadmin but a lot of this comes down to trust and auditing in the end
I suppose it’s a bit like being vice president
I checked the table for column properties, CategoryHash has YES for persisted next to it. — anakata 4 hours ago
Are we going to get a separate comment for every irrelevancy here
Not that that would inconvenience a legend
Am I being called a foot
That's one reading
> I am Leg End
If it is persisted and the self join doesn’t use the index on the computed column then the only reasonable item left is the set options
Unless they’re leaving out some spectacular details
Which never happens
I suppose I could have included hinting the index and seeing if it produces an error
10:09 PM
Being persisted or not isn't really relevant. I think they only responded to that because you mentioned it.
It seemed like a good potential reason for the clustered index scan producing a filter
I realize there are others, but I’ve seen that one enough times
I see
But yeah, it's the SET option
Won’t know until they leave a comment saying that it’s not the SET option
All feels a v long way off
We’ll get there eventually
I have faith
10:16 PM
By 'irrelevant', I meant the computed column is in the index. The question is why the index isn't being used. Persisting a CC is a lottery at the best of times
Been a while since I had to persist a hash bitten column
10:35 PM
There's an argument to say hash queries should always use FORCESEEK
Nothing worse than providing a hash index and getting a table scan
Especially since you're almost always expecting a lookup
10:46 PM
I would agree with that
It’s a shame the optimizer doesn’t recognize query patterns like that more widely
Q: Adverse effects of outdated statistics in a database

udhayan dharmalingamFaced a p1 issue recently due to outdated statistics in one of our DB (~500G). The sql instance was hosted in a i3.xlarge ec2 and 25G out of total 30.5G memory was set as max memory for SQL Server.So the normal CPU utilization on the host always hover in the range of 93 to 95%. We dont run any co...

Jeez pal
Sounds like you’re in need of some reasonable rates
11:23 PM
I wonder what a p1 issue is
Might be good
Might be bad
Wordle 1,074 X/6

@PaulWhite We have P1 through P5, its a measure of priority
Never playing distracted again - last go I replayed the letter of my first line - total muppet (maybe even the frog)!
so P5 is "no impact on anything"
P4 is up to 10% of customers affected for up to 60 minutes.
Ratings are very confusing
My uber driver was v upset with his P1 rating
11:31 PM
In biology, a P4 lab is the worst - Ebola and up - suits, masks, reverse air pressure!
I worked a place many, many years ago that used a DEFCON system
I think fighter jets appeared at DEFCON 2
@PaulWhite priority 1 maybe. In English that’s sev 1.
Thanks everyone for your earnest answers
I’ve heard recently about a place so riddled with sev 1 issues they had to invent sev 0 issues
So you can imagine everything is now a sev 0 issue
Surely that will lead to sev -1
They should standardise
Everyone should
11:36 PM
They should try reseeding
Never would have had this problem if the severity score went up
Instead of down
Never would have had so many problems if they hired someone with reasonable rates to fix them
Won't get out of bed for anything less than an S7 issue
S9 needed to get me to break off from this coffee & muffin combo
Payment required in physical gold
@PaulWhite Like hurricane/tornado ratings and the richter scale ?
@Criggie Exactly so
You're just painting yourself into a corner with the upside down system
Or just name everything with a GUID, as is the trend
Ooo, a severity 0301E103-0C60-495B-AD1D-5D3223CB9AAC just came in

« first day (4892 days earlier)      last day (25 days later) »