The Heap™ – Consultancy ©® - 2023-06-15

« first day (4545 days earlier) ← previous day next day → last day (622 days later) »

Sean Gallardy

00:01

@ErikDarling Vass

Paul White - On Strike

00:12

@ErikDarling Well, RR is just so you can view the locks more easily (and to avoid the row lock skipping optimisation)

Erik Darling

@SeanGallardy oh well too smart for me to answer.

Paul White - On Strike

@SeanGallardy What does a row even mean anyway

Erik Darling

@PaulWhite-OnStrike hmm. I was watching locks go wild on a heap + nonclustered primary key with a row lock hint earlier today.

Paul White - On Strike

Sounds wild

Untamed, even

Erik Darling

01:01

Well you know a heap is a table without a clustered index

Just FYI

4 hours later…

Paul White - On Strike

05:06

Sounds very b-tree

Zikato

06:06

Morning
Wordle 726 3/6*

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

John K. N.

06:36

morning

Paul White - On Strike

Wordle 726 4/6*

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

Paul White - On Strike

06:54

@Charlieface FYI dbfiddle.uk/xgxqOESG

I recently lost interest in temporal

2 hours later…

Charlieface

08:51

@PaulWhite-OnStrike Thanks interesting. Not surprised, so many bugs.

3 hours later…

Erik Darling

12:09

i have not done much with time zones. is there a good way to accomplish this? github.com/erikdarlingdata/DarlingData/issues/237

J.D.

12:21

@PaulWhite-OnStrike I'd assume because the table Account_Temp is not system-versioned?

Zikato

@ErikDarling SELECT GETDATE() AT TIME ZONE CURRENT_TIMEZONE_ID ( ) AT TIME ZONE 'UTC'

Erik Darling

i'm not sure that works

when i installed my sql server, windows thought it was on pacific time

so running all of those functions that i've looked at returns utc -7

but when i use the query store views, it seems to pick up on my local time

when i was looking earlier, it picked up on utc -4, which is accurate for me

'2023-06-15 07:58:13.7077709 -04:00'

just running SELECT GETDATE() AT TIME ZONE CURRENT_TIMEZONE_ID() gives me 2023-06-15 05:34:27.107 -07:00

Sean Gallardy

12:46

It depends if you're using SSMS as an intermediary or not. If you are SSMS is going to put them in your local timezone even if they are in a different one in the data, for example XE.

Erik Darling

yeah, in XE queries i do this: SELECT event_time = DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), SYSDATETIME()), c.value('@timestamp', 'datetime2'));

John K. N.

Wordle 726 3/6*

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

Lamak

Wordle 726 2/6

🟩⬜⬜🟨🟨
🟩🟩🟩🟩🟩

take that, john

sorry, I'm so used to get +4 tries that this one got to my head

Erik Darling

13:05

so i guess my question is, how is the query store gui figure out that i'm -4 when all of the date functions return -7?

Sean Gallardy

@ErikDarling GUI is running in SSMS which is running from where?

Erik Darling

the server is a vm set to pst. ssms is running from my laptop that hosts the vm, which is set to est.

Sean Gallardy

there you go

Erik Darling

well yes

but it doesn't help me get the right time zone from those functions

Hannah Vernon

Wordle 726 4/6*

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

Sean Gallardy

13:11

It's one of my peeves with SSMS

Hannah Vernon

@Lamak sweeeeet!

Zikato

@ErikDarling To be fair, I don't think that's a common use case. If the server is using a non-utc time, then all the other metrics will be in that timezone. So for example you have perfmon CPU graph and see a spike and want to investigate that window in Query store

John K. N.

@Lamak Congratulations

@Lamak ask Hannah about that

Lamak

yeaaah...it was my first 2 tries wordle

John K. N.

I so much want to vote and clean up....

-1

Q: Database in recovery mode

My database is entered in recovery mode due to power loss while executing query! I have tried a lot of queries but i am unable to resolve that problem

server slq-server-2019

Zikato

13:15

Even shorter version
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'UTC'

John K. N.

UTC shoud be UTC, no?

Zikato

Yes, but it's not UTC
https://learn.microsoft.com/en-us/sql/t-sql/functions/sysdatetimeoffset-transact-sql?view=sql-server-ver16

Erik Darling

@Zikato are the servers you work on in the same time zone as you? 😉

Zikato

@ErikDarling Either they were or I was using the server's time zone for everything

John K. N.

Ok. What I meant to say was: Use UTC, for everything.

Zikato

13:19

Or you can just add an extra parameter for user to pick their own timezone 🤷‍♂️

Erik Darling

ugh

Erik Darling

13:41

okay, so date searches need to be converted to utc, but display times need to be local

and display needs an optional parameter for a user specified time zone

Sean Gallardy

Why not have two items, one shows it in local and one states utc?

@JohnK.N. I've heard AI will fix it all

Erik Darling

why would i need both?

John K. N.

@SeanGallardy 🤣🤣

Zikato

I want to point out that Erik has some SQL top minds working on a request by some rando, but when I (a trusted friend) requested a simple change in WhoIsActive several months ago and prepared automation in exchange, it's still in the backlog.

John K. N.

13:58

Hypothetical Question: If SE were to spiral into oblivion, which medium would we use to meet up again? And I'm not talking about Whoopi Goldberg.

Lamak

@JohnK.N. that's an old reference

Zikato

@JohnK.N. Someone is on topanswers.xyz/databases, I'm also on the SQL Server slack

4

But Twitter imho did worse and people are still there

Charlieface

14:33

@ErikDarling Which is wrong, it's not adjusting for DST when relevant

@JohnK.N. Looks like that's already happened, the sites are down again

Sean Gallardy

@ErikDarling Aren't you displaying this to the user? Then the user can use whatever they want.

Erik Darling

@Charlieface yeah, like i said, i don't do a lot of work with time zone stuff. if you have a fix, let me know.

@SeanGallardy okay, but i need a reasonable default, which for most people is their local time.

Hannah Vernon

@Zikato my procs that return details from the XE sessions I've set up all have a parameter for time zone; with a default value because we happen to be in a single time zone.

Sean Gallardy

@ErikDarling Then I would default to server time, personally

Erik Darling

@SeanGallardy that's what i'm doing. you suggested both server and utc. weirdo.

Philᵀᴹ

14:44

All the cool kids use Unix Time

Erik Darling

all the cool kids don't know what unix is

Hannah Vernon

hey @Philᵀᴹ it's been a while!

Philᵀᴹ

Hey. How're you?

I tend to annoy people on product specific slacks these days, forget to come here :)

Hannah Vernon

pretty well thanks, aside from all the drama from Stack Exchange 🤦‍♀️

how're you doing?

Sean Gallardy

@ErikDarling I mean, yeah I'm super weird, this shouldn't be a surpise

Philᵀᴹ

14:46

I'm alright, mustn't complain! Tried to escape from Oracle, but still use Oracle. Larry just won't let go

Sean Gallardy

@Philᵀᴹ Until 2038

Hannah Vernon

@Philᵀᴹ he's got the mo' money blues

Philᵀᴹ

I wrote a video streaming site for DJs too, that was fun

Hannah Vernon

I am just in the process of ditching a very expensive DB2 instance by moving the schema on there over to SQL Server

@Philᵀᴹ cool!

Philᵀᴹ

I see SO has basically gone to shit though. Shame

Hannah Vernon

14:48

pretty certain IBM hates me, but that makes me happy anyway

@Philᵀᴹ yeah, they've handled another crisis extremely poorly

handling

Sean Gallardy

@HannahVernon 😂😂😂😂😂

Hannah Vernon

anything I can do to give them the finger is worth it

I mean aside from creating the first truly open PC, what have they done for me lately

Philᵀᴹ

OS/2 Warp was alright

AIX, not toooo bad

DB2, trash

Hannah Vernon

@Philᵀᴹ that's a good point, OS/2 was a fun platform that worked far far better than Windows of the day did.

and AIX I can respect a little, I suppose.

Charlieface

@ErikDarling Was going to suggest AT TIME ZONE CURRENT_TIMEZONE_ID() but that's only available on newer versions

I would be inclined to either leave the timestamp as is without adjusting, or to adjust to UTC. I don't understand, is it not at server time anyway?

Hannah Vernon

14:57

re the sites being down, apparently they broke the database.

Sean Gallardy

@HannahVernon If only they had a community...

Hannah Vernon

lol, not to worry Beaker is on it

something something about running out of int values

Josh Darnell

The status site is also down?

Oh it's back.

Erik Darling

@Charlieface you're asking me why xe doesn't store times in local server time?

J.D.

15:16

@SeanGallardy mine is still the lack of commas on tooltips lol

such an easy problem to solve. 👀

Hannah Vernon

what is it they say, ahh yes, the devil is in the details.

J.D.

...and we don't go in there

Charlieface

@ErikDarling So what time zone is it?

Hannah Vernon

I see who doesn't read the transcript

3 hours ago, by Erik Darling

when i installed my sql server, windows thought it was on pacific time

Erik Darling

15:40

@Charlieface utc

Charlieface

@ErikDarling UTC is universal oddly enough so it figures. I think that's actually the mostly correct decision to make, although it would have been better to just use datetimeoffset.

Erik Darling

15:55

@Charlieface not until 2016

xe has been around since 2008 or so

Charlieface

16:27

@ErikDarling 2008 introduced datetimeoffset IIRC

John K. N.

17:05

@Lamak go figure 😀

Erik Darling

@Charlieface oh! yeah, you're right

Josh Darnell

18:01

@SeanGallardy I remember the first time I encountered the behavior - I thought I was losing my mind.

Sean Gallardy

dude, for sure

Josh Darnell

I was looking at some XE output in the GUI thing, decided to dump it to a file to do a closer analysis, and couldn't find the trend that I was seeing in the GUI.

...because I was looking at the "wrong" time.

Sean Gallardy

I hated it in support, they'd send me XE traces and I'd load them up.. but it'd be in EST (my timezone) but the server was some random TZ so I had to constantly do TZ math

EXACTLY

which really sucks with AGs when "server 1" is in PST and "server 2" is in UTC

meanwhile my laptop is in EST and I'm just getting super screwed

Josh Darnell

OMG yeah, that sounds like a nonstop headache.

Sean Gallardy

I ended up writing a tool to bulk load XE data into a SQL table so I could just query it... then my drive crashed a year or so later and I lost it

because SSMS is what does the TZ changes

so when I would load it into a table it was all in UTC, which was amazing

Josh Darnell

18:10

Oh nice.

I mean, not nice that your drive crashed.

Although it is kind of ironic that Mr. HA experienced data loss like that 😜

Sean Gallardy

18:38

Haha, yeah, my work laptop I don't really back up

Erik Darling

18:49

imagine finding out sean's laptops aren't in an ag for full redundancy

who can trust this man

Sean Gallardy

^^

2 hours later…

Sean Gallardy

20:37

literally wtf.

> Interprets a string as an XML value, converts the value to a JSON, and returns the value as dynamic.

learn.microsoft.com/en-us/azure/data-explorer/kusto/query/…

J.D.

20:58

user image

Almost clicked that link until I realized it references so many things I don't want to read about...

Sean Gallardy

😂

Josh Darnell

I was chatting with a coworker today, he does some of our infa stuff internally, including managing an in-house FCI. He's competent with querying, although that's not his primary job focus.

Anyway, he's never heard of Azure Data Studio, and that made me very happy for some reason 🤣

Like, all the forcing ADS down our throats from Microsoft and people with boots on the ground haven't even heard of it 🤣🤣🤣

4

Erik Darling

21:32

KB5026806 - Cumulative Update 5 for SQL Server 2022

> Consider the following scenario:
> - You have an instance of SQL Server that connects to Azure Active Directory (Azure AD).

Hannah Vernon

22:08

@J.D. a veritable cornucopia of crap tech

Paul White - On Strike

22:40

CU5 already?

Hannah Vernon

It's as if it's riddled with problems

Erik Darling

23:40

That time zone thing seemed really easy with the 2022 functions

« first day (4545 days earlier) ← previous day next day → last day (622 days later) »

Transcript for

Jun '2315

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 © 2025 Stack Exchange Inc; legal

mobile