The Heap™ – Consultancy ©®

Evan Carroll

12:47 AM

There are at least 5 billion ways to implement binary trees and hashing

Evan Carroll

1:07 AM

I'm pretty excited about this guys videos, youtube.com/watch?v=Yw1ts57uL7c

Just thought I'd share.

Sean Gallardy - Microsoft

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...

Evan Carroll

1:58 AM

I'm definitely going to write my own GiST index this year. ;)

hot2use

7:24 AM

Wishing you all a Happy Monday folks!

0

A: A convention for RDBMs specific options?

The 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...

Andy K

morning and evening

McNets

7:51 AM

morning all

ypercubeᵀᴹ

9:45 AM

dba.stackexchange.com/posts/195383/revisions

The original title is ... awesome

Paul White

What is gum surgery?

hot2use

@ypercubeᵀᴹ :-D

ypercubeᵀᴹ

10:08 AM

@PaulWhite dental surgery I suppose

Paul White

I guess. I've just never heard "gum surgery".

hot2use

10:34 AM

Enjoy....

google.ch/…

McNets

Frenectomy and Laser Gum Surgery on Advanced Periodontitis

►

Have you breakfast?

hot2use

10:51 AM

Yes, had a nice cheese sandwich. Off for lunch now. :-)

Paul White

11:17 AM

@hot2use Thanks

hot2use

11:30 AM

Found a hair in the salad. Put me right off.

Colin 't Hart

12:26 PM

@ypercubeᵀᴹ His mistake is he should be reinstalling his OS.

hot2use

@Colin'tHart he should be doing lots of alternate things ...

ypercubeᵀᴹ

1:14 PM

@Colin'tHart like ... a jaw transplant? ;)

Joe Obbish

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

sp_BlitzErik

max server memory is like the warning on menus about eating raw or undercooked food

Joe Obbish

but why

sp_BlitzErik

3:09 PM

negotiations

Joe Obbish

I'd like to speak to the manager

sp_BlitzErik

call your agent

Evan Carroll

5:25 PM

I think I got 6 question badges today

3 popular, 2 nice, 1 notable

Master Database

6:11 PM

0

Q: Do we need version specific oracle- tags?

Additional 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...

discussion

Sean Gallardy - Microsoft

6:33 PM

Paul White

Ooo shiny! I want one.

Sean Gallardy - Microsoft

@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.

Joe Obbish

documented anywhere?

Evan Carroll

@SeanGallardy-Microsoft does that me get into the cool kids club at Evil Empire Company. ;)

I want penthouse access.

Sean Gallardy - Microsoft

@EvanCarroll Not unless you're in a TV show called "Mr. Robot"

Also, you'll have to go insane first...

Evan Carroll

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.

Sean Gallardy - Microsoft

@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."

docs.microsoft.com/en-us/sql/relational-databases/…

Joe Obbish

> mfw something is actually documented

what a lovely surprise

Sean Gallardy - Microsoft

@JoeObbish Every once and a while ;) Gotta keep you on your toes...

Joe Obbish

that stuff can't possibly add up to 70 GB

Sean Gallardy - Microsoft

how many cores?

Paul White

6:40 PM

@EvanCarroll Ah you mean the recently revamped Awesome Open Source Company :)

Joe Obbish

112

Sean Gallardy - Microsoft

6 GB just for thread stacks...

Joe Obbish

sure, but there's nothing else

Sean Gallardy - Microsoft

No OLE? SQL Server .exe's and DLLs themselves? No Hekaton? No Columnstore?

Joe Obbish

well that can't add up to 64 GB?

Paul White

6:43 PM

You have to account for memory leaks as well :-D

3

Joe Obbish

there's lots of columnstore, but how does that fit?

Sean Gallardy - Microsoft

.....

MATHS

Joe Obbish

if the sql server .exe requires 64 GB of memory we're going to a 100% oracle shop

Sean Gallardy - Microsoft

LOL

If you think SQL Server uses memory... you've clearly never used Oracle ;)

Joe Obbish

wait

Sean Gallardy - Microsoft

6:45 PM

brb

Joe Obbish

just using columnstore can cause you to go over max server memory? wth

Evan Carroll

@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.

Paul White

Life was easier when the lines were clearly defined and we knew who to love/hate.

Evan Carroll

I got my first bug ever closed by Microsoft with a status other than Won't Fix in the tail end of 2017.

Paul White

Not Reproducible? ;)

Joe Obbish

6:47 PM

I don't understand how Real DBAs can possibly do their jobs well

Paul White

But congrats.

Evan Carroll

I don't file those. =)

Lamak

@JoeObbish I always thought that their job was to complain

and they do that very well

Joe Obbish

@Lamak if that was true I would be exceptionally qualified to be a Real DBA

Evan Carroll

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.

Paul White

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.

Evan Carroll

Google is the new Microsoft. I can't wait for the Baidu to jump on the US scene.

Joe Obbish

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...

Evan Carroll

@JoeObbish were you at NASA?

Sean Gallardy - Microsoft

@PaulWhite Don't forget, your favorite - Trace Flags that change the behavior.. completely...

Paul White

For sure.

Sean Gallardy - Microsoft

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.

Paul White

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.

Sean Gallardy - Microsoft

@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.

Paul White

Ta

Sean Gallardy - Microsoft

I can check into it if you need to know

if there are any other oddities.

Paul White

@SeanGallardy-Microsoft No I was just mildly curious. I can barely spell Linux.

Sean Gallardy - Microsoft

@PaulWhite lol. The other thing to be aware of in terms of Linux and memory is OOM Killer

Paul White

7:26 PM

Oh I knew that one. Probably by osmosis.

Sean Gallardy - Microsoft

Linux will just kill things that use too much memory... because... stuff and things...

Paul White

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

Sean Gallardy - Microsoft

@PaulWhite lol though the questions start coming in "Why'd SQL Server just stop? It's a bugGGGGGGGggghghghgggG!G!G!GG"

Paul White

passes ticket to Linux support team

Evan Carroll

@PaulWhite Yes, Linux has the ability to set memory in PAL.

Paul White

7:30 PM

@EvanCarroll Thanks. /opt/mssql/bin/mssql-conf set memory.memorylimitmb?

Evan Carroll

@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.

Sean Gallardy - Microsoft

@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).

Paul White

How did I get involved in a Linux discussion lol

Evan Carroll

I don't read the docs. I read the system calls it makes. It makes me more awesome.

Paul White

As if that were possible.

Evan Carroll

7:32 PM

Microsoft will open up before they write decent docs.

Evan + 1 = Evan.

Paul White

TIL Postgres identifiers are case sensitive when quoted.

Sean Gallardy - Microsoft

@EvanCarroll Everything we do as a company, everything, is just to piss off @EvanCarroll

13

Paul White

That's in Satya's book

4

Sean Gallardy - Microsoft

@PaulWhite I literally just spit out tea

You have to give me some warning on those

Paul White

You're welcome

Evan Carroll

7:34 PM

=) it's a love hate relationship. Actually it's just hate but I'm a masochist.

Sean Gallardy - Microsoft

@PaulWhite What's the popular drink down there, these days?

Paul White

@SeanGallardy-Microsoft Anything cheap with alcohol in it?

Evan Carroll

ahahahha

Sean Gallardy - Microsoft

LMFAO

Joe Obbish

is hand sanitizer cheap?

Evan Carroll

7:39 PM

So I learned something super cool today

And, I want to share it.

Paul White

@JamesL Discussed it with Aaron and should be a simple fix. Could you send the plan to him please?

Evan Carroll

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.

Joe Obbish

"The examples assume that the server contains 256 CPUs that are arranged into four groups of 16 NUMA nodes each"

Evan Carroll

PostgreSQL implements the ANSI-SQL "Primary Key" on a "Secondary Index."

MIND BLOWN

Joe Obbish

BOL examples are great

Evan Carroll

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)

Paul White

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.

Evan Carroll

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

Paul White

I'm not sure any of the naming will stand up to a detailed analysis.

202_accepted

Can anyone offer any advice on correcting this? (Put an example of expectation in comment below SELECT.) dbfiddle.uk/…

Evan Carroll

@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.

Paul White

@EvanCarroll Could you link your CS.SE question here and ping me? I'm just interested to read it.

Evan Carroll

7:58 PM

0

Q: Why must a Primary Index be sparse?

Reading 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...

terminology databases

Paul White

Ta.

Evan Carroll

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.

202_accepted

Nevermind, managed to figure it out: dbfiddle.uk/…