The Heap™ – Consultancy ©® - 2023-02-03

« first day (4413 days earlier) ← previous day next day → last day (464 days later) »

12:11 AM

@PaulWhite put me on the teams invite

@CadeRoux that’s good. getting pretty sick of all these new ways to die.

@ErikDarling I'm attempting to get read_only routing work by exclusively reading the MS Learn page for CREATE AVAILABILITY GROUP

Lol sorry for all the dings

7 hours later…

7:34 AM

Morning

8:07 AM

Morning

2 hours later…

10:14 AM

@ErikDarling I'm wondering if the additional information they mentioned in their comment will be added.

10:43 AM

@JohnK.N. Apparently not. They just deleted their answer.

11:01 AM

What I shame. I was eager to learn something new.....

Well. It is an option for very large tables where statistics have no impact on the execution plan chosen, or the plan is forced, or forcing hints are used.

There aren't always sensible cost-based choices to be made.

And there's sometimes not much value in trying to summarise billions of rows of data in a maximum of 201 histogram steps.

So statistics wouldn't be created and/or maintained automatically?

Not if you turn those options off, no.

Would this result in a Full Table Scan for any query?

Not necessarily, no. The optimizer still goes through its usual considerations, they're just not supported by distribution information.

Indexes still come with statistics of course.

But anyway, a lot of the time with those sorts of queries you want a full scan of a table or index and hash joins.

Of course, it all depends on the queries, but everything depends.

11:10 AM

@PaulWhite Agree.

So how do you turn off statistics? By faking the rowcount down to 0?

That is normally a database wide setting, isn't it?

Yes, it is. But you can decide to do that and take responsibility for creating and/or updating them manually.

hmm, why does dropping a temp table take 15 sec?

are you in the cloud

also, sounds like a tempdb problem

allocation contention? do you see any waits etc?

@JohnK.N. In weird edge cases, yes, you can do that too

4

A: How to prevent statistics creation on a column?

I think the trick is to create the user-defined statistic using the same name that the equivalent auto-generated statistic would take. In my testing, when the correctly-named user-defined Statistic exists, an auto-generated one doesn't get created. I'm using 15.0.4102.2 with your trace flags 2371...

11:39 AM

No wait stats - only scheduler yield but with 0 duration. Actually not sure about the cloud.

it's not Azure SQL DB or managed instance, it would have to be VM

2 hours later…

1:54 PM

@PaulWhite there are plenty of large table scenarios where you only access very specific data points and aim to avoid scans, too.

up to date statistics aren't explicitly necessary for that either of course

in any case that table should be partitioned clustered column store on a server with 12tb of ram and nothing else on it

case closed

That might be worthy of an answer to said question, as well.

they'll probably tell me they're on 2008 standard edition or something

2

Q: Update statistics on a 4 billion record table

On a SQL Server 2016 SP3 database, we have several (under 10) tables with billions of records. It's a 24/7 system with limited maintenance windows so it's essential that user impact is limited while updating statistics. What would be the proper/safe way to get statistics updated using Ola Halleng...

sql-server sql-server-2016 statistics ola-hallengren

SQL Server 2016

close enough

the limited maintenance window would probably nix that anyway

2:17 PM

anyway HAPPY FRIDEE to my FRIDEE celebrating friends

According to the TVs at the gym this morning, it's happy Michael Jordan day.

yeah that's stupid

there's a 23rd of every month

2:45 PM

it's 23/23

apparently

2/3/23

all the non-iso ways that Paul likes

sounds v. localized

In other news, AlwaysOn cluster went down, all the storage was on the same SAN

why.

Why!

where else are you gonna put it

can someone throw a dupe vote on this, i had to edit the tags

26

Q: How to add new sysadmin account when no sysadmin accounts exist

During testing I deselected the sysadmin rights for my login and now can't re-add it (because I don't have sysadmin rights). There are no other sysadmin accounts for the instance except the [sa] account. I was set up for just Windows Authentication so I hacked the LoginMode in the registry to 2 s...

oops

0

Q: SA disabled and no other user has access to master db

I have inherited an old SQL Server 2008 R2 where SA login is disabled. No other logins gives access to master db, so I cannot really gain access to administrative stuff (create new logins, change login method etc). And at the same time, I cannot enable SA login either from SSMS. How to proceed to...

sql-server-2008-r2 logins

this one

forgot to add that

3:16 PM

👍

@ErikDarling Yea that was our use cases when I worked FinTech. It's how we survived on 16 GB of Memory for a 1.5 TB database lol. Columnstore was very helpful too.

i'd hate to see what would happen to that server if someone ran an ad hoc query

3:32 PM

This bothers me: dbfiddle.uk/7hqwmjAQ

well yes

for unique indexes the clustered index key is only added as an included column, not in the key

Oh wait.

Yeah, I'm just dumb.

Thanks, Erik 😅

i have stickers of an endorsement tweet from paul regarding the matter if you'd like one

@ErikDarling Completed. Invoice on the way.

@ErikDarling Only a few developers were allowed to query the database, and they had a rule of thumb to not let anything run longer than a minute that was ad-hoc. I typically helped tune their ad-hoc stuff otherwise, so it was a pretty well controlled ecosystem.

3:43 PM

"if this query runs longer than it takes you to smoke a cigarette, you have to cancel it"

Didn't have to support any non-tech end users executing ad-hoc generated code. No ORMs involved, etc. So that was cool.

@ErikDarling lol

3:53 PM

@SeanGallardy this dog is all you

1

Q: SQL HA Cluster TempDB Version Store blocking on secondary Replica due to open transaction?

I am currently investigating a repeating error which occurs on the secondary Replica of our 2 node Alwasy on High Availability cluster. The Replica is set up with Read-Intent only because we use a separate Backup solution (Dell Networker). The Tempdb keeps growing in the secondary replica because...

sql-server availability-groups snapshot-isolation

"have you considered log shipping?"

yeah I saw that and it was like... dude session 823 was not reused a bunch of times

2 hours later…

6:17 PM

So TIL about the .WRITE() method for the UPDATE keyword. Anyone else here honestly knew about this already?

yes, anyone who read the documentation in 2008

@ErikDarling don't hold back Erik

NTEXT had those as well

well, ntext, text, and image

I thought they deprecated all those commands though

maybe not?

6:32 PM

like how they deprecated not ending lines with a semi-colon?

i.e. never to be actually decommed

@J.D. I read about it in one of Paul's blog posts.

Blew my mind.

I'm trying to remember which one.

@HannahVernon Yeah, I bitch about the "eternally deprecated" constantly.

give people a year or two of notice

@SeanGallardy they are deprecated

but some items are still deprecated and not gone from 2005.

It's been 17 years

like Mirroring

6:40 PM

If people haven't updated their shit in 17 years then fuck it

agreed

but then there are some apps running at certain govt organizations that haven't been touched since SQL Server 2000

I feel like it would be helpful to have a trace flag you can enable that stops all deprecated behaviour

in dev lol

would be a great way to enforce semi-colon statement terminators

also tabs should be deprecated

and commas at the end of a line, too, while we're at it

yeah like the standard edition trace flag

dream on, dearie

I feel like Paul's going to read the transcript and have something to say

@HannahVernon 😡

it would be great to be able to turn off all deprecated behavior via connection string settings, ala DeprecatedFeatures=None

although I guess the Deprecated Features perf counter does a pretty good job of keeping track of the number of deprecated features that are being used.

SELECT *
FROM sys.dm_os_performance_counters dopc
WHERE dopc.object_name LIKE '%SQL%Deprecated Features%';

and XE has pretty good support for it to, via ADD EVENT sqlserver.deprecation_final_support and ADD EVENT sqlserver.deprecation_announcement

6:53 PM

this all sounds v. dreary

i regret reading the transcript

friday is RUINED

it's almost cocktail hour, though Erik.

i was in the middle of answering a question when it got deleted

hmpf

I hate when that happens

some ppl

7:19 PM

@ErikDarling just had this happen the other day but the site glitched and still let me submit my answer. Felt successful anyway.

@SeanGallardy Yea the method my example references was the replacement for the deprecated ones.

Curious if there's any others out there. I think it's an interesting choice in syntax.

> Hope this helps someone else and also serve as a lesson that when you contact Microsoft support, be prepared to do a lot of your own investigation unprompted and send it over to them, it really helped speed up the process. (from here)

the cheek

@J.D. not sure if you've seen this

@HannahVernon wowzers, that's a new one to me too. Thanks!

you're welcome

7:38 PM

@HannahVernon "at one point they told me that replication is not designed to replicate functions and stored procedures and to just not replicate them" - Lol.

first level support

they probably asked the OP to reboot first

lol

@HannahVernon "While you're in there, may as well add the semi-colons, too if you really want to protect your code 10 years into the future. - answered Apr 4, 2013, Aaron Bertrand". Closing in real close on 10 years, maybe Microsoft needs 10 more.

yah, it surprises me how quickly a decade can go by

For sure. Time is weird. Relative I guess? First decade after college blew by for me, but has felt much slower these days to me since COVID hit.

8:31 PM

"it really helps microsoft support if you do all the work yourself"

5

> Not sure i agree with it being called a Hotfix when you have to wait 3 months for it!

Warmfix?

let's consult the pepper chart

@HannahVernon lol, well... lots to unpack there on both sides.

user image

i'd probably give it anaheim or poblano

Anyone going to SQLbits this year?

8:45 PM

nope

if it's because you're avoiding me, I won't be there this time

i have conflicting travel for those dates

@ErikDarling PoblanoFix, I like it.

more like probelmofix

ho ho ho

merry christmas

🎄🎅

9:30 PM

What's really amazing is that all of those peppers are in just 3 species. All those bonnet peppers are the same species. And bell pepper/jalapeno/poblano/cayenne all same species

3 months is too long to even reheat it in the microwave, though

what are you a pepperologist

dr pepper over here

Dr. Pepper is my favorite drink!

have you ever had anything else to drink

I did have a garden full of peppers at the house, but it's all dead and blocked off during the renovation anyway

@ErikDarling I inadvertently had dry January, so that kind of tells you what kind of month it's been

how does that happen by accident

9:34 PM

Too much stuff going on. I don't even think we've watched one show on TV either

user image

Peppers from a time when I had an actual kitchen

that's a lot of peppers

I think we'll be back in the house in next few weeks

user image

The thai bushes put out a LOT

i need to get pepcid just looking at that

I dried a lot of that (you can see some sundried themselves a bit) and ground it and put it in a jar.

Once we get back over to other house, I have to move the kids' playhouse somehow and then totally redo the raised beds - they are all overgrown with weeds and then killed from the hard freeze. And then replant.

that sentence had me confused for a moment

i thought you meant the kids have raised beds that were overgrown with weeds

and perhaps your move-in estimate was off by a year

9:41 PM

We've been out since May 9 of last year, and it was supposed to be 6-9 months...

Or they started May 9, we had moved out at end of April. But that's about right

well, i'm glad you'll be back to normal soon

That would be a first!

I do need to walk over there and see what they have managed to get done today.

« first day (4413 days earlier) ← previous day next day → last day (464 days later) »

Transcript for

Feb '233

The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile