« first day (2568 days earlier)      last day (611 days later) » 

12:47 AM
There are at least 5 billion ways to implement binary trees and hashing
 
1:07 AM
I'm pretty excited about this guys videos, youtube.com/watch?v=Yw1ts57uL7c
Just thought I'd share.
 
1:33 AM
@JoeObbish Cursory look, I don't know what you're doing but it involves batch mode processing. Might also involve parallelism, can't say for certain as 1) cursory look 2) QO/QE code is rough...
 
1:58 AM
I'm definitely going to write my own GiST index this year. ;)
 
 
5 hours later…
7:24 AM
Wishing you all a Happy Monday folks!
0
A: A convention for RDBMs specific options?

hot2useThe setting QUOTED_IDENTIFIER is a SQL Server SET OPTION that tells the application to adhere to the ISO standard Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transa...

 
morning and evening
 
7:51 AM
morning all
 
 
2 hours later…
9:45 AM
The original title is ... awesome
 
What is gum surgery?
 
@ypercubeᵀᴹ :-D
 
10:08 AM
@PaulWhite dental surgery I suppose
 
I guess. I've just never heard "gum surgery".
 
10:34 AM
Enjoy....
 
10:51 AM
Yes, had a nice cheese sandwich. Off for lunch now. :-)
 
11:17 AM
@hot2use Thanks
 
11:30 AM
Found a hair in the salad. Put me right off.
 
12:26 PM
@ypercubeᵀᴹ His mistake is he should be reinstalling his OS.
 
@Colin'tHart he should be doing lots of alternate things ...
 
1:14 PM
@Colin'tHart like ... a jaw transplant? ;)
 
 
1 hour later…
2:43 PM
@SeanGallardy-Microsoft thanks for taking a look
we are indeed doing a lot of parallel batch mode stuff
in other news, apparently max server memory is just a suggestion instead of a directive
max server memory is 830000 MB. sql server went above 900 GB
 
max server memory is like the warning on menus about eating raw or undercooked food
 
but why
 
3:09 PM
negotiations
 
I'd like to speak to the manager
 
call your agent
 
 
2 hours later…
5:25 PM
I think I got 6 question badges today
3 popular, 2 nice, 1 notable
 
6:11 PM
0
Q: Do we need version specific oracle- tags?

miracle173Additional to the tag oracle we have the tags oracle-8i, oracle-9i, oracle-10g, oracle-10g-r2, roacle-11g. oracle-11g-r2 All tags contain the statement Please also tag oracle for search purposes. oracle-10g should be used for Oracle Database 10g r1 and not for all 10g releases. Similar is...

 
Ooo shiny! I want one.
 
@EvanCarroll Here you go.
It's a bit bronzish rust colored?
@JoeObbish No, it'll follow your specifications... however there are things outside the setting.
 
documented anywhere?
 
@SeanGallardy-Microsoft does that me get into the cool kids club at Evil Empire Company. ;)
I want penthouse access.
 
@EvanCarroll Not unless you're in a TV show called "Mr. Robot"
Also, you'll have to go insane first...
 
6:38 PM
Wait until I port OrcaMDF to C and bring it up to date.
Just so I can read Microsoft Databases in PostgreSQL, natively.
 
@JoeObbish "Max server memory controls the SQL Server memory allocation, compile memory, all caches (including the buffer pool), query execution memory grants, lock manager memory, and CLR1 memory (essentially any memory clerk found in sys.dm_os_memory_clerks). "
"Memory for thread stacks1, CLR2, extended procedure .dll files, the OLE DB providers referenced by distributed queries, automation objects referenced in Transact-SQL statements, and any memory allocated by a non SQL Server DLL are not controlled by max server memory."
 
> mfw something is actually documented
what a lovely surprise
 
@JoeObbish Every once and a while ;) Gotta keep you on your toes...
 
that stuff can't possibly add up to 70 GB
 
how many cores?
 
6:40 PM
@EvanCarroll Ah you mean the recently revamped Awesome Open Source Company :)
 
112
 
6 GB just for thread stacks...
 
sure, but there's nothing else
 
No OLE? SQL Server .exe's and DLLs themselves? No Hekaton? No Columnstore?
 
well that can't add up to 64 GB?
 
6:43 PM
You have to account for memory leaks as well :-D
3
 
there's lots of columnstore, but how does that fit?
 
if the sql server .exe requires 64 GB of memory we're going to a 100% oracle shop
 
LOL
If you think SQL Server uses memory... you've clearly never used Oracle ;)
 
wait
 
just using columnstore can cause you to go over max server memory? wth
 
@PaulWhite In due time. Whether they like or not. Regardless of the cultured borg, and their intransigent obsessions with the status quo. It'll happen. =) I'm optimistic about the future. But they got a lonnngggg way to go.
 
Life was easier when the lines were clearly defined and we knew who to love/hate.
 
I got my first bug ever closed by Microsoft with a status other than Won't Fix in the tail end of 2017.
 
Not Reproducible? ;)
 
6:47 PM
I don't understand how Real DBAs can possibly do their jobs well
 
But congrats.
 
I don't file those. =)
 
@JoeObbish I always thought that their job was to complain
and they do that very well
 
@Lamak if that was true I would be exceptionally qualified to be a Real DBA
 
I'll tell you something else that you may delight you about Microsoft, Google is fast moving in the wrong direction. At a much faster pace than Microsoft is moving in the right direction.
 
6:49 PM
Just be glad we've moved past the 32 bit days of MemToLeave somewhat.
Memory stuff is still very complex.
I imagine most DBAs take a pragmatic approach.
 
Google is the new Microsoft. I can't wait for the Baidu to jump on the US scene.
 
I'm sure that it needs to be complex
but we can do the math for things like thread stacks and leave that much reserved
docs seem to say that columnstore can just require you to reserve an additional unknown amount...
 
@JoeObbish were you at NASA?
 
@PaulWhite Don't forget, your favorite - Trace Flags that change the behavior.. completely...
 
For sure.
 
7:00 PM
@JoeObbish could be worse, we try to do the distribution of memory for you across all components through a single configuration item. Try configuring memory in Oracle or MySQL... 10 configuration items later you're not entirely sure you've set each individual component properly.
There is an argument both for more knobs and less knobs, respectively.
So if you like turning 1 billion knobs half a millimeter, "Because", then this style will annoy the piss out of you.
 
7:23 PM
@SeanGallardy-Microsoft Do you happen to know if it works differently on Linux? I thought I heard there was a single parameter that set an absolute maximum limit on the amount of memory the whole SQL Server process could use. Not at all sure though.
 
@PaulWhite The only thing I can remember off the top of my head is that on Linux, by default, we only see 80% of memory.
 
I can check into it if you need to know
if there are any other oddities.
 
@SeanGallardy-Microsoft No I was just mildly curious. I can barely spell Linux.
 
@PaulWhite lol. The other thing to be aware of in terms of Linux and memory is OOM Killer
 
7:26 PM
Oh I knew that one. Probably by osmosis.
 
Linux will just kill things that use too much memory... because... stuff and things...
 
It has the advantage of being simple I suppose.
Bit awkward when the whole database instance falls victim to OOM but I guess learning happens fast.
Clearly we need more and fewer knobs. Schrödinger's knobs, if you will.
4
 
@PaulWhite lol though the questions start coming in "Why'd SQL Server just stop? It's a bugGGGGGGGggghghghgggG!G!G!GG"
 
passes ticket to Linux support team
 
@PaulWhite Yes, Linux has the ability to set memory in PAL.
 
7:30 PM
@EvanCarroll Thanks. /opt/mssql/bin/mssql-conf set memory.memorylimitmb?
 
@SeanGallardy-Microsoft I think Linux uses full memory of the PAL/Library OS.
@PaulWhite not sure internally it's PAL_MEMORY iirc but it's undocumented. SO NOW YOU KNOW.
@PaulWhite it's an environmental variable.
 
@EvanCarroll It can, but you have to set it.
In order to ensure there is enough free physical memory for the Linux Operating System, the SQL Server process uses only 80% of the physical RAM by default. For some systems which large amount of physical RAM, 20% might be a significant number. For example, on a system with 1 TB of RAM, the default setting would leave around 200 GB of RAM unused. In this situation, you might want to configure the memory limit to a higher value.
See the documentation on the mssql-conf tool and the memory.memorylimitmb setting that controls the memory visible to SQL Server (in units of MB).
 
How did I get involved in a Linux discussion lol
 
I don't read the docs. I read the system calls it makes. It makes me more awesome.
 
As if that were possible.
 
7:32 PM
Microsoft will open up before they write decent docs.
Evan + 1 = Evan.
 
TIL Postgres identifiers are case sensitive when quoted.
 
@EvanCarroll Everything we do as a company, everything, is just to piss off @EvanCarroll
13
 
That's in Satya's book
4
 
@PaulWhite I literally just spit out tea
You have to give me some warning on those
 
You're welcome
 
7:34 PM
=) it's a love hate relationship. Actually it's just hate but I'm a masochist.
 
@PaulWhite What's the popular drink down there, these days?
 
@SeanGallardy-Microsoft Anything cheap with alcohol in it?
 
ahahahha
 
is hand sanitizer cheap?
 
7:39 PM
So I learned something super cool today
And, I want to share it.
 
@JamesL Discussed it with Aaron and should be a simple fix. Could you send the plan to him please?
 
First: I have 0-academic experience with RDBMS, I only read shit and play with shit I shouldn't play with. So people that have that background that don't work with Microsoft are generally smarter than me, substantially.
So I got the real definition of a "Primary Index" and "Secondary Index" and my mind is blown.
 
"The examples assume that the server contains 256 CPUs that are arranged into four groups of 16 NUMA nodes each"
 
PostgreSQL implements the ANSI-SQL "Primary Key" on a "Secondary Index."
MIND BLOWN
 
BOL examples are great
 
7:42 PM
A Primary Index requires a data file to be organized, and so you can't technically have a Primary Index on a non-sequential data-file (unordered heap)
 
7:55 PM
@EvanCarroll Right. It seems Microsoft might be gradually moving naming away from clustered/nonclustered to primary/secondary index. Confusion arose with clustered columnstore on an in-memory table, for example.
Naming is hard.
 
Yea, well this was like mind blowing to me. I've got a lot more questions about it.
I'm going to take them to CS.SE though
 
I'm not sure any of the naming will stand up to a detailed analysis.
 
Can anyone offer any advice on correcting this? (Put an example of expectation in comment below SELECT.) dbfiddle.uk/…
 
@PaulWhite not on RDBMS, surely. But the distinction is really useful.
With SQL, "PRIMARY" it's just a logical designation that is totally removed from implementation.
 
@EvanCarroll Could you link your CS.SE question here and ping me? I'm just interested to read it.
 
7:58 PM
0
Q: Why must a Primary Index be sparse?

Evan CarrollReading Fundamentals of Database Systems 7th Edition, on Page 603, it says, Indexes can also be characterized as dense or sparse. A dense index has an index entry for every search key value (and hence every record) in the data file. A sparse (or nondense) index, on the other hand, has index e...

 
Ta.
 
If the contents of blocks are not sorted by the field being indexed, then the index is not a primary index. Again, "primary index" is not an epithet you apply to indexing mechanisms that satisfy certain properties, it is itself a very specific indexing mechanism: a sorted table of pointers to sorted blocks. It is a specific data structure and associated algorithms, just as each of a B-tree, hash index, or bitmap index is a specific data structure with associated algorithms. — Derek Elkins 48 mins ago
That's the kind of comment that idiots like me need.
 
Nevermind, managed to figure it out: dbfiddle.uk/…
 
@202_accepted Slightly simpler syntax: dbfiddle.uk/…
 
@AndriyM I owe you a beer.
 
8:11 PM
@202_accepted Make it an orange juice
 
@AndriyM Deal.
 
 
1 hour later…
9:18 PM
Ok, so I have one more remaining question about logical abstraction. =) Time to bother cs.SE again
MY NEW BEST FRIENDS.
actually scratch that, I think I got this.
 
9:39 PM
has anyone ever successfully mixed ecc and non-ecc memory?
i know that it disables the error checking for the ecc chips, but curious if anything else wonky happened
til ireland has a president
 
10:27 PM
@SeanGallardy-Microsoft Up to 8, got another one?
 
11:27 PM
@bluefeet i really want the ability to save a prepared answer without publishing it =(
=(
 
11:44 PM
That's what notepad is for
 

« first day (2568 days earlier)      last day (611 days later) »