The Heap™ – Consultancy ©® - 2015-05-06

« first day (1583 days earlier) ← previous day next day → last day (3287 days later) »

12:04 AM

I paid the price for using that word by having to type a long comment reply on my phone

12:28 AM

I'll remove my comment then.

2 hours later…

2:56 AM

@MikeFal @MikeFal "never seen a CPU bound SQL Server." A little while back, just to see what would happen, I put a clustered columnstore index on a 300M row "values" table. Then did PIVOT query to produce 130 columns. 24 cores at 100% for 10 minutes. Then I cancelled it. I guess you're just not trying hard enough!

3:21 AM

@MichaelGreen story of his life

4 hours later…

7:26 AM

@MichaelGreen @MikeFal I've seen quite a few over the years. Usually it's been one or more of the following, in order of likelihood: someone turning their server into a compiler by stuffing RECOMPILE hints into everything, weird app + query design (e.g. an app where each client calls a stored proc as frequently as possible, where the proc builds several temp tables and calls scalar functions on them), or spinlocks.

Joe Sack talks about seeing it from 'I/O related CPU usage' or some phrase similar to that, which I guess is a lot easier to make happen with fast storage.

Oh. And then there's the virtual machines built with 1 vCPU, because virtualization is different, and ESX will just sort it out, or something.

2 hours later…

9:52 AM

Any word on what happened to the site last night?

We have some cleanup work to do. Plus we'll be writing up a postmortem report. Thanks for your patience, folks!

Tweeted by StackStatus on May 5, 2015 at 10:51 PM

10:45 AM

No then :)

1 hour later…

11:48 AM

Love the comment on this "add flag for more perfect answer." Because some days perfection is just not enough.

11:58 AM

shouldn't the word be 'perfecter'? I'll start using this

a teacher of mine had a word: 'subminimum'

Seems like a valid tag to me. Belongs on SO though.

Good answer:

1

A: Why these characters are all equal in SQL Server?

All character data in SQL Server is associated with a collation, which determines the domain of characters that can be stored as well as the rules used to compare and sort data. Collation applies to both Unicode and Non-Unicode data. SQL Server includes 3 broad categories of collations: binary,...

Kris Gruttemeyer

Very good answer, indeed

12:16 PM

Finally...! I'm still surprised my answer got so many upvotes...

It's a good answer as well, just not as comprehensive as Dan's.

@MarkSinkinson It has a lot of views for a question posted yesterday. It was probably twitted or something.

1:10 PM

Just had the UKIP candidate in having a whiskey. Think he must've given up

I am wondering what would happen to your country after expelling all immigrants

possibly the only pub remaining would be yours

@Phil he didn't threaten to shoot you did he?

@MarkSinkinson asked me who I was voting for. Told him it was none of his business :P They're a shower of *****

1:31 PM

@Phil Mine looks scary.... yournextmp.com/constituency/65824/holborn-and-st-pancras

@MarkSinkinson Cannabis is Safer than Alcohol, hehe

morning heapers

morning

Moo ning.

1:51 PM

Morning, @bluefeet. Are you recovered from your vacation?

unfortunately no

2:23 PM

vtc please

0

Q: Any GUI tools for Progress?

What GUI tools I can use for Progress databases? Something similar like MySQL Workbench or Navicat for Progress?

progress-database

There is no such thing as Navicat for Progress. See navicat.com/products — dezso 58 secs ago

@bluefeet I went to and then realised I have no reps here. What a letdown :(

slacker

ahhhhhh, he's here!

This may have to be the single most asinine suggestion I've ever heard in my life.

dailytelegraph.com.au/news/nsw/…

2:31 PM

@billinkc you did this

boy they are having tough time

Stack Overflow is now experiencing a DDoS attack. We are working to mitigate.

Tweeted by StackStatus on May 6, 2015 at 2:21 PM

@Zane along the same lines, no fair giving your kids healthier food

@bluefeet ugh

@Zane also if you can afford to have a stay at home parent, they should still send the kid to day care

@bluefeet I blame @jcolebrand. I'm not sure why, but it's definitely his fault.

2:50 PM

love the trolling

twitter.com/Rockies/status/595738304890429440/photo/1

ConcernedOfTunbridgeWells

@SteveHémond It is technically possible on a one's complement or sign extended system - and I note some comments to that effect on the question. Whether it's meaningful to do so is an entirely different question.

@billinkc Or, the natural state of an Oracle database table.

@Phil Speaking of which, any chance of an appearance for drinkies?

@ConcernedOfTunbridgeWells Or postgres (maybe others, too)

@ConcernedOfTunbridgeWells when?

ConcernedOfTunbridgeWells

@Phil Considering doing it on the 21st/22nd.

I think @MarkStorey-Smith is in town by then.

You can still crash on my sofa bed if you need to.

@PaulWhite Nothing like a spot of good old percussive stakeholder management.

3:06 PM

Stack Overflow is now experiencing a DDoS attack. Frantically adding NOLOCK to more queries to mitigate

Tweeted by StackNoLockStat on May 6, 2015 at 2:50 PM

somebody feels funny

mitigate meaning

@bluefeet but... but... why?

@Zane why what?

Why would anyone attack innocent little Stack Exchange?

3:11 PM

.PARTY are giving away free domains now. theheap.party?

@ConcernedOfTunbridgeWells Of this month? That's bank holiday weekend innit. Going to a beer festival

forgetting your headphones 2 days in a row post vacation == sucks

@bluefeet last time I did that I went out an purchased a backup pair I keep at the office.

@Zane I normally keep a back up pair in my bag but I took those on vacation :(

So now you're stuck listening to the every single conversation happening within a 5 cube radius?

stupid RANGE RIGHT partitions

3:17 PM

Come down to the Heap Casino and Bar to see the SQL related comedy styling of @swasheck, @billinkc and more!

Hey @Zane, let me know if you need a broom.

I know the reverse sweep is coming.

kane's clavicle looks just fine

I'll fucking say.

meh. you guys have gone farther than the avs

3:25 PM

They threw every but the kitchen sink at Crawford last night.

He always seems to play his best against us.

i'm pretty shocked, too. the 'hawks' goaltending situation was a mess in the first round

Like I said Crawford always plays his best against the Wild.

I' have however been very disappointed in the play of one Thomas Vanek. Which is a bummer as I've long been a fan.

mtl looks like they could use him back :)

3:41 PM

With the way that game went if Dubes hadn't given one up early they would still be playing since both goaltenders were playing out of their minds.

@swasheck lol yeah. I'd rather the two Canadian teams not lose to teams from Florida and California.

sry. rooting for the bolts, too

I just want the Wild to get knocked out now that the Wings are gone.

Guh. I don't like the habs but I always root for hockey teams north of the Mason Dixon.

Rangers?

@MikeFal Why do you hate me so much?

3:44 PM

@Zane I don't hate you. I hate the Wild.

@MikeFal one in the same :)

@Zane well that's just, like, your opinion, man.

The rug really pulled the room together.

@swasheck When it comes to Hockey I always root for places where people actually play the game/have winter.

Never been to a hockey match. Looks exciting to watch live

3:55 PM

It's a great sport live. Lot's of action and things can happen at any time.

Can spectators get sin-binned? 'cos that'd happen to me :)

@Phil you can be removed from the arena by th PoPo

The sport's definitely getting more popular over here

Kinda miss going to the footy every Saturday

4:13 PM

Why will there be no footy every Saturday? Season over? Some artifact of the screwed up world cup timing?

4:25 PM

season's about over

4:48 PM

I'd go and watch some cricket, but watching paint dry is more fun

5:11 PM

I can't even pretend to understand cricket

That shit is messed up

it's like baseball without the skill and action

I'm sorry, as a chilean, baseball and action don't seem to go together

This might be a dumb question but this says that the option is only available for creating an index. Is the sort in temp db not used any other time?

They converted the rugby pitch near my house to a cricket field. I watched them practice for a bit. At some point, I'd like to sit with an enthusiast and have them explain a test match

@Lamak that's the point of my comment. baseball is boring because of the lack of action. cricket has less action than baseball.

5:15 PM

@swasheck I just didn't think that it was possible

@billinkc but you'd have to have a free week on your calendar to be able to see it through

That's why I specified test

and think I remembered the right one

@billinkc left?

I think Test is the short match versus the week long match

@bluefeet you can rebuild a table with sort_in_tempdb (beginning in 2008r2)

5:19 PM

@swasheck Well we are trying to reduce the size of our trans log backups, I was hoping that when we insert/update/delete to tables with indexes that option would rework the indexes in tempdb. But I'm guessing it wouldn't work that way

and by 2008r2, i mean 2008

@bluefeet like your actual insert, you're hoping to sort in tempdb?

@swasheck yes :/

I know dumb

obviously I've got no clue

not dumb ... just not sure it's possible

INSERT INTO
SELECT * FROM
ORDER BY WITH (SORT_IN_TEMPDB=ON);

something like that?

hmm, not exactly. When we insert and the data needs to be sorted in the indexes, we'd want that to happen in tempdb. Does that make sense? Or am I just speaking nonsense?

That facility sort of exists and is called DMLRequestSort. The optimizer requires sorted input to any index changes marked with that property. The sort will normally be performed in memory, though it may spill eventually, even after possible dynamic memory grant increases.

5:25 PM

ok. again, please. this time in english.

ok, let me try again.

@bluefeet not you, @Paul

@swasheck ha ok

Index modification operators like (Non-)Clustered Index Update/Insert/Delete/Merge have a property called DMLRequestSort. You can see it in the properties window in SSMS.

@PaulWhite right. and that (in-memory) sort itself wouldnt be "log-affecting" right?

@PaulWhite just funnin' with ya

5:26 PM

When set to 'true', the optimizer ensures rows arrive at the index-changing operator in index key order.

@swasheck Not directly, no. Except DMLRequestSort = true is required to get minimally-logged inserts.

SORT_IN_TEMPDB as an option is not persisted to metadata, though some would like it to be. Primarily for scripting out indexes etc.

I'm not sure if I'm helping, to be honest.

I tried.

No, that's good info. Basically we are trying to write as little as possible to our transaction log. I thought we could use the sort_in_tempdb option to minimize index changes, but I guess not

Not really, no. DMLRequestSort is the thing, and SORT_IN_TEMPDB doesn't affect that. Changes arriving in key order should also reduce logging due to page splits in the index.

@PaulWhite right. that's why i assumed @bluefeet was ordering her insert by key order :)

@swasheck we are not.

I will do some testing though

@swasheck You don't always have full control over that. INSERT...SELECT...ORDER BY doesn't quite have the semantics of an ordered insert. And besides, there may be additional indexes besides the clustered index.

5:33 PM

@PaulWhite that was a nice way of saying, "that's way too simplistic of an approach, seth" :)

Everything's as complicated as you want it to be in SQL Server.

Probably other products too.

not MySQL. Nothing is complicated there

Ha.

They should call it BizarroSQL

6:04 PM

Oh boy SSIS packages that make heavy use of ##Tables

I'm getting to see our garbage code A game this week.

I am firmly convinced that anyone who uses ##tables in an SSIS packages should be hung from their entrails.

to be fair this is an old package on the other hand ugh!

Did people know how to SQL back in 2007?

What does your query plan look like if you remove those hints? — Zane 30 secs ago

6:38 PM

I get nervous about having those sorts of hints.

7:11 PM

2

Q: SQL Server 'CONTAINS' slower than 'LIKE' with multiple expressions

This takes about a minute: SELECT * FROM someBigTable WHERE someColumn LIKE '%foo bar%' OR someColumn LIKE '%hey macarena%' This takes 4-6 minutes: SELECT * FROM someBigTable WHERE CONTAINS(someColumn, '"foo bar" OR "hey macarena"') I repeated each query several times to make sure it wasn't...

sql-server sql-server-2012

^^^^^ wait what?! Those execution plans.

what about them?

Did you read the question? he's saying he just changed IN to Contains but if you look at the query plans the indexing on the tables is different. Other tables are involved as well as a TVF.

@Zane that's because the second query uses fulltext index

that can't be used on the first one

Yeah but that wouldn't change that the first query uses an RID lookup to a heap.

would it?

yes. it would. because the access paths are totally different. you're retrieving different data. see @Lamak 's comment

those queries aren't equivalent. The first can return results like "mafoo bartender" — Lamak 42 mins ago

7:19 PM

Interesting.....

@Zane yup, the clustered index is on a different object. If you look the second plan, you can see that it still scans the original table (no clustered index there)

My knowlege of Full-Text is lacking.

and you can only have one clustered index on a table

MySQL allows you to have as many clustered indexes as you want

i dont care about your sql

7:26 PM

@Lamak That's the part I'm confused about. Full text is showing as a Clustered index scan.

@Zane yeah, but as a different object. It's like another table with that index

@Zane because the full-text index is clustered

Gotcha.

I did not know that.

How did you expect Oracle to know what columns were the 'right' columns if you didn't make it explicit? You should always, always, include the column names in an insert. — Jack Douglas ♦ 45 secs ago

Never seen one of those in the wild.

7:28 PM

a lot of effort expended on that question (not by me) to fix a basic mistake!

8:06 PM

either you can read minds or are really making a big guess at what the OP wants — KM. 2 mins ago

Or maybe I tried to actually understand what's being asked

8:20 PM

Seems right to me.

Can we VTC this.

The link to the other question is dead so there is no relevant information.

which one?

0

Q: Send Row from one DataFlow Task to Another [Solution]

In response to my other post SSIS DataReader ConnectionString I was looking for a way to send processed rows from one DataFlow Task to another to simplify my design and have better control.

ssis dataflowtask

Oops apparently didn't hit enter.

voted

Biking home before the storms hit.

Be back in 20'ish

good luck

8:32 PM

0

A: Unpredictable row transfer

I figured it out , turns out that I horribly misconceived the idea that matching only one column to another related column in LookUp Transformation was sufficient to mark the row as unique. All the rows in that first column had same data. So after I matched all the columns from input column to ...

I think is better classified as unpredictable user

@billinkc close as 'too localized'?

@JackDouglas SO doesn't have that

@JackDouglas dude, it's been years (or...year)

@Lamak I can't tell the difference between the sites :(

get with the program @JackDouglas ;P

8:41 PM

you.....can't tell the difference between DBA and SO??...blasphemy

where's the real @JackDouglas?

since this: meta.dba.stackexchange.com/q/1242/1396

on the plus side I can still tell the difference between meta.dba and meta.so

@bluefeet couldn't handle.. had to put your name on a card :)

OH: “Who likes pivot? No, who ACTUALLY likes pivot?"

Tweeted by Kendra_Little on May 6, 2015 at 5:08 PM

@Marian I'll have to respond to that

hope I didn't step on your blue feet :)

9:44 PM

sqlsaturday.com/444/eventhome.aspx

Be there or be square

is this the canonical @AaronBertrand MERGE post?

mssqltips.com/sqlservertip/3074/…

It's the one I reference

eggscellent

10:13 PM

@swasheck @zane Come visit us for SQL Saturday!

@mmarie i would but it's really crummy timing

ugh

you say that every year

we even changed the week we are having it this year

you need to change the month

strike that ...

you need to change the season

@mmarie at least i'm consistent

11:07 PM

@mmarie I can go there or denver.

« first day (1583 days earlier) ← previous day next day → last day (3287 days later) »

Transcript for

May '156

The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile