TopAnswers Databases
or
Why isn't this query eligible for a hash join?
Joe ObbishJoe Obbish
sql-server
Top Answer: This is because of `HASH` join algorithm requires join equality predicate, but query optimizer eliminates it during simplification.
i-one
Can I have 'Query Store' automatically on when I create new databases?
James JenkinsJames Jenkins
sql-server-2016
Top Answer: Query Store is not active for new databases by default.
SqlWorldWide
Enable query store for all databases
James JenkinsJames Jenkins
sql-server-2016
Top Answer: In both cases for the attempted queries in the question, the problems are simply syntactical.
solomon rutzky
Answer #2: First, `sp_MSForeachDB` is undocumented and unsupported, so stop using that. I've actually seen it miss DBs completely in a real-world scenario.
Kris Gruttemeyer
What is 'Log Memory' in Query Store 2017
James JenkinsJames Jenkins
sql-server-2017
Top Answer: [LowlyDBA's answer][2] covers what the metrics actually *mean.* This answer is just to explain why the numbers in the Query Store user interface don't totally make sense.
Josh Darnell
Answer #2: If we look at the documentation for the underlying object, [`sys.query_store_runtime_stats`](https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-runtime-stats-transact-sql?view=sql-server-2017), we'll see it has the following descriptions:
LowlyDBA
Why is Query Store missing details?
James JenkinsJames Jenkins
sql-server
Top Answer: I see this on my SQL Server 2016 instance as well (same settings as yours: 15 min data flush, and 1 hour stats collection). I noticed that the plans with missing information correlated with AG failovers and maintenance-related reboots (which I found by looking in the SQL Server error log).
Josh Darnell
Answer #2: **TL:DR** It happens, it is not unusual, it is by design.
James Jenkins
What to consider when setting flush_interval_seconds in SQL Query Store?
James JenkinsJames Jenkins
sql-server
Top Answer: >Is query store data written to the log? If so would a restore let us recover any Query Store data, that had not been pushed to disk?
Kin Shah
How can I export Query Store data?
James JenkinsJames Jenkins
sql-server-2017
Top Answer: First of all, you might be able to get acceptable performance with queries directly against the query store catalog views by updating stats, adding query hints with plan guides, or changing the database compatibility level / CE. See the answers from Forrest and Marian here:
Josh Darnell
Answer #2: As a supplement to the [great answer by Josh Darnell](https://dba.stackexchange.com/a/231658/21924) I read through all the descriptions of the data views that are being exported into tables. The following code adds the primary keys, clustered indexes and foreign keys as described in the Microsoft documents. It should help with queries against the data.
James Jenkins
What benefit is there from enabling Query Store on msdb?
James JenkinsJames Jenkins
sql-server-2016
Top Answer: @SqlWorldWide answered the "why `[msdb]`" part of the question so I won't duplicate that here. But to answer the "why not `[master]`, `[model]`, `[tempdb]`" part of the question:
solomon rutzky
Answer #2: Microsoft enabling a feature does not mean it will be useful for everyone. For systems using some of the features can mean relying on information stored in MSDB. In those cases Query Store can be useful.
SqlWorldWide
What is an “unmentionable word” to SQL?
James JenkinsJames Jenkins
sql-server
Blockchain (Bitcoin) as a database?
James JenkinsJames Jenkins
Top Answer: > Is blockchain a **potentially viable database** solution for modern, **high transaction volume** applications?
ypercubeᵀᴹ
Answer #2: I'm very familiar with cryptocurrency and databases, and I can tell you it's not a great DB engine at all.
Ali Razeghi
Answer #3: In 2014 we built ascribe.io with the premise of using Bitcoin as a database for Intellectual Property claims. Upon release, we plugged the network because it couldn't handle the throughput, latency was at least 10 minutes and we were limited by what we could put into the OP_RETURN, forcing us to store the actual digital file relating to the claim in Amazon S3. We realized that Bitcoin in its current form could never be a high transaction database.
Bruce Pon
The Heap
chat
transcript
Josh Darnell 
Ah, okay.  No worries about eXtreme details.  I was just curious if you remembered off the top of your noggin.
Paul White 
That might have been part of the 4199-covered fix but honestly I don't recall that level of detail
Paul White replying to Josh Darnell 
As I recall it was back-ported
Josh Darnell 
Do you know if the article is wrong, or the calculator was backported (or something else)?
Josh Darnell 
@Paul Regarding [this article](https://www.sqlshack.com/join-estimation-internals/), it says the `CSelCalcSimpleJoinWithUpperBound ` calculator was introduced in SQL Server 2016.  I repro'd the problem (and solution) on 2014, since that was the OPs version.
Lamak 
lol
GeorgePalacios 
Pretty impressive.
Paul White 
yowza
GeorgePalacios 
Microsoft Sequel Server (MySQL Query Language)
GeorgePalacios 
One line under skills reads
GeorgePalacios 
Going through CV's today for a new DBA starting
GeorgePalacios 
Had to share this.
Paul White 
Someone v. reasonable proposed it
Josh Darnell replying to Paul White 
Aha, that sounds like a v. reasonable change.
Paul White 
Implied a rather higher level of structure and organization than was justified
Paul White replying to Josh Darnell 
The Heap 1.0 was originally called The Clustered Index
Lamak replying to Zane 
hey!, good to hear from you
Jack Douglas replying to Josh Darnell 
well Joe is a fellow Oracle man ;)
Josh Darnell 
Is that you, Joe?
Jack Douglas 
heaps rule
Josh Darnell replying to PeterVandivier 
O_O
PeterVandivier 
  
> clustering is a one-time operation
PeterVandivier 
be ye afeared
PeterVandivier replying to Josh Darnell 
https://www.postgresql.org/docs/current/sql-cluster.html
Josh Darnell replying to PeterVandivier 
Postgres doesn't have b-tree indexes?!
PeterVandivier replying to Josh Darnell 
postgres backend demands respect
Josh Darnell 
That's definitely been a popular decision, and one of the larger departures from how SE does things.
Zane 
I like the bit where it's just always chat on the side. Turning comments into conversations.
Josh Darnell 
I wonder why Jack didn't name it "The B-Tree"
Josh Darnell 
Well welcome to the upgraded Heap.
Zane 
That is correct. I did live and continue to do so!
Josh Darnell 
I had to edit in some social distancing.
Josh Darnell replying to Zane 
.  
.  
.  
He's alive!
Zane 
Testing.
PeterVandivier replying to Jack Douglas 
i figured you might be, but then i thought it would sound snooty to ask if that's what it was if it was a bona fide outage
PeterVandivier 
:-D
Jack Douglas 
probably should put all that in a blog
Jack Douglas 
the biggest change is that backends mostly put a hard limit on disk space per fiddle now
Jack Douglas 
you might notice things being faster as I've fixed a few bugs along the way
Jack Douglas 
some backends will be unavailable for a while
Jack Douglas 
I need to move the physical hardware from one rack to another in the DC before we get total lockdown here so it's bee a bit of a rush
Jack Douglas replying to PeterVandivier 
just doing some unannounced maintenance while America is asleep :)
PeterVandivier 
> _shudder_
PeterVandivier 
having to resort to... sql fiddle...
PeterVandivier 
@Jack is db<>fiddle okay? seems unreachable on my end atm
Andriy M replying to Jack Douglas 
Not as clever as the solution itself! Thanks though
Jack Douglas 
that's some very clever tweaking ^^^
Jack Douglas 
::: quote 856 32208 33 209,232,209 37,71,37
Andriy M *— 3 hours ago* *in [Join by overlap](/codegolf?room=856#c32208)*  
>Hey @Jack, I managed to squeeze your solution down to 364 bytes after finding a few more redundant spaces and observing that table aliases in column references could be removed by renaming columns of `z`. Then I noticed that the nested `with` could be rewritten relatively compactly as a single `select`. The result was 320 bytes. Somewhat annoying that I could manage to get it below 300. Oh well. Here's the result: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=f04c882d7c13e752566490642fef8c8c Feel free to use it in your answer!
:::
Paul White 
Morning
Andriy M 
Morning
swasheck 
stay safe everyone
swasheck 
still
swasheck 
denver
Lamak 
were are you now?
Lamak 
that sucks
swasheck 
my daughter likely has flu, and my wife is beginning to flu symptoms
swasheck 
3/5 of my household is healthy
swasheck 
roughly the same. we've been in a state of quarantine for two weeks now.
Lamak 
how are you?
Lamak replying to swasheck 
hi!...I'm quarantined, but healthy
Jack Douglas replying to swasheck 
my pleasure, literally in this case because I've found it really useful :)
swasheck 
how's everyone doing?
swasheck 
@jack thanks for getting screen clipping up and running
swasheck 
howdy, all
Paul White 
Also clearing the last notification doesn't take one back to chat if the notification is a system one (I got one from CodeGolf)
Paul White replying to Jack Douglas 
Quite impressed you managed that in SQL at all
Jack Douglas 
(once we've finished messing around with db<>fiddle!)
Jack Douglas 
yes that is something we need to fix
Jack Douglas 
I need to read better
Jack Douglas 
oh wait
Jack Douglas 
does that not happen for you?
Jack Douglas replying to Paul White 
no, clicking on reply should take you into the chat pane
Paul White 
Is it a known restriction that *replying to* links don't work in the Notifications pane?
Jack Douglas 
@@@ answer 991
Jack Douglas 
I didn't realise code golf can be so addictive
Paul White 
eeek
Jack Douglas 
had to write a bit of C code either way which I find painful
Jack Douglas replying to Paul White 
I eventually settled on using xfs quotas, after getting a more fragile implementation working with loop mounted files
Paul White 
How did you do it for Postgres?
Jack Douglas 
and about a million percent easier to implement then the postgres variety
Paul White 
I forget things from the future sometimes
Jack Douglas replying to Paul White 
actually that is pre-release as well
Paul White 
I don't abuse db<>fiddle clearly :)
Paul White 
I had forgotten how it worked already for SQL Server.
Paul White 
Oh I see. Well that makes sense.
Jack Douglas 
![blob](/image?hash=4c7a5c476d130cf67fd9650328360c8c2a94c8e6e1155c1d1197a1df0b146527)
Jack Douglas replying to Paul White 
I guess I'll do that if it becomes an issue — but it will need custom code for different backends which I like to avoid.
Paul White 
I was wondering if you could catch the error and print something more helpful instead
Jack Douglas replying to Paul White 
true but I think I'll wait and see on that one as the limit is high enough to only catch people who probably already know they are testing the limits
Paul White 
i.e. it could generate support requests
Paul White 
That could cause people to think db<>fiddle has run out of disk space, rather than their individual fiddle being limited?
Jack Douglas 
not released yet
Jack Douglas 
![blob](/image?hash=c734ab6e24a2abdd2d0cc98584b1f72119e86feebd59e40dc35fef154f0c7909)
Jack Douglas 
I think I have finally figured out how to impose disk usage quotas on postgres fiddles
Jack Douglas 
morning
Monica 
Here they just expect us to find out via the media, it seems.  Which I guess works because of social media, but still... somebody pushes amber alerts and storm warnings to my phone but a stay-at-home order doesn't rate?  Governments are weird.
PeterVandivier 
![9E527581-1F84-4283-AF34-5062CECF68B7.jpeg](/image?hash=f9e99a22a653b232919bfc76a6840721f012c942015ccf6227fc9649605a237e)
PeterVandivier replying to Jack Douglas 
just got my copy of that
Lamak 
I'm quite impressed that the quarantine un the UK includes that you can exercise once a day
Jack Douglas replying to Tom V 
![blob](/image?hash=52430ea4fd43456764cb259311e7e7d295f5c0d28b2f942f15b8271b7c658f38)