The Heap™ – Consultancy ©®

9:08 AM

Hey @PaulWhite Interesting note from you about Optimiser Timeout on that post, I tend to associate those with over-complexity in the query or missing info like FKs and stats. If I see them (and performance of the query isn't great or is suspect) I would consider breaking the query up into smaller chunks, code for predictable behaviour if not necessarily fastest possible. Sound like a waste of time?

2 hours later…

11:25 AM

@wBob Well yes, but in the same way, you wouldn't look to make a plan bigger or more complex just because you hit good enough plan. Time out isn't really a reliable indicator of anything, including a too-complex plan. It's either good (enough) or its not.

@PaulWhite Fair enough, thanks!

For me, it's marginally informative, but not more than that. It doesn't affect the real measures against which a plan should be evaluated. I am all for breaking stupidly large plans up, but I wouldn't equate that with t/o.

@PaulWhite Makes sense. So I guess that begs the question (and this is a big question really) what are "...the real measures against which a plan should be evaluated" ?

I understand if this is not possible to answer in a tweet (or equivalent chat mechanism ; )

@wBob Whatever is important. That might be adequate performance in all scenarios, top performance in a critical case, resilience to data changes, high concurrency...it depends.

More generally, and less ambitiously, I like plans that don't contain anything blatantly stupid.

95%+ of the time, we never have to worry about plan quality.

@PaulWhite with good table and index design, FKs, NULLability, non-excessive normalisation then yes.

11:36 AM

@wBob Well, with that rare combination the percentage is probably even higher. I simply mean that most queries run by SQL Server have plans that no human ever has to look at or worry about.

Anyway. These are just my opinions right now. There are surely other valid points of view.

@PaulWhite lol it's good stuff, thanks for your time as always.

I've read the Craig Freedman 'bushy tree' stuff, there's not otherwise that much stuff about approaching plans from a troubleshooting perspective. A gap in the otherwise crowded blogging market?

(for the 5% I guess I should say).

@wBob Interesting topic. Let's pick this up tomorrow - I'm tired and need to get some sleep.

@PaulWhite cool, thanks

1 hour later…

Mark Sinkinson

12:58 PM

Has this ever been true?
"The SQL Server query optimiser cannot make use of table indexes when subqueries are used"

1:13 PM

Slightly strange question but has anyone ever needed to use DISTINCT that wasn't because of bad db design or not knowing the data?

gbn

@Shaneis Yes

To sample values in a column without doing a JOIN to a lookup table

Or to populate a lookup table from existing values (as part of refactoring)

@gbn I was laughing to myself thinking "I deserve that" but then you explained further so cheers!

gbn

Or LEFT JOIN (SELECT DISTINCT SomeCol) X where I need the null or somecol

(instead of COUNT and getting 0 or more than zero)

Most of mine are in code that needs refactored. The newer code removed it as we've cleaned up the data and design

Tom V

I write all my DISTINCTS as GROUP BY

@TomV Same, I know it's pretty much the same thing but it seems faster that way

although haven't tested it yet

Also maybe I just don't like DISTINCT :(

1:27 PM

@Shaneis It's also sometimes needed in aggregates COUNT(DISTINCT some_expression)

@ypercubeᵀᴹ But I'm right in saying that it's not allowed with Aggregate Windows functions?

Tom V

@Shaneis If I'm not writing pure SQL I'm working with an app that doesn't support a distinct keyword

@Shaneis in SQL Server, I think yes. Not implemented (yet).

connect.microsoft.com/SQLServer/feedback/details/254393/…

1:53 PM

Thanks all, colour me answered and satisfied :)

@TomV ...what? really?

Tom V

@Shaneis distinct is the least important thing missing

You can make the thing fall over with multiple outer joins

@TomV Definitely something that I've always thought was missing from SSMS :(

2:20 PM

For the UK folks, how does an iced finger/iced bun compare to an American doughnut?

1 hour later…

swasheck

3:22 PM

docs.microsoft.com/teamblog/introducing-docs-microsoft-com

so msdn -> docs.microsoft.com

4:03 PM

@swasheck I'm a little frightened that it looks like a SharePoint site.

swasheck

it probably is

Q: May I know why there is -3 on my question. what's wrong?

Alright folks time to work on answering and asking my own question.

Master Database

4:30 PM

I have a question about my Database Administrators Stack Exchange post: MySQL database innodb tables

support

@Zane you brain works like Deep Thought ;)

Andriy M

4:43 PM

Hmm, it looks as if we are trying to solve a database problem in a meta question.

That person's going to be having a bad day...

Lamak

@billinkc I'm disgusted

I'm defending you indirectly

How to win friends and influence people, by Yesha

Lamak

I'm taking notes

5:01 PM

@ypercubeᵀᴹ Yesterday I went looking for how much recovery model would effect things like bulk insert and didn't find much so I'm going to research then post a Q and A based on my findings.

5:12 PM

Surprised the comment count has tripped the fun police alarm

I guess it has but since the discourse is thus far civil, no need to clean

Lamak

yeah, it has remained civil

5:44 PM

I've been told that bulk insert would go faster on bulk and simple recovery, than it would on full. Thus far I'm not finding that to be true.

6:09 PM

Well it is true but not by as much as I would have expected I suppose.

6:25 PM

@Zane Remember databases in full recovery aren't really in full until their first full backup; does your test rig account for this?

@wBob I did not think about that!

Is that also true of bulk?

@Zane Don't think it will make a massive difference to your results although worth checking. It's more about log size, eg you don't need a 1TB log if you manage it properly (and do not need point-in-time recovery).

I have zero need for point in time recovery we are an overnight load shop

@Zane Yes, same principle.

@Zane You can use the old backup to nul trick for your test rig, but never in production...

good call dude I completely forgot since these are fresh test db's

@wBob Well yeah I'm not exactly testing this on Prod box.

6:37 PM

@Zane A commonly misunderstood fact about bulk-logged recovery is around point in time restore. BOL confuses the matter by stating "Can recover to the end of any backup. Point-in-time recovery is not supported.". Point-in-time restore is of course possibly with bulk-logged recovery model, it is only impossible with transaction log backups that contain bulk-logged transactions. Subtle isn't it?