The Heap™ – Consultancy ©®

swasheck

12:19 AM

Yeah. I started with lynx

James Lupolt

1:06 AM

@KrisGruttemeyer Really? I don't have any special access. I'm just another prole.

Aaron Bertrand

1:32 AM

Trying to copy 900,000 rows from an SSMS grid to a spreadsheet

0

Q: System.OutOfMemoryException SQL Server 2012 and 2008R2

I have a 64-bit and 64gb memory for both SQL Server 2012 and 2008R2, when I generate a report w/c has 900K rows, it finished the query smoothly and no problem, but when I'm going to copy the result with header. The error System.OutOfMemoryException is prompting. What do you think causes this prob...

sigh

Thank you @Aaron but our customers demand is to do it using copy with header :( But I'll suggest that. Thank you! — spideynok 31 secs ago

Does your customer even sql bro?

swasheck

2:42 AM

if only microsoft packaged a free reporting tool that allowed users to save/export query results as csv/text/excel. perhaps one of these days they'll see the light. i'd suggest that they name it "sql server reporting services" so that people could clearly identify its function — swasheck 14 secs ago

James Lupolt

7:46 AM

So it sounds like the TF 4199 optimizer fixes are going to be on by default in 2016

James Lupolt

8:01 AM

And Books Online now has the word "automagically" in it.

5

Andriy M

@JamesLupolt Seriously? Where?

or do you mean, in user comments?

James Lupolt

@AndriyM No I mean in the MS-written docs. It's here: msdn.microsoft.com/en-us/library/…

Scroll down to 'temporal tables'

I'm pretty sure it will get updated before the release. They probably don't want to encourage people to think SQL Server is made of magic

Andriy M

Ah, very nice.

James Lupolt

Maybe it was even a typo?

Andriy M

Could be, the two letters are very close. I wonder if the word itself first came into existence because someone simply made a typo.

swasheck

8:21 AM

> Since columnstore indexes operate on a copy of the data, queries against the rowstore and the columnstore can run at the same time with minimal impact on performance.

soooo ... just out of curiosity ... does this open the door to having a clustered columnstore and a clustered rowstore index, or am i pressing it too far?

Paul White

12:21 PM

@swasheck No.

James Lupolt

12:36 PM

@PaulWhite Does that mean it doesn't open the door, and he isn't pressing too far?

(Bad logic joke)

Paul White

Neither, both, and either.

ypercube

@AndriyM I doubt it.

Andriy M

@ypercube You may be right. Still, I find it remarkable that the differing letters actually sit next to each other on the keyboard (never realised that before).

ypercube

12:51 PM

dourish.com/goodies/jargon.html

> AUTOMAGICALLY adv. Automatically, but in a way which, for some reason (typically because it is too complicated, or too ugly, or perhaps even too trivial), I don't feel like explaining to you. See MAGIC. Example: Some programs which produce XGP output files spool them automagically.

Kin

1:51 PM

Hello All

simply open a new query in SSMS?? how. – Jas 1 hour ago

should this be closed ? dba.stackexchange.com/q/102667/8783

Andriy M

2:04 PM

tip of the iceberg, I think

Unless they want to be more elaborate on what they are doing

swasheck

2:59 PM

OH:
"you're german, right?"
"i'm german, lithuanian, ... oh ... and irish."

bluefeet

@swasheck so a mutt?

swasheck

aren't we all?

bluefeet

yes

that mix above sounds like me except for the german

but I've got the other 2

swasheck

i'm polish-swede-german

bluefeet

I've also got others

ypercube

3:13 PM

You are confusing me.

I thought you guys were all Americans.

(and some of you Canadians)

Mark Sinkinson

(and British) ?

swasheck

@ypercube I'm Afraid of Americans

ypercube

@MarkSinkinson I meant the gang from the other side of the Atlantic (swasheck, bluefeet, JNK, jcolebrand, Aaron, Kermit, ...)

and Lamak. Sorry if I forgot anyone. (hm, Maxvernon, mmarie, @billinkc ...)

Paul White

We're all Heapians.

4

swasheck

@ypercube we're arrogant mutts

billinkc

3:17 PM

Estamos Heapamos

Lamak

@billinkc "Somos"

billinkc

Estamos somos just sounds weird. Maybe you need a refresher in Espanol

Lamak

I see you were improving your sense of humor

ypercube

3:39 PM

I think this is inefficient - to say the least - but I'll wait from our heavy Postgres users (Erwin, Craig) to comment:

1

A: pgsql select distinct on multiple columns

You can, but as I wrote and tested the function I felt wrong. It is a resources waste. Just please use a union and more select. Only advantage (if it is), one single scan from main table. CREATE OR REPLACE FUNCTION fn_readuniqu() RETURNS SETOF text AS $BODY$ DECLARE a_array text[]; ...

I can't see why array_agg() first, then unnest(). Nor why they need the subquery and the DISTINCT.

@dezso is this valid array_agg(distinct a) ?

Kin

3:58 PM

@ypercube Not me :-)

ypercube

@Kin I wasn't sure for you. In the UK then?

Jack Douglas

@ypercube yes, thanks for the tip ;)

Kin

@ypercube I am in US .. but not a citizen ..

Jack Douglas

@ypercube having a function at all is bonkers

ypercube

@JackDouglas is || doing array concatenation as well?

Lamak

4:01 PM

citizen @Kin

Kin

@Lamak India

Jack Douglas

@ypercube yes

only the outer distinct matters for the result, but the distincts in the aggregates could affect how much memory gets used

Lamak

@Kin I was just trying to reference "citizen kane"

ypercube

@JackDouglas This answer stackoverflow.com/questions/3994556/… says that there is a intarray module with uniq(int[]) function. But we don't know the type of the array.

swasheck

@Lamak fwoosh

Lamak

4:11 PM

Hey, I'm not that versed in pop culture yet....just you wait

Jack Douglas

@ypercube you could write a function with an anyarray type :)

Jack Douglas

4:31 PM

@ypercube @AndriyM is a clever chap

you could use a regular cross join without the lateral too but his way is far more concise

Paul White

PG doesn't have unpivot?

bluefeet

> I'd post, but its a long butt procedure

ypercube

@PaulWhite No

It has unnest() and crosstab()` that be used instead

Paul White

Thanks. Also, why is the simple UNION answer not an answer at this point? It is in a comment.

ypercube

I'm lazy

Paul White

4:36 PM

Aha

Oh go on post it. I'll upvote it. You'll never catch me otherwise. ;)

@bluefeet The mind boggles.

bluefeet

@PaulWhite I voted to close it as unclear

there are far too many possibilities without some code

swasheck

@bluefeet clearly

Paul White

Agreed.

Please reward @ypercube's lack of laziness. Seems the simplest answer to me.

2

A: pgsql select distinct on multiple columns

A very simple query would be to use UNION DISTINCT. I think it would be most efficient if there is a separate index on each of the four columns: SELECT a AS abcd FROM tablename UNION -- means UNION DISTINCT SELECT b FROM tablename UNION SELECT c FROM tablename UNION...

Jack Douglas

@ypercube out of curiosity, if there is a big index and only a few distinct values, will pg always scan the whole index anyway?

Andriy M

@JackDouglas Hey thanks!

Paul White

4:42 PM

Or skip-scan it

The lack of apparently-simple optimizations for distinct values in database engines confuses me.

Jack Douglas

@PaulWhite with recursive doesn't qualify as a simple workaround in my book :)

Paul White

e.g. even DISTINCT col on a columnstore index scans all rows performing a separate hash distinct in SQL Server. You'd think distinct values would be easy to access directly at the storage level.

@JackDouglas I agree

Jack Douglas

wow, yes, that makes it even harder

Paul White

A source of some sadness :)

Kin

Why is sql server 2016 referring to 2015 compatibility mode .. which is 130 ? Did MS just forgot to change this ?

Paul White

4:52 PM

@Kin Yes. There are a few references to 2015 in various places. Often happens in CTPs.

Aaron Bertrand

@Kin Yes, that happens in every version. SQL Server 2012 had SQL Server 2011 in that box for much of the beta cycle.

swasheck

i cant use it because ...

windows 7

Paul White

@swasheck Windows 7 supports VMs :)

Kin

thanks @PaulWhite and @AaronBertrand .. good to know

Paul White

@swasheck You could also spin up an Azure VM. There's a template for CTP 2 apparently.

swasheck

4:55 PM

@PaulWhite yes. but the organization now forcefully removes virt. software upon installation

@PaulWhite ah. that may be more of an option

Paul White

I can't help you with your broken workplace :)

Well, I can, but only if you move to Auckland.

swasheck

heh. that's very expensive. but i've considered it

Paul White

You couldn't pay me enough to work in Auckland.

Traffic, in a word.

ypercube

Traggic

swasheck

@PaulWhite apparently it's the worst in this place called, "australasia"

Zane

5:09 PM

Hmmm google IO switching to talking about watches probably time to get some lunch.

Until a smartwatch can shoot lazers I'm just not interested.

2

swasheck

5:25 PM

bond. james bond.

bluefeet

oh boy

-1

Q: Parameter Sniffing Question

SQL Server 2008 R2 Enterprise 32 cpu's & 128gb ram. I know this is going to be somewhat vague, but I've been pulling my hair out. Our procedure pulls news type data. Depending on the customer, you can have none to thousands of news articles available to you. For the most part, the query re...

sql-server-2008-r2 stored-procedures

swasheck

@bluefeet VtC ... wall of code ;)

ypercube

Can you cut the code down to something that could be posted? Otherwise we'd be guessing. — bluefeet 55 mins ago

I guess they misunderstood the "cut down".

swasheck

@PaulWhite ... are you still around?

Paul White

Yes

swasheck

5:38 PM

awesome. i have a question that will betray my ignorance so please dont laugh too hard

i'm still fooling around with low priority waits

so i kick off an index rebuild

alter index ix_tmp_numbers_num on dbo.numbers
rebuild with (
		data_compression = page,
		online = on ( wait_at_low_priority ( max_duration = 0 minutes,
											 abort_after_wait = blockers ))
		)

(oof. formatting)

and then, in a separate session, i kick off an insert with recursive CTEs to add to the numbers table

WITH    Pass0 AS ( SELECT   1 AS C UNION ALL SELECT   1),
	Pass1 AS ( SELECT   1 AS C FROM     Pass0 AS A , Pass0 AS B),
	Pass2 AS ( SELECT   1 AS C FROM     Pass1 AS A , Pass1 AS B),
	Pass3 AS ( SELECT   1 AS C FROM     Pass2 AS A , Pass2 AS B),
	Pass4 AS ( SELECT   1 AS C FROM     Pass3 AS A , Pass3 AS B),
	Pass5 AS ( SELECT   1 AS C FROM     Pass4 AS A , Pass4 AS B),
	tally AS ( SELECT   row_number() OVER ( Order BY C ) AS N FROM Pass5 )
insert numbers
select *
FROM    tally
WHERE   N <= 1000000

when i did this in the opposite order, i had one kill/rollback on the insert

when i do it in this order, i have 2 rollbacks with one process kill event

does the insert get resubmitted after the first rollback?

Paul White

No I don't think so. Does the insert generate a parallel plan? Do you see the same with MAXDOP 1?

Mark Storey-Smith

gday folks

Paul White

Kia ora.

Mark Storey-Smith

@bluefeet Is the linkedin request I got a few days ago from you?

Aaron Bertrand

@bluefeet pagination and kitchen sink. I would just use dynamic SQL with OPTION (RECOMPILE) and be done with it. There's no way you can optimize that "long butt procedure" for all combinations and values.

bluefeet

5:44 PM

@MarkStorey-Smith yes :)

swasheck

@PaulWhite serial plan ... but looking through more data ... the first statement is started with a "normal" state, then comes a rollback, then the statement is started again with a state of "Recompiled" ... neither statement event has an actual event fire for completion

Mark Storey-Smith

@bluefeet done!

swasheck

same activity id

Paul White

@swasheck The recompile could be triggered by the cached plan for the insert (assuming you ran it before) being invalidated by the metadata version change caused by the index rebuild, or for some other reason. I don't think there's anything particularly strange going on there.

bluefeet

@AaronBertrand they shoe-horned everything into that proc

Paul White

5:48 PM

I wrote this huge complex thing and occasionally it does odd stuff. VtC.

I almost bought a Surface.

swasheck

@PaulWhite cool. i figured it was something explicable, just not within my capabilities

Zane

New google photo's seems badass.

Paul White

There should be a recompilation reason you can capture. There was in Profiler.

Not always helpful, but worth checking.

Depending on how much you care.

swasheck

ah yes. thanks.

Paul White

Going to sleep on the Surface purchase. Catch you all later.

Mark Storey-Smith

5:51 PM

@PaulWhite I've got one. It's a bit "meh"

Paul White

@MarkStorey-Smith 3 Pro?

Mark Storey-Smith

yip

Paul White

@MarkStorey-Smith 512 GB / i7?

Mark Storey-Smith

Bought it for travelling/sofa

Paul White

How so "meh"?

Mark Storey-Smith

5:52 PM

Got an i5 as it wasn't going to be used for work work

keyboard trackpad is an annoyance

Paul White

I'm looking to replace my laptop completely. Fed up with size/weight.

Zane

Replace with a surface?

Mark Storey-Smith

In reviews it gets good press but I find it difficult to use. Every click nudges the pointer

Paul White

@MarkStorey-Smith Pro Type Cover?

Mark Storey-Smith

yup

Paul White

5:53 PM

@Zane Yes

@MarkStorey-Smith Hmm. Thanks.

Zane

Why not just pick up a dell m3800

Mark Storey-Smith

Could be I'm a heavy handed fool of course

Paul White

@MarkStorey-Smith Anything's possible :)

swasheck

@MarkStorey-Smith all thumbs, this one is

Paul White

@Zane I'm a bit off the Dells.

Mark Storey-Smith

5:54 PM

I'd definitely get your hands on one before you commit to it

Zane

Gotcha.

My pops got one instead of a surface and it's pretty super fly

Paul White

@MarkStorey-Smith Yes maybe I'll pop down to Harvey Norman or Noel Leeming tomorrow.

@Zane I'll have a look, thanks.

Zane

Yeah it's expensive, but it's lightweight, super fast, and stays cool.

Mark Storey-Smith

Docking station is comically over priced also and doesn't do dual external monitors. You have to use docking station plus a dongle.

swasheck

recompile_cause Schema changed

Paul White

6:14 PM

Oh so it was the metadata version bump.

swasheck

6:58 PM

2

Zane

@swasheck image not found.

swasheck

@Zane "loan-sharking, extortion, and racketeering are fine for now, but someday I'd like to get into soccer"

Zane

Anyone have a good recommendation for picking up a suit?

swasheck

goodwill

mmarie

@Zane What are you suiting up for?

Zane

7:13 PM

Wedding this weekend.

bluefeet

@Zane kinda last minute, huh?

Zane

I forgot I don't have a suit anymore.

At least one that fits since I became a fat tub.

bluefeet

slacker

Zane

I always like to wing things last minute.

swasheck

it's the only way to be sure that it's going to fit when you need it

Zane

7:16 PM

@swasheck Exactly!

swasheck

soooo ... goodwill

Zane

Never had good luck with that.

bluefeet

do you have any consignment shops near you?

Zane

I'm looking that word up at the moment.

Oh yeah we have tons of those.

I was just going to buy a new one.

bluefeet

oh ok, then Macy's they always have coupons

they are my go to for last minute

swasheck

7:31 PM

@Zane do you have a Kohl's?

Zane

My issue with them isn't that they are expensive. It's more that they are more expensive than the same thing elsewhere.

@swasheck we do.

swasheck

I've found Kohl's to have good prices. Or maybe a Ross, TJ Maxx, or Marshall's. Kohl's is like Macy's version of Ross, I believe.

Zane

I should probably just go to the Mall of America.

The only problem is navigating that torrid hell.

Alright I'm out to do that wish me luck.

James Lupolt

8:13 PM

In the UK we have TK Maxx

They got into a business dispute and one of them decided to just left- or right-shift a letter

Mike Fal

0

Q: Get .csv file from ftp server and save it to a local location with batch

I am trying to your a batch file to automate the extraction of a file from an FTP server and save it to a local location "C:\scripts". I want to do this all with a .bat file. However, I have no idea how to do this.

csv-file

Move to StackOverflow plzkthx:

bluefeet

no please don't

it will just be closed

just close that as too broad

Mark Sinkinson

Does anyone know how to view/interact with the Query Data Store in SQL Server 2016?

bluefeet

I take it back it looks like you already asked this on SO. When posting questions pick one site where the question is on-topic and make it a quality question. — bluefeet 8 secs ago

cc @MikeFal ^^

Mark Sinkinson

8:29 PM

Ah...never mind...knew there must be something on BOL... msdn.microsoft.com/en-us/library/dn817826(v=sql.130).aspx

Mike Fal

I still haven't downloaded 2016. No time.

I still have to build a presentation for SQL Sat CO Springs, which is a little over a week away.

mmarie

@MikeFal Me neither. And now I have office 2016 and SQL Server 2016 to play with

and a presentation to update for Nova Scotia

Can someone please add more hours to the day?

Mark Sinkinson

@mmarie I can give you 2 more, but that's all

swasheck

i'm just getting on a plan at 630 am on saturday for a vacation. SUCKAS!!!

mmarie

@swasheck Where ya going?

swasheck

8:33 PM

mmarie

:-)

Mike Fal

@swasheck You just successfully built a new BI environment! What are you going to do now?

swasheck

@MikeFal just successfully migrated my 2008r2 data whorehouse to 2014

and yes ... DIDNEY WORL!!!

swasheck

8:47 PM

> Query Store settings initialized with enabled = 1,

eh? 2014 has some query store bits? or is that something different

Max Vernon

sql is such a pain in the ass. There has gotta be a way we can discourage the use of that tag without an accompanying platform-specific tag.

bluefeet

@MaxVernon do you want the same warning that SO gets when they add a sql tag? stackoverflow.com/questions/ask - put in sql and exit the tags

Max Vernon

@bluefeet oh yes yes yes - that would at least be a fantastic start!

it might be nice if the wording was "Include a tag for one specific database engine (SQL Server, MySQL, Oracle, etc...)" since the "SQL Server" option is what I expect most people really mean when they use sql

26

Q: Can We / Should We Strongly Encourage Users to Specify RDBMS on the "Ask" page?

More than most other sites, the majority of our questions require a specific piece of information: What RDBMS and what version is the asker using? If it's possible, would it be a benefit to add this to our "Ask" page similar to the high quality mockup below as an extra reminder for the detail...

discussion feature-request design asking-questions

bluefeet

I'll see if it can be added here

it might be a few weeks though

remind me about it around the 15th

Max Vernon

so, 6 to 8 weeks, then? meta.stackexchange.com/a/19514/193320

:-)

bluefeet

9:02 PM

yes, see I'm already using the company line

Max Vernon

@bluefeet its inevitable, I suspect. I deliver all my deliverables, even if they seem to be out-of-scope, and probably better spoken about off-line, in 6 to 8 weeks.

@dezso - you da man flagging like that!

so, is it considered "bad form" to VtC a question where the OP has offered bounty? Even if the question is clearly a "shopping list question"?

bluefeet

@MaxVernon if there is an open bounty you won't be able to close it. The most you can do is flag it for a moderator and ask them to refund the bounty and close the question

Max Vernon

@bluefeet thanks for the clarification!

dezso

9:19 PM

@MaxVernon how do you see my flag? does this superpower come with rep?

Max Vernon

@dezso I don't have enough rep for that; however I just guessed once I saw that you commented on the question, and saw it in the "low quality" queue.

dezso

@MaxVernon ah

so tricky

Max Vernon

@dezso it is a pretty terribad answer, by the way. Can you say "link only"!

dezso

yes, why not

Max Vernon

@dezso I'm not sure if you get my slightly strange meaning of "Can you say". It comes from a terrible (in my opinion) children's TV show where the main character asks if the watcher, normally a child (clearly), can pronounce some word like "stranger". The phrase "can you say xxx" is normally followed up by "I knew you could." and is typically meant facetiously.

some dev here just came up with a new one: "I'm being drawn in quarters". I don't have the heart to tell him it is really "I'm being drawn and quartered".

dezso

9:28 PM

@MaxVernon you are right, I didn't get it

@ypercube yes, as seen from @JackDouglas's answer

Max Vernon

9:51 PM

@dezso here's an example:

Mr Rogers.wmv

►

Max Vernon

10:08 PM

@PaulWhite latinopost.com/articles/18527/20150526/…

dezso

0

Q: Server configuration for database installation

I will program a payroll system on visual studio 2012 C# and will be using postgresql as database for a small business but if you have any recommendations they will be well received. Now, what i need to know, is how to configure a server to take charge of this database, since it's a growing busin...

postgresql database-recommendation c# windows-server-2008-r2 visual-studio-2012

VtC as unimportant details only

James Lupolt

10:21 PM

@swasheck Yo

swasheck

10:38 PM

@JamesLupolt what's shakin', bacon?

James Lupolt

@swasheck Not much

swasheck

you rang?

James Lupolt

@swasheck I was wondering, did you encounter many plan regressions in your 2008 -> 2014 testing and migration?

swasheck

yes. a few. some were horrible. i'd say that the ballpark of 5% was pretty accurate for us.

the worst ones would spin forever while trying to find a plan. it was odd.

James Lupolt

@swasheck ah ok. What did you end up doing with them? Using a hint to get the old optimizer, or something else?

swasheck

10:47 PM

we narrowed it down as far as nested (unnecessary) ctes with a lot of aggregates. it's like it was hoping you'd use a columnstore index ;)

James Lupolt

Did you end up introducing columnstores as well?

swasheck

@JamesLupolt hinting isnt an option for many of the workloads because of how cognos handles connections. we would verify that the issue didnt exist under the old CE through hinting, but then we'd refactor

@JamesLupolt no

James Lupolt

I'm looking at some test queries a dev sent me for a migration from 2012 to 2014. Two have pretty bad plan regressions. The rest are bad examples (most of the time is spent copying the results to the client).

swasheck

we suggested refactors primarily as intermediate materialization via temp tables --- one was bad enough to need an index on the table. this seemed to help the CE generate stats and get a better plan.

but our worst performers were stored procedures with thousands of lines.

James Lupolt

Sounds fun.

swasheck

10:50 PM

heh.

James Lupolt

These are actually pretty simple queries.

swasheck

multistatement? aggregates? is your screwy partitioning in effect?

James Lupolt

I think the stats are just necessarily wrong. The tables have hundreds of millions of distinct values with widely varying counts per value.

swasheck

@JamesLupolt i found the whitepaper to be particularly helpful when picking through our regressions

James Lupolt

Yep, some are aggregates. I can probably help them build a columnstore copy of the data for those.

swasheck

10:52 PM

msdn.microsoft.com/en-us/library/dn673537.aspx

James Lupolt

Most of the tables are full of repeating values.

swasheck

@JamesLupolt columnstore may help (aggregates with a lot of repeating values seem to be in the wheelhouse for CSI) ... but then you've got a whole new level of management (rebuilding indexes)

do your histograms accurately reflect your skew?

James Lupolt

@swasheck No, they're way off.

swasheck

do you do much updating of data, or are you pretty insert heavy?

James Lupolt

@swasheck Both, but usually only a few rows at a time.

Actually all of the data in the database is updated by a single service.

swasheck

10:56 PM

hm. have you considered filtered indexes/stats?

James Lupolt

@swasheck I tried last year. There are lots of different values to create filtered stats on, and I think filtered stats get ignored in estimates for joins anyhow (at least in 2012, I haven't looked at that in 2014).

I probably haven't looked hard enough at what I could do with multicolumn stats.

swasheck

yay correlation

@JamesLupolt ah. so these are join predicates? interesting.

James Lupolt

@swasheck It's a mixture, but some are join predicates. I'll probably look at it more tomorrow.

swasheck

ah

James Lupolt

The storage and power management settings were also messed up on the server, so that was confusing my test results.

swasheck

10:59 PM

it happens :)

good luck and have fun

i've gotta take off

James Lupolt

It would be nice to work with Windows sysadmins who know how to set these things up. Do you know if they exist? :)

swasheck

not here

James Lupolt

Ah OK, have a good night.

Aaron Bertrand

11:46 PM

GIVE IT A TRY !!!

0

Q: Does the scope for 'Alter Any Database' operate similar to 'Create Any Database'?

Does the scope for 'Alter Any Database' operate similar to 'Create Any Database', as in, only those databases created by Login-A under the 'Create Any Database' umbrella can be altered, and not the other databases on the server?

sql-server permissions alter-database

I don't understand why people will post a question that's easier and faster for them to test than to wait for someone to answer and hope they're right

Transcript for

The Heap™ – Consultancy ©®