The Heap™ – Consultancy ©®

Erik Darling

00:42

WITH regex AS

ypercubeᵀᴹ

00:54

@Vérace dbfiddle.uk/R_A6I09J . Using DISTINCT as it is the theme of week on the Heap™ ;)

Erik Darling

nice formatting

Erik Darling

01:10

live query plans are so terribly unreliable

Paul White

Barely alive plans

Erik Darling

like what.

what what.

Paul White

01:26

What seems to be the trouble

On mobile

Erik Darling

timing assigned to operators strangely

oh this is all a mess

i don't know why i bother

Paul White

Perhaps adding a DISTINCT would help

Not too late for a showbiz career

Erik Darling

with my luck someone would ask me to develop a sequel and i'd end up in prison

Paul White

🤣

I hear the army is recruiting

I wonder if there's much of a Twitch market for competitive query tuning

Though I think it's compulsory to wear a smart watch

So that's out really

Erik Darling

01:43

hm

i don't watch enough twitches to say

i'll put my dumb watches up against a smart watch any way

Zikato

04:47

Morning

Not gonna lie, I would watch competitive query tuning.

Vérace

05:39

A chairde - Morning all!

John K. N.

morning

Vérace

@Zikato What about Olympic Query Tuning? It'd certainly be better than watching effing synchronised [swimming | diving | snowboarding] (note use of regex!), BMX, dressage, artistic swimming, rhythmic gymnastics, breakdancing or whatever stupid sport they're going to introduce next! I can't believe that they have room for 5 cycling events - counting track as only 1 event!

They have keirin (motorcycle pacing!) and no room for cyclocross in the Winter Olympics! The Czechs are good at that, aren't they?

@ypercubeᵀᴹ Nice - my own humble effort is on the way - and not a DISTINCT, CTE or regex in sight! Well, to be fair, I didn't want to annoy His Holiness with a regex!

Zikato

@Vérace No idea. I don't follow sports ever since I didn't have to. But I would watch tuning Olympics

I like CTEs. They get a bad reputation because of their misuse.

John K. N.

@ErikDarling If you run a SQL Server instance on 512 MB RAM then you might hit some issues like these. Basically everything is slow.

Vérace

05:55

@Zikato Agreed - MATERIALIZED they're little different to a subquery!

Oh, and they definitely help with legibility!

Vérace

09:12

@ypercubeᵀᴹ - Le Beaujolais Nouveau est arrivé! It's about that time of year :-) But seriously, I've give my own answers to that question! I see you've deleted yours - I was going to put it in as a community wiki - maybe when it's more polished? The fiddles aren't with my answer yet - tried tidying up and messed them up! Should be back up and running shortly!

Busted a gut on these answers - first time I've used GIST indexes - look promising - at least from a performance point of view! I would really appreciate any input you may have - on table design, indexing and/or the query itself! And that goes for any of my fellow Heapers!

ypercubeᵀᴹ

@Vérace I didn't want to delete it. I accidentally posted a 2nd instead of editing the 1st, so I just then deleted the 1st.

Paul White

09:33

morning

ypercubeᵀᴹ

@Vérace why this?

('[' || st.start_tz::TEXT || ',' || st.end_tz::TEXT || ')')::TSTZRANGE

wouldn't this do the same?

TSTZRANGE(st.start_tz, st.end_tz)

Vérace

Thaks for that - I tried this - I'll tidy it up when I have a moment!

My code is incorrect - it was correct - I'm deleting my answer. Last time I try modifying fiddles while clock-watching for an imminent bus! I'll restore it this evening - with working code!

ypercubeᵀᴹ

09:53

don't delete it man. just edit later

(and sorry for the "man". no idea what pronouns you use. we have to be careful these days ;)

@PaulWhite is there any way to completely eradicate my 1st answer - or "merge" somehow with the 2nd?

Paul White

@ypercubeᵀᴹ Unfortunately, no.

ypercubeᵀᴹ

we can ask Aaron ;)

Paul White

Well, an SO developer could do it directly in the database, but they won't

Heh yeah

Anyway, it's only visible as deleted to >= 10k users

You can edit the text down to a minimum size if it bothers you

Stupid bug

Zikato

10:21

I've tried my luck and asked a question on the main site instead of chat

Paul White

falls over

Zikato

10:38

I'm prepared to be disappointed :)

Vérace

@ypercubeᵀᴹ "Man" is fine - I use it all the time - "Thanks man...", "Oh man.." - I even use that with women! I've even heard women use it amongst themselves!

Hopefully the code will be back up shortly - I've had to backtrack a bit - ya lives n' ya learns!

Paul White

@Zikato Disappointment is always a risk. I was disappointed by the leading statement terminator, lack of a statement terminator, and prefixed comma-spaces.

You could possibly improve the question by showing what sort of results you would ideally like to see. You might think it obvious.

Zikato

good feedback, thank you

Paul White

Otherwise, it's a well-presented question. I don't know how likely it is you'll get a good answer.

Much better. @ypercubeᵀᴹ will still not like the lack of statement terminators tho

Zikato

11:02

Can't please everyone

Paul White

Very true

Erik Darling

11:38

i run into that all the time

ditto the deadlock report

ypercubeᵀᴹ

11:53

@PaulWhite ha!

Not sure if I am more annoyed by that or the abbreviation of blocked to blcked

or maybe they mean blacked and blacker

or blicked - is that a word?

Zikato

I wanted to use bler and bled

ypercubeᵀᴹ

and Paul, thnx for the edits!

@Zikato eve and bob ?

Paul White

np

ypercubeᵀᴹ

Evil Eve blocked naive guy Blocked Bob

Zikato

Missed opportunity. Those are indeed better names. I've probably lacked fantasy on that one

Paul White

12:01

@ErikDarling Run into what all the time?

Erik Darling

@PaulWhite irrelevant statements

Paul White

@ErikDarling I thought that's what you meant by, "ditto the blocked process report"

Erik Darling

oh that should have said the deadlock report

but hey

it's very late at night here

ypercubeᵀᴹ

@ypercubeᵀᴹ says the guy who is missing a letter from his name ;)

Paul White

27 mins ago, by Erik Darling

ditto the deadlock report

I suppose the fundamental issue here is how to associate a particular lock with the statement taking that lock, when the statement (even batch) has completed

@Zikato Does you question accurately reflect a typical scenario? Ad-hoc SQL in multiple batches? Or are you more likely to be interested in the T-SQL call stack of the blocking process?

Not that I'm suggesting you would ask an XY question

Zikato

12:11

It sort of does. Usually it's an App that opens a transaction and then calls several procedures

Paul White

@Zikato Sequentially in a transaction, rather than as nested calls from one procedure to another?

Zikato

in this case sequentially.

It can also be a stored procedure that hast 10+ statements and I want to know which one caused the blocking

Paul White

So, in the case of long blocking, you want to trace the execution history of the blocker to the statement taking the blocking lock

Without logging details for every statement taking a lock, just in case it turns out to be a blocker

Erik Darling

shorter: you need to know who is active?

Paul White

indeed

Erik Darling

12:36

@PaulWhite did you experience earthquakeness?

Paul White

@ErikDarling No. I saw the reports come in, but didn't feel it myself

Erik Darling

well i hope you stay afloat this weekend

Exec_session and exec_connection have got session_id in the 1st column. — variable 7 hours ago

wisdom.

Erik Darling

13:06

@Zikato it took me a second to find the post (bad SEO obvs), but this is what i run into quite a bit: erikdarlingdata.com/…

Paul White

@ErikDarling Good to know

Perhaps the docs -> learn redirects don't work for him

DNS problems perhaps

Erik Darling

how many haps per second are we looking at here

Paul White

13:40

Leaving aside the question of how to find the blocking information, it seems like @Zikato is looking for a more complete or flexible blocked process report. One way to achieve that is to use an event notification.

CREATE EVENT NOTIFICATION ... ON SERVER FOR BLOCKED_PROCESS_REPORT TO SERVICE ...

Erik Darling

but then he won't be able to shut down sql server

Paul White

I was just typing the same thing 🤣

Z is professional, though. He would defo use a timeout.

Erik Darling

best friends club

Paul White

13:59

0

Q: POSTGRES: Change Column Data Type from numeric(18, 2) to numeric(22, 6) on a BIG Table (8 billion plus rows)

We have a very big POSTGRES table containing more than 8 BILLION rows and growing at a very high rate (30 million rows per day). Our Table is Partitioned on date. Each partition contains 6 months data except the first partition which contains data for almost 18 months. Postgres version: PostgreS...

4u

Erik Darling

i wonder why this archive database is 1tb and this production database is 9tb

Hannah Vernon

14:15

@PaulWhite I'm guessing I'll never live that down

Paul White

Jul 28, 2017 at 12:58, by sp_BlitzErik

chat never forgets

Hannah Vernon

I mean if you have to have a legacy, it might as well be a notorious one.

Paul White

14:37

BIG notorious

Charlieface

0

Q: MSSQL recovery model for POS

In MSSQL server, which recovery model can I use for the Point Of Sale? In addition, I am working at the company that imports and sells automobile parts (car parts). So there should be a lot of transaction data. The reason why I am asking this question is that I shrank the database because the sto...

sql-server point-of-sale

I wonder if POS stands for anything else....

Sorry just can't unsee some things...

Paul White

😀

Zikato

15:12

@ErikDarling While interesting, I don't see how to apply this knowledge to my question

@PaulWhite So the idea would be to sent the blocked_process event to service broker, parse the sessionIds and use the sys.dm_tran_locks to find (and store) the locks?

Paul White

@Zikato Well, yes, essentially to do the needful close to the time of the event, automatically.

I'll leave the details of the implementation to the interested reader.

😛

Zikato

I can skip the Service Broker and use XE SmartTarget instead.

I do like to shut down the SQL Server occasionally

Paul White

Sounds like implementation detail

Zikato

Thinking aloud: that would get me the locks, but I still wouldn't know which statement took them at the time

Paul White

Yes, you're limited by what's possible

So often the case

Zikato

15:20

I guess there isn't some hidden history of previous commands issued by the session that I'm not aware of.

Paul White

@Zikato Yes, you could skip the built-in feature that guarantees correct behaviour and message delivery in favour of an app running somewhere else using the Power Of JSON

@Zikato No, but in a sense, you have two problems. (1) Customizing and automating what is collected after an interesting blocking event; and (2) Collecting the information you want.

Zikato

@PaulWhite When you put it like that

It's a really good tool though. The JSON is only for settings

Paul White

@Zikato Yes, I know. I am employing humour as part of making a broader point.

architecture diagram

I do love a system with complex and non-obvious dependencies.
Yes, it's a neat idea, and no doubt well implemented.
And comes with an Enterprise Support Agreement.

Erik Darling

@Zikato it's more that there are many reasons why and situations where irrelevant looking queries appear to be blocking each other.

Zikato

right, I can see that. This ones are relevant though and we do have RCSI

Paul White

15:29

I guess the built-in tool for 'query history' is SQL Audit? Or maybe you could run XESmartTarget against an event collecting query history.

Or build a time ship.

Do something wild with CONTEXT_INFO and collect that with XE.

Consultants with creative solutions are standing by.

In short, I don't know 😀

Erik Darling

@Zikato maybe i'm not making the point well. the blocked process report is not designed to be perfect.

it's going to be hard to get what you want

learn.microsoft.com/en-us/sql/database-engine/configure-windows/…

> The blocked process report is done on a best effort basis. There is no guarantee of any real-time or even close to real-time reporting.

3

Paul White

Another factor. Is this for a general framework, or particular troubleshooting in response to a known problem?

For the latter, you could set a lock timeout in the appropriate place and log what you need to as a part of your retry logic.

Perhaps you're already running change tracking or CDC to help determine what things did recently.

Erik Darling

logging who is active to a table could likely get you more reliable results

Paul White

@ErikDarling That's just them employing CYA though. It's triggered by Resource Monitor during its routine check for deadlocks.

Rolling your own JSON solution might be more reliable, might not.

The built-in wheel might be round enough.

I bet this can be done with PowersHell or regex.

I wonder if Z is glad he asked yet.

Hannah Vernon

I know I'm glad I asked about why Windows didn't shut down without checking for Service Borker first

2

so glad

Paul White

15:40

Someday ImageMagick will finally break for good and we'll have a long period of scrambling as we try to reassemble civilization from the rubble.

Time for an xkcd, I think.

Hannah Vernon

lol

I'm currently working through getting sqlpackage.exe to understand that certain Service Broker infrastructure already exists, and cannot be created without first dropping the existing item.

its probably a me problem though

Paul White

Is sqlpackage.exe still a thing

Hannah Vernon

Azure DevOps uses it to deploy changes

so yes

Paul White

Sounds deprecated

Is DevOps still a thing

Erik Darling

16:02

well redgate is still open so yeah

Paul White

I heard their logo has a bug

Hannah Vernon

@PaulWhite probably a fair number of people would contend DevOps is the thing.

Paul White

Perhaps a new thing will be along shortly

Hannah Vernon

16:21

new thing = probability 1

shortly = probability approaching 1 the longer you wait

ergo I concur, even if I use Service Broker.

Erik Darling

16:44

whenever something works easily i'm worried i've overlooked something terrible

4

dezso

@ErikDarling you have

Erik Darling

gist.github.com/erikdarlingdata/…

dezso

and when it doesn't work, you have just as well

ypercubeᵀᴹ

20:16

github is half dead githubstatus.com

@dezso about "add the new column with the desired data type". Perhaps you can add with a "default null" as they want minimal downtime.

Paul White

20:36

@ErikDarling Multiple row variable assignment?!

SELECT TOP (1000)
    @NextBatchMax = aia.id

Sean Gallardy - Mostly Retired

21:27

@ypercubeᵀᴹ The greatness of the cloud.

Erik Darling

21:49

@PaulWhite u mad

Paul White

22:45

Little bit yeah

In a nondeterministic sort of way

Erik Darling

23:20

Primary key with order by?

What am I missing about determinism?

Aside from a serializable updlock hint I suppose 🫠

Erik Darling

23:34

You can blame a Canadian for putting bad idea in my head

Paul White

Do you aggregate strings using variable assignment?

SQL Server doesn't guarantee which value will be assigned even with order by. Wrap it in a top (1) with order by

You're so Quirky

@ErikDarling I saw that follow-up post. Didn't seem to have a lot of content

Maybe my 🇨🇦 expectations are set too high

Despite the recent Service Broker adjustments

Inflation I guess

mustaccio

23:55

0

A: How do I .bat log into isql automatically and run query?

thanks for comments. i found out and it's working. With that I will be able to open many doors. The problem was in the syntax. bye ;D

Probably should move to home improvement. Or lifehacks?

How to bat your log to open many doors

Erik Darling

@PaulWhite only in scalar functions 🤾

Paul White

wat

Erik Darling

Doesn’t wrapping it in a top 1 not get you the top 1000?

I guess you could add 1000 in the where clause

Hard to tell with that accent of yours though

Transcript for

The Heap™ – Consultancy ©®