The Heap™ – Consultancy ©®

Erik Darling

00:11

Strings were a m_st_k_

Vérace

01:31

@ErikDarling Maybe you meant? :-)

Erik Darling

you might wanna give that another shot on your blackberry

Vérace

I'm the next Fabrice Bellard!

I'm also planning on running a PostgreSQL cluster on the Blackberry to store all the factors to Mersenne primes that I'm finding!

Erik Darling

02:04

The BlackBerry Bold was the last good phone I owned

I have merely tolerated every phone since then

Vérace

02:51

Never had one myself - my first phone was a Sony and my last 3/4 have been Samsung - with maybe a Nokia or 2 in between. I'm not a huge "fan" of phones - I put up with them because they're useful. <rant>The one thing that really bugs the shit out me these days is that everybody wants you to have a fucking app - what was the problem with accessing stuff using the shagging browser.

Recenly my bank has basically forced me to download it - web access impossible without the damn thing!</rant>

Zikato

06:00

Morning

John K. N.

08:47

Morning

Vérace

09:16

A chairde - Morning all!

McNets

Morning

ypercubeᵀᴹ

11:46

@Vérace which bank is that?

Vérace

@ypercubeᵀᴹ AIB - Allied Irish Banks - why? Is nobody else being forced down the road of getting an app that they don't particularly want?

ypercubeᵀᴹ

@Vérace there is a tendency yeah, to lead you into apps. I agree 100% with your feelings

Zikato

12:02

Hmm, I have a larger table (2 mil rows, 8 cols, 600 GB fragmented). I'm removing a surrogate PK and replacing with combined index over 4 columns. But then I need to update in batches without creating a temporary index

I've tried offset - fetch in a loop, but it reads the offset rows, so every iteration has more reads

ypercubeᵀᴹ

don't use offset?

Zikato

12:20

then I'll just update the same rows all over again. If I add condition to check whether it's updated or not, it will scan the table without the temporary index

Paul White

@Zikato Sounds like you need an anchor michaeljswart.com/2014/09/take-care-when-scripting-batches

ypercubeᵀᴹ

Update with a WHERE clause. Say first you update only rows with col_a <=3. The second batch has WHERE col_a > 3 AND <= 6. The 3rd WHERE col_a > 6 AND <= 9. etc.

Paul White

I'm trying to imagine what the batch update does and how that's related to replacing a surrogate PK

ypercubeᵀᴹ

Yeah, that blog describes it much better ;)

Paul White

Or is the problem how to update in batches once the PK is replaced by the four-column index?

If so, take a look at this recent Q & A: dba.stackexchange.com/q/316232/1192

Zikato was never seen again

Charlieface

12:30

@Zikato Don't get what you are trying to do. Maybe mock up a dbfiddle.uk

If you just want to drop a column why can you not just do ALTER TABLE t DROP pk

Paul White

Or...ask a question 😀

ypercubeᵀᴹ

@PaulWhite something like: "use the method of that question to get 1000 rows with an index seek, update them, get the maximum (4-column) values of the rows updated. use that in the next iteration" ?

Paul White

We'll never know, sadly

Zikato

12:44

sorry, I'm back

I'm doing online indexed view clustered index deployment michaeljswart.com/2015/06/how-to-create-indexed-views-online

since there is no single column I can use as an anchor/high water mark, I have only order by the four-column index. I'll check the Paul's link

Paul White

@Charlieface Dynamic pivot answer added stackoverflow.com/a/73609654

@Zikato I don't like that option much, for reasons Michael explains well in his post. What are you looking to avoid by creating an indexed view 'online'? As I recall, Sch-M is no longer taken on the base table during the build. Lack of an offline window just in case? Too much pressure on the server when the view build is in progress? Don't you hate that IsMigrated column?

Zikato

12:59

I'm worried about blocking, because the indexed view references a load-bearing table. I don't like the IsMigrated column, but ends justify the means. The index creation took 16 minutes on the Test environment which in case of blocking would be an outage

Paul White

Blocking concurrent modifications you mean?

Zikato

there definitely was a SCH-M lock which will block everything, but I'm not sure if it was for the whole duration or just beginning/end

ypercubeᵀᴹ

@PaulWhite +1 for that "hate"

Zikato

I could live with writers being blocked, but not readers

Paul White

Are you running an old version of SQL Server?

I'm pretty sure SQL Server only takes S on the base table these days.

But no doubt you already tested all this in your test environment

10

A: Why does an index rebuild requires a Sch-M lock?

The documentation for How Online Index Operations Work says: SCH-M (Schema Modification) if any source structure (index or table) is dropped.* * The index operation waits for any uncommitted update transactions to complete before acquiring the S lock or SCH-M lock on the table. That is specific...

Zikato

13:08

I'll retest just to be sure

Paul White

Please tag your chat question with your SQL Server version

Zikato

SQL 2019
I agree with online index rebuilds, but clustered index on view cannot be done online

Paul White

sql-server-2019

amateur

@Zikato I see you're too busy to read the whole answer, let me quote the relevant section for you

> Some time ago, the Sch-M restriction was applied when creating an indexed view. That was pointed out to be unnecessary (Connect link no longer available) because no structure was being dropped, only created, so the behaviour was changed (to only take Sch-S and Tab-S).

For the first time in my life, I have some sympathy for Forrest

Zikato

Sorry, I'm indeed juggling a few tasks and skimmed it.

Paul White

It's ok I'm only teasing you

And every other muppet that asks incomplete questions in chat instead of on main

Zikato

13:17

If I were to ask the question retroactively in main, would the answer be a link to this answer?

Paul White

You'd get downvoted to oblivion, commented to death, and finally closed as a dupe

3

Anyway, even without blocking per se, the usual concern is the extra load on the table giving the impression of blocking due to resource constraints.

Especially if the indexed view build is massively parallel

And you're running 2019 on the typically weedy VM

Zikato

I'm testing right now and reader is blocked - requesting SCH-S

Paul White

The indexed view build is holding Sch-M on the base table?!

Zikato

the indexed view is holding SCH-M on the view but the reading process against the base table requests SCH-S on the indexed view and is blocked

so the locks held/requested are opposite of what I was expecting, but the end result is same

Paul White

well that's just stupid

I assume you're running Enterprise Edition

What happens if you try to run the same read query with OPTION (EXPAND VIEWS)?

Just out of interest

Zikato

13:32

not blocked

Paul White

Jesus Christ

Zikato

hmm, so it's this helpful Enterprise Edition feature

Paul White

Yeah it's trying to match your query to the indexed view that isn't available yet

Zikato

now I just need to set the Expand Views for the whole database for the duration and I'm golden

Paul White

Which completely defeats the point of not taking Sch-M on the base table in the first place

@Zikato Or upgrade to standard edition 😀

3

Zikato

13:34

it would be funny under different circumstances

Paul White

I sometimes wonder if Microsoft ever test anything

Zikato

do you know if there is a trace flag for this? Which wouldn't require restart?

The IsMigrated is starting to look good again

ypercubeᵀᴹ

13:49

Looks like this is gold material for a blog post.

just noticed: gold - blog are almost mirror image of one another

Zikato

yeah, I'm so angry, I just might

even though Paul did most of the research

Charlieface

@PaulWhite I still think we should edit the original, there is no way you are going to get another 300 votes

@ypercubeᵀᴹ dolp and plod are also if you mirror vertically. I have no idea the significance of that though...

@PaulWhite Logically you'd think the optimizer wouldn't even be able to see the indexed view, because the system objects for it haven't been committed yet.

ypercubeᵀᴹ

@Charlieface not exact mirrors though either. You need to mirror vertically and then mirror individually p and d ;)

Zikato

I'm also wondering how will the indexed view affect the readable secondaries. They shouldn't be blocked, because the index is created by the redo thread only after it's finished right?

Paul White

14:19

@JohnK.N. This question is still in its bounty grace period.

Erik Darling

14:34

i've had some blog comments about blocking around indexed view creation

ah, here it is

well bless my heart

Zikato

14:52

the very same situation. I'l ask Ben on Slack

Paul White

@ErikDarling Good lord, useful content buried in a comment. Well I never.

Zikato

yeah, it should have been posted as an answer

Paul White

@Zikato I don't think there's a trace flag to implicitly add EXPAND VIEWS to all queries on a session, if that's what you're asking. There might be other ways to work around this, but it'll take me a while to check.

@Charlieface Yeah, nah we've been over that.

@Charlieface Well, the view exists alright. It might be indexed, but you'd need to check the metadata to determine that. I guess that's where the blocking occurs, but I haven't checked yet.

@Zikato Oh so you have this in an AG too.

Zikato

of course, can't have it too easy

Paul White

I'm really cross about this blocking you know

Zikato

15:01

yeah. I'll try to blog about it tonight. It's been a while anyway

Erik Darling

15:18

@PaulWhite that's how great my blog is

Charlieface

15:37

The most egregious thing I ever saw re indexed views, was a case where I'd created a view something like CREATE VIEW v AS SELECT a / b FROM table WHERE b > 0 and then after I indexed it, when inserting I got weird "divide by zero" errors, even though I'd filtered the zeroes out. Ended up having to put it in a CASE to prevent that happening, but debugging it was annoying as it only happened when inserting to the base table, not when I created the index.

Erik Darling

i've had the same thing happen with filtered indexes and computed columns. the computed column one was really funny, because it only happened when i created an index on it.

i'd bet it's all the same mechanism

erikdarlingdata.com/computed-column-follies

Paul White

Can't rely on order/number of scalar evaluations etc.

There are probably 50 questions on site complaining about 'illogical errors'

> but I eliminated those rows in the WHERE clause

Zikato

I think the statement would have the same problem. Wasn't that the cause for some additional Scalar Inlining restrictions?

Erik Darling

@PaulWhite yeah, i don't think it's so much that they exist, it's just the sheer number of places you can hit them

Paul White

Feb 10, 2018 at 18:15, by Paul White

Amazing anything ever works frankly

Well I hope you're instinctively more defensive these days

Erik Darling

15:51

i'm not defensive you're defensive why would i be defensive

Paul White

Also aren't you the one that said EXPAND VIEWS was the most useless hint ever?

I'm sure I have an invoice for that somewhere

Erik Darling

fact checkers say that your post is missing context

Paul White

Best kind

Erik Darling

my main complaint with expand/noexpand is hint placement in the query

Paul White

Oh yeah right

OPTION (NOEXPAND VIEWS)

Charlieface

15:57

Also EXPAND VIEWS doesn't always work dbfiddle.uk/… obviously not relevant in this particular instance

Erik Darling

16:07

that's a blast

Paul White

That's like saying NOLOCK doesn't work because you have a READCOMMITTED hint in the view

Erik Darling

it just throws an error

Paul White

Don't think that's right

Erik Darling

erikdarlingdata.com/…

Paul White

Oh right

Erik Darling

16:15

🤾‍♂️

Paul White

What was that particularly amusing hint combination

OPTION (KEEPFIXED PLAN, RECOMPILE) perhaps

Erik Darling

who wins

Paul White

who knows

Erik Darling

the nose

Paul White

Also CREATE INDEX WITH (PAD_INDEX = ON, PAD_INDEX = OFF)

Erik Darling

16:19

sus

Paul White

Unsurprisingly, there has been little interest in 'fixing' all possible hint combos

For extra power, you can repeat them too

SELECT P.*
FROM Production.Product AS P
    WITH (NOLOCK, READUNCOMMITTED, NOLOCK);

Particularly emphatic

Zikato

For a limited time only, 100% more NOLOCKs

Paul White

@Zikato I confirmed the blocking happens when the query processor goes to load dependent views, anticipating that view matching might be tried later. When EXPAND VIEWS is hinted, that step is skipped, so no blocking.

There's no neat way to prevent automatic indexed view matching without that hint on Enterprise Edition, at least without a number of hairy side-effects.

Erik Darling

16:38

someone should get sacked for that

Paul White

and forced to work for Mongo?

not that sort of Sack perhaps

Erik Darling

17:27

dynamic seeks, trivial plans, simple parameterization, hidden implicit conversion

Paul White

17:38

hm?

Erik Darling

1

Q: Why Does an inequality Search on an indexed column Give constant scans

Using the StackOverflow2010 database, I can create an index on the users table as follows: CREATE INDEX IX_DisplayName ON dbo.Users ( DisplayName, UpVotes ) And then run an inequality search on the key of the index: SELECT DisplayName, UpVotes FROM Users WHERE DisplayName <>...

Paul White

Ok I'm going to start suspending people for answering in comments, then invoicing for the time

Zikato

@PaulWhite Thank you for checking

Erik Darling

@PaulWhite i was mobile, walking around a liquor store

Paul White

Have a mobile suspension and invoice

Erik Darling

17:43

i'm going to change my name to Erik Darling - Mobile At The Moment

then i'm invincible

Zikato

twitter.com/Zikato25/status/1566505825757282305

Missing N in NVARCHAR

Erik Darling

etflix

Zikato

my first idea was Zorro rotated 90°

Paul White

Erik Darling

it me

Paul White

17:48

you've bulked up

Erik Darling

18:00

hey, vacation will do that to you

Paul White

That guy was p cool, I was sad when he died

Zikato

Is that from Goldeneye?

Paul White

Yeah

Zikato

pen spinning is cool

Paul White

Really is

Shame about the evil streak and whatnot

No one's perfect though

Zikato

18:04

I can only do a ThumbAround, Sonic and FingerPass

Paul White

Well that's three more than me

Have you tried being more evil

Zikato

well I could make a coat out of Dalmatian's puppies for the winter

Paul White

It's a start, but cartoon evil doesn't really count for much

No rush

Erik Darling

get some henchmen like paul did

can't be evil without henchmen

or at least goons

Zikato

best I can do is minions

Paul White

18:07

Ultimate aim is an underground lair like Solomon

Zikato

Rutzky?

Paul White

Solomon Rutzky, Raleigh, NC, United States

66.2k 6 137 268

sql-server t-sql sql-server-2012 collation sql-server-2008-r2 sql-server-2014 sql-clr

@Zikato Seems to fit the facts

Though why anyone would choose NC is beyond me

Erik Darling

i hear it's good for jogging

Paul White

popular with bass players

Erik Darling

gas stations have good whiskey selections

not quite up to par with wisconsin gas station pizza of course

Paul White

18:11

aren't Nickelback from there

Erik Darling

i'm unfamiliar with their biography

Paul White

@JoshDarnell could tell you all about it

If he weren't busy rebuilding your website from the ground up

Erik Darling

i wonder what they're worth with today's inflation

quarterback?

Paul White

I don't understand the metric system

Zikato

there's tons you don't understand then

Erik Darling

18:15

america is worth more

deal with it

Paul White

the typo twins strike again

Erik Darling

4q

Paul White

ha ha

@ErikDarling what is the net worth up to these days

Erik Darling

trillions of freedoms i hear

Paul White

that much eh

just casually dropped a 5-part series link into the dynamic seek Q & A

p sure no one noticed

Erik Darling

18:19

well the market is closed today, labor day

Paul White

Oh I had vaguely heard it was a holiday weekend of some kind

labor day with no u

Zikato

feeling cute, might drop a 5-part series link into the dynamic seek Q & A

Paul White

might rollback later

Erik Darling

i stopped looking to avoid further comment-answer violations

now why the devil would you do this with an edit

13

A: Can you explain this execution plan?

The constant scans are a way for SQL Server to create a bucket into which it's going to place something later in the execution plan. I've posted a more thorough explanation of it here. To understand what the constant scan is for, you have to look further into the plan. In this case, it's the Comp...

> ADDENDUM: That last sentence is off. There were two seeks. I misread the plan. The rest of the concepts are the same and the goal, minimal passes, is the same.

Paul White

some people really don't get the concepts of editing & revision history

or don't like it, whatever

Erik Darling

18:25

at least it's not an edit in a comment

John K. N.

@PaulWhite Yes, I noticed after searching. Hence the removal of my comment.

Paul White

@JohnK.N. Oh it was still there when I replied

Erik Darling

18:49

life comes at you fast

Paul White

FAST 1

I wonder what people expect to happen when they leave an anonymous and poorly-considered comment on my blog.

Perhaps they think I'll publish it and respond to them.

Life is so full of little disappointments I guess.

mustaccio

19:26

> labor day with no u

Canada -- is where they put "u" in labour

Charlieface

19:51

@PaulWhite IMO you shouldn't edit someone else's answer to drop in links to your own blog, even (or especially) if you are the mod. Self-promotion and all that. Just put in a comment, I would probably have added it anyway.

Paul White

20:08

Don't be so tedious. It either improves your answer or it doesn't. You already had two links to my work in there. I left a lighthearted edit comment. Do try to develop a sense of humour, even if only a basic one. Jesus

3

Hi @charlieface, I think given Erik's comments above along with your answer, simple parameterization is the answer - something for me to read up on — SEarle1986 3 hours ago

Transcript for

The Heap™ – Consultancy ©®