The Heap™ – Consultancy ©® - 2015-01-08 (page 1 of 2)

mmarie

01:18

The Big Disconnect with Connect: spaghettidba.com/2015/01/07/the-big-disconnect-with-connect

2

ypercube

10:42

Happy New Year to all (back in work today ;)

What's with all the licencing questions these days? I voted to close for 5 or 6.

ConcernedOfTunbridgeWells

@swasheck Wait until they start doing a triple skinny mocha version.

Paul White

11:36

yesterday, by Master Database

0

Q: I propose we add a Vote-to-Close reason to handle licensing questions

I would propose the following wording, or some variation there-of: This question appears to be off-topic because it is about licensing. Questions on licensing should be referred to the vendor, and an answer obtained in writing from them.

discussion

Jack Douglas

11:47

0

Q: what could be the Best practices with very very large amount of data

I have a question on building a solution.I have a few txt/csv file having huge amount of data approx 30000000000 rows .By size they are 1.5 TB.The tables based on these data are only for lookup,I mean, there will be no update or insert or delete. So please guide me as how to plan and proceed. Sho...

sql sql-server sql-server-2014

^^^ I feel we need to close this, but I'm not sure what the best close reason would be

'too broad' perhaps?

One of the answers says "There really is no right answer to this question. There are too many variables regarding your environment and needs"

so maybe 'unclear what you are asking'?

0

A: what could be the Best practices with very very large amount of data

Can't you create a ssis package and transfer the data in increments? Have validation so you can roll back if needed too?

^^^ also, isn't this the start of an answer rather than a comment, despite the phrasing as a question?

Paul White

12:00

@JackDouglas Too broad I think.

@JackDouglas Probably. The reason I added the standard review comment was because the author clearly intended it as a comment.

Jack Douglas

@PaulWhite that is true

there is also a general perception that very short half-answers should be comments

Kermit

13:32

@JackDouglas perfect for vertica

ypercube

@Kermit Are there limitations on the RAM of a vertica install?

Kermit

@ypercube just limited by hardware

ypercube

14:34

Soemone is very confused about foreign keys and locks.

@ypercube Locks due to FKs are a very common issue (it's part of the tradeoff that FK's bring), and this approach to dealing with it is very common too, much like denormalising. It's done as a cleanup because that helps maintain code separation (avoids having one model deleting records from another's table), but it's not a bad idea to do as you suggest. — Synchro 10 mins ago

My guess it's they're using some ORM.

and that they probably mean "deadlocks" when they say "locks"

Kris Gruttemeyer

Maybe it's everyone's favorite, Entity framework

I know, personally, that's my favorite

............ :/

bluefeet

@ypercube can I ask you for some feedback on something?

ypercube

@bluefeet sure

MySQL and EF. That would be deadly combo ;)
And I've seen the results (@MarkStorey-Smith remembers)

bluefeet

@ypercube this relational division question is pretty much the canonical one on SO but it's tagged with mysql and postgresql. I had used it the other day to close and oracle version and I overruled because "the top answer won't work in oracle". Does that seem correct? Do you see any improvements we can make to the post to let this apply to all questions?

I'm asking you because you've got an answer on this

ypercube

@bluefeet What was considered the "top answer"? The accepted answer has 13 queries.

I'm pretty sure some of them work with Oracle.

bluefeet

14:44

@ypercube the 66 upvote one was referred to as the "top"

ypercube

Yes. And it has 13 queries. The ones with USING will not work in Oracle I suppose.

Perhaps we can convince Erwin to change the syntax to use ON so more queries work with other DBMS

like Oracle and SQL-Server

bluefeet

maybe, I'll ask him

I think it'd be good to use that for a canonical reference but I was shot down when I tried :(

ypercube

The fast queries (5 and 6) would work in Oracle as they are I think. Probably 4 as well.

bluefeet

I know they could be modified to be used in most DBs but I'm not sure if it is clear how to other users

ypercube

But editing the answer (and the question) to be more agnostic would help to convince others to use it.

bluefeet

14:49

I agree

I'm trying to figure out how to help with some of the dup situation

Aaron Bertrand

15:05

I'm glad we have lots of crappy questions and primarily good answers, rather than the other way around, but man are our questions getting worse.

bluefeet

@AaronBertrand welcome to the SO problem

just be glad that you have so many users who'll close instead of answer

Aaron Bertrand

@bluefeet and this is one of the main reasons I stopped answering there. 95% of the questions posted are dupes are so unclear they're impossible to answer.

bluefeet

@AaronBertrand but people still answer them.

Aaron Bertrand

@bluefeet of course. That isn't necessarily a good thing.

bluefeet

I recently had a lengthy discussion that if the answer was different it's not a dup

@AaronBertrand I totally agree

ypercube

15:10

@bluefeet with another mod?

bluefeet

@AaronBertrand which is partly why I'd like to create some good canonical questions on SO to use as a dup target

Aaron Bertrand

@bluefeet that's silly. If a question has 4 different answers then it should mutate into 4 different questions?

bluefeet

@ypercube no

a user

Aaron Bertrand

Well F the user, they don't get to dictate how closure works

bluefeet

I know I do it with PIVOT questions so that's bad as well

the SQL tag has been a struggle lately on SO

I can't keep up with all the bad stuff being answered

Kris Gruttemeyer

15:13

Nickel for every time a SO question could be solved with PIVOT.

bluefeet

So feel free to chime in a vote stuff :)

@KrisGruttemeyer I'd be a millionaire

Kris Gruttemeyer

@bluefeet amen to that, wouldn't have to work anymore, that's for sure

bluefeet

except to answer moire pivots occasionally

because I'm a glutton for punishment

Kris Gruttemeyer

HA! Very good point

ConcernedOfTunbridgeWells

@bluefeet You're the one who wanted to be a mod ;-}

Caption competition anyone?

billinkc

15:19

@ConcernedOfTunbridgeWells Step high, you don't want to get any SO on your feet

2

ConcernedOfTunbridgeWells

@billinkc I think I just stepped in some SO.

Mike Fal

Quick, quarantine him

bluefeet

@ConcernedOfTunbridgeWells Yup, sure did

billinkc

Perhaps distilled to: just stepped in SO feeling

ConcernedOfTunbridgeWells

Do not taunt happy fun mod.

billinkc

15:26

Oh lookie, an opportunity to use the VtC Licensing question on SO

This question appears to be off-topic because it is about licensing. Questions on licensing should be referred to the vendor, and an answer obtained in writing from them. Any answer received here is speculative and liable to open you, your organization and any product you derive from it open to lawsuits — billinkc 1 min ago

With my own added jibberish

Aaron Bertrand

@bluefeet for some things that's inevitable - complex things like PIVOT. If you point them to a duplicate they'll say "but I don't have a column x" and that sort of thing. So in a lot of those cases they need help solving a specific PIVOT problem, not a generic one.

On the other hand, how many duplicates of "A network-related or instance specific..." do we need?

bluefeet

@AaronBertrand I agree with that which is partly why if it is obviously a specific problem, I'll answer.

billinkc

@AaronBertrand What we really need is a tag for it. Mysql people get error-7230, we should get "a network-related or instance specific" tag and yes, I want it that long

bluefeet

But how many group by error questions do we need?

Or how to join tables?

There are pretty good questions that can be used as a dup target - if we can find them

Aaron Bertrand

I do confess that I often answered questions because it was easier/lazier than finding the dupe. And finding the right dupe is no picnic even when I'm not being lazy. Especially since many questions are closed as dupes where the answers are no longer the best answers possible on more modern versions (or maybe the accepted answer has actually been terrible all along).

billinkc

15:29

0

Q: SSIS C# Script error

I'm trying to take in ssis 2010 a big image, change the size, and set the byte[] result into a column. Please see the picture below (sorry for the low quality). What am I doing wrong?

c# .net ssis

Aaron Bertrand

@bluefeet yeah there is definitely some subjectivity there on when a query is difficult enough to justify a targeted answer.

bluefeet

Like the concatenating multiple rows into a single one - I've got a canonical question that I use for that.

billinkc

Oh my, a photograph of your screen instead of actually capturing it via printscreen

swasheck

@billinkc everything

Aaron Bertrand

I think it is much easier to make that case for PIVOT than for most of the other drivel.

bluefeet

15:30

@AaronBertrand finding them is hard - but when we do find them we should attempt to keep them up to date so they can be used as a dup target, you know?

swasheck

i thought that was the purpose of a "canonical answer." shouldnt those be made more prominent somehow?

or is that just theory

Aaron Bertrand

@bluefeet meta tag for canonical

bluefeet

@swasheck there isn't an easy way to find these

@AaronBertrand maybe

swasheck

yeah. what @AaronBertrand said

bluefeet

The PHP folks have it down

swasheck

15:32

that's because there are billions of them and the spawn like rabbits

bluefeet

@swasheck and they have a group that actively works to keep the tag clean

we don't have that for the sql tag

billinkc

But we have Gordon

bluefeet

no comment

swasheck

and jobs

bluefeet

@swasheck yup, I know

Kris Gruttemeyer

15:35

@swasheck that's the truth

billinkc

Close this turd please

-1

Q: New Package creation in SQL Server 2012

I have some DTS packages in SQL Server 2000. I know that DTS packages does not support in SQL Server 2012. But, how can I create a new package or equivalent stuff in SQL Server 2012 similar to SQL 2000 DTS?

ssis sql-server-2012 microsoft etl dts

Kris Gruttemeyer

That one is just all kinds of awesome

billinkc

I wonder if this person stackoverflow.com/q/27836318/181965 is trying to get an answer so they can apply it to the bounty stackoverflow.com/q/27622031/181965

Mike Fal

Oh booo

Comments can't contain Let Me Google That For You links?

bluefeet

@billinkc probably

Kris Gruttemeyer

15:40

You can, at least the shortened version

Jack Douglas

@MaxVernon I think it's a bit early to say - as things stand I'd interpret that as "let the community decide case by case".

swasheck

@MikeFal gfylmgtfy

Max Vernon

@JackDouglas I still think having a VtC reason that handles it properly would be a Good Thing™. If a particular question is easily answerable and doesn't put anyone in jeopardy by answering it, then the question likely would not be closed.

Jack Douglas

@MaxVernon having a close reason would strongly imply that they are always off-topic

otherwise I'd agree

billinkc

So then Licensing is addressed under VtC, Too Localised?

> not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users

At least for bespoke licenses

Max Vernon

15:48

for me, the close reasons aren't why we close something. They are used when I look at a question and think, man that stinks. How can I make this go away.

and prevent DBA.SE from turning into a steaming pile of carp.

Jack Douglas

close everything that stinks

custom close reason "this stinks"

Max Vernon

"I can smell it from here"

billinkc

Score the bones and then cook the carp in a pressure cooker, takes care of bone issues

Jack Douglas

tbh more and more I'm just hitting the 'delete' button

Aaron Bertrand

Maybe the close reason could explicitly state something like "not all licensing questions are off-topic, but this one is! Neener-neener!"

3

Jack Douglas

15:50

that would be perfect

Aaron Bertrand

"I don't always close licensing questions, but when I do, I use THIS hammer"

Jack Douglas

does anyone think it'd be useful to have SQLFiddle-like functionality built into DBA.SE?

I'm working on something for postgres

will post on meta when it's done...

Aaron Bertrand

@JackDouglas I think an easy way to upload attachments would be higher priority than a fiddle replacement

3

bluefeet

@JackDouglas I'd be worried about that - mainly because if it is found by SO users, DBA might get an influx of SO like questions

@AaronBertrand can you edit this comment to add "sure" after pretty - dba.stackexchange.com/questions/88855/…

Aaron Bertrand

Licensing questions aren't always off-topic, but licensing questions that are off-topic are always off-topic, if you follow me. A lot of problems are "too localized" but they're not closed as such because they're still interesting to solve. I think a "Some licensing questions are off-topic" close reason, with a link to the meta discussion(s) about it, would be sufficient.

@bluefeet Like this?

I'm pretty! Sure they are called User-Defined Variables and it's outlined in the docs. — bluefeet 7 mins ago

bluefeet

15:57

@AaronBertrand nice

and thanks :)

Aaron Bertrand

People are idiots

If it's an option, I believe you can stop the SQL service, rename the .mdf file and start the service. The database would show up as Recovery Pending and you can delete it then. — ZabadakGalorex 2 mins ago

Jack Douglas

@AaronBertrand Out of curiosity, what kind of attachments?

billinkc

Emotional

ConcernedOfTunbridgeWells

@JackDouglas Code snippets and suchlike.

Something that can't get blocked by a firewall independently of SE.

Jack Douglas

@ConcernedOfTunbridgeWells like pastebin you mean?

theregister.co.uk/2015/01/08/…

ConcernedOfTunbridgeWells

16:02

@JackDouglas Yes, but one that lives under the radar so it doesn't wind up on blacklists.

Jack Douglas

@ConcernedOfTunbridgeWells not any more :)

ConcernedOfTunbridgeWells

Something living under the SE/SO domain

billinkc

warez.se.com

Kermit

@billinkc pssh.. i use napster

billinkc

thepiratebay.se

That wouldn't get nipped at all

Jack Douglas

16:05

SO have this: blog.stackoverflow.com/2014/09/…

Aaron Bertrand

@JackDouglas execution plans. We ask for them all the time, and it's always a pain because they have to upload them to some external site. And often they choose sites which require consumers of those attachments to register.

Kermit

@JackDouglas can this block the news feed popup

Aaron Bertrand

@JackDouglas that is much more like fiddle than attachments

Jack Douglas

@AaronBertrand true but it's the same principle of bringing functions under the SE wing

no reason they couldn't do the same with pastebin

billinkc

Plan Explorer SE version, allows for upload to dba.se

Aaron Bertrand

16:08

Fiddle isn't perfect but doesn't it already solve that problem (except for the not being on our domain part, which SE could fix if they want to)?

@billinkc I'm all for that, actually, but they need a place to put them on their side, first.

billinkc

Just busting your chops. No need to fragment the product or draw traffic away from sqlperformant.com

Kermit

@JackDouglas i want to advertise dba.se on my vertica sites. is there a button or small image readily available for it?

Jack Douglas

@AaronBertrand Yes, it does, but I guess the logic of 'Stack Snippets' versus JSFiddle is that it reduces friction

Aaron Bertrand

Honestly, we don't need 40 different places to put code (or even running code).

swasheck

@JackDouglas except there are many who are behind corporate firewalls. i can't access google drive, dropbox, sky..ahem...onedrive, pastebin, etc. i have SE/SO, github, and box.com

Jack Douglas

16:10

@Kermit 'your' vertica sites?

@swasheck probably a good thing that you can't access onedrive that thing drives me nuts

Kermit

@JackDouglas vertica.tips, vertica.help

swasheck

vertica's tips don't lie

billinkc

Normally I'd agree with you on onedrive with their direct-link-not-really-direct thing but since I had pushed all of my onenotes onto it and this client blocks it, I'd do unseemly things with a host of barnyard animals to tear down this firewall

swasheck

@Shawn no, he's saying you need to spell sqlsever correctly. — Aaron Bertrand ♦ 1 min ago

billinkc

vertica.tips tip of the day: try wearing your hair swept up to create a leaner appearance

2

swasheck

16:14

@JackDouglas i'm just noting all of the things that are blocked here that could be useful for hosting/sharing stuff

Jack Douglas

@swasheck I know :)

is gist allowed?

swasheck

yes

(github)

Jack Douglas

Let Me Sever Fault That For You

Is 'Sever' = 'Server' in some language?

swasheck

@JackDouglas lazytype

Jack Douglas

twice though?

Kris Gruttemeyer

16:20

Thought that was harry potter for a quick second

swasheck

@JackDouglas extra lazy

@KrisGruttemeyer eh?

ypercube

Does MS-Access allow subqueries to be used in FROM, like this?: FROM ... JOIN (subquery) AS a ON a...

I thought it did but I can't test now.

Kris Gruttemeyer

@swasheck The user that jack posted

swasheck

ah

Shawn Melton

dba.stackexchange.com/revisions/82674/2

"eyes bleeding"???

Aaron Bertrand

16:28

Seems a little harsh

billinkc

Omg, omg, omg, check out the "edit"

0

Q: SSIS C# Script error

I'm trying to take in ssis 2010 a big image, change the size, and set the byte[] result into a column. Please see the picture below (sorry for the low quality). What am I doing wrong?

c# .net ssis

John M

I'm a little OCD about walls of text :/

Aaron Bertrand

But I can't stand lazy capitalization either

John M

Aye, that makes it worse

JNK

@billinkc lol

That reminds me of a way my dad set his wallpaper on his phone

He couldn't figure out how to save a picture from the web so he took a picture with his phone of the picture on his laptop monitor and used that

Aaron Bertrand

16:30

@billinkc wtf

billinkc

If that person's trolling, by god I award them 2 full internets

bluefeet

@billinkc closed

billinkc

I favorited it so I can always click back and laugh

swasheck

17:02

@AaronBertrand sorry

Aaron Bertrand

@swasheck oh in text, chat etc. I don't give a sh*t, but if you're formulating proper paragraphs and asking for help, find your shift key

Zane

Yarg the indexing in this database is poop. There are so many full table scans in this eff query.

Kris Gruttemeyer

Seriously, this entity framework mess is going to make me old, rapidly.

Any of you guys have any experience with troubleshooting performance with this garbage?

I can barely even interpret this horsesh*t

swasheck

exactly

Kris Gruttemeyer

He told me "you can get the execution plan to get the table names"

I almost threw him out and punched a kitten

JNK

17:11

@KrisGruttemeyer YIKES

Kris Gruttemeyer

Let me pastebin this, it's just got magic leprechauns and unicorns all over it

pastebin.com/DMZS7AZW

swasheck

@KrisGruttemeyer you can ... sure maybe. (stalling, waiting for an @AaronBertrand analogy to appear)

@KrisGruttemeyer blocked. and you're now on the cia blacklist

Kris Gruttemeyer

@swasheck You can't see it?

Aaron Bertrand

I can also tell what kind of car you're driving if I let you run me over and then analyze the tread marks.

3

Kris Gruttemeyer

And there it is :)

swasheck

17:14

@KrisGruttemeyer no. i work for a fear-based security policy-implementing company

Aaron Bertrand

@KrisGruttemeyer some people's gestapo IT departments block sites like pastebin

Kris Gruttemeyer

@AaronBertrand Ah gotcha

Aaron Bertrand

Wouldn't want a competent user, you know, catching ebola or anything

swasheck

@AaronBertrand you've never failed me

Kris Gruttemeyer

HA! Just to throw in a little extra credit for this query, the [Message] column, yeah it's varchar(max) and stores RIDICULOUSLY long amounts of data. I think the max length is somewhere near 69k characters

swasheck

17:18

does message need nvarchar?

Kris Gruttemeyer

varchar, not nvarchar

billinkc

This Websense category is filtered: Personal Network Storage and Backup.

swasheck

URL: pastebin.com/DMZS7AZW

Category: online-storage-and-backup

billinkc

What net nanny program do you get to deal with?

swasheck

dunno. doesnt announce itself

#2, baby

huffingtonpost.com/2014/08/28/…

billinkc

17:27

Looks like Cisco Ironport

JNK

17:57

@KrisGruttemeyer that's from EF?

Kris Gruttemeyer

@JNK As i understand it, yes.

JNK

yikes

billinkc

"Hey, want to attend a brown bag lunch on Apache Spark" Sure, always happy to learn more. (receive invite) Ohhh, it's lunch time in Belgium which means 6 a.m. here

JNK

So get a biscuit or whatever you eat for breakfast in Kansas City

swasheck

late enough for a Tripel

Kris Gruttemeyer

18:01

Yikes, indeed. "It ran fine 3 months ago". Yes, and your DB wasnt 16GB with a varchar(max) column that, right now has 679k as its max length (I thought it was ~60 earlier, coffee hadnt take effect yet)

billinkc

At 6 a.m. I am still tucked into my bed

Hell, on a good/bad day, I'm still tucked in bed at 7 a.m.

Jack Douglas

"I've a fair idea about storing the day of the week by using 7 bit boolean value"

oh dear

swasheck

msdb could really use a rethinking

billinkc

Does postgres not have the concept of a date?

Jack Douglas

@billinkc it's not that

he need another table I think

it's SO fodder really

billinkc

18:06

not even sure then, seems to opinionated for me. Well, at least my opinion is that this is going to end badly regardless of the approach taken for their problem..

Kris Gruttemeyer

"fair idea"

ypercube

@JackDouglas I noticed that ;)

Kris Gruttemeyer

This was one of my recent favorites:

1

Q: Check if database has changed to determine need to perform backup

I want to check SQL server database is modified or not. Reason to check database is modified is for backup(sql database backup) purpose. So I will be doing a full backup first and if any data inserted, modified or any structural or any changes done then I have to identify that there is some chang...

sql-server sql database-administration

bluefeet

does he expect a lecture?

0

Q: Understanding Database Table Normalization

I am new to Databases. Can someone, please, give me a clear and concise understanding of DB table normalization? I keep hearing and reading that tables should not have redundant data. But is it not possible to have a table named "Customers" and a field named "State" and have 'Michigan' repeated f...

database ms-access table

Lamak

Is this SQL...for ants?

0

Q: sql script execution fails when called using ANT SQL task

There is a SQL file which contains some Transact SQL statements in it and some plain table queries as follows: IF NOT EXISTS (SELECT * FROM [dbo].[SYSTEM_PROPERTIES] WHERE SYS_PROP = 'ABC') BEGIN DECLARE @SYS_PROP_ID INT; INSERT INTO SYSTEM_PROPERTIES (...,....,...) values ('...','...','...'); S...

sql sql-server tsql ant

bluefeet

18:18

transnational?

0

Q: Transnational replication

I am getting below error while initializing push transnational replication using backup. Command attempted: if @@trancount > 0 rollback tran (Transaction sequence number: 0x00093D2B00006137000100000003, Command ID: 1) Error messages: Cannot update identity column 'id'. (Source: MSS...

transactional-replication

boo @MaxVernon

Max Vernon

@bluefeet sorry. I couldn't help myself.

bluefeet

transnational was funnier

Max Vernon

I figured I was about to get schooled about how to perform transactional replication outside of the country; but alas no.

@bluefeet it certainly was.

Lamak

damn you @JNK

bluefeet

@Lamak what'd he do?

Lamak

18:23

@bluefeet he commented a couple of seconds before me, with a similar explanation

(in the question about normalizing tables)

bluefeet

y'all are too nice. I just closed it

Lamak

the guy just seems so...clueless, it made me uncomfortable

billinkc

@Lamak ANT, another neat tool, was a build thing I used back in 2000 for making builds in java. I spent far too much energy helping on their email list

Lamak

never heard of it

ypercube

@Lamak in case you didn't notice, Another Neat Tool

Lamak

18:29

ooooh, mind. blown

Mohammad

Hi

I am preparing for the TOEFL test, so please some advise

billinkc

Find another channel is advice #1

Not to be mean, just that we generally converse about database related topics in this chat

JNK

@Lamak sorry!

Paul White

@Mohammad Try the English Language Learners stack exchange site

Kris Gruttemeyer

18:48

@Paul

Paul White

@Kris

Kris Gruttemeyer

@PaulWhite Thanks for adding that to my answer, didn't think about that

Paul White

No worries.

Lamak

@JNK ok, just this time

JNK

@Lamak Next time I'll post "Actually, you can...hang on, Lamak did you want to take this one?"

and hope you comment

Lamak

18:50

that's the right thing to do, thanks man

Zane

19:17

@AaronBertrand question about your GUID article when you get a free moment.

Aaron Bertrand

Surely

twitter.com/shawndumas/status/552929341126893569/photo/1

Zane

Shouldn't the large volume of page splits in the non-sequential guid lead to a more significant jump in the amount of pages in the buffer pool?

Shawn Melton

It is always interesting responses you get from folks when you turn in your resignation (or tell them you are).

swasheck

@Zane no

billinkc

@ShawnMelton Grats!

Zane

19:28

@swasheck care to elaborate?

Shawn Melton

@billinkc Thanks.

swasheck

@Zane yes

Zane

Good grief you sure are helpful.

swasheck

yes

oh sweet!! thanks google play. yellow bedwetter just spun up

Zane

??

JNK

19:31

best unintelligible song ever

ah say, I dona wanna say ah on ah box or on a bag

swasheck

@Zane in theory what you'd see is a drop in PLE. you'd like to think that your BP is filled with pages already, right? with all the splits you'd keep (roughly) the same number of pages in memory but they'd get flushed to disk and new pages would come in. i think that sounds right

Mike Fal

I don't listen to songs with lyrics. </snob>

swasheck

►

JNK

@MikeFal then you'd like Yellow Ledbetter

bluefeet

awesome explanation

-1

A: Why pivot with "extra" columns doesn't combine results

The reason is your identity column Try this..... SELECT Fishtype , [3] AS [3 Years Old] , [4] AS [4 Years Old] , [5] AS [5 Years Old] FROM (SELECT Fishtype, AvgLength, FishAge_Years FROM dbo.AverageFishLength)t PIVOT( SUM(AvgLength) FOR Fis...

swasheck

19:35

@JNK so many youtubes with so many versions

the only thing you can make out is "make me cry (now)" before the solo

Zane

@swasheck I was talking about the amount of pages that go into the buffer pool when queried.

JNK

I mean you can make out some stuff but it's rare

@Zane that affects PLE though not buffer pool size

swasheck

jess

JNK

b/c your buffer pool is gonna be maxed anyways if your server is getting used

Aaron Bertrand

@Zane I thought that the impact would be larger too, however I suspect that a lot of the page splits happened in the middle 70-80% of the workload, and toward the end those pages just finished getting filled up (not every insert leads to a page split, unless you're constantly correcting fillfactor by defragging after every insert). Still, I am surprised that the clustered index was fragmented 99% but the buffer pool usage was only 10% higher.

JNK

19:36

You have umpteen jigawatts of pages in memory regardless

swasheck

@JNK depends of the version

Aaron Bertrand

@JNK in my case it wasn't - I'm the only one on there and I restarted SQL Server between tests

JNK

@AaronBertrand oh this is in reference to your blog post

Aaron Bertrand

right

JNK

With all those graphs that stayed roughly the same size for all the tests

I found that one surprising

Zane

19:38

@JNK Yeah. I was referring to this specific scenario. It's why I was asking about the increase in pages and not the PLE.

Aaron Bertrand

SQL Server works in mysterious ways. I also found it surprising that BIGINT required such a small amount of extra space, even without compression. I guess it's all relative - in a two-int-column table it would have much more impact.

@Zane well I didn't even measure PLE

Zane

I know. Seth brought that up.

swasheck

where were you when we were getting high ... ahhh 90s. also, screw you google for putting mariah carey in there

Aaron Bertrand

@Zane sorry I'm kind of half-following

(two webinars back-to-back and I have to go catch a train to Boston)

Zane

It's cool homie. you answered my question.

swasheck

19:49

@AaronBertrand @Zane was talking about whether or not guids as a PK would inflate the number of pages in the buffer pool because of all of the splits. i didnt realize it was in response to your blog post and i generically said that it'd drop PLE because of the number of pages getting flushed out and read back in.

Aaron Bertrand

only if you're really using all of your memory. Mine was a local test and everything fit into memory, so no flushing happened as far as I can tell.

I realize that is a severe limitation in my test - I do plan to do more where memory is actually an issue

swasheck

right. we arrived at that once it became clear he was referring to your post and not "in general"

Aaron Bertrand

None of these posts are ever one-parters. But if I tried to cram all variables into one post it would take a year to write and nobody would ever read it all.

swasheck

exactly

Zane

I'm very interested in seeing where these continued tests go.

swasheck

19:57

@Zane you missed the 90s man

Zane

I did not.

<----86

Not to mention I live in a hipster part of Minneapolis. It's as if the 90's never ended.

Transcript for

The Heap™ – Consultancy ©®