The Heap™ – Consultancy ©® - 2018-02-10 (page 2 of 3)

Paul White

16:00

0

Q: Slow query performance when searching for a particular value, but fast with a different value on the same column

I have a SQL Server 2017 select statement that is executed by the front end application. The query has three joins and UNION. Each join statement has a clause where NAME LIKE '%ibm%'. When I change %ibm% to NAME LIKE %services% is runs fast as usual. I rebuilt all the indexes on all of the tabl...

sp_BlitzErik

@PaulWhite yeah, every single one. all the queries are generated by a web gui.

Paul White

Far out.

sp_BlitzErik

Sean Gallardy - Microsoft

typical this is an oltp database and we had a locking issue once because we don't know why so let's nolock the shit out of everything

Paul White

The problem, as I see it, is mostly in the name

Sean Gallardy - Microsoft

16:02

?

Paul White

No one can be arsed to write WITH (READUNCOMMITTED)

@SeanGallardy-Microsoft Well we don't want locks do we

Sean Gallardy - Microsoft

code golf, NOLOCK is smaller

Paul White

Exactly so

Sean Gallardy - Microsoft

plus it doesn't require you to know isolation levels, since most people that use it have no clue they exist anyway

Paul White

And sounds like A Good Thing

sp_BlitzErik

16:03

i like when code starts with setting the isolation level to read uncommitted and it still littered with nolock hints

Sean Gallardy - Microsoft

LOL

Paul White

@sp_BlitzErik Disappointed that no field contains "your mom" there

2

Sean Gallardy - Microsoft

Yes, you get an extra piece of cake

Paul White

Sprinkling NOLOCK adds joy to the move from locking RC to RCSI

sp_BlitzErik

@PaulWhite i'm sure mrs. white is a nice lady; i would never besmirch her

Sean Gallardy - Microsoft

16:04

rough crowd today

Paul White

@sp_BlitzErik She's too good for me, that's for sure

> today

sp_BlitzErik

@PaulWhite i like the "we turned on rcsi and it got worse" people

"did you remove nolock hints?"

"no, why?"

deth

Paul White

jesus

Sean Gallardy - Microsoft

I don't think Jesus can save you from NOLOCK hints in RCSI

2

sp_BlitzErik

@PaulWhite again, we get a different level of pants on than you do

i had to answer the "do more indexes make the database bigger?" question recently

Sean Gallardy - Microsoft

16:06

Mine are +5 blue jeans of ripping, ehnaced ability ot randomly rip in places

Paul White

How do these people end up in database work rather than programming e.g. java script?

Sean Gallardy - Microsoft

L M F A O

beggars can't be choosers I guess

"We wanted you to ruin a website today with javascript code, but you've been promoted to breaking databases today... it hurts much more people, faster"

Paul White

Perhaps a special edition of SQL Server could be created for these people. One that only contains NOLOCK and JSON.

sp_BlitzErik

@PaulWhite the problem is that they do both and they're only good at one

ypercubeᵀᴹ

@PaulWhite remember this one?

8

Q: NOLOCK hint changes order of records returned

There is a clustered index on a table Client field LastName. When I simply dump all records from the table, they appear in the alphabetical order unless (nolock) hint is used as in the query in question. That hint changes the order of records. Should it?. I am positive that no other session has...

sql-server-2012 hints order-by

Sean Gallardy - Microsoft

16:09

XML, JSON, and other high level constructs IMHO don't belong in a database

If it requires its own DOM... yeah, no thanks

Paul White

@ypercubeᵀᴹ ugh

Sean Gallardy - Microsoft

complete with sp_who2 reference

Vote for Sean, Deprecation CZAR!

3

sp_BlitzErik

"nothing looks different in activity monitor"

Sean Gallardy - Microsoft

My motto will be, "We can and will remove it!"

If someone artistic makes that in a Marxist style portrait with that motto, I'll set my internal photo to it

we'll count down the days until I get fired

a "fire pool" if you will

Paul White

+1 would star again

Sean Gallardy - Microsoft

16:12

lol I see my spelling was off

I'm leaving it

Paul White

@SeanGallardy-Microsoft where?

Sean Gallardy - Microsoft

my eyes are going, this monitor has such small font

so much for that Lasik

Paul White

It's just your death approaching

Sean Gallardy - Microsoft

oh, that's good, thought it was serious

Paul White

Nope, everyone has that condition

Sean Gallardy - Microsoft

16:14

NOLOCK? yeah, it's rampant

sp_BlitzErik

what you can't see is paul using his reality debugger to flip memory switches inside you

Paul White

WITH (NOLUCK)

Sean Gallardy - Microsoft

We need a picture of Paul with a vaccine shot in hand, then a quip about nolock

Paul White

I've done my quota of photos for this decade

sp_BlitzErik

drawings don't count

Sean Gallardy - Microsoft

16:15

So two more years and then another photoshoot?

Paul White

Well ok so it's more of an artist's impression, but still

Feeds

Paul White has added Joe Obbish to the list of this room's owners.

Paul White

Ha nearly forgot

sp_BlitzErik

we went through such a spree of needing to order stuff that the days feel a bit hollow with nothing getting delivered

Sean Gallardy - Microsoft

The ups and downs of capitalism

Joe Obbish

16:22

@sp_BlitzErik Well? Do they?

Sean Gallardy - Microsoft

smh

sp_BlitzErik

@SeanGallardy-Microsoft NOT YOU TOO

Sean Gallardy - Microsoft

I'm 100% capitalist

free market baby

sp_BlitzErik

@JoeObbish i dunno, do heaps?

Joe Obbish

what if I create a disabled index

Sean Gallardy - Microsoft

16:23

I shouldn't be regulated by the government to use 25% Postgres because it can't get any market traction

Joe Obbish

or a hypothetical index

Sean Gallardy - Microsoft

@JoeObbish Those are stats

sp_BlitzErik

they have no pages, so no

Joe Obbish

or an index on tempdb

Sean Gallardy - Microsoft

technically it still has a definition in the base system tables, so yes it does

sp_BlitzErik

16:24

create index ix_tempdb on tempdb

Joe Obbish

@SeanGallardy-Microsoft couldn't it fit in an existing page somewhere?

Sean Gallardy - Microsoft

You assume there are free pages

Joe Obbish

I don't assume that, but there might be

Sean Gallardy - Microsoft

or there might not be

It's a Paul White's Schrodingers Page

Joe Obbish

that's fine

I live in a flexible world

anyway it sounds like a wonderful question to me, despite what the Haters are saying

Sean Gallardy - Microsoft

16:25

I'll let that one alone

Joe Obbish

florida man shows restraint for the first time in his life

Sean Gallardy - Microsoft

Maybe the 3rd

MAYBE

sp_BlitzErik

florida man often shows restraint in not sobering up

Joe Obbish

I'm honored

Sean Gallardy - Microsoft

lol

sp_BlitzErik

16:27

"better not, might realize i'm in florida"

Sean Gallardy - Microsoft

There are better and worse places

Worse: New York

Better: Ireland

sp_BlitzErik

HERESY

Sean Gallardy - Microsoft

mmmm I don't think that means what you think it means

sp_BlitzErik

if it were worse here we wouldn't need to give our homeless bus tickets to you

Paul White

Is there any conceivable case where adding more indexes would not make the database bigger might be a better question, but I somehow doubt the asker was so sophisticated.

sp_BlitzErik

16:28

create index... where id = -1 and id = 1

or i hear columnstore is so well compressed it basically takes up negative space

Sean Gallardy - Microsoft

define: database?

Paul White

@sp_BlitzErik KFC loves it

sp_BlitzErik

@SeanGallardy-Microsoft the thing you drop on your last day at a job

Sean Gallardy - Microsoft

If we're taking the @JoeObbish route of free system pages

any Hekaton index

Paul White

I've said it before and will say it again - Columnstore compression is a hell of a thing. My table (#2) has 8 months of measurements at minute interval. #1 has 15 days of data. Same measurements

Tweeted by billinkc on February 6, 2018 at 2:47 PM

Sean Gallardy - Microsoft

16:30

or any disabled, or otherwise 0 page index

Paul White

Of course he smokes 11 herbs and spices so

Sean Gallardy - Microsoft

L-O-L

Paul White

@SeanGallardy-Microsoft I like that angle

sp_BlitzErik

mark it here folks, the day paul likes something about hekaton

Sean Gallardy - Microsoft

Screenshotted for proof

Now you can't pix or gtfo

Paul White

16:33

@sp_BlitzErik I've never been anti-Hekaton have I?

sp_BlitzErik

i mean not in the way that you're anti social

Paul White

Fair

sp_BlitzErik

dunno that you've ever quite been pro hekaton

Sean Gallardy - Microsoft

My only issue with Hekaton (with recent additions) is that people still try to use it in places they shouldn't

Paul White

I admire the technology greatly

sp_BlitzErik

16:34

i always get asked about it for reporting

Sean Gallardy - Microsoft

"My 32 GB server has memory pressure and crashes - my 200 GB database uses Hekaton"

sp_BlitzErik

"we don't want reporting queries to get blocked. it's lock free, right?"

Sean Gallardy - Microsoft

"We used Hekaton but things didn't get faster"

Joe Obbish

I think it's latch free

I got a lot of pesky latches on my system

Sean Gallardy - Microsoft

It's both

sp_BlitzErik

16:35

yeah, but that's not the question i get asked

Joe Obbish

LATCH_SH is the number 4 wait on my system

Paul White

@sp_BlitzErik Ha ha very good the ultimate nolock

Joe Obbish

should I use The Hekaton? X/Y

Sean Gallardy - Microsoft

LOL

Want to see if your laptop can run Hekaton?

sp_BlitzErik

@PaulWhite plz change that to "X/Y"

Joe Obbish

16:36

I use servers unlike the rest of you muppets

Paul White

virtually

Joe Obbish

it's a virtualized world

I don't like it

I just live in it

Sean Gallardy - Microsoft

can't make it see the code right, curse this formatting

Joe Obbish

we got a SAN

I don't want a SAN

but that's how it is

sp_BlitzErik

@SeanGallardy-Microsoft when you paste stuff in, a button will pop up next to send/upload that says "fixed font"

ymmv

@JoeObbish sans aren't bad when they're all flash with 10gb ethernet connex

and they're not more than 10 feet from the server

Sean Gallardy - Microsoft

16:38

hrmmm

@sp_BlitzErik multiple 40GB + 12x ports

FTFY

Joe Obbish

how many sans are like that?

sp_BlitzErik

have many customers with that setup?

highfive

Sean Gallardy - Microsoft

the high end ones

that have people that know how ot set them up

that aren't using SVCs improperly in front of them

Paul White

@sp_BlitzErik and there's a competent and friendly SAN admin

Joe Obbish

this all sounds good

sp_BlitzErik

16:40

so... thomas grohser customers who all bought superdomes

Joe Obbish

how do I get one

Sean Gallardy - Microsoft

Spend a few million

sp_BlitzErik

@PaulWhite oh wow, both?

Joe Obbish

is there a timesharing system available?

Sean Gallardy - Microsoft

then realize you could get better performance on local NVMe

sp_BlitzErik

16:40

keys to the kingdom, this guy

Paul White

@sp_BlitzErik It's the impossible dream

Sean Gallardy - Microsoft

for 50k

Paul White

Like that job ad Erik shared

sp_BlitzErik

hah

@SeanGallardy-Microsoft fun game: when people tell you what their san is, go look for it on ebay

just sayin

Joe Obbish

I wonder what would happen if we used in-memory CCIs for our import tables

sp_BlitzErik

16:43

says the guy complaining about memory waits for 3 months

Joe Obbish

yeah but that's separate memory, right?

Paul White

Let's combine two new and very complicated technologies. What could go wrong.

3

sp_BlitzErik

just use nolock, joe

Sean Gallardy - Microsoft

@sp_BlitzErik Hahaha I had a cx tell me their Tier 0 storage was an IBM XIV Gen 3

Joe Obbish

you guys are no fun

Sean Gallardy - Microsoft

16:46

FYI - I was there because they complained that the SAN looked fine but SQL Server had "15 second IO" errorlog messages

Joe Obbish

I bet you're jealous

no one can generate memory waits like I can

Paul White

It often takes me days to remember stuff so

sp_BlitzErik

@SeanGallardy-Microsoft single 1gb iscsi connection

Sean Gallardy - Microsoft

of course

over the same switches as their normal traffic

@PaulWhite That glaucoma medicine working, eh?

Paul White

SELECT * FROM Network WITH (NOLUCK)

Ha no I never got on with that. Sends me straight to sleep.

Joe Obbish

16:48

sounds useful

Paul White

My wife is quite different in that regard.

Sean Gallardy - Microsoft

Vacations at Paul's!

sp_BlitzErik

@PaulWhite so that's how you get hours to yourself!

Paul White

:)

sp_BlitzErik

@PaulWhite: serious question -- would you go to summit if el brento picked up the tab?

Paul White

16:52

@sp_BlitzErik Why would he do that?

Dunno. What, to speak? Or just hang out and chat.

sp_BlitzErik

well speaking is up to you

probably in exchange for a day of brain picking

if you'd rather discuss via email i'll start a thread

McNets

@PaulWhite would you prefer Mobile World Congres in Barcelone, BTW it's winter here

Sean Gallardy - Microsoft

WINTER HAS COME!

McNets

;)

Sean Gallardy - Microsoft

oh wait, no it's florida, still 83 degrees

McNets

16:56

haha Fahrenheit I hope!

Sean Gallardy - Microsoft

yes, and though I'm for Metric, I do believe Celsius is a terrible unit

Kelvin all the way

McNets

then now 276.15 k

Sean Gallardy - Microsoft

better than 28.33333

I shouldn't need 6 sig figs

McNets

Our pharmacy thermometers show the temperature without decimal digits

Sean Gallardy - Microsoft

27 is too cold and 28 is too hot :(

too much of a range for me

McNets

17:01

You can see -0, it's always bizarre

Edit: Since it seems this question got some attention (maybe never asked before or maybe I am doing it wrong?), I will try to explain. The table is the tax rate. One of the tax rates is the default (standard since default is a Postgres command). When a new product is added, the standard tax rate is applied to the product. If there is no standard, the database must either do a guess or all kinds of unneeded checks. The simple solution, I thought, was to make sure there is a standard.

4

Q: Constraint - one boolean row is true, all other rows false

I have a column: standard BOOLEAN NOT NULL I would like to enforce one row True, and all others False. The are no FK's or anything else depending on this constraint. I know I can accomplish it with plpgsql, but this seems like a sledgehammer. I would prefer something like a CHECK or UNIQUE const...

postgresql constraint postgresql-9.5 referential-integrity

sp_BlitzErik

i think i've given paul a conniption

McNets

conniption?

sp_BlitzErik

don't be a lazy spaniard

conniption

Noun: conniption (plural conniptions)

(informal) A fit of anger or panic; conniption fit.
When she came downstairs and saw what her children were eating, she had a conniption.
...threatened by the conniptions gripping Wall Street (Businessweek Oct.20, 2008)
A fit of laughing; convulsion.
The joke was not that funny, but he went into conniptions laughing.

McNets

,👍

I was looking at urban dict

urbandictionary.com/define.php?term=CONNIPTION

ypercubeᵀᴹ

sp_BlitzErik

17:11

that's just lovely

Sean Gallardy - Microsoft

What the literal ef

ypercubeᵀᴹ

Just noticed it in her twitter timeline: twitter.com/Kendra_Little/status/962130155048071170

Paul White

17:29

@sp_BlitzErik No I just had to service another nested level of interrupt

Sean Gallardy - Microsoft

~~test~~

sp_BlitzErik

@PaulWhite i've been a good little spinlock

Paul White

@sp_BlitzErik You backed off then went to sleep?

sp_BlitzErik

except to call mcnets a lazy spaniard, yeah

Paul White

@sp_BlitzErik Well we can talk about it over email but I can't see how it would make sense for you. What do I know though.

The ghost on that cert is going to haunt @SeanGallardy-Microsoft

Sean Gallardy - Microsoft

17:32

lol

I am just trying to master the cray cray that is the markup language here

Joe Obbish

@sp_BlitzErik How much time do I get if I chip in $50?

Paul White

👻👻👻👻👻

sp_BlitzErik

probably just a hander, glove on

oh, time

dunno

Paul White

huh, ghost looks different in Chrome

Joe Obbish

what's the price for a handshake and a selfie

Sean Gallardy - Microsoft

17:33

Chrome... you must have extra cpu and memory to throw away

Paul White

free of course unless you're charging me

@SeanGallardy-Microsoft I write very efficient queries, so have plenty of free memory and CPU

Sean Gallardy - Microsoft

Google, however, does not

Paul White

Though I do have Edge open as well

Sean Gallardy - Microsoft

I swear half of Chrome's code is "new SuperLargeObjectNeverGivingBackMemory[10000]"

don't get me started on Edge

@PaulWhite HOW MUCH MEMORY DO YOU HAVE?!

That's asking for memory pressure, chrome and edge open?!

Paul White

16 glorious gigabytes, 7.5 free

Sean Gallardy - Microsoft

17:36

This is just with Edge open, 4 tabs

Paul White

Right, but what were you saving that 55GB for?

Sean Gallardy - Microsoft

Hekaton

;)

Paul White

Ha ha nice

Sean Gallardy - Microsoft

TBF - outlook is chewing up a bit of memory too

Paul White

Sean Gallardy - Microsoft

17:39

2 GPUs, look at high roller over here

Paul White

39 Chrome tabs using 1850MB, Edge 94MB

Sean Gallardy - Microsoft

Not sure why Edge has a 3rd eye...

Paul White

@SeanGallardy-Microsoft Hyperthreaded to four!

Sean Gallardy - Microsoft

LOL

Paul White

Oh I see you said GPUs.

Well one is on the motherboard, no choice about that.

Sean Gallardy - Microsoft

17:40

I know :) just giving crap

Paul White

That's FINE then

Sean Gallardy - Microsoft

You could have a good time with hashcat though

Paul White

Sends me straight to sleep (glaucoma medicine ref)

Sean Gallardy - Microsoft

The radeon's do more hashes/sec than the Geforce

Paul White

So SSMS hasn't run out of window handles for a couple of days. Time to start saving my work every 30 seconds or so.

Sean Gallardy - Microsoft

17:43

hahaha

but we don't have to worry about memory, the GC takes care of that

says every high level language person ever, right before their app crashes on memory issues

sp_BlitzErik

@PaulWhite that started happening to me a bit after upgrading to 17.4

Paul White

I hope SOS doesn't go the same way

Sean Gallardy - Microsoft

eh?

Paul White

@sp_BlitzErik The odd disconnected/un-connectable session as well?

sp_BlitzErik

yeah

Paul White

17:45

@SeanGallardy-Microsoft SQL Operations Studio

become bloatware

Sean Gallardy - Microsoft

Oh, that's dead to me

Paul White

@SeanGallardy-Microsoft How come?

It is a shame they dropped the cool name (Carbon)

I haven't installed it since

Sean Gallardy - Microsoft

It's like... hey let's take a tool and create the same thing but make it open source and cross plat, but not really do anything better, worse, or different

thanks, no thanks

Paul White

Right absolutely

sp_BlitzErik

"under new management"

Sean Gallardy - Microsoft

17:47

"We disrupted an entire division to bring you THE SAME THING but changed where the buttons are, just to piss you off"

had to include buzz words

no product is worth anything unless it disrupts an industry

Paul White

What's the new command line tool called again?

mssql-cli or something?

Sean Gallardy - Microsoft

sql-cli

it's SQLCMD but with intellisense

sp_BlitzErik

what the hell is that

Sean Gallardy - Microsoft

gratz

Paul White

I installed that and Python for Windows

sp_BlitzErik

17:48

get out

Sean Gallardy - Microsoft

You're dead to me

Paul White

The IntelliSense intrigued me. It was awful. I uninstalled it.

sp_BlitzErik

i thought sqlps was the future

Sean Gallardy - Microsoft

Python, who do you think you are - some sort of "data scientist"

Paul White

Python was required for the sql-cli install

At least I think it was Python

Sean Gallardy - Microsoft

17:49

Python - when you can't code but really want to get that CS credit

Python - why write your own code when you can ask someone to create a library for you

McNets

@TomV do you know any on-line (free) service to validate XML-EDI files?

Paul White

Yep was python github.com/dbcli/mssql-cli/blob/master/doc/…

sp_BlitzErik

Paul White

Works as designed

Sean Gallardy - Microsoft

Closed as won't fix

Paul White

17:52

Migrated to User Voice with votes cleared

Sean Gallardy - Microsoft

:golf clap:

Gym time, bbl

Paul White

@sp_BlitzErik Mails received thanks. Will read tomorrow; too tired now

sp_BlitzErik

:thumbsup:

Paul White

call getTomorrowsDate()

Sean Gallardy - Microsoft

SELECT TOP (1) WITH TIES Date FROM Dates WITH(READPAST) WHERE 1=1 ORDER BY 1

Paul White

17:55

@SeanGallardy-Microsoft Only works for dates <= GETDATE()

Otherwise you'd need a READFUTURE hint.

And yes every time you edit that I get pinged again

Sean Gallardy - Microsoft

ahahahahaha

You're welcome

<3

sp_BlitzErik

FOR SYSTEM_TIME

Paul White

See there's another feature I haven't ever used

Sean Gallardy - Microsoft

I wanted to put nolock in there... but...

Paul White

READPAST requires READ COMMITTED, yes?

(from memory)

Sean Gallardy - Microsoft

17:57

I think technically you can use it in any isolation level

sp_BlitzErik

@SeanGallardy-Microsoft get your k2

Sean Gallardy - Microsoft

but it only makes sense if you have locks

"Vitamin K2 supplementation was associated with a 12% increase in maximal cardiac output" whoa

Paul White

> READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. When specified in transactions operating at the SNAPSHOT isolation level, READPAST must be combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

sp_BlitzErik

that's better than a six pack of weekend prince

Sean Gallardy - Microsoft

There you go

alright bbl

sp_BlitzErik

18:02

such a go-getter, hitting the gym on the weekend

Paul White

That documentation entry is really confusingly worded.

Luckily no one uses readpast.

sp_BlitzErik

oh lord

Paul White

Such an admission of failure

What's msdbCentral?

sp_BlitzErik

long story

Paul White

IF  EXISTS (

    SELECT 1
    FROM msdbCentral.dbo.backup_worker bw WITH (READPAST)
    WHERE bw.last_log_backup_finish_time < '99991231'
    AND bw.last_log_backup_start_time < DATEADD(MINUTE, -5, GETDATE())
    AND EXISTS (
		    SELECT 1
		    FROM msdb.dbo.backupset b
		    WHERE b.database_name = bw.database_name
		    AND b.type = 'D'
			    )
	    )

Formats OK for me

Weird

sp_BlitzErik

18:10

i don't have the magic touch

Paul White

Use spaces instead of tabs, that might be it

sp_BlitzErik

hey pal, i use your sql prompt settings

Paul White

Yep spaces/tabs is an SSMS setting IIRC

sp_BlitzErik

so many levels

Paul White

Amazing anything ever works frankly

-> zzz

sp_BlitzErik

18:16

yes goodnight

McNets

gnight

ypercubeᵀᴹ

Microsoft Style Guide: ask

3

sp_BlitzErik

18:37

it's a bummer that any adult needs to be told that

Tom V

@sp_BlitzErik yeah well he's an 18 percenter

sp_BlitzErik

lol

Tom V

I would guess I'm around 18 too but I'm just skinny

82 kg for 1m 98

sp_BlitzErik

speak english, bub

Tom V

It's not a big ask to convert that youself

@McNets nope

McNets

18:56

@TomV not bad

@TomV runner?

Tom V

Nah just skinny

McNets

76 Kg 1.80

Transcript for

The Heap™ – Consultancy ©®