The Heap™ – Consultancy ©®

4:23 AM

@swasheck covering ground you did years ago

0

Much like a related question by swasheck, I have a query that historically has suffered from performance problems. I was looking through the query plan on SSMS and noticed a Nested Loops (Inner Join) with the warning: No Join Predicate Based on some hasty research (confidence inspiring Scar...

Erik

4:52 AM

Never mind it was caused by not putting the stupid WITH (NOEXPAND) query hint on an indexed view.

Paul White

I'll look at it in a bit if you post the view definition. Need to attack the washing up first.

Erik

@PaulWhite Sweet thanks. I added the view and will delete my comments.

@PaulWhite If you don't think this will be helpful for others, then feel free to delete my question.

I flagged it as such too.

Also since I know how much people like Meta around here:

0

A: Delete question with OWN answer

The accepted answer seems to be obsolete. I tried to follow that procedure with a question and I got the pop-up warning me that I could be banned from asking questions if I deleted my question. In the end I asked a moderator via chat to delete the question if they didn't think it would add con...

Paul White

5:56 AM

1

Q: Deleting self-answered question gets "Delete this answered question" warning

While attempting to delete a question which was answered by myself, and with no other answers, I got the following warning: Delete this answered question? We do not recommend deleting questions with answers because doing so deprives future readers of this knowledge. Repeated delet...

discussion warning self-delete

Tom V

7:02 AM

Morning

Erik

@PaulWhite That is a better Meta post than the one I found.

20

A: How can a question banned user fix their questions if they're deleted?

Users can now see recent deleted questions in their profile. We're normally not particularly eager to direct folks in that direction though, since in many cases they'd be hard-put to salvage them: most folks who hit the quality bans have made a lot more mistakes than just deleting their questions...

This is another good one that ShadowWizard found

Andriy M

@Erik As I can see now, there's no way the view would produce duplicate (Monitor, Monitored) entries, correct?

Erik

@AndriyM It shouldn't considering that is the UNIQUE CLUSTERED INDEX

Even if that constraint wasn't there it would be a business rules violation which would mean I'm in trouble... :)

Andriy M

7:17 AM

@Erik Ohhh, that's what I missed completely. So my comments make no sense whatsoever

Erik

@AndriyM No worries. I tend to produce really long questions with lots of code.

I'm sure the square brackets don't help much either. I'm used to them, but I've gotten some good natured heckling here due to them.

Andriy M

@Erik I wouldn't say there's lots of code in that question, and if there were, that still wouldn't be much of an excuse for my careless reading. I guess I should either focus on my work or put it aside completely. I'll probably do the former :)

Erik

7:33 AM

@AndriyM Focusing on your work is probably the wise move :)

Andriy M

@Erik I've deleted my comments by the way. And I haven't flagged yours (yet) :)

Erik

@AndriyM lol I already deleted mine too.

Phil

7:50 AM

Mornin'

Erik

@TomV @Phil It's nearly 2am, I know that is technically morning but it sure feels like night to me. ;)

So good night!

Phil

9:02 AM

I hate to say it, but MySQL Workbench really has come along as a tool. It was utter crap last time I used it. Quite impressed now

ConcernedOfTunbridgeWells

@PaulWhite Bloody permies. Always on holiday when you need them.

@PaulWhite Yeeeeeharrrr

ypercube

10:38 AM

@ypercube I assume this is SQL Server, where the logical processing order of the sql statement (msdn.microsoft.com/en-us/library/ms189499.aspx) processes JOIN before WHERE so the optimzer will have to do more work to come up with a good enough plan. Which would lead to the index suggestion above. — Spörri 13 mins ago

I'm not sure what they are thinking really. The answer doesn't make much sense either. But I see they have some highly upvoted answers in administrative issues.

Mark Sinkinson

Does anyone know what the EXECSYNC wait stat is in SQL Server?

Paul White

@MarkSinkinson Usually associated with a parallel eager index spool.

Basically it is an parallel execution synchronization wait though.

Mark Sinkinson

Ah, excellent thanks

Just had to kill an analyst doing a SELECT * with a sort on 140GB of data...

Paul White

Killing him or her seems a bit harsh.

Mark Sinkinson

The way today has been going it seemed justified :)

Paul White

10:48 AM

Fair enough!

Mark Sinkinson

The EXECSYNC happened after they cancelled the query through SSMS. It only stopped with a KILL

ConcernedOfTunbridgeWells

11:01 AM

@PaulWhite Yes, a good beating should have been sufficient.

2

James Lupolt

11:12 AM

SSMS Tools Pack now supports SQL 2016 -- so exciting.

billinkc

11:31 AM

Boo hoo hoo, they didn't pick my answer

My precious unicorn points, what will I do without them?

ypercube

@billinkc you mean this answer?

Thank you for your response man, great explanation — osm2 2 hours ago

and then, they go and pick the -1 answer ;)

(not that I have any idea about SSRS and what is correct or wrong)

billinkc

Yeah, wasn't going to link it for heap retribution. Just found it ... curious

Their comment on mine and this on the accepted answer

exactly, works just fine, thank you, i just needed to put this len([Modified By]) instead of this FINDSTRING([Modified By],"#", 1) — osm2 2 hours ago

Oh well, I think I like putting the image there in the answer though with the character positions. Probably should have leveraged it more effectively in the answer itself

Phil

This week has flown by! <3 Friday

JNK

12:24 PM

hi all

bluefeet

12:39 PM

hi

billinkc

hi hi

JNK

So today is my Boss's last day

he has been at this company like 15 years

billinkc

What did you do to make him leave?

Just your normal self?

Tom V

@ypercube I was contemplating editing out the 'performatic' in my edit :)

JNK

@billinkc incredibly it wasn't me!

I think he's just burned out and wants to not be a manager any more

he's gonna go work for Bloomberg and roll around in piles of money

billinkc

12:55 PM

And you want to be a manager. Interesting coincidence...

JNK

@billinkc completely UNRELATED I am sure

billinkc

Playing Scrooge McDuck sounds good until you realize how filthy money is

ypercube

@TomV haha. The question is performatic > performant or vice versa?

Tom V

@ypercube 'little performatic' > 'not performing well'

ypercube

yes. Lets make a puzzle.

Please put in order of "performancy":
'performant', 'performatic', 'little performatic', 'big performatic', 'unperformatic', 'unperformant', 'super performant', 'ultra performatic'

5

Kin

1:18 PM

Morning All

Paul White

Hiya

Kin

Just asking ... Anyone learning data science or Machine learning ? Since its a buzz word now a days .

James Lupolt

@Kin A little, and very slowly

Most people who are employed in that space are far more knowledgeable and quicker-witted than I am and have much nicer degrees.

Kin

1:34 PM

@JamesLupolt Same here .. edx is having a good course on machine learning from Microsoft .. its free

James Lupolt

@Kin Cool, I'd like to learn more of the MS machine learning stuff. I've been taking the Johns Hopkins series on statistics and machine learning on Coursera. It is mostly using R.

dezso

@PaulWhite I am wondering about the bounty reason here: dba.stackexchange.com/questions/101570/…

Paul White

@dezso Yes?

dezso

it is, in my view, reading the documentation carefully before assuming GRANT works this or that way

Kin

@JamesLupolt This course is geared towards AzureML + R or Python courses.edx.org/courses/course-v1:Microsoft+DAT203x+1T2016/info

Paul White

1:46 PM

@dezso Noted.

dezso

For clarity, the syntax is valid, but it refers to tables - while the OP meant it for his schema. Still, I see a possiblity to broaden the picture a bit

Paul White

@dezso There's more to it than that, though. Phrancis narrowly missed out earning the bounty on this question and awarding a new bounty for 500 (they double after the first) didn't seem appropriate. So, bounty offered on a different Q & A with behaviour that I would like to encourage. There are a limited number of bounty reasons to choose from.

@dezso The question is still open to edits/answers.

dezso

2:02 PM

@PaulWhite I see

Erik

2:44 PM

> Try moving the Join up and down the table reference stack, making sure the alias references are in correct dependency order. Meaning if you have 4 joins, I have had success and changed things up moving a less referenced or low selective table join to the last position of the joins and visa versa.

Is this true? I always believed that the order of the joins didn't matter. I was the relationships in the joins that mattered.......

-----------------------------------------------------------------------------------------^ It

swasheck

@zane you're wrong on one point ... there's plenty to read into that game for the rest of the year. last year they got smoked at home and dug a huge hole for themselves. this year ... we'll see, but there's plenty of concern to be had for that third period

Mark Sinkinson

@Erik No it's not true.

Erik

@swasheck my belief or the quoted statement?

swasheck

@MarkSinkinson unless you forceorder or ... some other hint

@Erik no. the hockey game last night :)

Erik

@swasheck lol thanks

Paul White

2:54 PM

@Erik For small queries, it may not matter. For a larger number of joins, or if the joins are outer joins, it can matter a lot. The number of ways to rearrange joins explodes very quickly, so the optimizer cannot consider all the options. It is also limited in its ability to reorder outer joins.

@swasheck Any join hint.

Erik

0

A: Help finding join without predicate

I have had similar situations and read a while back that the engine will determine if the type of join algorithm is most efficient and will sometimes drop the join predicates in behalf of the efficiency over the written code. By doing no expand, you're telling the engine to no descend to the view...

Paul White

One good point among a sea of muddled thinking does not an up-vote make.

Erik

@PaulWhite Agreed. I was trying to figure out if there was some pearl of wisdom buried in there before I down-voted the sea of muddled thinking.

I'm not going to down-vote since you said the order may matter, and that wasn't something I knew and/or would have considered.

user58869

3:31 PM

on sql server if i use FILESTREAM storage would i be able to read, add, update and delete the files via the file system, or must i use tsql to access them?

Andriy M

3:46 PM

In my very limited experience interacting (mostly playing) with FILESTREAM I've been able to read filestream files using a file manager utility. Never tried adding, modifying or removing them, though.

Kin

4:08 PM

@Alice for small filestream files use TSQL and for larger ones use FILESTREAM API. This guarantees consistency.

Paul White

@Alice Yes and no. See Managing FILESTREAM Data by Using Win32

So, yes, but it is transactional. No, if you meant simply using files as you normally would.

Kin

4:26 PM

@PaulWhite I have a small confusion that would like to clear .. when we edit the questions with proper tags, is it recommend to always tag sql-server along with sql-server-version e.g. sql-server, sql-server-2014 ? I assume sql-server is parent followed by sql-server-version ?

Paul White

10

A: Should we tag with all terms in a tree?

My answer has changed. See the history for the previous version. Rules: Questions should only have one version tag referring the most specific major version known or applicable. The database product tag should also be present. Examples: A question about Oracle 11.1.0.7 should be tagge...

Aaron Bertrand

Yes I prefer to see both.

sql-server is useful for monitoring for new questions and broad segmentation, while sql-server-2014 is much more useful when actually answering the question.

Paul White

@Kin ^^^

Kin

ok .. got it .. Thanks :-)

Going forward I will use both Parent + child for tagging.

Paul White

The site thanks you :)

Max Vernon

4:33 PM

and I thank you too.

Paul White

Why thank you!

ypercube

@bluefeet, you around?

Your merging made it kind of a mess:

1

Q: Second normal form does not produce the desired result

I have a problem with 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, Grade} Dependencies are ...

database-design

Now the question has lots of duplicate answers ;)

On the funny side, we have 2 answers on "preparation to be deleted"!

Paul White

Oh God. I knew that question was going to be trouble. I thought it was over! LOL.

ypercube

9 answers ...

bluefeet

I just deleted a couple of the dup answers, I did a quick glance of them and obviously it was too fast

Paul White

4:39 PM

what about MySQL? — naWin 15 mins ago

wut

@ypercube Look right to you now?

ypercube

great.

Aaron Bertrand

4:56 PM

ugh

Thanks! I spent so much time trying to figure out the equivalent of Oracle's BREAK ON and your LAG line easily did it -_- thanks — Sara 1 min ago

Erik

@AaronBertrand lol looks like your suspicion was confirmed...

Aaron Bertrand

@AndriyM Thanks for the nice edit

...but looks like using a spoiler was for naught.

Andriy M

@AaronBertrand It was an interesting exercise

Paul White

5:20 PM

Going to sleep. Later people.

swasheck

5:41 PM

@PaulWhite any join hint. nice

Max Vernon

6:32 PM

@AaronBertrand I get why you're saying formatting shouldn't be done by SQL Server; but sometimes it's just really nice to have SQL Server do it when there is no presentation layer, other than SSMS.

swasheck

6:45 PM

@MikeFal are you around?

Aaron Bertrand

@MaxVernon That is a pretty rare scenario, where SSMS is the only consumer, and in that case, what is the value of having blank rows in the output?

Anyway, there are always exceptions, but the tendency seems to be that people want all of the presentation logic in the wrong tier. So I will try to encourage the right way, knowing that there may be pushback for exceptions, rather than just say "Great idea, here you go!"

The biggest one is with dates. C# has rich formatting functionality but people want to do all kinds of ugly things with dates at the storage or query layers (or both).

billinkc

It's a good thing we've got Format built into tsql now

ducks

Aaron Bertrand

FORMAT() is a slowass piece of shite

Between that and EOMONTH() I weep at the loss of man hours that could have been put into something useful

Max Vernon

@AaronBertrand - yes agreed, it mostly shouldn't be done in SSMS.

I use this code for looking at details about newly created databases (they are actually created by a different department):

DECLARE @cmd VARCHAR(MAX);
SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) + 'UNION ALL' + CHAR(13) + CHAR(10) END + '
SELECT 	DatabaseName = d.name
	, DataSpace = COALESCE(ds.name COLLATE SQL_Latin1_General_CP1_CI_AS, ''LOG'')
	, CurrentLocation = mf.physical_name
	, FileSize = mf.SIZE * 8192e0 / 1048576
	, MaxSize = mf.Max_SIZE * 8192e0 / 1048576
	, FileGrowth = mf.Growth * 8192e0 / 1048576
	, sort_order = CASE COALESCE(ds.name, ''LOG'') WHEN ''PRIMARY'' THEN 0 WHEN ''LOG'' THEN 1 ELSE ds.data_space_id END

it uses LAG() exactly like she wanted it.

and don't even ask me why a different department creates the databases :-(

Aaron Bertrand

7:08 PM

Dude you've got to let go of EXEC() and embrace sys.sp_executesql

However it does mean you'll need to start using N'around your strings' :-)

Anyway I guess that's most valuable with results to text so your eye is drawn to "new" rows. Typically though something like that gets turned into a report or something, when there is a true presentation layer.

Mike Fal

@swasheck

Ahem. @swasheck What do you need?

Tom V

7:55 PM

o boy

not on friday evening

swasheck

8:15 PM

@MikeFal warm-up preso? (sorry. wife just threw out her back)

Tom V

ah someone has been playing around with the reverse proxy and the sharepoint farm

I'm running out of fucks I can give on a friday night

dezso

@PaulWhite I see you've added a 500 rep bounty on the question you mentioned

if it's because I've added my answer on the other question (and Phrancis, for some reason, marked mine as accepted after unaccepting his), then sorry, I didn't want to interfere

Mike Fal

8:41 PM

@swasheck Sure, I'll figure something out over the weekend.

swasheck

@MikeFal are you sure? you dont have to

but i appreciate the willingness.

fricklowe is going to be doing his java preso for the main

0

Q: how to resolve deadlock in sql server 2008 when update record in table

When database tables start accumulating thousands of rows and updates start on the same table concurrently, insert queries on the tables start producing lock contentions and transaction deadlocks. I am getting this error (System.Data.SqlClient.SqlException (0x80131904): Transaction (Proc...

sql-server sql

use (nolock) ... duh

Tom V

@swasheck if you don't care about correctlessness

swasheck

@TomV right.

even then ... may not work :)

Mike Fal

9:00 PM

@swasheck It's cool, I can do it.

swasheck

9:11 PM

@MikeFal werd. thanks.

srutzky

9:26 PM

@MikaelEriksson Regarding the following question:

7

Q: Create hierarchy of multiple levels where each node has a random number of children

I need to create some test data that involves a hierarchy. I could make it easy and do a couple of CROSS JOINs, but that would give me a structure that is completely uniform / without any variation. That not only seems dull, but lack of variation in test data sometimes masks problems that would o...

sql-server t-sql hierarchical random

Did I handle it correctly with respect to not immediately posting my answer? I know that when people answer their own questions, they do so fairly immediately (or did I misunderstand that?). But here I didn't want to prejudice any results, nor even getting any responses if I said I already solved it (since I didn't know if there was a better way).

Not sure what the protocol is here and it was my first question ;)

Tom V

@srutzky any reason to not accept your own answer in your opninion?

srutzky

@TomV Only reason would be to give more time to see if there are other thoughts on better approaches. I didn't want to answer immediately and remove the impetus to find a solution since mine might not have been the best way, just the best that I came up with before posting it :)

Tom V

on first sight @MikaelEriksson 's post seems fine too but if you didn't think that solved it...

srutzky

I didn't want it to be like a blog post that, while there are comments, are fairly one direction, even if horribly wrong ;-)

Max Vernon

@AaronBertrand I like sp_executesql enough... if I was writing some dynamic code for a stored proc or something other than a one-off, I'd surely use it. As for the unicode marker, am I living dangerously leaving it out?

srutzky

9:33 PM

@TomV His answer is really good, and I think pointed to where I went wrong in matching up the TOP(n) in the CROSS APPLYs, and I like seeing the XML example just to have a variety of approaches since someone else with similar but slightly different circumstances might find those other ideas quite helpful. And that was the idea of putting the question out there.

Tom V

@srutzky I don't XML often so I won't vouch for any post, but IMO it depends on what solved the problem at hand

srutzky

@TomV But in the end, I really need the names to have a more natural looking structure that isn't just numbers.

At the moment, without that one piece, his is a very close 2nd place.

If it is preferred for me to choose one since the problem has been solved, then I can accept mine since it has everything.

@MaxVernon I think the N prefix is actually required for the parameter declaration.

Tom V

@srutzky I'm junior so I don't know, but I would guess whatever solved the OP (yours) question is in fact the answer

anyways, i'm off to a series episode & bed

I have a maintenance window early tomorrow morning so I shouldn't be up late

srutzky

9:54 PM

@TomV Thanks for sharing your thoughts. Have fun with the maintenance.

srutzky

10:07 PM

If you try this: `sp_executesql 'SELECT 1;';` you will get the following error:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

And executing: `sp_executesql N'SELECT 1;', '@Test INT';` gets you this error:

Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.

Max Vernon

11:55 PM

@srutzky yah, I'd never pass a direct string to sp_executesql like that. I always use a variable. I'm a big fan of using the correct strongly-typed version of code, T-SQL or not, for every piece of code I write. Having said that, I'm no good at doing SET @a = N'sometext'; I pretty much always do SET @a = 'sometext'; quite certain that is not giving me any performance problems, unless perhaps that was inside a really long loop.

of course, I could easily be wrong.

Transcript for

The Heap™ – Consultancy ©®