The Heap™ – Consultancy ©®

12:37 AM

@sp_BlitzErik Bragging rights? Maybe the NoSQL kids were being mean "You can't sit here. You don't do graph. Ha ha. Go sit over there with Larry."

More seriously, do you remember SQL Server 7.5? In-memory 1.0? The first iteration of columnstore? Everything starts somewhere, I suppose. Graph as it stands won't an RTM feature but come vNext.Next.Next it will mainstream. Applications will be re-written to take advantage and life will have moved on a step.

Oops! Missed the edit window. Feel free to sprinkle a few "be"s over that last post.

sp_BlitzErik

@MichaelGreen sure, you could also name a ton of features that have been abandoned or deprecated too ;)

Michael Green

12:59 AM

@sp_BlitzErik True dat!

Joe Obbish

bring back PINTABLE

Michael Green

I don't think this one's going away, though. Stonebraker gave a lecture back when showing how specialist DB engines will beat the "dinosaurs" every time, with lots of examples. One by one those specialist engines have been incorporated into dinosaurs. Graphs one of the big omissions to date. Not for much longer.

ypercubeᵀᴹ

@MichaelGreen I find his claims a bit irritating sometimes. Just a bit ;)

Michael Green

@ypercubeᵀᴹ Truely: "column store is awesome!" said the man trying to exit a column store startup company.

Paul White

4:34 AM

@MichaelGreen Any public materials?

Michael Green

@PaulWhite tinyurl.com/y84l7aol

Paul White

@MichaelGreen Sweet thanks

Michael Green

The pdf is the preliminary talking notes. Then "tables and metadata" to show SQL Server's implementation. "OrgChart" to cover a well-known example in the new syntax and highlight some of the deficiencies. "Neo4j Org Chart" does the same topic in a more mature tool to show what one possible future may hold.

"Neo4j Movies" is their canned example DB with 10k or so nodes & edges. Good for playing Six Degrees of Kevin Bacon, and thinking how it would have to be done in standard SQL instead.

Joe Obbish

5:05 AM

dba.stackexchange.com/help/badges/238/sql-server-2016

do I get to feel special?

Michael Green

You are special, Joe.

Joe Obbish

even better!

McNets

7:03 AM

Mornin'

Andriy M

7:31 AM

Morning

Paul White

7:51 AM

🐮 ning

Andy K

morning

dezso

8:21 AM

@JoeObbish did anyone already JEAGL it to you?

(no desire to read the scintillating conversation up until this morning)

Buna dimineaţa otherwise (Romanian)

Paul White

8:39 AM

@dezso many times

dezso

@PaulWhite very well then, one less item on my ToDo list

Andy K

@dezso what is JEAGL?

McNets

@AndyK look at the description of this room

Andy K

@McNets thanks.

McNets

my pleasure

ypercubeᵀᴹ

9:53 AM

Weird. 4 downvotes on answers in this q: dba.stackexchange.com/questions/182331/…

David Spillett's got 2 down. I wonder why

Tom V

That doesn't really fit into my code above — K09 41 mins ago

"Don't explain how it works, just fix my code"

OTOH, all answers have downvotes

McNets

@ypercubeᵀᴹ And you got one, I'm still not sure what is the desired result.

ypercubeᵀᴹ

@TomV except the last one, it's (still) on 0/0. Whoever downvoted, hasn't seen it yet.

@TomV Yeah. "I can't be bothered reading and understanding. Fix my code."

McNets

Then it deserves a down-vote and a VtC as unclear.

ypercubeᵀᴹ

10:19 AM

Too much drama for a rather basic error.

Tom V

10:32 AM

13

Q: SQL Server clears plan cache and execution stats periodically

After upgrading SQL Server 2014 to 2016, the server keeps resetting cached execution plans and dm* views (like dm_exec_query_stats) etc. every couple of hours As if someone executes DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS manually (except for no one does, it happens automatically). The sam...

I think there really is something to it, we've had too many similar questions here and on so, usually well documented and I'm not sure the standard answer applies any more

maybe they're only zero-cost plans or something

McNets

10:54 AM

(\@P0 bigint,\@P1 nvarchar(4000),\@P2 datetime2,\@P3 datetime2,\@P4 nvarchar(4000),\@P5 datetime2,\@P6 float,\@P7 nvarchar(4000),\@P8 nvarchar(4000),\@P9 int,\@P10 nvarchar(4000),\@P11 nvarchar(4000))insert into recus (banque_id, created_by, created_date, date_recus, last_modified_by, last_modified_date, montant, numero, releve_id, sequence, ville, id) values (\@P0, \@P1, \@P2, \@P3, \@P4, \@P5, \@P6, \@P7, \@P8, \@P9, \@P10, \@P11) these are the values i get minus the \ because it wouldn't let me @ — IWonderHowLongANameICanTypeInH 2 mins ago

Could you decipher this comment?

Tom V

11:13 AM

@McNets I think he means it's working since he captured some insert statements?

ypercubeᵀᴹ

12:18 PM

yey → Trash

Paul White

@TomV I've heard multiple reports as well. I'm not sure anyone has been able to provide a stable repro though, so no idea what the progress is on any sort of fix.

Tom V

12:33 PM

I think I have witnessed it myself, some plans that I could no longer find in the cache while I expected them to, but I had no repro and no time to investigate them and chalked them up to a stats update or something

sp_BlitzErik

i've seen the plan cache clear out frequently when min and max memory have the same value set

but it's nearly impossible to repro

just happens ~sometimes~

Tom V

If it's not documented or reproducible I doubt we can get the details of the eviction implementation

McNets

1:01 PM

@sp_BlitzErik I can't set up an exemple on dbfiddle due a lack of permissions. dbfiddle.uk/…

sp_BlitzErik

ah, that's fun

but the dbcc command worked?

McNets

@sp_BlitzErik No if you use SP

Neither using a dynamic query. dbfiddle.uk/…

Tom V

1:57 PM

An answer from last November was just accepted :)

Paul White

A carefully considered acceptance

sp_BlitzErik

they probably realized that they get two points for accepting an answer

McNets

He has accept his own answer instead of the other with the bounty

Andriy M

@McNets Who did?

Or has

McNets

2:12 PM

@AndriyM I suppose this one:

3

Q: Two facts, two dimensions, one query, one cube, can it be done and how?

I have four tables, two Facts and two Dimensions. Below I've listed the relevant columns of these tables, and the results I would like to see in the Cube. Fact Order table ---------------- SK_Order FirstEverOrder Dim Order --------- SK_Order Fact Product Order ------------------ SK_Order SK_P...

ssas

Tom V

@McNets I didn't refer to that one but one of my own answers

McNets

@TomV sorry then

Tom V

@McNets funny though how that guy took 6 months to figure out my comment

But he didn't ping me in his reply and I forgot about the question I think

McNets

@TomV doesn't a comment on one f your answers ping you automatically?

Tom V

2:28 PM

No the one about the fact/dimensions

Why are you not storing that as a property on the order? There is no link between product and order so you don't know how many "first orders" there are for a product — Tom V Mar 9 '16 at 8:05

McNets

@TomV ok

Joe Obbish

2:43 PM

@dezso I'm still a lost little lamb

sp_BlitzErik

our delicate fawn has arrived

bees and butterflies flitting around his eyes~~

Joe Obbish

how come TABLESAMPLE scans aren't parallel against CCIs?

just clicked upvote on a connect item and timed it

25 seconds

dezso

3:14 PM

Lets start saying you have a SMALL table - nothing large here. Second - how are you going to query? The select speed is determined by the RIGHT index, and the right index by the queries. Your current index will help if you look by DATETIME and THEN (!) filter by x and y. Only. 3 seconds is plenty, btw.... on decent hardware I Would expect way way way below 1 second responses. And 500 inserts per second - I think I do 70.000 per second on my main insert application. Small. Really. THis is 2013, not 1990. — TomTom Dec 9 '13 at 21:13

So, you have a small SQL Server table (less than 50% of the RAM of a lower end database server these days). With low activity (50.000 inserts per 300 seconds is not that much). And use an oudated SQL Server version (which one: express, standard or enterprise). — TomTom Dec 8 '13 at 9:53

McNets

70,000 inserts per second?

dezso

TomTom

1 2 55 111

c# sql-server .net sql asp.net database sql-server-2008

in a longish ban currently, but I still have to find at least one phone-y comment

McNets

@dezso only banned on SO

Transcript for

The Heap™ – Consultancy ©®