The Heap™ – Consultancy ©®

5:55 AM

How to get a date and time from a SQL Server RowVersion value with T-SQL

girls_laughing.gif

6:12 AM

What

6:48 AM

🤡

7:04 AM

see also sys.dm_tran_commit_table

7:19 AM

Morning

8:55 AM

@J.D. regarding that estimate you have "That's because B-Tree's have an O(log(n)) search time complexity. That means if your table has 1 billion rows, in the worst case, it would take log2(1 billion) = 40 to find any subset of the data. If that table grew to 1 trillion rows, log2(1 trillion) = 50 to find any subset."

I believe the branching factor of b-tree indexes in most DBMS is even higher, so the numbers (40, 50) are even lower. I can't remember where I read it (might some article from the "Queen of Indexing") that common b-tree levels for tables with billions+ rows are between 5 and 8.

John K. N.

9:11 AM

Morning

1:12 PM

Ghoul evening

👻

J.D.

1:27 PM

@ypercubeᵀᴹ Correct, that's why I always qualify my statements around B-Trees with "in the worst case", nowadays. You'll notice when I talk about the generic search time complexity, I don't apply a base # to the log function, as to your point, the branching / fan-out factor usually results in even better performance in practice. But then when I give an example after, I use base 2 as a worst case scenario example, which even in the worst case, the numbers are quite small.

The simple yet amazing wonders of the B-Tree are what keep me going day after day. Good morning!

J.D.

1:45 PM

@ypercubeᵀᴹ ...Though I just realized I was off even with just log2(n), the numbers are actually smaller even in the worst case not considering branching. 30, and 40 respectively. Thanks for drawing my attention to that, lol.

1:57 PM

@Zikato I just finished Day 2.

@JoshDarnell I'll start when the kids are asleep

@Zikato Nice.

This was a good read: Cardinality Estimation Feedback in SQL Server 2022

It kind of bugs me that they don't just come out and say which hint is persisted in QS for each of those items.

> CE Feedback can detect such scenarios and turn off the special row goal optimization. If it turns out that the query is indeed faster without this assumption, we keep this change by persisting it in the query store in the form of a query store hint, and the new optimization will be used for future executions of the query.

OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'))

There should also be an Advent of Code archive from previous year, if you want more challenge

2:20 PM

@Zikato I better not haha.

I have a tendency to get sucked into stuff like that too easily as it is.

@JoshDarnell I don't believe more than 50% of that is accurate

But it has pretty graphs so

she used the phrase a priori

i can only assume that means she's getting ready to sue someone

quo vadis et tu bruto

It does read like someone initially had a lot of time on their hands, and then a deadline approached

@PaulWhite I feel so disillusioned.

Get used to it, the feeling will serve you well

2:27 PM

@PaulWhite or marketing deleted a bunch of stuff

that too

@PaulWhite I had that thought too. I read "In this blog, I will explain these model variants and what they mean in more detail." and then I looked at the scrollbar and was like "that's not nearly small enough."

honestly, they seem so scared to be actually specific about anything these days

it's not like it's ever so compilcated under the hood

explanations lead to expectations

best when users don't have those

but I suppose saying, if estimates are off, we'll try a few likely-looking but still petty conservative hints to see if things get better or not. You could do this yourself, but you're too dumb to understand...wouldn't go down well

My suspicions were aroused when it started off by talking about only having one CE model

2:31 PM

a better article would have been examples of queries that benefit and the feature improving their runtime metrics

instead we get a bunch of legally blonde hokum about theatre patrons

Then people would just try the various USE HINT options in advance and see what worked better. No secret sauce, AI, or cloud sales down that road

Morning

unless they're using EF

The row goal explanation is also highly suspect

The modelling assumptions have been dumb and wrong forever on that

And they only even thought about row goaling because I brought it up

You'd think they'd never used the product

But hey it's better than nothing

Like DOP feedback

And Leeloo's multi plans

they didn't start with row goals?

2:35 PM

They did not

after some genius exposed them 15 years on in query plans

It started, IMO, as an attempt to rescue the new CE project

Turns out, a lot of the complexity of the original CE was there for a reason

Who would have thought

with proper multi-column stats you wouldn't need two CEs and an AED in query store

no way to comment on that post

that sounds nice, but most users don't know how to write an EXISTS clause

so automate the multi-column stats

teehee

2:38 PM

meantime, ok so the product quality isn't that great, but the greatly expanded number of employees feel safe and valued in their roles

stats are useful for maybe the first two or three operations in a query plan
after that, it's a pretty much a guess

well yeah

but lots of downstream impact

makes me laugh when people try to fix the plan for a multi-page query by updating stats

@ErikDarling for sure there's a diminished chance of success if the root estimates are way off

but even the model pretty much abandons science after 2-3 ops

sounds like i could be a model

the new new CE

I remember a few years ago the lofty idea of pausing execution if estimates were stupid and doing a partial recompile

Honestly, if people chose better keys (or actual keys, for that matter) a lot of these issues don't show up. I write some pretty bonkers stuff and the need to use hints almost never occurs. Disk/memory is almost always the limiting factor, not something the query engine is guessing wrong at.

2:43 PM

That was an ambitious idea. Tweaking hints is not so ambitious.

@bbaird Yes, the CE model has its roots in the relational model. If you don't have a relational model (and most don't) you're relying on luck

A fair amount of the new stuff relies on people's natural expectation that newer things will also be better, through some mysterious (and undocumented) mechanisms

i feel like the only sensible future iteration of sql server has to be a tear down and rewrite to ditch all the backwards compatibility and reliance on un/semi-documented behavior

my no calls before 10am rule seems terrible when i have a call at 10am that i don't want to have before 11am

You couldn't do it. Everyone would move to a different database.

they're doing that anyway i hear

Or things that only look like databases, like MySQL

sql server DOA

2:48 PM

We've had two new sub-engines already. Columnstore and Hekaton (I'm not counting cloud nonsense). How complete are those.

There are fundamental reasons T-SQL doesn't get more improvements.

It's not only extremely hard and time-consuming; there's no money in it

Might as well fork off yet another Azure variant and sell that on subscription

they could call it something snappy like

If you want SQL Server to be more robust, Sybase is sitting over there waiting for the people with deep pockets

sql server developer edition

Not much wrong with Sybase as far as I can tell. At least it's not pretending to be everything to everyone

Meanwhile, you get to be excited about the next tiny thing Mangled Instance offers that the base product has had for 20 years

Or being able to pay a subscription for on-premises instances

(woo)

so what would get you excited to see in sql server

let's do some MARKET RESEARCH

3:02 PM

HOW ABOUT GRAPH SYNTAX BECAUSE GRAPHS ARE HOT AMIRITE?

oh god

yes drop it like it's hot

I was so annoyed with that when every example they showed could be handled more easily with a join or a subquery

there's no shortage of language/engine improvements they could make, just look to any moderately influential bloggers

none of whom asked for dog beta clusters, or whatever it was called

I need coffee - didn't get to sleep until 4:30 AM Z, then had to wake up 2 hours later.

3:06 PM

yeah I also have a sleep deficit, will probably bail soon

early goal from Portugal, looks like it'll be an entertaining game

not a nil-all thriller?

exactly

@PaulWhite dog beta?

can Portgual overcome the mighty South Korea though

@ypercubeᵀᴹ Big Data Clusters

3:09 PM

ah

launched with much fanfare

no one used it

Portugal is guaranteed to be in the next phase, so no pressure to them

retired less than one version later

Big Dog Data Cluster sounds a better name

@ypercubeᵀᴹ Still a typo after the edit, kudos, sir

3:10 PM

@PaulWhite I don't know anymore...Japan did everything to make me win zero points with their games....they won their first game against germany, then lost the second against Costa Rica, and ended winning again spain

@Lamak Probability is currently working backwards, HtH

@Lamak tell us your other predictions, so we can bet online

Some British University had an AI model for the soccer cup that had Belgium winning IIRC

@ypercubeᵀᴹ hey, Japan was my worst team, I'm actually still third among the 100 that are playing

I wasn't joking. We can make money ;)

3:12 PM

Prize pool held in GingerCoin on FTX

those are my predictions for today

Your spelling is attrocious

Corea lol

3:27 PM

Uruguay score their first goal in the tournament and they are off to next phase ;)

Exciting!

Yeah, I'm not sure why I put that they'll score 2 today

Ghana had lost a penalty 5 min earlier!

Lost? Or missed?

I quite like the idea of a team being awarded a penalty, but then being unable to find it

missed I meant

2-0

you were right on that Lamak but they may put a few more, the way the game goes

3:31 PM

Ghana have scored 2 or 3 goals in each of their previous games

Ghana 2 Switzerland 0
Portugal 3 Ghana 2
South Korea 2 Ghana 3

Ghana Switzerland? when was that?

> The 128-vCore compute size runs on Intel's Xeon Platinum 8370C and AMD's Epyc 7763v chipsets. With the new size, the databases and elastic pools deliver maximum input/output operations per second (IOPS) of 327,680 and 409,600 respectively. Microsoft says this is the highest of any Azure SQL compute size.

But what's the latency

@ypercubeᵀᴹ 17 November, apparently

@PaulWhite ah, friendly match?

@ypercubeᵀᴹ idk man I can barely spell the game's name, I just looked at Google 😀

@HannahVernon That's the price per hour

3

3:36 PM

@ypercubeᵀᴹ let's hope they decide rest now

@ypercubeᵀᴹ Googled more, yes it was a friendly

Dammit Korea

1 - 1

Corea del SUED

Exactly

Get your lawyer in invoke a priori

You've got them in flagrante delicto

Erik can prob recommend someone if needed

3:54 PM

as they say: draco dormiens nunquam titillandus

good advice

as they say: ooga-chaka ooga-ooga

4:19 PM

watching ENCRYPTBYKEY make a server basically fall over under load

@PaulWhite no doubt

4:49 PM

oh my

Korea scored

and Uruguay now needs 1 more goal to go through

@ErikDarling Which version of SQL?

2019

Many threads in parallel?

Are they doing a single row at a time or multiple?

We made a change in 2016 to add in a switchpreemptive

it's in an update query that runs single threaded

5:04 PM

oh yeah, if they don't batch that (if it's a singleton lookup) they are gonna get hurt

Korea beat Portugal and kicked Uruguay off

@SeanGallardy i had them dump the encrypt portion into a temp table and do the update off that

load test in dev clears the blocking issue

Yeah that's ways better

@ErikDarling clearly the invoice machine is not blocked hey!

I added a TF in 2017 or somewhere around there to bypass that and keep it cooperative, but you're asking for a NYS.

5:19 PM

@HannahVernon the invoices have been flowing like mothers milk lately

suweeeeet!

@SeanGallardy NYS ?

Non-Yielding Scheduler

ahhh

A: How to get exact year difference between two dates in SQL Server?

it seems odd that this was a new answer and not an edit to the exiting answer

0

I have taken Robs method, and turned it into a function, and written a test script for it. I can no longer find any mistakes. CREATE or alter FUNCTION dbo.CalcAge ( @DOB date, /* birthday */ @Today date /* either today or the day you want to calculate the age for */ ) RETURNS TABLE AS RETURN ...

A: CTE vs. temp table for batch deletes

5:50 PM

weird, they marked my answer as the answer without answering paul's comment

3

weird The performance issue in both queries has nothing to do with CTE vs. temp table. I see there is a timing difference, but hear me out a little bit. just delete In the straight delete, you spend the most time deleting from tables, not selecting from them. The waits in this query are all rela...

inquiring minds!

someone should suspend their account until they answer

7:18 PM

Ah well

@ErikDarling people are strange sometimes. It does seem like there's missing context there, but I have no idea what it is. Perhaps there was an off-site interaction

It's a curious method though. Part of me is interested in why it works

@SeanGallardy You've been doing that a bit recently. Didn't HASHBYTES get the treatment for 2022?

7:58 PM

@PaulWhite no one talks to Rob!

8:15 PM

Fair

Largest Prime Number Ever Found Is 22 Million Digits Long

Seems a bit much

8:46 PM

that's 7 years old news. 2 more have been found since then

9:15 PM

oh

my dad sent it to me today and acted like it was a big deal

weird

perils of aging i suppose

trust, but verify

unless it's Paul. Then you have no chance of verifying anyway

so what's the current largest prime number?

mustaccio

Probably the one on your invoice

optimus prime

I only know the largest even prime number

9:18 PM

@mustaccio you know the way to my heart, friend

@JoshDarnell I've solved it as well, but I'm not happy about my solution. Loads of CASE WHEN

@Zikato what ELSE would you do THEN?

mustaccio

I'd prefer to know the way to your wallet...

@ErikDarling NULL

mustaccio

Do you keep it close to your heart?

9:19 PM

@mustaccio check with my wife on that

yes, to stop bullets

is that a common occurance?

new york city is a crazy place

That's why all the superheroes live there, it's a practice ground

9:34 PM

Hm

Is Switzerland beating Serbia a surprise result?

Not on the scale of previous surprises I'm sure but it seems like Switzerland wouldn't be a top tier team

The Swiss control time

They’re unstoppable

9:50 PM

Nation of chocolate watchmakers I'm sure

Certain amount of Nazi gold maybe

Makes the best watches I hear

Could be

ok, so do I get a lenovo mini-tower (~400) just to run a Plex server because I hate Youtube music so much?

at 4pm on a Friday

@bbaird I have a Zotac for that. Been happy with it for 5 or so years now.

10:09 PM

@ErikDarling I'm running just audio so I think the i3 is probably going to be overkill, but $395 is about the cheapest I can find and cheaper than buying parts by themselves.

I suppose I could also try my NAS, but it's 6+ years old at this point.

Aw heck, this widdle 5i lenovo thingy is cuter and 30 bucks less

@PaulWhite not really a surprise

@ErikDarling the largest Mersenne prime can be seen here: mersenne.org/primes

and it is indeed the largest known prime as well.