The Heap™ – Consultancy ©®

« first day (5034 days earlier) ← previous day next day → last day (54 days later) »

J.D.

00:15

And eat steak from Ruth's Chris

1 hour later…

Vérace

01:33

A chairde -Morning all!

Hannah Vernon

It's only 2:30am - that sure is morning

3 hours later…

Vérace

04:31

@HannahVernon 03:30

Hannah Vernon

05:16

Ahhh, well good morning regardless of time zone

Wordle 1,215 2/6*

🟩⬛⬛🟩🟩
🟩🟩🟩🟩🟩

John K. N.

05:30

Wordle 1,215 4/6*

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

John K. N.

05:46

Hierarchical data (SQL Server)

3 hours later…

John K. N.

08:54

One for the MS Team

Q: Why is my transaction log full of LOP_LOCK_XACT?

I have recently experienced a strange issue with one of our sql server staging databases, whereby the transaction log and log backups suddenly grew around a hundredfold in size, causing a disk space issue within the space of a couple of hours. The server is SQL2019 and the database uses FULL logg...

4 hours later…

Sean Gallardy

13:00

@JohnK.N. Needs a repro

John K. N.

13:27

It was in the vtc queue, but I thought there might be a slight chance, that somebody knew what was up.

Sean Gallardy

13:40

The lock suggest columnstore, but the OP vehemently denies the use of said item

2 hours later…

Paul White

15:41

Wordle 1,215 3/6*

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

1 hour later…

J.D.

17:02

Let me know if this is a stupid question, a Post-worthy question, or I'm just crazy. But does SQL Server ever re-write the actual query text of a query when it generates an execution plan? Particularly I'm seeing this around a reference to a temp table in a stored procedure. The StatementText in the plan shows a MAXDOP hint, and the procedure definitely doesn't use one.

Erik Reasonable Rates Darling

are you creating an index on the temp table?

J.D.

Yes. Unique clustered, if it makes any difference.

Erik Reasonable Rates Darling

it matters more that you're on standard edition

J.D.

Seems like that's the statement text for the index creation - neato. Learn something new every day.

Erik Reasonable Rates Darling

if you pay the microsoft friendship™️ tax you won't have maxdop 1 index problems

J.D.

17:08

@ErikReasonableRatesDarling 🤙. Gotcha. Is that Microsoft's way of preventing parallelism for that operation because of our Edition?

Yea, word.

We're fixing that problem on next upgrade. :D

Erik Reasonable Rates Darling

there goes your consulting budget

J.D.

Heh, I found a way to re-architect our SQL Server stack (infrastructurely) so that the same licensing costs we were already paying, for the most part, stay the same and we end up with unlimited Enterprise instances. So that's coolio.

Btw, that's interesting that the MAXDOP 1 limit is exposed in the statement text. I would've figured it would've been a more lower level lever that Microsoft would've kept hidden from the developers eyes.

Erik Reasonable Rates Darling

they like to put it right in front of you

sounds like someone discovered vm host licensing

J.D.

@ErikReasonableRatesDarling yuppers. License the entire OSE and get real dedicated cores for our SQL Server instances instead of sharing with other non-SQL Server servers, and only getting vCores, all for the same licensing costs. Win-win.

Paul White

17:40

@ErikReasonableRatesDarling Does that also populate the non-parallel plan reason? Or just add the MAXDOP 1?

I'm certain I used to have a Standard Edition instance, but I can't seem to find it now

Erik Reasonable Rates Darling

@PaulWhite it should yes

Paul White

I only recall one for Express but not Standard Edition

Maybe I should use SE more often

Erik Reasonable Rates Darling

4what

Paul White

So I would know the answer without asking

My first thought was a plan guide because those can add hints you didn't write

Or the QS equivalent

Erik Reasonable Rates Darling

17:56

imagine dolphins using plan guides

J.D.

@PaulWhite oOo good thoughts.

@ErikReasonableRatesDarling I've tried, and very quickly turned back around.

Though to be fair, Query Store Forced Plans aren't that much better. More so they just make the management easier.

Paul White

At what point does my browser become an OS of its own

Edge is currently using more RAM than many SQL Servers I've seen in the past

J.D.

@PaulWhite In this particular plan the reason says MaxDOPSetToOne.

Very informative.

Paul White

@J.D. Thanks. I'm not really surprised. I would have been surprised by a specific licencing or edition restriction message

Erik Reasonable Rates Darling

LOLStandardEdition

Paul White

18:00

That would be a gr8 reason code

What does OSE mean?

> License the entire OSE

Erik Reasonable Rates Darling

operating system environment

probably

Paul White

That's a very convincing edit

I thought it was going to be something something Estate

Erik Reasonable Rates Darling

i edited the plan xml directly

fwiw

Paul White

Ah yeah that would do it

There's me thinking you carefully matched fonts

Erik Reasonable Rates Darling

have we met

Paul White

18:07

Hence surprise

But it's not impossible Lamak or Z have passed on some shopping skills 2 u

Erik Reasonable Rates Darling

if either of them posted that it would have 17 stars by now

Paul White

assuming they haven't croaked

hoping 4 an early death la la la

Erik Reasonable Rates Darling

there's a lot of that going around

Erik Reasonable Rates Darling

18:54

-1

Q: Can I tell a table to ignore trivial changes?

In my experience with data, unless measures are taken, ETL processes update a lot of rows to sync targets with sources. In most cases, there is no change. To keep syncing as light as possible, we come up with tricks to reduce the scope of updates. Is there a way for a table to take care of this a...

trigger etl

Uh. Okay.

Paul White

Insightful

Zikato

@PaulWhite Erik is our expert on croaking

Paul White

yes

Zikato

@ErikReasonableRatesDarling I won't rest until I'll have a post with enough stars to make a star-spangled banner

Erik Reasonable Rates Darling

Try reposting Paul quotes

That seems to do exceedingly well

Zikato

19:05

Good idea

9 mins ago, by Paul White

yes

Erik Reasonable Rates Darling

11 mins ago, by Paul White

Insightful

(removed)

J.D.

19:30

@PaulWhite Yea, Erik is right, Operating System Environment. In the context of SQL Server licensing, I guess it's a generic enough term to mean you can choose to license your SQL Server instance within the environment of the VM's OS (if you're using virtualization) or at the entire server / cluster / host level instead. At least that's the gist of what my dolphin-sized brain comprehends.

Erik Reasonable Rates Darling

standard edition is per vm, but you only have to pay for cores assigned to the vm. enterprise edition you license all of the vm host cores, and can have as many instances running on it as you want.

from there it largely becomes a game of resource distribution

J.D.

20:07

@ErikReasonableRatesDarling I think you have the option to license all of the host cores or just the VM, with Enterprise Edition. But I could be mistaken, the licensing docs I read a few months ago have already gone out my brain.

Erik Reasonable Rates Darling

most people who are installing enterprise edition are going to use the entirety of the vm host for it anyway

having a small chunk carved out for it is somewhat of an antipattern

J.D.

20:42

@ErikReasonableRatesDarling Yea, we were previously mashing up application servers with the SQL Servers on the same host, and sharing vCores, and all that fun stuff. No mas.

Sean Gallardy

@J.D. Now, imagine if you will, some nice 6th gen Xeons with tons of memory and TB of local gen 5 NVMe, hitting 100's of GB/sec throughput with latency under 1ms.

Imagine it at a fraction of the cost of a SAN

and a fraction of a fraction of a fraction of cloud

J.D.

@SeanGallardy heh, overkill for our shop. Maybe at my last gig where individual tables were pushing 1 TB big.

But we do have some blazing fast NVMes here. I think they do pump out about 1ms latency.

Sean Gallardy

Sounds right

Once you get that sweet local nvme, it's hard to ever go back

J.D.

We are an on-prem shop, so I can sleep well at night. :)

Sean Gallardy

awwww yeah

J.D.

20:50

I don't even understand how cloud is an option for anybody. I was looking at the top end of the Gen Purpose tier in Azure recently and the IOPs those drives pump out are like 1/5 of an SSD I can go pickup at BestBuy today. That was their best in the Gen Purpose tier, like wtf.

No exaggeration.

J.D.

21:03

Nothing like opening up someone else's code and seeing a comment dated pre-Y2k...

And the year dated in the comment is only 2 digits ironically...

Erik Reasonable Rates Darling

21:15

@J.D. it’s all garbage and everyone has been hoodwinked into low expectations and high costs.

J.D.

@ErikReasonableRatesDarling Yea, it's just crazy how hot that trash is. pSure I get more IOPs out of my graphing calculator.

Sean Gallardy

22:07

@J.D. Yeah... if you only saw what I see when I open up these files :D

@J.D. Imagine paying thousands a year for 1 cpu, 4GB of ram, and 60 IOPs.

Buy a low end laptop and you'll get more, built in battery backup too

« first day (5034 days earlier) ← previous day next day → last day (54 days later) »

all rooms

Transcript for

Oct15

Oct16

Oct17

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