The Heap™ – Consultancy ©® - 2022-06-29 (page 1 of 2)

Paul White

6:14 AM

WTF

Zikato

Morning

ConcernedOfTunbridgeWells

Morning all

McNets

6:54 AM

Morning

Paul White

Morning

ypercubeᵀᴹ

7:57 AM

an aircraft skeleton, from the long extinct Aircraftsaurus Rex

Zikato

I didn't know that if you zoom out far enough on Google maps, it shows you daytime/nightime

Vérace - Слава Україні

9:07 AM

A chairde - Morning all!

Paul White

10:25 AM

@Zikato Google maps or earth? I just tried it with maps in a browser and couldn't zoom out far enough

Zikato

@PaulWhite Google Maps. I've clicked on the link to Aircraftsaurus Rex from @ypercubeᵀᴹ

Paul White

Still doesn't work for me from that link. Oh well nvm

Google Earth does show light/dark, but it's not accurate to the time of viewing

ypercubeᵀᴹ

you need to click on the bottom left (Layers or whatever) -> More and check "Global view"

Paul White

ah

ypercubeᵀᴹ

to see the round earth - if you are believer ;)

but I still don;t see night/day either

Paul White

10:34 AM

that's pretty well hidden in the more... menu

also to get accurate day/night, I have to zoom one more step than the full globe

suddenly changes from a standard angle to actual day/night

my view

ypercubeᵀᴹ

ah true!

Paul White

looks pretty flat tbh

turtles not shown

Zikato

Perhaps they are racing Achilles

ypercubeᵀᴹ

heh, didn't realize they also have maps of several planets and moons as well

Erik Darling

12:07 PM

the assumption that removing indexes will dramatically improve write activity is so weird to me

for most oltp-type singleton modifications the impact is in the microseconds

i wonder if this is the same spinning disk mentality that leads to people freaking out about fr*gmentation or p#ge spl!ts

Paul White

12:28 PM

it's true that in a sensible system with sensible resources, a sensible number of secondary indexes isn't a problem

moving away from the sensible to the dumb or extreme can produce problems

unfortunately, people and clouds excel at both

also, people often over-emphasise the value of covering indexes over a point lookup

I guess I'm saying it depends

but most of the time, a handful or half-a-dozen secondary indexes don't indicate a problem in themselves

Erik Darling

i don't usually see much of an impact until you get to the 10-15 index mark

Paul White

you must work with sensible systems with sensible resources

Hannah Vernon

I think it depends in the table design, the index design, and the write frequency requirements.

Paul White

10-15 isn't super-unusual, but I'd wonder if all of them were strictly adding value

Hannah Vernon

I've seen tables where one index is one too many.

Erik Darling

12:41 PM

@PaulWhite they aren't; plenty of unused and merge-able indexes around, but they were able to keep adding on to that point without cleaning up because there was nothing noticeable happening

Hannah Vernon

And of course, tables with an index per column with 30 or 40 columns is just asking for an issue.

Paul White

@ErikDarling For sure you can get away with it, up to a point

If you're doing low-volume 'OLTP' and/or your log, memory, disk can keep up with the required I/O

Erik Darling

yeah, i just can't imagine a transactional table's existence being greatly improved by going from (likely) 1 pk/cx and 3 nc to 1 nc.

Paul White

yes you'd need to be very close to the edge for that to be important

Erik Darling

i do sometimes wonder if merging includes to make generally wider indexes would lead to more deadlock likelihood

Paul White

12:45 PM

deadlocks are a meme

as are includes

but seriously, anaemic cloud I/O can be a factor

both for log and main db

Erik Darling

right

i would like to start a class action suit against the cloud et al for that

Paul White

log governor

@HannahVernon 30 or 40? you're not even trying

but yeah indexes added to support catch-all queries are the worst
though then we're talking a hybrid workload at best, not OLTP

Zikato

If a table has over 250 columns, no amount of indexes will provide a good coverage

Erik Darling

one nonclustered columnstore index

Paul White

all you'll ever need

Zikato

12:51 PM

I dunno, I rather solve the bad design than attempt to hide the problem

Paul White

the debate reminds me somewhat of the argument that heaps are 3-4x better than clustered indexes because lookups are direct rather than requiring a b-tree traversal

Erik Darling

@Zikato lol normalization

good luck making 10 new tables and moving the data over and getting the app to use them

Zikato

Let's normalize normalization

Paul White

well you can spend a day carefully designing the right indexes and constructing routines to build optimal, and safe, dynamic SQL

Erik Darling

too much work

Paul White

12:53 PM

or you could add a NCCI

Zikato

I'd rather not encourage that behaviour

Erik Darling

if you have that kind of time on your hands and people will let you do it, god bless

Paul White

horse, water, but no drinking

Zikato

heh

Erik Darling

redesigning schema in a 10+ year old app ain't happening most of the time

Zikato

12:56 PM

usually because the tooling sucks

Paul White

Speaking as someone who has implemented quite beautifully optimal designs to support kitchen-sink queries in the past, I wouldn't bother 90% of the time these days

Erik Darling

with which part?

Paul White

I'd just add an NCCI and send the invoice

Zikato

Isn't consultants' job just to fight the fire and move on?

Paul White

2 days ago, by Zikato

If you're a startup, you don't have the money to pay experts. When you are successful, you focus on expansion and not tech debt. It's gotta take a near-death experience to make it a priority

Erik Darling

12:57 PM

oh yeah

Paul White

Z giving me whiplash

Zikato

ouch

Paul White

@Zikato not when I do it, can't speak for everyone

the beautiful design needs re-evaluating whenever someone wants to search on a new field; the NCCI just continues to work

Erik Darling

@Zikato yes; this week i turned on rcsi and used query store to force serial plans for 3 queries rather than try to fix a bunch of bad design choices and dynamic orm queries

Paul White

most of the time, it's client-driven. many want the quick and dirty fix

Erik Darling

12:59 PM

gotta stop the bleeding

Zikato

Can't argue with that

Erik Darling

i'm usually working with a few execs/managers and a bunch of tech staff watching expecting a miracle immediately

Paul White

well that's what you get for charging $1k/hr

Erik Darling

ha ha, that's just what i pay you

Paul White

oh right

> expecting miracles on the cheap

Erik Darling

1:01 PM

white privilege©

Zikato

I get that from a consultant's perspective but from employee's perspective wouldn't you want to improve the design because you're gonna stick around for longer?

Paul White

ha!

Zikato

Or do you just change jobs frequently enough to outrun the tech debt?

Erik Darling

@Zikato i hope to never find out

Paul White

@Zikato IME most employees capable of that level of work leave to become consultants so they have more freedom and pay

in theory, anyway

working for people sucks massively
the best you can do is keep the experiences short

Erik Darling

1:03 PM

most of my FTE work was supporting 3rd party apps where i couldn't redesign anything anyway

Zikato

And then those experienced consultants just implement NCCI everywhere and move on

Paul White

when it's the optimal solution, yes

Erik Darling

just getting batch mode is enough half the time anyway

Paul White

yep

Zikato

I had a few edge cases when batch mode went crazy, but generally yeah

Erik Darling

1:04 PM

like yesterday it got a query from 50 seconds of cpu time and 12 seconds duration to 1.4 seconds of cpu time and 400ms duration

Paul White

it's funny because the initial expectations were that CS storage would provide most of the benefit. in practice, most comes from RS batch mode ops on modern hardware

Erik Darling

i do like cs storage in many scenarios

Paul White

it's a bonus

but it many cases batch mode RS scan/seek is close enough

for true 'OLAP' CS is important

but most workloads are hybrid to some extent

witness all the demos people had to rewrite when RS batch mode serial queries came into being

Erik Darling

i'm sorry you must be talking about REAL TIME OPERATIONAL ANALYTICS

Zikato

Paul White

1:08 PM

it's a nice meme, but it's surprisingly tough to find a scenario where NCCI isn't truly optimal for that scenario

not just because it's quick and easy

@ErikDarling you broke my meme-o-meter

Erik Darling

ahem

should we demand hybrid indexes

Paul White

you could argue filtered NCCIs are such

Erik Darling

they don't support point lookups so well

Zikato

I should probably experiment with them more. I'm not sure about the maintenance and tables with not enough rows

If batch mode is the main benefit, there's still that trick with the filtered NCCI or dummy LEFT JOIN

Erik Darling

i just hit them with a weekly reorg with the compress all row groups option

Paul White

1:14 PM

@ErikDarling not many kitchen-sink reports look up a single row?

Erik Darling

that's generally good enough as long as you don't have massive daily deletes

@PaulWhite that's a trick question

Paul White

but it's not like you can't have a couple of RS b-tree indexes as well if you want

@ErikDarling best kind

Zikato

Thank you both for the tips. Even though I'm more on the idealistic/purist side, I'm still open-minded

Paul White

I'm an idealistic fascist dictator and I still prefer NCCI

Erik tends to favour clustered columnstore on hekaton

The turning point for me was an instance where the correct 'purist' design was slower and larger than the NCCI and couldn't cope with future changes

Plus I really hate dynamic SQL

Zikato

Why not both? Good design and NCCI? Yeah, I know…

Erik Darling

1:20 PM

lol

this guy

Zikato

The Heap™ – Consultancy ©®
Where dreams are broken

Paul White

Well a decent design is important of course

But don't make me tap the sign

Erik Darling

changing a bad design to a good design is often a months to years long process that no one wants to take on

Zikato

I still blame tooling

Erik Darling

and there's no guarantee it'll be better aside from making you feel superior for doing things right

hitting large tables with unpredictable search and retrieval patterns is still a generally bad fit for row store indexes, even with paging in place

Paul White

1:22 PM

It's funny because non-db devs are often big fans of an API

then refuse to code against one for the database

Everything's so tightly-coupled it's insane

In principle, the db design should be entirely an internal concern

code-first web devs being the worst

Zikato

Low cohesion, tight coupling

Paul White

Putting the F in EF

@ErikDarling one might say especially with paging

People do love solving the same problem over and over again

Zikato

@PaulWhite Which API do you mean?

Paul White

@Zikato Stored procedures and views for the most part

The external developer's contract with the database

Erik Darling

@PaulWhite people love having the same problem solved for them over and over again

Zikato

1:29 PM

Right, wanted to be sure.

Paul White

Rather than direct table access

@ErikDarling and will pay invoices for the privilege.

Zikato

In conversations I had, Developers don't consider it an API. And SPs can have weak contract if designed badly - changing the number of result sets

Paul White

Many things can be done badly

Erik Darling

chat interfaces for example

some of them will double bonk you when someone edits a message you got tagged in

Zikato

Duplicate voting

Paul White

1:31 PM

I see Google renamed their chat product again

@ErikDarling sounds annoying

Erik Darling

i only saw they were nixing hangouts

Zikato

It's not Hangouts anymore?

Paul White

correct

blog.google/products/workspace/hangouts-to-chat

Zikato

Apparently, it's a different product.

They discontinued the Hangouts and introduced Google Chat

Not even Google wants to do refactoring

Paul White

Always was

Erik Darling

1:34 PM

building a new product was probably easier than fixing poor design choices in the old one

Zikato

I feel disenchanted today

Paul White

Looking forward to Gmail attempting to force me to use it

@Zikato welcome to the modern world

Erik Darling

@Zikato if you want to do things right, build a new product

Zikato

@PaulWhite Shouldn't that come with alcohol?

Paul White

well mine does

Erik Darling

1:36 PM

@PaulWhite don't talk about me like i'm not here

Zikato

@ErikDarling I was at 3 companies that tried that instead of the refactoring. It's always called something like Phoenix or Lazarus. They hire double the people - the old guard to support the old platform and new ones to build the state-of-the-art.

Paul White

Huh. I just realised I don't have a bottle open

Zikato

and after 5 years it's not even at 20% parity

Paul White

the absolute state of the art

Zikato

I heard it has cloud and microservices!

Erik Darling

1:38 PM

sometimes good is the enemy of mediocre

Paul White

I hate the mediocre

Tolerating it requires significant exchanges of money

Erik Darling

mediocrestore indexes

Zikato

mediocrestore enhanced heap - meh for short

Paul White

I blame capitalism

Huge mistake

Zikato

We should hire new people to work on a new system while the old guard supports the capitalism

Paul White

1:40 PM

correct

ETA 6-8 weeks

Erik Darling

someone's taking their jacinda pills

Paul White

Never met the woman

Erik Darling

well i never met dr. pfizer

Zikato

I had to google that. I thought it was some herb

Paul White

but I hear she's a rabid capitalist

@Zikato jury's out on that

Zikato

1:43 PM

Speaking of capitalism, have you played the Suzerain game?

Paul White

nope

Erik Darling

the only game paul plays is windbg

Paul White

I spend all my time trying to accumulate dollars

Zikato

4 mins ago, by Paul White

I blame capitalism

Paul White

I do wonder if it is possible to win dbg

Erik Darling

1:45 PM

@PaulWhite i hear seo is good for that

Paul White

double ping!

Erik Darling

> double bonk

Paul White

@ErikDarling is SEO the ultimate meme

seems like an absolute racket to me

Erik Darling

maybe

Zikato

I pity the poor soul that rejoins the room and sees 100+ messages

Paul White

1:47 PM

I imagine one could waste a lot of money 'investing' in SEO optimisation

Erik Darling

i used to laugh at the bou folks for adding "in sql server" or spelling things out to the max but then i ended up going through 900+ posts on my site to add the same seo memes to headlines and post bodies etc

Paul White

An editor tried to SEO one of my articles once

ONCE

Zikato

Your content started in paragraph 5?

Paul White

It's a game where people pretend to have insight into search engine algorithms

They'll be selling NFTs of crypto apes next

@Zikato It was worse than that. They tried adding stupid and irrelevant hyperlinks everywhere and all sorts. It was stupid

Zikato

Why do you need insight into the search engine algorithms? Just vomit every possible keyword into your text

Paul White

1:50 PM

Focussing on stupid metrics like raw click numbers. Even if every click pisses off the clickee so much they vow never to use your product

Zikato

To be fair, I only have about 20 visits a day

Paul White

Ah but who are those 20 people

Erik Darling

you have a lot of family members eh

Zikato

I don't know, I try to respect their privacy

Paul White

I bet they're all top-flight influencers

Zikato

1:51 PM

It's probably all me, I don't know if I've set up a filter

Paul White

Movers & shakers

CEOs

Erik Darling

small businesses with critical sql server issues

Paul White

and vendors

Erik Darling

ready and willing to pay any invoice to solve their problems

Zikato

well, one is definitely Kevin Feasel, because he features me on CuratedSQL

Erik Darling

1:53 PM

what a nice lad

Paul White

Erik pays protection money to the Brent Memeletter

Zikato

mama, I paid it

3

Paul White

ha ha

Erik Darling

there are some weeks i can't believe that i don't make it in there when i see the other links

Zikato

it's gotta be balanced. It can't be all Erik Darling show

Paul White

1:55 PM

Well it could be, for a price

Zikato

Capitalism strikes again

Paul White

grabs steering wheel

Zikato

Testing markdown

Erik Darling

get in the limo we're doing capitalism

Paul White

heh

@Zikato Now do ~~strikethrough~~

Zikato

1:57 PM

~challenge accepted~

Paul White

a set of eyebrows appears

Zikato

I just wanted to decorate that text

Paul White

thought as much

Zikato

~~Strike one~~

Paul White

that's much better than most people manage

we've seen some terrible flapping about in the past

<s>bugger</s>

<stike>oh no</strike>
etc.

Zikato

1:59 PM

I dedicated time and effort to finding out. And I'll probably never use it again.

yeah <stike> doesn't work at all

Paul White

imagine what you just forgot to make room for that info

Erik Darling

nope

Paul White

flapping continues

Zikato

@PaulWhite probably my kids' birthday

Paul White

DOUBLE BOINK

Zikato

2:01 PM

I finally know what that means

Paul White

@Zikato they were all born on the same day?

Zikato

well I don't know now, do I?

Paul White

true

but you did use the singular

Erik Darling

who put the bonk in the bonk bah bonk bah bonk

Paul White

so whichever day it was, it was the same

Zikato

2:03 PM

I gotta be honest, my English not very good

Paul White

it's better than most in this room

Erik Darling

no apostrophe abuse

Paul White

apostrophe abuse is rampant in here

Erik Darling

wait he hasn't experienced sean

Paul White

* fewer

@ErikDarling no one has, despite his outlandish claims

Zikato

2:04 PM

apostrophes are one of the harder ones for me. Missing articles is another one. Doubled letters and commas in a sentence

Erik Darling

also, a blogger whose rss feed i follow has been posting about "CTE's" a lot recently and i cringe every single time

Paul White

JFC

Zikato

CTE's syntax?

Paul White

there are some complex rules around the plurals of initialisms, but that's not a tricky one

my kid's birthday or my kids' birthdays, for the record

I'm an idealistic fascist dictator grammar nazi and I still prefer NCCI

Zikato

@PaulWhite Thank you

Erik Darling

2:08 PM

i always have to validate how to apostrophe a word that already ends in s

Zikato

I could have gone for triple boink

Paul White

Automatic kick for a triple

@ErikDarling both options are acceptable in most cases?

Zikato

Physical or out of the room?

Paul White

Like formatting parentheses

Erik Darling

@Zikato depends on what rank grammar nazi you are

Paul White

2:10 PM

@Zikato Physical is an option, but for that I have to pay Erik's invoice

Zikato

@ErikDarling It was my job at one point. But for my native language

Paul White

Czech please

Erik Darling

true apostrophuhrers have different standards

Zikato

V jednu chvíli to byla i moje práce, ale pouze pro mou rodnou řeč

Paul White

sounds right

Erik Darling

2:12 PM

that's how i would have said it

Paul White

> ‎At one point, it was my job, but only for my native language.‎

Results of in-line translate

Zikato

I should just use that instead of trying

Paul White

might be funny for a while to pretend not to understand anything Tomáš Zíka says

Zikato

pretend?

Paul White

what

it occurs to me that many people in this room speak more than one language

Erik Darling

2:16 PM

some speak fewer than one

Paul White

not counting Canadian

Zikato

ESL gang unite

Paul White

eh?

Zikato

English as a Second Language - ESL

Paul White

sorry?

Zikato

2:19 PM

ENGLISH AS A SECOND LANGUAGE - ESL

Paul White

nope sorry can't make it out

translate not working

Zikato

no worries, it's probably my accent

Erik Darling

oh wait, paul prefers his aliases differently

a second language = english

Paul White

looks backwards

Erik Darling

you're just upside down

stand on your head and drink wine

Zikato

2:21 PM

it was not syntactically correct. I was missing brackets

Paul White

the level of hemisphereism is too damned high

Erik Darling

you started it with the whole "wrong season" meme

Paul White

benefits of blue text, I need not be consistent

anyway when does Winter Bash start

seems late this year

Erik Darling

i wonder if todd cares about his domain and full name being posted online

Zikato

Typical StackOverflow experience. First, they complain you don't give them enough info, now it's too much info…

Erik Darling

2:29 PM

i wonder when one box support for tik tok videos will come to chat

Zikato

Maybe it's obfuscated so well that's not even his real domain and name

Fixed

Erik Darling

i was just listening to a podcast about how government hackers will find linkedin profiles of db/sys admins who work places they want to get into and then find forum posts by them asking for help with stuff to recon their infrastructure

they'll even answer questions for them in ways that make their domains unsafe

anyway it was amusing

i wish todd all the online safety in the world

Zikato

meaning you'll delete the picture in chat as well?

Erik Darling

too late for that

Paul White

we're all being spied on through our coffee machines anyway

Erik Darling

2:33 PM

admin/admin

Paul White

@ErikDarling where's that from?

Zikato

0

Q: Cannot Backup to Network Share using Ola Hallengren's Scripts

SQL Version: Microsoft SQL Server 2016(SP3)(KB5003279) - 13.0.6300.2 (X64) OS Version: Windows Server 2012 R2 Standard I am trying to setup the backups and database maintenance jobs using Ola Hallengren's sripts. I have the following job setup: sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q " ...