The Heap™ – Consultancy ©® - 2017-10-18 (page 1 of 2)

I've never posted one of these before so let me know if any information is missing. I have a relatively simple query that performs pretty poorly with the default sample size on a few key columns. The costing of a clustered index scan used by a row count spool appears to be impossibly low. I will...

sp_BlitzErik

> This is the SQL Server version that I'm testing on:
Microsoft SQL Server 2014

Smh

Joe Obbish

what

sp_BlitzErik

12:30 AM

Basically 2012

Joe Obbish

it was over a year ago

Paul White

1:02 AM

@sp_BlitzErik Already added to my answer

But thank you

"Produce content with long-term value" -- well, there go all my answers. — sp_BlitzErik 13 hours ago

Ha!

Joe Obbish

I was just happy that I finally found a question that Erik already hadn't answered

Paul White

@JoeObbish Now we've established that challenges aren't off-topic, are you going to try setting one? Or ask a new meta about how they should be presented? I don't mind either way.

Joe Obbish

@PaulWhite Did we establish that? Maybe I missed something

sp_BlitzErik

@PaulWhite you're a peach.

Paul White

@JoeObbish That's how I assess the outcome of the meta (going only by upvotes), yes. There are some concerns about topicality, but nothing that should prevent a good example being well-received I reckon.

Joe Obbish

1:08 AM

is there a way that I could ask the question so that upvotes go to me and downvotes go to you?

Paul White

@JoeObbish It's pretty rare (here) to get overwhelming support on meta. As I read it, you're good to go, as long as you take the criticisms on board.

@JoeObbish I doubt you'd get much in the way of downvotes, but I've been wrong before.

Today, my only real reservation is keeping them within Q & A format, and preventing them taking over the site.

It does feel a bit like "fun" but I can get past that.

Joe Obbish

hmm

I did find it hard to express what I was after in the meta question

Paul White

That's often the way.

Asking questions is hard anyway, probably doubly so on meta.

sp_BlitzErik

@PaulWhite I could make them not fun for you.

Joe Obbish

the only thing that comes to mind is "Can anyone construct a data set that gets worse compression with columnstore than rowstore?"

I don't think that would be covered by the meta question

Paul White

1:12 AM

@sp_BlitzErik Appreciated :)

Joe Obbish

or "Can anyone construct a demo where nesting views leads to a measurable performance hit compared to using derived tables?"

that one has bugged me for years

it's one of those things that seems like someone said it 15 years ago and everyone has repeated it since

sp_BlitzErik

Don't derived tables not get inlined the same way views do?

Joe Obbish

@sp_BlitzErik that's the thing

sp_BlitzErik

Like since they can't be referenced multiple times or whatever

Joe Obbish

I've never heard a serious warning along the lines of "don't nested too many derived tables"

In fact I go deep all the time

sp_BlitzErik

1:16 AM

Ah

Joe Obbish

all

the

time

sp_BlitzErik

Well, you have a blog

Paul White

There's precious little difference between chaining CTEs, nesting derived tables, or views.

Joe Obbish

I KNEW IT

Paul White

Nesting views is just something people do a lot.

Joe Obbish

1:17 AM

@sp_BlitzErik How is that relevant? I'm not following

sp_BlitzErik

@JoeObbish 🤔

Paul White

@JoeObbish I think you'd have to think about that a bit and phrase it carefully to avoid getting trivial answers. It would need to be clear what success looks like as well.

Joe Obbish

right, as you said it's not an excuse to ask bad questions

I mean, it's not like chat or anything like that

Paul White

I'm hoping snapshot views will help with some of the top-1/min/max/per group views.

Joe Obbish

has MS said anything publicly about them?

Paul White

1:20 AM

@JoeObbish Not that I've seen.

sp_BlitzErik

@JoeObbish just that they're not compatible with column store.

Jk pal

Paul White

Ooo you had me

Joe Obbish

@sp_BlitzErik no EAGL

you had me too

Paul White

I was just about to ask for a link

Joe Obbish

ok I think I got a decent "challenge" question

do either of you know of a better source than sys.messages for getting random words?

within SQL Server, of course

Paul White

1:23 AM

Nope. I dislike sys.messages quite a lot though. Last time I used a public download of the complete works of Shakespeare I think.

Just personal preference, wouldn't read too much into it.

Ha ha read

Joe Obbish

too late

@PaulWhite pastebin.com/e3tgJDGC

way better than Shakespeare imo

Paul White

@JoeObbish What am I looking at there?

Joe Obbish

"to JOIN use generated re-establishing scan memory-optimized"

beautiful

a random string generated from sys.messages

Paul White

Oh OK.

Joe Obbish

"Failed has not to function."

can't argue with that

sp_BlitzErik

1:32 AM

@PaulWhite what's your issue with it?

Paul White

@sp_BlitzErik plan

Joe Obbish

I was at first confused and then amused by the halloween protection needed when inserting from it into another table

sp_BlitzErik

@PaulWhite serial? Underlying query?

Paul White

ugliness

sorry eating my lunch

Joe Obbish

I got it

I want to be one of those people who uploads plans of DMV queries to paste the plan

@sp_BlitzErik brentozar.com/pastetheplan/?id=Hykbx4NT-

it's disgusting!

although Paul may be referring to something else

sp_BlitzErik

1:38 AM

@PaulWhite do enjoy

@JoeObbish I think it's cute

Joe Obbish

new yorkers have such poor taste

oh yeah

I need to go buy lemonade apples

but before that, Erik I have a dumb hardware-related question for you

sp_BlitzErik

Shoot

@JoeObbish we can afford it.

Paul White

@sp_BlitzErik I think it's when it's joined to itself a few times. I seem to recall thinking the plan was particularly ugly; and yes, the serial thing.

Compared with the standard constant scan numbers generating thing, anyway.

I guess beauty is very much in the eye of the beholder.

Maybe it wasn't sys.messages at all. Ho hum.

sp_BlitzErik

1:56 AM

Three cross joins with it will slow things down iirc

@JoeObbish email me the question, going to bed soon

Joe Obbish

will ask later

@PaulWhite The store is sold out of lemonade apples

Can you send me one through the Post?

Paul White

@JoeObbish That's your punishment for procrastination!

Let's hope you have learned an important lesson today

Joe Obbish

hold on here

I had to do research before such an important purchase

why should I be punished for that?

Paul White

2:20 AM

Because life is unfair

And competition for apples is strong

Joe Obbish

Will you send me an apple if I stump you with a sql challenge question?

Paul White

Probably not

We can't just be sending apples willy-nilly

Also: export restrictions and biosecurity

I could probably send you a picture of an apple

Joe Obbish

in the Post?

Paul White

Fax if you like

waits for Joe to look up what a fax is

Joe Obbish

sticking with the MS Access theme, eh?

Paul White

2:26 AM

Very much so

I've decided to fully embrace the retro

The modern world is just too confusing

Joe Obbish

first step is to ditch the smartphone

Paul White

I mean, storing data in columns sheesh

My phone barely qualifies

NZD 99 Huawei something or other

Oh it's a Huawei Y6 Elite apparently. Would hate to see the non-Elite model.

Joe Obbish

I knew you were an elitist

lg.com/ca_en/cell-phones/lg-LGC441

Paul White

Ooo! Review Queues are Red! AWOOGA

@JoeObbish A proper phone

I had a Motorola Star-Tac once

Motorola StarTAC

The Motorola StarTAC is a clamshell feature phone manufactured by Motorola. It was released on January 3, 1996, being the first ever clamshell/flip mobile phone. The StarTAC is the successor of the MicroTAC, a semi-clamshell design that had been launched in 1989. Whereas the MicroTAC's shell folded down from below the keypad, the StarTAC folded up from above the display. In 2005, PC World put StarTAC at #6 in The 50 Greatest Gadgets of the Past 50 Years. The StarTAC was among the first mobile phones to gain widespread consumer adoption; approximately 60 million StarTACs were sold. == Release... ==

Joe Obbish

first ever, eh?

Paul White

2:32 AM

According to WP

Joe Obbish

I was 7 when that came out

Paul White

I wanted a microTac but was out of my price range at the time

@JoeObbish In the modern world you'd be on your third or fourth phone by that age

Joe Obbish

I can't understand that at all

Paul White

Me either but there we are

Paul White

3:25 AM

One more week of identicon

Paul White

3:39 AM

3

Q: How does the review-needed indicator work exactly?

With the rollout of the new top bar, you’ve probably noticed the new review indicator: It’s possible there will be a different review icon by the time you read this. But this post is just about the red circle on the icon. You’ll see the indicator is turned on: if any review queue has a red d...

discussion review top-bar

Right at the moment, there is a bug that prevents the indicator from lightning up for people with less than the moderator tools privilege. (See this screenshot.) The bug should be fixed soonish. — Jon Ericson ♦ 9 mins ago

Joe Obbish

3:51 AM

"Hi there,I check your blog named 'Create a website or blog at WordPress.com' daily."

the benefits of improperly configuring your site

hot2use

6:11 AM

@JoeObbish Yeah

Morning

Andy K

7:16 AM

Morning

Tom V

Morning

Paul White

Morning

Tom V

@PaulWhite It always makes me chuckle when you say that

Paul White

@TomV I've adjusted to it :)

Never mind it's 8:20pm

Andy K

@PaulWhite you live in Australia?

9

Paul White

7:19 AM

@AndyK Don't you start.

6

New Zealand.

Andy K

@PaulWhite lips sealed, Sir ;))

Paul White

That's more like the level of respect I should get around here!

See if you can educate the others :-/

Andy K

@PaulWhite will try my best (or something like that)

Paul White

Good on ya

Tom V

We're incorrigible

gbn

7:21 AM

He's clearly not Australian, he said "morning" not "G'day cobber"

Andy K

going back to mining my dirty data

Paul White

7:38 AM

@TomV The Heapizens shall never be corridged!

Paul White

8:00 AM

Oh, Heapsters.

Andy K

9:22 AM

@ypercubeᵀᴹ thinking about our conversation yesterday

I found something similar today

SELECT dbo_T02_Customers_Kunshan.[customer code] AS Customer_code,
       Nz([Update_name],[customer name]) AS Customer_name,
       IIf([dbo_T02_Customers_Kunshan].[Global customer]="azo","azo",IIf(Nz([dbo_T02_Customers_Kunshan].[Global customer],"")="","#ND",[dbo_T02_Customers_Kunshan].[Global customer])) AS Global_customer,
       dbo_T02_Customers_Kunshan.Address1,
       dbo_T02_Customers_Kunshan.Address2,
       dbo_T02_Customers_Kunshan.Address3,
       dbo_T02_Customers_Kunshan.Address4,

Would this be the same as this query

SELECT dbo_T02_Customers_Kunshan.[customer code] AS Customer_code,
       Nz([Update_name],[customer name]) AS Customer_name,
       IIf([dbo_T02_Customers_Kunshan].[Global customer]="azo","azo",IIf(Nz([dbo_T02_Customers_Kunshan].[Global customer],"")="","#ND",[dbo_T02_Customers_Kunshan].[Global customer])) AS Global_customer,
       dbo_T02_Customers_Kunshan.Address1,
       dbo_T02_Customers_Kunshan.Address2,
       dbo_T02_Customers_Kunshan.Address3,
       dbo_T02_Customers_Kunshan.Address4,

I found this article explainextended.com/2009/09/15/…

ypercubeᵀᴹ

@AndyK No, not necessarily. The EXISTS would be equivalent to IN (or to INNER join if tmp_nameunified_customer_azo.Customer_code is the PK)

Not to LEFT JOIN.

They would be equivalent only if there is an FK that makes the LEFT join equivalent to INNER join.

or if the query has extra WHERE clause that makes the LEFT join equivalent to INNER

Andy K

@ypercubeᵀᴹ ok

@ypercubeᵀᴹ The LEFT JOINin the first query is here to make sure that if there is a match for ` dbo_T02_Customers_Kunshan.[customer code]` in tmp_nameunified_customer_azo.Customer_code then take the row.

too bad there is no other way to do that

Andriy M

@AndyK What is the tmp_nameunified_customer_azo table needed for? How does it participate in the SELECT clause? Is it for the Update_name column?

Andy K

9:38 AM

@AndriyM thanks for that. you're correct.

it is for Update_namecolumn

this can be dropped then

Andriy M

If it's only one column that you are pulling from that table, than one other way is to use a correlated query.

If Access supports correlated queries, I mean

And I'm assuming here that tmp_nameunified_customer_azo.Customer_code has no duplicate values.

Andy K

If we happen to meet one day, I treat all of you but one by one to the restaurant

@AndriyM no duplicate values, yes

@AndriyM thanks for spotting that

Andriy M

Please keep in mind that correlated queries are often not very efficient (probably less efficient than equivalent left joins, on average).

I only mention this method because it does present an alternative to a left join, and sometimes it works well enough, just not very often.

Andy K

@AndriyM there is not much use of a correlated query in here. Most of the queries she has written were to amend datas that she could not ask the team in China or elsewhere to amend because she got embroiled with them.

the table tmp_nameunified_customer_azo is not much of use here, it contains only 6 lines

dezso

10:00 AM

I suddenly turned into a how-to-set-up-a-printer hero

the joys of moving to a new office

McNets

morning

Andy K

10:15 AM

Alesso - Heroes (we could be) ft. Tove Lo

►

Andy K

3:23 PM

see ya

ConcernedOfTunbridgeWells

@PaulWhite My dad used to have an old klaxon. It did, indeed go awoogah if you powered it with a six volt battery.

Dodi82

4:41 PM

@sp_BlitzErik Looking good thanks

Tom V

5:35 PM

@dezso be very careful

sp_BlitzErik

5:46 PM

@TomV surprised you're not all over this

1

Q: Troubleshooting non-working Plan Guide in SQL Server 2008 R2

I have problems with a Plan Guide I have created in Microsoft SQL Server 2008 R2. The Plan guide is purposed for a performance issue in Microsoft Dynamics AX 2009 and forces a specific question to use a TABLE HINT index. However, when I perform a SQL Profiler Trace, the execution plan still uses ...

sql-server-2008-r2 performance-tuning profiler microsoft-dynamics plan-guides

ypercubeᵀᴹ

6:03 PM

@JackDouglas any chance to add Oracle 12c in dbfiddle?

Joe Obbish

@ypercubeᵀᴹ last time he said there was a licensing issue

Tom V

@sp_BlitzErik travelling today and in a restaurant right now

ypercubeᵀᴹ

@JoeObbish Ah, thnx

Joe Obbish

which is definitely too bad

Tom V

But thanks I hadn't seen it yet

Joe Obbish

6:06 PM

over four years old at this point

sp_BlitzErik

@JoeObbish you are?

Tom V

He probably shouldn't be bossing the optimizer like that though

ypercubeᵀᴹ

dba.stackexchange.com/posts/188691/revisions

@AndriyM an edit, a bit ironic

(jeagl: AndriyM correcting identitiy to identity)

3

Andriy M

Ahh, I see

sp_BlitzErik

slow day, huh

Andriy M

6:11 PM

or night

both actually

Joe Obbish

@sp_BlitzErik well, yes

not sure what you were going for there

sp_BlitzErik

did you still have a hardware question?

Joe Obbish

yes

sp_BlitzErik

your mom

Interjection: your mom

(pejorative, US, Britain, slang) A general purpose insult, often used to comedic effect due to becoming stale.
Conrad: I am not sure if this phrase is commonly used...
Dominic: Your mom is commonly used.
2004, Napoleon Dynamite
Deb: Well, isn't anyone else here trying to earn money for college?
Kip: Your mom goes to college.

Joe Obbish

you too

Tom V

6:14 PM

Also are you sure the code you say that is working actually is working? You seem to have a single quote issue there — Tom V 1 min ago

Or am I missing something

I have Tom's phone not TomTom's so it won't spin up a vm

Joe Obbish

@sp_BlitzErik are you ready?

sp_BlitzErik

my soul is prepared

Joe Obbish

9:26 PM

@sp_BlitzErik everything is burning to the ground

that is why I have not asked my question yet

sp_BlitzErik

@JoeObbish anything i can help with?

:D

Joe Obbish

I can pay you in popcorn

sp_BlitzErik

deal!

Joe Obbish

I don't understand...

sp_BlitzErik

looks like an fbi sting

Paul White

9:36 PM

What in the name of small fluffy bunnies is gourmet popcorn

Joe Obbish

ask the new yorker

he can explain it at great length

sp_BlitzErik

it has strippers and cocaine on it

maybe a lobster

Joe Obbish

I was going to guess that you used champagne instead of oil to pop it

sp_BlitzErik

you eat it in a bathtub while wearing a top hat

2

note to self: paul has a notification for popcorn

sp_BlitzErik

9:54 PM

comments always make me regret answering

1

Q: Sql Server 2016 not using index used in 2012

What do I check? I have almost identical tables with a slight difference in row count. One on 2012, and the other on 2016. Indexing is identical. This drop dead simple query for single record using a single column. 2016 version has 8254356 rows 2012 version has 8254427 rows

sql-server-2016 index-tuning nonclustered-index

Joe Obbish

@sp_BlitzErik yep, also asking questions

sp_BlitzErik

at least i've never answered a question about access

Joe Obbish

me either

sp_BlitzErik

pinned messages disagree

but i suppose you'll pull some lamak-esque semantics to escape your fate

as a lowlife access dba

Joe Obbish

it was a chat discussion. there was not a question asked on the main site

also, your hero recently answered an access question on the main site

sp_BlitzErik

10:03 PM

i didn't know karl lagerfeld had a dba.se account

Paul White

You'd be surprised who we have here hidden behind userxxxxxx names

And bland identicons

sp_BlitzErik

when brent fires me for spending all my time in this chat i'm gonna change my name to user9223372036854775807

Joe Obbish

shouldn't you change it before you get fired?

sp_BlitzErik

then he'd know who i am

cartoon is a dead giveaway

Joe Obbish

go for the identicon

sp_BlitzErik

10:11 PM

you're a worse criminal than a dba

Joe Obbish

all the cool kids are doing it

sp_BlitzErik

my identicon color is an awful blue

ain't happenin

Joe Obbish

don't be such a fashionista

Paul's is brown

it's the worst identicon I've ever seen tbh

sp_BlitzErik

@PaulWhite cheeky.

real cheeky.

Paul White

@JoeObbish Six days to go.

> This does not give me confidence in the new optimizer.

This does not give me confidence in the OP.

@sp_BlitzErik What have I done now? :)

sp_BlitzErik

10:20 PM

mod stuff

Paul White

Ah well that will happen.

sp_BlitzErik

until the rev.

Paul White

Making the most of the time I have left

sp_BlitzErik

did you get your $100 in the mail?

Paul White

@sp_BlitzErik Did you not see my reply?

But yes I did and it is fully awesome

sp_BlitzErik

10:23 PM

hah, no. i have a man servant that rings church bells when email from you shows up.

perhaps you didn't reply all.

ypercubeᵀᴹ

@PaulWhite I suppose you can change it in 5 ;)

Paul White

@ypercubeᵀᴹ Trust me I check it every day.

ypercubeᵀᴹ

I mean you can say that it's NZ time

Paul White

@sp_BlitzErik Just checked it came from Brent alone with no cc:

@ypercubeᵀᴹ Oh I see. Pretty sure Joe would be onto me pretty sharpish if I cheat.

ypercubeᵀᴹ

Or change it today. SET TIMEZONE = +120 hours

Paul White

10:28 PM

Still what's he gonna do.

sp_BlitzErik

Oct 10 at 22:46, by Joe Obbish

DATEADD(DAY, -2, THE_REV)

Paul White

@ypercubeᵀᴹ I'd be lying if I said I wasn't tempted.

It had to be bloody brown eh

ypercubeᵀᴹ

We'll find a question to distract him

Paul White

Maybe I could send him an apple instead

sp_BlitzErik

tell him his row goal demo is wrong

Paul White

10:30 PM

I try not to state the obvious

sp_BlitzErik

get the aloe

Paul White

:)

sp_BlitzErik

> I wont insert any records only import more records later on from a excel sheet

:wat:

0

Q: Is there anything I can do to increase performance in the future once table gets bigger?

I created a table importing a excel sheet with 600,000 rows. Most of the queries I will run will be using C_CustomerID example: select * from testtable where C_CustomerID = 12345678 CREATE TABLE [dbo].[testtable]( [id_card] [int] IDENTITY(1,1) NOT NULL, [C_CustomerID] [int] NOT NULL, ...

sql

Paul White

BTW @sp_BlitzErik dba.meta.stackexchange.com/a/82 so please add sql-server as well as any version-specific tag.

sp_BlitzErik

didn't i?

Paul White

10:36 PM

@sp_BlitzErik The previous question, the faulty optimizer one.

Regarding the latest one though: stackoverflow.com/q/44687182

Seems this is an ongoing struggle

sp_BlitzErik

oof

wait so the question i answered should have 2012 AND 2016 tags or just 2016?

i'm guessing just 2016

Paul White

I would say so yes.

sp_BlitzErik

why can't i add tags to my answers?

"ace-answer"

Paul White

Self-answered.

sp_BlitzErik

"voting-ring-activate"

"suck-it-joe"

Paul White

10:38 PM

Ha!

sp_BlitzErik

oh boy, that's enough of trying to keep up with that

this is someone getting level 100 confused with level 400

Paul White

@sp_BlitzErik Is it fun being able to see deleted questions now?

sp_BlitzErik

only when i'm going to answer them :D

Paul White

> What the??? I think 2016 optimizer has been smoking something seriously strong.

grrr

sp_BlitzErik

yeah, that's where i bailed

I DON'T GET IT I'M ON A TOTALLY DIFFERENT SERVER WITH DIFFERENT SOFTWARE AND THINGS ARE DIFFERENT

Paul White

10:46 PM

It is weird.

sp_BlitzErik

quite the oddity

i'd file a connect item

Paul White

Time to go DEFCON 1 I reckon

Possible alien invasion

sp_BlitzErik

possible illuminati interference

they used the undocumented INTERCEPT operator to change the query plan

Joe Obbish

@sp_BlitzErik compare my answer to Brent's

I didn't want to say anything because I'm a polite guy

sp_BlitzErik

link

Joe Obbish

10:52 PM

14

A: 'SELECT TOP' performance question

SQL Server builds different execution plans for TOP 100, using a different sort algorithm. Sometimes it's faster, sometimes it's slower. For simpler examples of it, read How Much Can One Row Change A Query Plan? Part 1 and Part 2. For in-depth technical details, plus an example of where the TOP...

sp_BlitzErik

what should i compare? they're different questions

Joe Obbish

@sp_BlitzErik you're one of the people who voted them as duplicates!

sp_BlitzErik

yeah, i thought it should have been closed. voted to that end.

Joe Obbish

59 mins ago, by sp_BlitzErik

but i suppose you'll pull some lamak-esque semantics to escape your fate

Transcript for

The Heap™ – Consultancy ©®