The Heap™ – Consultancy ©® - 2017-08-10 (page 1 of 3)

@PaulWhite I'll have to leave you more, then.

Only if they point out how I really ought to be using a real database

Or NULL of course; that's always welcome

I'll leave them as NULL Torme.

2:21 AM

@PaulWhite Do you mind answering a question on query transformations?

@JoeObbish Fire away

ok, it will take me a little while to write up a proper question

no XY questions this time

@JoeObbish On the main site?

no, I think it would be closed on the main site

need to translate our IP into something sharable

first I'm going to answer Phil's question though... not sure how he attracted so many low quality answers

2:40 AM

@MaxVernon I went ahead and renamed it to string-searching as discussed

Feel free to improve the usage guidance as normal; I just added a brief thing

Q: Two system declare server name same is possible?

Oh wow

0

I am from India, Developer Startup Company Please ask my question reply

sql

O_O

Q: Populating missing data based on previous month-end values

@JoeObbish This one?

5

Given the following data: create table #histories ( username varchar(10), account varchar(10), assigned date ); insert into #histories values ('PHIL','ACCOUNT1','2017-01-04'), ('PETER','ACCOUNT1','2017-01-15'), ('DAVE','ACCOUNT1','2017-03-04'), ('ANDY','ACCOUNT1','2017-05-06'), ...

sql-server sql-server-2008-r2 gaps-and-islands

Yeah, those answers are rough

@sp_BlitzErik Not as rough as that question you linked was

See people say we don't vote enough but the voting there seems about right :)

@PaulWhite yeah

I missed my chance to down vote

2:52 AM

If that flag got declined I would leave for like nine days.

@sp_BlitzErik Dammit!

oh yeah

now is my chance to get all of the unicorn points

Q: How do I properly use the "Not an Answer" flag?

56

What is the "Not an Answer" flag? What is the purpose of this flag? When should I use this flag? When should I not use this flag? Return to FAQ index See Also What is an acceptable answer?

support faq flags answers not-an-answer

I still don't understand how to use that flag

@JoeObbish It's not really massively complicated. If it is any sort of an answer, it's not not an answer.

2:56 AM

I remember one time someone misread the question completely

and used the wrong RDBMS

so it was both answering the wrong question and doing it in a way that wouldn't be helpful

@JoeObbish So you downvoted and didn't flag?

anyway, all I flag are comments because I understand the rules around those

@PaulWhite at the time I was a delicate fawn so I flagged

"was"

Stop talking about Evan like he's not here.

I don't know what you're talking about

3:00 AM

4 mins ago, by Joe Obbish

and used the wrong RDBMS

could have been anyone!

also I was too poor to downvote

> doing your demo in master by mistake

looks like I got some autogrowth

> You have to consider the question, other answers, or even the comments on the answer itself in order to determine if it qualifies for the flag

Often it's better to use a custom flag and explain what you think is wrong and what we should do

We'll often mark them helpful even if we take no action

ah

so there is a private messaging system around here

@JoeObbish ?

You mean like private chats?

3:07 AM

will EAGL, but my demo doesn't work at all

I wanted to show something not happening

but then it happened

this is a problem

A great demo

Demopposite

I quite like the description of NAA as "not even wrong" as well.

hm

I think this is sufficient

@PaulWhite Do you prefer db fiddle?

@JoeObbish What ever works for you

3:11 AM

it'll probably time otu

pastebin.com/0eQDvYAW

that's the set up

I'm not doing anything too fancy

@sp_BlitzErik Seems like it still has some learning to do :)

I may have just answered my own question

at least to a degree

let me know once you've had a chance to look at the queries

no need to run them

@JoeObbish What is your question?

3:23 AM

Why would you expect 3 and 4 to get the same plan?

Is that the question?

so looking at queries 1 and 2

we can see that they return the same results

in theory, the optimizer could transform them to be the same query before doing optimization

Based on the output of TF 8606, that doesn't appear to be happening

it just so happens that the optimizer picks the same plan for both of them

they're very simple queries, after all

@sp_BlitzErik I don't, personally

My gut feeling is that sql server is unlikely to do a query transformation that requires looking at the entire plan in order to verify that the transformation is valid. Is that about right?

@JoeObbish It never does that, except for the simplest plans that consist of no more than a single construction the optimizer knows how to simplify or transform.

@JoeObbish I'm tempted to say that it is right

All these things are orthogonal, at least to a very great extent. Individual things either match or they don't, and the new output may match with something else.

3:31 AM

@PaulWhite Is it fair to say that it's done on a piecemeal basis?

@JoeObbish Absolutely. One rule at a time.

So going to the second set of queries, with the join

they still return the same results

in theory, someone could write an optimizer that converts them to the same tree

@PaulWhite never thought about calling them orthogonal

but that's just not going to happen in SQL Server

interesting concept

3:32 AM

But aren't there heuristics that limit search space which would lead to some rules being skipped?

Basic simplifications happen early, before cost-based optimization. These are generally aimed at fixing redundant generated SQL, or where constraints make something impossible. Later cost-based simplifications can occur as well, but via a slightly different route.

Could it be argued that it's a good thing that these advanced transformations don't happen?

Moving the DISTINCT around gives the person writing the query more control over how the query is optimized

@sp_BlitzErik Absolutely. The search is guided and rules have "promise" values depending on the context they find themselves evalauted in.

One version might perform better than another (although none of that is guaranteed in future versions)

In Oracle I occasionally have the problem where the optimizer is too smart for its own good

rewrites three different queries to the same thing

@JoeObbish You can argue it both ways; each has strengths and weaknesses. Rule based optimizers used to work that way, broadly speaking.

3:34 AM

@JoeObbish it does that so it can charge you 3 times as much

Semantic optimization - rewriting for the total meaning of a query - is not as well explored topic as other areas.

Not least because SQL has some unusual semantics of its own.

And it is very easy to optimize beyond correctness.

i.e. a rewrite might return the same results on data set A, but not on all possible data sets.

Right

optimizing based on some secret information that the optimizer doesn't know

that was the question, thanks

@JoeObbish Yes, but also information that the query writer hadn't considered e.g. null handling, type changes, potential error conditions...etc.

yes, you called me out on that once

For example, it would probably take me twenty minutes or so to determine if queries 3 and 4 were semantically equivalent, and I might still be wrong.

Which is not to say the current optimizer is teh awesome and that other approaches wouldn't be valuable.

There is always that trade off between final plan quality and compilation time to consider as well.

3:40 AM

lately we've been having a lot of trouble with query compile times

sql_handle has been figuring out all kinds of interesting stuff, but I don't think he's written it up yet

I once had a conversation with someone from the optimizer team about this sort of thing. They said it's super easy to come up with new transformations (explorations/simplifications/whatever) but super hard to come up with generally useful ones that perform well.

interesting

I have yet to be convinced that there is a better way to write queries than the way I do it now.

Super-simple, basically relational stuff with good indexes and constraints.

@PaulWhite just wait a couple of years, you'll look in horror at the way you used to write them

Temporary tables with statistics and useful indexes for small to moderate intermediate results.

3:43 AM

@PaulWhite Is there an opportunity for anyone to convince you otherwise?

Then, once the optimizer has had it's way with the individual queries, I might go on to combine them, if performance needs dictate, and the result remains resilient enough.

I have done crazy things with queries to get the last 0.1% of performance, but it always starts with that process.

still makes me sad that I missed the 2013 precon

@JoeObbish There is always a chance, but if the people I have worked with to date, and the data sizes and performance needs I have worked with so far haven't needed a change in approach, I think the chances are relatively slim.

@JoeObbish There's only so much one can cover in a single day.

@PaulWhite what does that mean?

and what I meant more was people hire you because they need help

so it's not like they're in a position to convince you

@JoeObbish It means I could talk/teach for months on this topic.

So you might have found the 2013 precon unsatisfying.

3:46 AM

so it wasn't that great? Erik was a big fan

@JoeObbish yeah, but that was Erik

No I was super awesome for certain :) but there's only so much one can cover in one day, to 200 people at once.

And of course things change over time as new technologies and hardware come along.

e.g. Hekaton, columnstore, batch mode, window agg etc.

I feel so behind

but it doesn't seem like many people blog about columnstore

not really sure why

Time was when parallel nested loops apply with SQLCLR was the bees knees.

2013, you mean?

3:49 AM

@JoeObbish We all do. The product is bigger than it has ever been, and it has always been too much for one person.

@PaulWhite how could you feel behind?

seriously

@JoeObbish Up to about when SS 2012 came out yeah i.e. when row mode row store was our only option.

should say "you all do"

ok, you've convinced me

you can just do a precon in 2018

@Lamak Because there are vast areas of the product I know next to nothing about.

Erik and I will sit in the front row

3:51 AM

@Lamak And because batch mode/column store/in memory is so very different under the covers and extremely poorly documented.

don't worry, I'll document it

@PaulWhite but on the overall product, you can't really feel behind...

yeah, @JoeObbish will

Well, it's well documented for users but not internals freaks.

some of the rewrites that give you better performance are hilarious

I don't know if you saw the rowgroup elimination one

@Lamak It's a productive state to be in. As soon as you feel you're comfortable with something, chances are you're out of date.

3:52 AM

I'll admit that most of it is impractical, but it's still kind of silly

@JoeObbish On your blog? No sorry I haven't had time to look for a week or so.

no worries, it's not going anywhere

That's what I heard :)

Sorry could not resist don't mean it

hey man

I'll have you know

@PaulWhite I know. You could've stated it as "I have a lot to learn"...it was the "feeling behind" part

3:53 AM

that I have 1288 views

and not all of them are from Erik!

although he definitely helped

@JoeObbish link?

@Lamak to what

you want proof?

@JoeObbish That's very good.

@JoeObbish The blog post you were referring to

but he quoted the view count

that's for the entire blog over all time

@JoeObbish your blog

3:55 AM

One of the big problems (I think) is the rate of change. It is now impractical to write a book.
Hence SQL Server Internals not being updated since 2012.

oh, sure

@JoeObbish He's new to chat and doesn't always get the reference links right.

@JoeObbish @PaulWhite knows me better

@Lamak orderbyselectnull.com/2017/08/07/rowgroup-elimination

Flagged as spam

3:56 AM

I don't even make money off it!

in fact I lose money

@JoeObbish thanks

@JoeObbish But gain knowledge and skills.

@PaulWhite yes

it's funny

I've written 10 pages

@JoeObbish seems interesting

and 2 of them were basically proven totally wrong

by things others have written

3:57 AM

That can happen.

and I've been doing this for less than 2 months

yeah, I don't mind

@JoeObbish ok, I need to ask, why is the TABLESAMPLE tag the only one all caps?

The way I see it, I can either write 10 things that are 90% correct or 1 thing which is 99% correct

@Lamak good question

3:59 AM

perspectiveapi.com/# is quite fun thanks Erik.

I think because it's a SQL keyword

just force of habit

@PaulWhite sure, compared to those who write things which are 100% correct...

I hear there are other things wrong on the Internet as well

@JoeObbish ah, I get it

@JoeObbish I'm not sure that exists tbh

We all do our best within our current limitations

@PaulWhite how often do you get comments pointing out errors that aren't typos?

4:01 AM

I don't get many comments at all :)

@PaulWhite I think I understand now why you sometimes get irritated:

uh huh

I'm sure you could find some instances on SQLblog.com

anyway, it was funny

Hopefully not too many

4:02 AM

Erik got the TF 610 one added to the BOU newsletter

and two weeks later MS put out that blog post

@Lamak 9%?!

good thing I don't feel embarrassment!

4:03 AM

@Lamak Oh that makes sense ha ha nicely done

@PaulWhite quite the difference

@JoeObbish I don't recall your content actually being wrong was it?

buddy

I got some bad news

lol

Pom only gets 60%

4:04 AM

@PaulWhite It was. I originally thought that TF 610 was a no-op, but after looking harder concluded the opposite

there was a subtle error in my testing

Oh I see. Well anyway.

apparently creating a table, inserting rows, and rolling back that insert doesn't result in the same initial state as creating a table

Well no

that's expected?

Only if you expect it I guess

4:05 AM

did you expect it?

I would, yes.

pfft

SQL Server is a FAKE DATABASE with FAKE TRANSACTIONS

There's a whole heap of one-off stuff that happens when the first page/extent is allocated.

And system transactions do not roll back, as a rule.

Hence e.g. identity.

this is kinda weird

I suspected after the fact that it had something to do with extents

4:06 AM

that is weird

Thought it would be higher?

me too

Anyway yeah you can get some insight by looking at what gets logged by create table, the first insert, a roll back, and subsequent insert.

@Lamak Only 34% if you put a space in.

> You are a Nazi -- 100%

4:09 AM

Ha!

I liked the aussie one better

strongly disagree

100% toxic

ok I'm done

59 mins ago, by Paul White

@sp_BlitzErik Seems like it still has some learning to do :)

4:26 AM

I hate it when you miss the first 30 seconds of a video

and it won't let you move the position back to the start

it tries to be intelligent

gbn

5:59 AM

morning

6:34 AM

Morning

7:00 AM

morning

@PaulWhite thanks for the edition

@McNets ?

@PaulWhite In this answer. dba.stackexchange.com/a/183035/110455

@McNets Oh, thank you for the edit - I see

Or "addition" maybe?

oh oh so sorry

Anyway, sure no worries

7:09 AM

Still sleeping

Morning

7:39 AM

morning

Not checked the news today, has Trump nuked North Korea yet?

Not yet, neither us as a collateral damage.

@Philᵀᴹ has got any answer for your question? I'm not good with gaps & islands

8:19 AM

Hello and a very good morning to you all.

8:46 AM

@PaulWhite I hope it keeps you entertained while I'm away :)

@sp_BlitzErik How long is your holiday?

9:03 AM

Aw, you're the first person to ever call one of my trips a holiday. I feel so continental.

It's about 10 days total

9:23 AM

@sp_BlitzErik you guys might want to remove the twitter link on this page

@TomV Who is this ugly men?

@TomV thanks, done

9:39 AM

Mar 20 '15 at 18:12, by mmarie

I'm not familiar with Erik Darling

2

Mar 20 '15 at 18:14, by billinkc

He's a dumb chauffeur

2

@McNets yeah, a few so far. I've upvoted them all so far. More the merrier

Hmm, I've got a DB whose transaction log backup failed because the backup target disk filled up. The dialog was in the background and nobody noticed. As a side affect, the log itself has filled up & filled the disk. It won't let me shrink the log as it says there's still a log backup in progress, but the log backup has been cancelled. Is it likely to just be doing some sort of recovery in the background that will take a while to sort itself out?

10:05 AM

@Philᵀᴹ You could tell by running sp_whoisactive

Oh, the disk the log and data files is on is full :/ If I add another log file on a different drive will it be able to sort its life out by itself?

@Philᵀᴹ You can do that temporary, yes. You might also need to add an additional data file to accomodate for cahnges.

But if I add a new data file will it be easy to shift the data from it back to the other disk?

If there is enough space, you should be able to migrate the data back. There is a function inside the SHRINK DATABASE UI that allows you to migrate the data from one data file to another. Check first on a non-affected DB somewhere.

The syntax is other wise docucmented as DBCC SHRINKFILE <filename>, EMPTYFILE as can be seen here DBCC SHRINKFILE (Transact-SQL) (Microsoft Docs)

10:29 AM

Cheers - have added a new log to another drive & it's chugging away

I'd like to learn about gaps and islands, any good blog?

10:55 AM

LOG_BACKUP is still showing in log_reuse_wait_desc. Grr

Yes, it will still require a TLOG backup so long as transactions are chugging away. Can you stop the application?

Backing up the TLOG now & it should truncate once it has finished, then should be sorted!

The application already fell over :)

This isn't my database, it's a customer asking for a favour because it's already bust

sqlskills.com/blogs/paul/…

The Heap Consultancy to the rescue! ™️

2

:-)

Q: Postgresql How to multiply with a percentage value

11:07 AM

Ya, that's what made me do what I'm doing :)

Colin 't Hart

11:32 AM

Am I alone in this question making me want to bang my head against a brick wall?

2

I will like to be able to make the following operation 30*12% = 3.60 in a query. I have two tables, one to store the value of the sale of data type numeric and another table to store the value of the commission for each sale of data type numeric.

sql postgresql

2 upvotes

Colin 't Hart

@TomV Very strange, IMHO...

And 3 close votes, all different... anyone want to add a fourth vote to move it to Stackoverflow as it's a software development problem, not database administration?