The Heap™ – Consultancy ©® - 2015-10-05 (page 1 of 2)

1:10 AM

@oNare Then you should specify some context. Random advice without context is worthless, regardless of how useful the advice would be with context. This is especially true when it is tangential to the OP's stated goals. I recommend they double their RAM, save money, and wear sunscreen. None of those things are helping the OP solve their problem, and none of them belong here. — Erik 47 secs ago

Let me know if I'm a jerk and should delete.

Michael Green

1:41 AM

@Erik Nice line in sarcasm. Have you been read Aaron's posts?

Erik

1:56 AM

@MichaelGreen I've read many of his posts (pithy or not). I meant this comment to be a WTF without scrapping the "Be nice" policy. The original comment irked me...

@MichaelGreen You're right though, I could see @AaronBertrand writing something similar.

Paul White

3:10 AM

@srutzky Terrific, thanks. Bounty awarded.

And congrats on becoming a Fanatic :)

Michael Green

5:20 AM

@Erik ... and you don't like to be irked (TM).

Paul White

Indeed.

Andriy M

@Erik I can't tell if your reaction was over the top because I feel mine would likely not have been nicer. I found both the suggestion and the response to your comment very provocative.

Paul White

It was irrelevant to the question at hand anyway.

Paul White

5:50 AM

0

Q: How to display all columns with empty string in MySQL

I want to select all columns in a certain table and replace its value to empty string without updating the field values in the database. I have already done it for a column only. Example: SELECT if(a.id,'','') FROM person a LIMIT 1;` What I want is that, I don't have to enumerate all the colu...

What? Why? Huh?

ypercube

6:28 AM

I hope the OP picks an answer soon. This q seems like a train wreck.

9

Q: Explaining 2NF vs 3NF with an example

I have a problem with second normal form (2NF) and I have been unable to solve it by using Google. It is making me crazy because I am a teacher and I don't want to teach wrong stuff to my students. Let's have a table with 5 fields. Gradings = {StudentName, SubjectCode, SubjectName, #Exam, Grad...

database-design normalization relational-theory

Paul White

@ypercube I was hoping so too, but the OP hasn't been seen onsite since Sep 25. I did clean up a little earlier, but it seems just OK to leave it be otherwise at the moment. If nothing else, it shows how normal forms are not as simple as one might hope.

Just out of interest, do you disagree that the top answer (or answers) to in fact address the question sufficiently to be marked correct?

ypercube

No, the top voted (5) answer, I agree with.

It's weird how so many people get it wrong. I mean normal forms are not trivial but I would think that someone who tries to answer at dba.se would at least check their answer first.

Paul White

@ypercube I have to confess I'm not overly impressed with philipxy's 'answer' or comments there.

ypercube

The last paragraph (or half of it) would suit more as a comment.

Paul White

@ypercube Much of the answer is a critique of the question. Comes across to me as superior-sounding, without actually doing much to help.

@ypercube Which answers do you feel worthy of a down-vote?

Aside from Edward's.

ypercube

6:39 AM

The comment I made there

"when you create the table you must choose one set of candidate keys to make the primary key. The remaining columns become non-prime attributes." This is plainly wrong. — ypercube Oct 1 at 15:25

Paul White

I'm in two minds about TommCatt's answer, for example.

ypercube

applies to BillThor's answer as well (it's in the starting paragraph)

Paul White

Thanks.

ypercube

but it has a +3-1 ...

Paul White

Had.

ypercube

6:40 AM

Tommcatt's original was wrong.

Paul White

Yes, I mean post-edit.

ypercube

he now seems to have edited it but to be honest, if someone comes now and reads it, is it understandable?

Paul White

Certainly not a model answer; hence in two minds. Thanks for the feedback though.

Overall, I feel the Q & A is not at trainwreck status yet.

ypercube

I wouldn't downvote if I saw it now.

I'm thinking about removing my previous downvote

"If you pick one of your candidate keys, either subject_code or subject_name becomes a non-primary candidate key." This is plainly wrong. The rest of the analysis has some valuable points but when one starts from a false point, we can't rely on the conclusions. — ypercube 46 secs ago

@PaulWhite tell me if my comments are "overdue" and I'll remove them.

or just scrap them ;)

Paul White

@ypercube They seem to continue to be relevant to me.

Phil

8:00 AM

@JackDouglas That doesn't sound good. Have they got rman backups?

dezso

9:13 AM

good morning

I need some native English speakers

Phil

Twitches

Colin 't Hart

@dezso Loudspeakers? :-D

dezso

hahaha

I wrote a sentence is some blogpost, which starts like that (speaking about to, completely unrelated things):

> First, don't, ever, use blah blah

does this make sense? a reviewer removed the last comma - that way it sounds very strange to me

here I try to put some emphasis on 'ever'

Colin 't Hart

that comma is essential!

But you could make it

First, don't -- ever -- use blah blah

The "ever" is optional. Without the comma the "don't" becomes optional!

dezso

9:30 AM

yeah, looks better

Colin 't Hart

Or you could do

First, don't ever use blah blah

ypercube

@ypercube Sorry if my question confused you. I need all fields by distinct email. — deepu 3 mins ago

haha, i'm not the one confused ;)

Paul White

@dezso I would write that, "First, don't ever use..."

Oh, like Colin already said :-/

dezso

@PaulWhite is it something to be congratulated for?

Paul White

@dezso Well, it is a gold badge. They're quite rare.

dezso

9:41 AM

@PaulWhite I think it was my first gold badge

Paul White

But yeah, arguable about whether 100 days on site should be congratulated or not :)

dezso

somehow I feel it deserves more condolences than congratulations :D

2

ypercube

@dezso that would be appropriate for Fanatic at SO.

Paul White

Perhaps, yes.

dezso

0

Q: DB Engine Stolen Server Memory is too high

How to solve this error? SQL DB 2012 Engine Stolen Server Memory is too high. I run query and get this counters: Stolen Server Memory (KB) ...

sql memory

solution: call the police

6

Andriy M

9:50 AM

@dezso Unless it said, "First, don't ever call the police if you want your memory back."

5

Phil

I do wonder what they'd say if you said "I've lost my memory". Not sure they'd think the same way us geeks do :P

dezso

@Phil and then compare it with 'My memory was stolen' - that's not even a police issue

on an other note, if I change the position of two parameters in a query, can I say 'I swap a and b'? Or is it 'I swap a with b'?

Paul White

@dezso Either seems acceptable to me.

dezso

@PaulWhite thanks

Michael Green

11:00 AM

@ypercube Been there, done that, got the ... ummm ... I'll get my coat.

Colin 't Hart

A bit chilly?

ypercube

@Phil, you around?

ypercube

11:15 AM

1

A: Trying to create a table with foreign keys and its missing a few rows

The foreign keys are defined on course_no and student_id. Assuming the error is a foreign key violation (big assumption, please post the full error in future questions) it must be one of those two at fault. Other rows reference course EE200 so it's not that; so student 018 doesn't exist in the st...

@MichaelGreen Sneaky. Voted to close. Then managed to post an answer. 3 minutes after the question was closed!

@Phil (or @JackDouglas). I got a recruiter email with something like:

> Oracle IT Trainer required by ... for an initial 3 month contract. We are looking for candidates with some, preferably all, of the following modules: Sourcing; Contract Management; iSupplier; P2P – Procurement, Purchasing, Receipting; Accounts Payable.

Is there some Oracle product about Accounting/Finance they are talking about?

E-Business Suite.

Aye, thnx.

Indeedy

11:36 AM

@ypercube And I got a Unicorn for it too! Not sure how that happened. I think I VtC'd before answering. It's not a question that deserves a long life here, but I don't mind pointing a lost soul in the right direction. No mater, it will all be tidied away soon enough and the world will be put to rights.

ypercube

11:56 AM

19

Q: This answer was posted *after* the question was closed, how is that possible?

How can somebody post an answer when the question is closed (marked as duplicate)? I see a question marked as duplicate 8 mins ago and someone posted an answer 5 min ago. This means the answer was posted 3 minutes after the question was closed.

discussion bug status-bydesign answers closed-questions

and this

82

A: How was this answer posted after this question was closed?

If a question is closed while you're answering on the full version of the site, you will receive a notification that the question was closed and the 'Post Your Answer' button will be disabled. However, this is only a client-side restriction, so it is possible in some cases that this process fails...

answered by "our" Tim Stone ;)

dezso

It's completely feasible with PostgreSQL (and, of course, with SQL Server, but that would cost you more, AFAIK). It's hard to tell what sort of HW you need for it, but you can already test it (possibly with a smaller dataset) on an average notebook, too. If you have to do aggregates over the whole dataset, having a big amount of RAM and many smaller disks (SSDs) connected to a decent RAID controller will be definitely needed (or time - if you can wait for the results, it's always cheaper). — dezso 4 mins ago

is my assumption correct that SQL Server for a 500 GB database costs money?

Phil

12:14 PM

Loadsadollar

Andriy M

The free Express edition limits the DB size to 10 GB. There's also a Developer edition, which is equivalent to Enterprise by features, but if I understand correctly it can only be used for development, not for production.

So yes, the assumption must be correct.

Jack Douglas

@Phil yes, and the datafiles check out fine with BACKUP VALIDATE too. Any idea why Oracle isn't recording the error in the alert log? It makes me think the error isn't really happening and the client is mis-reporting.

JNK

12:31 PM

morning all

Phil

@JackDouglas nothing in v$database_block_corruption; ?

Lamak

morning

Jack Douglas

SQL> select count(*) from v$database_block_corruption;

COUNT(*)
----------
0

thanks for the idea, should have checked that straight away

there is something iffy about the database — perhaps an incomplete upgrade

ypercube

there is a disturbance in the force

2

Jack Douglas

SQL> select sum(bytes), sum(blocks), sum(extents) from dba_segments;

SUM(BYTES) SUM(BLOCKS) SUM(EXTENTS)
---------- ----------- ------------
0 .261817 2

^^^^ this really doesn't look right!

Phil

12:40 PM

It's odd it hasn't written to the alert log. Has it put anything in bdump?

Jack Douglas

like a tracefile? No there isn't enything in there with a timestamp later than Aug 17 (except the alert log). I did have a plan to reproduce with tracing on and see if that sheds any light

but I've got a window later this week to run catalog/catproc.sql which I think might clear up the dictionary problems

Phil

I'd seriously think about creating a new instance & doing an exp/imp into it from the current live database. It's obviously very confused

Paul White

Or upgrading to a proper DBMS like SQL Server eh Phil? ;)

Phil

La la la. I can't hear you

Paul White

:-D

ypercube

12:52 PM

dba.stackexchange.com/questions/116970/…

@PaulWhite, for the record, USING is in the SQL standard, as an alternative to specify the join condition. Postgres and MySQL have it implemented.

Paul White

@ypercube Yeah I know. It just looked like MySQL to me. Point being, it was a particular DBMS.

Phil

Oracle has it implemented too

Jack Douglas

indeedy

Phil

1:09 PM

A big party is being held to honor relational database systems and their impact on modern society. Outside the venue, the host awaits the guests.
The first limousine arrives and out steps Oracle followed by 4 people.
Host: Who have you brought along?
Oracle: I have 4 DBA’s in tow. One to install me, one to design the databases, one to administer me, and the other to justify the cost.
A second limo arrives and out steps DB2 followed by 40 people.
Host: Who have you bought along?
DB2: I have 2 DBA’s, 2 hardware specialists, and 36 consultants.

18

JNK

@Phil PINNED

10 internets to you, sir

Phil

Shamelessly stolen from the internets

JNK

still good

Paul White

Self-managing! Yeah, right.

JNK

> The MySQL server only performs basic checking on the validity of a date: days 00-31, months 00-12, years 1000-9999. Any date not within this range will revert to 0000-00-00. Please note that this still allows you to store invalid dates such as 2002-04-31. It allows web applications to store data from a form without further checking. To ensure a date is valid, perform a check in your application.

good grief

Paul White

1:17 PM

CHECK constraints would fix that. Oh.

Phil

It will be 05/10/15 20:25:30 this evening

dezso

the best part is the excuse

Paul White

MySQL really is strange isn't it.

Phil

@PaulWhite You spelt "shit" wrong

Paul White

Ha ha.

@Phil Is it the tenth of May?

The fifth of October was yesterday.

Phil

1:19 PM

@PaulWhite Americaland date formats can do one!

And time zones

Paul White

Agreed.

dezso

@PaulWhite that's a myth

Paul White

@dezso Huh?

dezso

@PaulWhite at the time of writing, there is still a good 8 and a half hours left from the fifth of October at this location

and nearly a full day globally

ypercube

@dezso it's 02:26 in NZ

Paul White

1:26 PM

Ah I see.

So on average I'm wrong.

dezso

@ypercube obviously

ypercube

@PaulWhite You could repeat Phil's comment, changing "will" to "was".

JNK

@dezso yep. Apparently the philosophy is data constraints should live in application code

It's today until Noon UTC at which point it becomes tomorrow

Get it straight, futureman!

Andriy M

@JNK But naturally. If you put them into the database, that will slow it down.

Paul White

12 mins ago, by Phil

It ~~will be~~ was 05/10/15 20:25:30 ~~this~~ last evening

:)

Michael Green

1:42 PM

dba.stackexchange.com/review/suggested-edits/56933

Poster and editor have the same name but different icons. Duplicate accounts do you think?

Andriy M

Very likely

Michael Green

I'm guessing it's not malicious. Is there a way to tell him / her how to combine them?

dezso

@MichaelGreen dba.stackexchange.com/help/merging-accounts

ypercube

U-SQL

dezso

@ypercube you got the newsletter, too?

Phil

1:55 PM

The helpdesk just had one of those "Have you checked that the server is powered on?" Customer hangs up in shame type-calls :D

Michael Green

How to contact the OP? A comment on the question doesn't seem right somehow.

dezso

@MichaelGreen a comment is good enough, I think

@Phil you played which role?

Phil

@dezso Nothing to do with me, I was just laughing when I heard it. The helpdesk people sit behind me

Kin

Morning all

Michael Green

Done. Good night all.

Paul White

2:05 PM

@MichaelGreen A comment is fine. That's usually what I do in that situation.

Begging works!

Please accept my answer then because I am desperate for the validation and internet points. — Paul White ♦ 53 mins ago

Colin 't Hart

@PaulWhite You need those Unicorn points to replace all the ones you've been giving away?

Paul White

Deleted now. Humour value documented.

@Colin'tHart Yeah a bit :) Passed 10K in bounties today. Bit irrelevant really.

Kin

@PaulWhite wanted to ask about this answer (dba.stackexchange.com/a/116968/8783) .. I see this comment in review --> not an answer – Kin 12 hours ago declined - a moderator reviewed your flag, but found no evidence to support it

Phil

Hmm, tired today. Already written way too much code. zzz

Kin

Thats not an answer IMHO ..

Phil

2:17 PM

Clickety

Paul White

@Kin It's the OP, as you mentioned to Erik. It may not be a good answer, but it certainly is an answer.

Thanks for answering your own question; I guess there's not much more to say? Though if you can think of anything else that might make this Q & A more useful for the future, please do edit that into your answer. Also, once the 48 hour waiting period is over, please also mark this as the accepted answer. Thanks. — Paul White ♦ 11 hours ago

I left a comment ^^^

The alternative is to delete the OP's own answer and leave the question hanging. Doesn't seem to be any value in that.

I might be wrong, but I am generally loathe to delete a self-answer. By definition, that is the answer.

So, I declined the NAA flag. People are free to vote on the question or answer as they see fit.

Kin

@PaulWhite ok .. may be its just me .. but that answer clearly does not add any value (and its a low quality - thereby will not help any future visitors) even though it is posted as answer by OP.

Paul White

@Kin That may be, but you're arguing now that the question adds no value. If so, you should have voted to close it. I disagree that the answer adds no value. Someone else searching on the same topic may find the answer useful: it caused no problems. People search for all sorts of stuff.

Kin

@PaulWhite ok .. fair enough ..

Paul White

Not saying I'm right, just that's my reasoning, since you asked :)

Oh and the other thing is the question has three up-votes and no down-votes, so clearly it was well-received.

Kin

2:26 PM

@PaulWhite thats fine .. like your answers .. your reasoning is perfectly valid to me ... just wanted to discuss to adapt my perspective on how to be better :-)

Paul White

@Kin Dunno about better; I can only teach you to be more like me ;) That may not be a good thing.

Kin

@PaulWhite well it would be good .. since I can take good things ... I see a word "Mentor" or "Mentoring" coming up :-)

Paul White

Ha.

Phil

-3

A: Should developers be able to query production databases?

last comment - wow another bitter developer......somebody hurt your ego!

Paul White

poof (the sound of an answer being nuked from orbit with extreme prejudice)

Colin 't Hart

2:38 PM

@PaulWhite Have you been abusing your superpowers again? :-)

Paul White

@Colin'tHart I do little else.

1

A: Meaning of 'locks rec but not gap waiting' in deadlock report

Question 1: InnoDB is trying to get an exclusive lock on the row (it's actually a lock on the clustered index record, the PK), but not the surrounding gap (a gap lock). You can read more about record and gap locks here: https://dev.mysql.com/doc/refman/5.7/en/innodb-record-level-locks.html The e...

This guy is a MySQL Product Manager at Oracle. We should encourage him to contribute here.

2

Phil

Is that a MySQL dolphin tattoed on his head?

Paul White

@Phil Surely that's part of the employment enrollment process there.

dezso

@JonathanFite I don't know anything relevant about PowerShell - how well do you expect it to perform on a 500 GB dataset? — dezso 45 secs ago

any idea from the PS people here?

Paul White

@MikeFal ^^^

ypercube

2:49 PM

@dezso probably not more efficient than a Python script. But the comment seems to assume that they want only to open file once, do some calculations/aggregations, save results and nothing else.

The original question at SO suggests they want to run several queries on the data.

dezso

@ypercube yup, they explained it a bit more

Phil

3:14 PM

I've decided that the best way to get data from SQL Server to MySQL automagically periodically is to use DBD::ODBC. <3 perl

@Phil DBD?

perl?

MySQL?

data?

@Phil's technology stack is ~~heaped~~ doomed

Paul White

3:22 PM

At least we didn't spam him with @mentions like we did poor old @billinkc

Lamak

when did we do that to @billinkc?

anyway, yeah, poor old @billinkc

dezso

@Lamak on Friday, IIRC

Phil

DBD. Perl database access module. The 2 servers can't see each other & I've got a Linux box in the middle that can see them both. It's the best* option

mmarie

It was lost on him then as @billinkc and I were BBQ crawling

Phil

* Lies

Paul White

3:26 PM

All sounds very ... creative :)

sigh

Colin 't Hart

@PaulWhite Go to bed!

Or have you just gotten up?!

Paul White

But people are still wrong on the Internet!

Colin 't Hart

@PaulWhite LOL

Still?!

Paul White

Always have been so far.

Muppets.

Mike Fal

3:46 PM

@dezso Read the comments. Not sure that Powershell is the best option. It could work, but I'd be skeptical of performance for that much data. I'd probably process it through SSIS or some other proper ETL tool.

dezso

@PaulWhite and, saying that, @Paul fell asleep

Paul White

DOINK woke me up.

ypercube

-1

A: Looking to improve query performance

I'd look at changing the union to union all to remove the overhead of making the data in the subquery distinct. Then use group by to remove the duplicates

Yeah, remove the DISTINCT, then add GROUP BY. makes perfect sense.

Paul White

Must be a guru.

0

A: Locking SYS and SYSTEM Accounts

Lock them both (SYS and SYSTEM). You shouldn't need to use them day-to-day, nothing will break. On a day to day basis you should be using named user accounts that have SYSDBA or SYSOPER. Tom Kyte recommends doing it, so you can always blame him if something does go wrong :-)

Phil answers.

Phil

@PaulWhite Indeedy

Paul White

3:53 PM

I would vote but.

Phil

I'm going to start answering all of the SQL Server questions now that I'm an expert*

* More lies

Paul White

You start at Guru and progress from there.

dezso

@ypercube it may make sense if the outer query limits the scope enough, mightn't it? <- how does one do this

ypercube

@dezso if they explain, (perhaps with code?), I might remove my -1 ;)

dezso

@PaulWhite there is a contradicting answer

who's right?

Paul White

3:57 PM

No clue.

Phil

I even upboated the other answer, because that's how a roll

ypercube

@dezso the 2nd answer does not answer the question though (if it's a good idea). Only if it's possible.

dezso

@ypercube that's also true - seems like an answer to @Phil, not to the OP

ypercube

@dezso and (assuming I understand what the Phil's link says):

> using O7_DICTIONARY_ACCESSIBILITY=FALSE (default in 9i and up), people cannot log in as
SYS, they must provide *their* credentials and log in as SYSDBA.

one could potentially login as SYS, but it's disabled by default.

Phil

24 minutes until hometime \o/

Paul White

4:14 PM

@Phil Badge hunter!

1

A: Access (Jet) SQL: DateTime stamps in TableB flanking each DateTime stamp in TableA

Added additional attributes and filter conditions. Any form of cross join is eliminated by using min and max nested queries. This is the biggest performance gain. The min and max flank values returned by the inner most nested query are primary key values (scans) that are used to retrieve a...

Looks good.

Phil

Yay, my dodgy perl works! And with 3 minutes to spare. WIN

Paul White

4:47 PM

Well this is weird:

-3

A: Explaining 2NF vs 3NF with an example

I'm preparing to delete this as it is considered incorrect Subject Name is also a non-prime attribute and it depends on part of the primary key Subject Code (breaks rule - there must not be any partial dependency of any column on primary key). This is prohibited in 2nd Normal Form and should th...

What preparation is needed, I wonder?

Max Vernon

any chance of a quick sanity-check on this?

1

A: SQL Server equivalent of Oracle USING INDEX clause

The SQL Server syntax for creating a clustered index that is also a primary key is: CREATE TABLE dbo.c ( c1 INT NOT NULL, c2 INT NOT NULL, CONSTRAINT PK_c PRIMARY KEY CLUSTERED (c1, c2) ); The primary key and the clustering key do not have to be the same columns. You can defi...

Andriy M

@PaulWhite Create a backup?

Zane

5:02 PM

@MikeFal gg this weekend.

Mike Fal

@Zane Eh?

ypercube

@AndriyM do they need hours for a copy-paste?

Zane

Good game

Mike Fal

I know what it means. I'm not clear to what you're referencing. The SQL Saturday?

Zane

Vikings Broncos.

Andriy M

5:06 PM

@ypercube One would hope not. That was the only extraordinary explanation that came to mind.

Paul White

@MaxVernon Honest opinion: It's overly long and references undocumented functions that will be confusing or dangerous for a user new to SQL Server.

JNK

@MikeFal you don't know any powershell folks in the NYC area do you? Powershell/SQL

Paul White

@MaxVernon Also, a clustered index does not define the physical order of rows, only the logical order.

@AndriyM Ah yes that must be it.

Max Vernon

@PaulWhite true enough. I'll fix that. Thanks for looking!

Mike Fal

@JNK Sorry, not off the top of my head. Do you know Robert Pearl?

JNK

5:13 PM

@MikeFal no I don't

Mike Fal

@Zane Oh yes. Mike Wallace is a beast and Teddy played well. But 6 sacks!

JNK

@MikeFal We tweaked it some but got a job opening now: careers.stackoverflow.com/jobs/98781/…

Mike Fal

@JNK Tweeted you both

Zane

@MikeFal Yeah we had O line problems prior to Loosing our star center.

JNK

@MikeFal thankee

you're the man

Mike Fal

5:15 PM

@Zane Peyton's got to work better on passes and security. Those 2 INTs let you guys back into the game. But we're actually starting to see a running game develop.

Max Vernon

how do I code a superscript number (for a footnote)?

Paul White

@MichaelGreen The user didn't request a merge, so may not have understood the process. I asked a Community Manager to do it instead, which has now been completed.

Zane

@MikeFal most definately. The one Harrison Smith got was bad on Peyton for sure. The one Barr got was really smart play on his part. Called off the blitz he was supposed to go on. I think part of your runing problem was switching between guys. I'm pretty sure you know who to go to now.

Paul White

1

A: SQL Server equivalent of Oracle USING INDEX clause

Is there a SQL Server 2008 equivalent of the USING INDEX clause in Oracle? No. When you create a primary key or unique constraint in SQL Server, an index to support that constraint is created automatically, with the same keys. Which seems to imply that there is a way of specifying what i...

My attempt.

Mike Fal

@Zane I'm scared about going to Hillman. He doesn't have a good track record for running in between the tackles. And CJ has always played hard. I think he's still fighting some foot issues.

@JNK Can you DM me a contact email for you that I can pass to Robert?

JNK

5:21 PM

@MikeFal he already DMd me

Max Vernon

@PaulWhite +1

JNK

but I'll send it to you in case you need in the future

Zane

@MikeFal either way it was a fun game to watch. Glad Teddy never seemed flustered with the amount of pressure he was getting. Most QB's crumble when they get knocked around like that.

Mike Fal

Teddy played great, considering the pressure he was under.

swasheck

5:41 PM

too many mental mistakes

stupid defensive penalties (persistent offsides?) did a lot to keep the vikes in the game ... not to mention peyton claus

@MikeFal my favorite RB in the system is juwan thompson ... was a big fan of his at duke and he's a tough runner but he got his bell run against the lions

mmarie

Go @swasheck. Scoring points with the wife on FB ;-)

swasheck

@mmarie points have already been scored. i mean, we're married.

mmarie

lol

Mike Fal

@swasheck The offsides is going to happen with guys as aggresive as our defense

I'm more concerned with the o-line penalties, like not being on the line of scrimmage

swasheck

@MikeFal yeah. derp. twice.

@MikeFal but ... i mean ... he's (ware) a vet. and then the ward penalty to get them out of the shadow of their own end-zone. dumb. dumb. oh well.

Mike Fal

5:50 PM

@swasheck Yes, but the defense is doing its job for the most part. I'm surprised Peterson only broke off that one big run.

The offense is NOT doing its job. O-line needs to get better

swasheck

@MikeFal yeah. that also happens on a critical 4th and short. load the box and if someone breaks through ... they're gone

@MikeFal no joke

Max Vernon

6:03 PM

any idea how I kill a session that is blocking itself? kill <spid> doesn't work. the request is in "KILLED/ROLLBACK" state, and is blocking on itself.

it's been blocking itself for over 10 minutes now.

JNK

@MaxVernon it's showing blocked by <spid> which is itself?

Max Vernon

@JNK yep

JNK

AHH

It's parallelism

Max Vernon

blocking_session_id
52
session_id
52

JNK

it'll unblock

one thread is blocking another

it's still rolling back

Max Vernon

6:06 PM

I started the rollback after about a minute of it running, and it's been rolling back for over 12 minutes now.

JNK

yep sometimes rollbacks take longer, I've definitely seen that before

Max Vernon

mee toooo but this is ridiculous

JNK

rollbacks just take as long as they take unfortunately, I've seen them take longer when there's a lot of log activity too

there's log contention b/c your rollback is undoing things as other processes are adding to the log

Max Vernon

hmmm... this is on my test instance, and I'm the only one using it. The transaction is in tempdb, and was writing to a temp table when I killed it.

billinkc

You should just reboot the server. It's faster

Max Vernon

6:08 PM

@billinkc lol.... that's what I'm about to do. I'm thinking recovery at least won't block itself.

billinkc

Pause might be less drastic. Doesn't let new processes start

Max Vernon

the session is waiting on a LCK_M_X lock.

shutdown with nowait worked a charm. And of course, no recovery in tempdb.

now to see if I can recreate the problem.

swasheck

@MaxVernon which version of sql server?

Max Vernon

Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
May 4 2015 19:11:32
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

swasheck

wimp

2016 or bust

Max Vernon

6:15 PM

I wish. I need to get myself an MSDN sub.

swasheck

try this

DROP DATABASE tempdb

Max Vernon

lol

swasheck

but really ... did you see what the wait resource on the blocking spid was?

Max Vernon

@swasheck yes, but I couldn't determine what actual object was being referenced. I'm fairly confident I can recreate the problem however.

I couldn't -> I didn't really try

it was KEY: 2:281474979397632 (49c098b01c56)

weird. Not reproducible.

swasheck

was the self-block after you cancelled and started the rollback?

Max Vernon

6:21 PM

I believe so. Although to be honest I didn't check to see if it was blocked before I started the rollback.

swasheck

imagine trying to put your butt into reverse after running full steam ahead ... you might stumble over your own feet. we'll see it live, and in person, at Summit

Max Vernon

@swasheck yup, sweet! 3 weeks to go!

I was trying to build a test case for this problem with the following code:

SELECT req.start_time
	, '1'
	, SUBSTRING(t.text, req.statement_start_offset, CASE WHEN req.statement_end_offset > 0 THEN req.statement_end_offset - req.statement_start_offset ELSE LEN(t.text)-req.statement_start_offset END)
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) t
WHERE req.session_id = @@SPID;

SELECT c1.*
INTO #t
FROM syscolumns c1, syscolumns c2;

SELECT req.start_time
	, '2'
	, SUBSTRING(t.text, req.statement_start_offset, CASE WHEN req.statement_end_offset > 0 THEN req.statement_end_offset - req.statement_start_offset ELSE LEN(t.text)-req.statem…

every time I run it now, I can roll it back without issue.

swasheck

liar

Max Vernon

see my deleted answer for giggles. Seems @srutzky and I were both under the impression start_time represents the start of the request, although it clearly doesn't.

Zane

ETL process as it stands. Takes file. Stages file. Then inserts into the main table. Then updates row status of all rows to be obsolete.

Yikes. 108 rows inserted and updated fully logged operation.

swasheck

6:36 PM

@MaxVernon ... give this a try

just spitballing ... maybe a dm_exec_requests->dm_os_tasks->dm_os_workers->dm_os_threads with a thread creation time would yield some results? — swasheck 13 secs ago

srutzky

6:54 PM

@MaxVernon actually it does represent the start of the Request, it is just that the request is the batch and not the current statement, which is why there is one sql_handle and it just moves through the statement start and end offsets. However, I did find the answer (I believe) via sys.dm_exec_query_stats. Just working it into the query now :-)

Max Vernon

@srutzky I've been watching your answer progress. It's pretty good. One thing about sys.dm_exec_query_stats would be concurrency is likely an issue for getting precise measurements.

srutzky

As I mentioned in a reply comment, I did just try that query via 'SELECT r.*
FROM sys.dm_os_tasks t
INNER JOIN sys.dm_os_workers w
ON w.worker_address = t.worker_address
INNER JOIN sys.dm_os_threads r
ON r.thread_address = w.thread_address
WHERE t.session_id = 56` and did not see any time field that incremented. It is possible I missed something, though.

swasheck

@srutzky right. saw that. i was only hoping that the task creation datetime would provide some insight but evidently not

srutzky

@MaxVernon Yes, I noted the potential concurrency issue just below the MSDN quote that states the data might not be super accurate under load. But I suppose something is better than nothing, right? ;-)

Max Vernon

@srutzky agreed. I've been looking on connect to see if there are any bug reports about this; so far nothing.

srutzky

6:58 PM

@swasheck I was only restating that query here in case Max hadn't seen that exchange in the comments (and because the comments are soon to be deleted by Paul ;-D, so this is the better place for it anyway)

swasheck

very true :)

Max Vernon

I would expect sys.dm_exec_query_stats is not updated until after the query is executed, since it contains total_worker_time which would be hard to know until the query completes.

DBCC FREEPROCCACHE

SELECT *
FROM sys.dm_exec_requests req
	INNER JOIN sys.dm_exec_query_stats qs on req.query_hash = qs.query_hash;

That returns zero rows the first time it runs.

which to me indicates sys.dm_exec_query_stats is empty at that moment.

well, maybe not "empty", but has no results that can be linked to dm_exec_requests

srutzky

That is correct and what I am running into. However, I should be able to combine the prior statements last_execution_time with the last_elapsed_time to get effectively the current start time.

And to your most recent point, I should be able to OUTER JOIN that and if NULL, then just use the request start time :-)

Phil

7:34 PM

Dammit, the world didn't end at 5/10/15 20:25:30.35 !

srutzky

@MaxVernon Done. Check it out :). Also, I am JOINing on [sql_handle] since it seems that dm_exec_requests.query_hash is populated as 0x0000000000000000 which won't JOIN to the real values coming from sys.dm_exec_query_stats. At least that is what I am seeing.

Max Vernon

7:49 PM

@srutzky interesting; I like it!

of course, I'd put the column aliases, and commas separating the columns, at the start of each line. But that's just me, and some would call me crazy.

Transcript for

The Heap™ – Consultancy ©®