The Heap™ – Consultancy ©® - 2017-02-27 (page 1 of 2)

5:58 AM

@McNets Yes it seems much slower against 2016 than v.next for me // cc: @JackDouglas

8:41 AM

@McNets yup, I noticed that too: I don't know exactly why 2016 is slower. My guess is that it is the database creation step — maybe that's been streamlined in vNext, or maybe it's faster on Linux — I'll find out when I add a Windows vNext VM.

McNets

Morning @JackDouglas, well it is quite insignificant.

at least for small scripts

I think it is a constant overhead, not dependant on the script size, is that wrong?

McNets

@JackDouglas No, no. It woks fine. There was just a feeling.

I used /SQLSVCINSTANTFILEINIT="True" when installing, though I can't find a way of verifying it actualy worked

There is some trace flag trick @JackDouglas

ha, and in 2016 it tells you in the error log by default

sqlskills.com/blogs/paul/…

8:56 AM

Thanks got it — it is turned on. Also CREATE DATABASE is quick from sqlcmd

must be my VB code that is slow

shame, I was hoping I'd never have to look at it again

9:23 AM

yes, that's my best guess at the moment, I may need to convert my VB.NET code to C#. Not sure when that will happen though :)

@JackDouglas Is the code viewable somewhere?

@TomV I haven't uploaded it to GitHub yet, but here is a gist: gist.github.com/jackdouglas/31f126a82c32652d35add06a77938adc

@JackDouglas I find it hard to believe VB code could ever be slow.

A: Is C# code faster than Visual Basic.NET code?

47

That is a myth. They compile down to the same CLR. However the compiler for the same routine may come out slightly differently in the CLR. So for certain routines some may be slightly better like (0.0000001%) faster in C# and vice versa for VB.NET, but they are both running off the same common...

@JackDouglas Yeah, if you decompile the intermediate language you can decompile it to c# or vb.NET without issue

you could "rewrite" your code by downloading JustDecompile and have it generate c# from your assembly

dezso

9:31 AM

"People who like Postgres and MySQL tend to vote down answers that mention SQL Server and Oracle, people who like Waterfall tend to down vote answers that mention Agile and so forth.": Utter nonsense. I may like Postgres and SQL Server, and dislike MySQL but I never base my voting on my likes for a product. There is zero evidence that users vote the way you claim. — ypercubeᵀᴹ 11 hours ago

There doesn't seem to be anything obvious in that code that would be slow. Have you confirmed that it is the create database executenonquery that is actually responsible?

@PaulWhite I've confirmed that it isn't :S

Might be worth tracing (Profiler etc.) exactly what's going on during execution vs. the sqlcmd option.

@JackDouglas Ah.

Or it could be the opening/closing of the connections that's improved in vnext

9:48 AM

For a simple select 1 foo, the database creation and destruction (which includes two connections to the DB) takes 0.5s our of about 3.5s total

but just adding in the user connection and closing it again adds another 3s, wierd

connecting/disconnecting several times as the new user doesn't add further overhead. Perhaps first time connection by a newly created user takes longer in 2016 than vNext?

I seriously doubt that's it. Isn't your infrastructure very different between 2016 and v.next?

yes, very

but connecting as sa is fast on 2016, and as newly created user is slow (on same instance)

@dezso My guess is that he read my answer in his other question (in our meta) about the mechanics, and the only thing he (mis)understood is the "Someone read your answer and didn't like it".

so even ignoring vNext/Linux, that is still weird

Since you're connecting with a new user every time there is no chance of connection pooling

but still, 3s sounds a bit much

dezso

9:58 AM

@ypercubeᵀᴹ whatever, I'm afraid we will never understand the guy

on a funny side note, someone mentioned Perl there as a bad example, and I just stumbled upon one: dba.stackexchange.com/a/73896/6219

@TomV I think I could fix the problem by maintaining a pool of pre-created users/databases, but it feels like a lot of extra complexity — I'll definitely wait until I've seen vNext on Windows

@JackDouglas So the connection.Open() for the user connection is definitely the slow part? Have you checked what's going on via Profiler at that time?

@PaulWhite I'll have to find out what profiler is first ;)

@JackDouglas Ah. You're running Express aren't you.

yes, and no GUI, no SSMS

but I could possibly install SSMS elsewhere and get it to connect. No point if profiler doesn't work on Express though, is that what you are saying?

10:04 AM

@dezso I didn't see it as "Perl a negative example" but perhaps. Anyway, that guy's comments seem good judgement. And he can't see the deleted content at dba.se (answer and comments).

dezso

@ypercubeᵀᴹ it could have been whichever technology that is different from the one the OP asks about

@JackDouglas You could indeed do that. But before you do, can you confirm that the newly created databases are all created with AUTO_CLOSE set ON? I believe that is the default for Express, and may explain the slowness. Consider ALTER DATABASE xxx SET AUTO_CLOSE OFF; immediately after/as part of creating the database.

Depending on how slow the db close/reopen process is on your setup that might be it. Databases unused for a while could be set back to AUTO_CLOSE=ON to conserve resources if that makes sense for you.

More information about Express and AUTO_CLOSE

Your v.next won't be Express of course.

no that is true

10:21 AM

You could also change the setting of AUTO_CLOSE on model, which is the template for new databases. I think that would work, but test to be sure.

whats wrong with this syntax:

CREATE DATABASE BLAH WITH AUTO_CLOSE OFF

"Incorrect syntax near 'auto_close'."

Perhaps AUTO_CLOSE is not supported as a WITH option.

ah, OK

@PaulWhite setting it to OFF doesn't change the timings noticeably

Ok so it's not that then :)

@JackDouglas Try the test with the Initial Catalog set to master (or tempdb).

@JackDouglas Profiler works fine with Express, it just doesn't come as part of that Edition.

Depending how things work, you might be able to get connection pooling by connecting to master/tempdb always, then immediately changing context to the user database via USE.

@PaulWhite Wouldn't that need the username/password combination to be the same too?

i.e. you need the exact same connection string

10:32 AM

@TomV Ah yeah :(

EXECUTE AS LOGIN = 'login' WITH NO REVERT; maybe. But that's getting a bit silly.

I believe the first time you connect to a new db it has to build the connection pool too

I still think tracing the login/connection process & server messages at the time might be revealing.

I'm sure you are right

It could be a hundred different things.

Good job this is chat or we'd have to close Jack as off-topic :)

:)

10:39 AM

Tip of the iceberg?

Are the 'computed' column headers a driver thing?

eg dbfiddle.uk/… versus dbfiddle.uk/…

Possibly. I thought you were adding those manually. I've never seen 'computed' on computed columns.

maybe it's a quirk of FreeTDS

In SSMS it's always (No column name) for me (which I think SSMS is adding).

with curly brackets?

10:47 AM

Round brackets, sorry

like this: dbfiddle.uk/… ?

Yup

if that's what SSMS users are used to, I think it's the sensible option for DBFiddle too

Fair enough

11:53 AM

What's with MSDN today

it's unbearably slow for me

@TomV No worse than usual for me, if you mean BOL links.

Everything just takes a minute to load, SQL Server links or Dynamics links doesn't make any difference

@PaulWhite you are on the far side of the universe, I guess it's always slow !

ConcernedOfTunbridgeWells

@TomV Blame (a lack of) caching :)

@ypercubeᵀᴹ Ha! No worse than usual = about a second or two for a typical BOL page.

12:28 PM

@PaulWhite Or something funky taking place on the caching servers that he's using.

@ConcernedOfTunbridgeWells Yes.

400 years of experience being similarly disrespected on meta.SO

Don't worry, though, he's probably insulated against that too.

Oh look, it's +1-20 now

I downvoted this answer to balance some upvotes. — Alexei Levenkov yesterday

@ConcernedOfTunbridgeWells I sent you a message at linkedin. Not sure if you have given me your email

@TomV They have sense of humour, there in Redmond!

12:48 PM

SELECT COUNT(T1.RECID) FROM (SELECT TOP (2147483647) 1 AS RecId FROM table1)

I just can't even

ha ha

You're right, they do have a sense of humour there in Redmond

TOP (2147483647) is to ensure that COUNT doesn't break, correct? :)

Far out. If only there were some sort of bigger version.

We could call it COUNT_BIG or something.

Ironically, counts are always count_big internally, with a conversion to integer if count is used.

@PaulWhite "Heh, heh. It's been funny to watch this one. At first it swung upwards, now it looks like it is going down again. I guess it illustrates the central point either way. – Matthew Sontum yesterday "

1:00 PM

Swung upwards by one vote? I wondered about that when I first read that comment a couple of hours ago.

If it weren't hilarious, it would be (by now).

I wondered about that too. Perhaps there were upvotes at first, later removed. The timeline doesn't reveal much, only shows -12 as a summary of votes for the day.

It must be hard living in a world where everybody around you is an idiot

@TomV Imagine that even as he explained how the voting mechanics were broken, people went on to downvote the explanation. Which just proved again how right he was.

I pity his wife and children

1:08 PM

Resumable online index rebuilds in SQL Server v.Next CTP 1.3

> Indirect checkpoint performance improvements. Indirect checkpoint is the recommended configuration for large databases and for SQL Server 2016, and now it will be even more performant in SQL Server v.Next.

sigh

ConcernedOfTunbridgeWells

Still waiting for that page to load :(

@ypercubeᵀᴹ Got it.

Is 'v.Next' how the SQL Server team choose to style the name or is that an official change from 'vNext'?

1:17 PM

On a side note:

bbc.com/future/story/…

"But beyond pure nutrition, there are other barriers to narrowing one's diet to a single food. Humans have built-in mechanisms to avoid just such a situation (probably because it eventually leads to malnutrition) – specifically, a phenomenon called sensory-specific satiety: The more you eat of one thing, the less you can stomach it."

Is that valid for individuals too?

@AndriyM No idea.

Perhaps they are exploring options.

@hot2use I thought they meant that to apply exactly for individuals. Related: Protein poisoning.

1:40 PM

I was relating more to "...The more you eat of one thing, the less you can stomach it" in relation to individual people and the way others people react.

I like some food better than some other but I know for certain that I couldn't eat even my favourite food exclusively. At some point I'd be unable even to look at it.

James Lupolt

There's a well-regarded compsci professor who specializes in databases who uses the word performant.

I don't think there's any use fighting it any more

> performant means that your customer hasn't complained about the speed (yet)

@AndriyM They tried to use v.Next as the moniker - in fact they sent out an e-mail to a group of us (not sure which set of people exactly) asking us to use that term. In the end they couldn't even get their own people on the same page.

Yes, you're only getting down-voted on "bulletproof" answers:

@Oded Aha, perhaps that is what is happening now! I thought there were safeguards against that. But I've seen a number of odd down votes on answers that I've given that are bulletproof. — Matthew Sontum yesterday

A: Best design to reference multiple tables from single column?

0

One easy solution is to include all possible attributes as columns on the main clothes table, and make all of the brand specific columns nullable. This solution breaks database normalization, but is very easy to implement.

But I thought NULLs were bad?

1:53 PM

@JamesLupolt name?

James Lupolt

@ypercubeᵀᴹ andy pavlo @ cmu

LOL

@PaulWhite cheeky!

@PaulWhite muted sobs of despair paired with wild thrashing

@Gnemlock that is correct. I've been trying to dispel common misconceptions regarding the inner workings of SQL Server and it has not been going well. Many downvotes as expected. When I say I am being targetted I mean that I have received random down votes on other answers (not the ones that challenge misconceptions) like this one: dba.stackexchange.com/questions/164938/… This one has at least two downvotes and the only comments are a celebrity DBA explaining that the answer is technically correct. — Matthew Sontum yesterday

Dispel myths about the inner workings of SQL Server by saying "Paul Randall is usually right

1:56 PM

@ypercubeᵀᴹ I know, I'm terrible :)

NashGC

howdy!

may I ask? =)

Asking is free.

Since I know it's going to torture him to not be able to respond:

But I thought, and I quote: "Allowing NULLs is exceptionally problematic for a number of reasons. Unless developers are skilled they will continually write bad code regarding NULLs, and allowing NULLs degrades database performance and increases storage space." Many choices have trade-offs; obviously you agree that an option that has down-sides - whether real or imagined - is still an option. You should think about this when arguing absolutes with people. — Aaron Bertrand ♦ 2 mins ago

NashGC

wow )) Great!

I don't understan how to use this function

function hook_rules_action_info_alter(&$actions) {
// The rules action is more powerful, so hide the core action
unset($actions['rules_core_node_assign_owner_action']);
// We prefer handling saving by rules - not by the user.
unset($actions['rules_core_node_save_action']);
}

@AaronBertrand "real or imagined" laughing ...

@NashGC This looks like PHP or Perl or C# or some other obscure language. Unlikely you'll get an answer here. Check the chat rooms for the appropriate one

NashGC

2:05 PM

@ypercubeᵀᴹ yeap it's PHP in drupal )

thank for indicate way!

@NashGC the PHP room then. Or the Drupal site

2:22 PM

ProTip: "Not possible" and "cannot be done cleanly" have quite different factual definitions. — Aaron Bertrand 13 mins ago

On a different note, can someone explain to me what 'te' in the jeaglte stands for?

'To everyone'?

'Till expired'?

'Truly excellently'?

@AndriyM I blame cache

@AndriyM Refresh the room.

2 days ago, by dezso

room topic changed to The Heap™ – Consultancy ©®: General on- and off-site discussion for http://dba.stackexchange.com [jeaglte] [mysql] [nosql] [oracle] [postgresql] [sqli] [sql-server]

He fixed the typo almost immediately.

@TomV @PaulWhite Thank you.

I saw those two almost in a row, thought I missed a later one that reinstated the 'te' version.

2:51 PM

@AndriyM till eternity

@ypercubeᵀᴹ Better

3:08 PM

@PaulWhite did you just bump that RCSI question so the only (and thus correct) answer would be downvoted into oblivion again?

-1

A: Switching to RCSI

At my current job the majority of database objects use (NO LOCK) or READ UNCOMMITTED isolation. I agree it is a bad solution, but if you go back far enough it is used quite often. I was not familiar with the acronym RCSI, but in googling it I found that it referred to using Read Committed Snapsh...

> I agree it is a bad solution, but if you go back far enough it is used quite often.

Oh no. It's the second time today that I've learnt about him giving in to groupthink. This is unbearable.

2

That answer is just missing the point of the question

but then again, he just quickly googled RCSI

@TomV No I just saw a question that could be improved, and deserved to be bumped to the front page to perhaps get a better answer.

He did expand RCSI factually correctly.

Objectively so.

3:17 PM

If you go back far enough slavery was pretty popular too.

Also he mentions googling RCSI and then suggests using snapshot isolation. I suppose his googling didn't quite get deep enough for him to realize those are two different things.

RCSI just stands for Really Cool SI (according to my Google research.)

thanks, @PaulWhite

for the edit just now

@MaxVernon No worries. It just looked odd to have part of the answer in the question.

Thanks for raising the SSIS question @PaulWhite, I wasn't notified of his answer to my comment.

3:32 PM

So many thanks today.

Thanks!

Thanks for thanking everyone Paul!

(Suddenly this room feels very Canadian.)

2

And thanks everyone for thanking Paul.

Well thank you for taking the time to thank me for thanking everyone!

Actually that would be more the case if it was a lot of sorrys

Please be careful with the accuracy of the terms you're throwing around - they may sound similar, but read committed snapshot isolation is not the same thing as snapshot isolation. Also, just because a lot of people use NOLOCK does not make it a good idea - if you go back far enough, a lot of people thought slavery and asbestos were great ideas, too. — Aaron Bertrand ♦ 1 min ago

@AaronBertrand We should bring back Asbestos to make America great again. Think of all those poor folks who used to work in the asbestos industry; they need jobs too!

3:47 PM

Indeed

that was a rather bad misquote of Jim Jefferies

@MaxVernon I'm not sure a lot of people who used to work in the asbestos industry still need jobs

@TomV undoubtedly they are probably dead now.

But we could slowly kill a new round of people.

@MaxVernon business-humanrights.org/en/…

I'm not proud of it, but that was a Belgian company

3:52 PM

it really was a reference to Trump's desire to bring back the coal industry.

admittedly, I did a bad job of conveying that.

No I got it, I laughed

oh good

Not because the subject matter is humorous but because Trump's ideas are so sad you kind of have to laugh

exactly

apparently the makers of "Cards against Humanity" just sent "Hidden Hitler" to all the members of Congress.

its a game about how Hitler subverted well meaning liberal members of govt in 1933

That is delicious

3:56 PM

yah, I chuckled quite nicely when I heard it.

You said "verify that it improves performance"; you should probably mention to verify the correctness of various reporting etc that could be negatively affected by any change in isolation level. I've personally seen upper management confused by subtle changes as a result from a shift away from NOLOCK. — Max Vernon 18 secs ago

of course, I've seen upper management confused by all sorts of things.

Poor Matt

lol

How is he ever going to impress his co-worker with 40K rep

There's little point commenting on the posts of a suspended user. If I were to offer advice, I would suggest people redirect their keyboard efforts into providing better answers. Goodness knows some of those questions need them. I've done my bit.

McNets

You're bad boys. Anyone is coming to MWC

A: Change query to improve operator estimates

4:08 PM

2

The plan was compiled on a SQL Server 2008 R2 RTM instance (build 10.50.1600). You should install Service Pack 3 (build 10.50.6000), followed by the latest patches to bring it up to the (current) lastest build 10.50.6542. This is important for a number of reasons, including security, bug fixes, a...

@PaulWhite I was actually just thinking the same thing. Also, poor old Matthew cannot respond.

My comments on the RCSI "answer" were as much for other readers as they were for him. But I've deleted them. If I have a chance I will write an answer.

@MaxVernon Yeah it's just pointless.

You mods can reinstate him earlier, can't you?

If he responds to the suspension mean

@ypercubeᵀᴹ Technically, yes.

4:12 PM

Or much, much later, as the case may be

3

I fell off my chair!

I'm just imagining him telling his co-worker "40K huh, watch me, I'll make an account right away" and now his co-workers following his actions on the site and telling each other "see, we aren't the only ones telling him he's wrong"

Thomas Ward

@PaulWhite That's my fault, sorry, this heavy clunky moderator-diamond-powered battle mech tends to push people over when I walk past...

Heh. No doubt.

Thomas Ward

(sorry, referring to something started in another room, don't mind me :P)

4:14 PM

@ThomasWard I got it. The others don't count.

Thomas Ward

lol

@AaronBertrand Seriously, people talk of splurting coffee on their monitor or whatever, but I literally fell off my chair when I read that.

3

:-)

It's all in the timing of course. All comedy is I guess.

@PaulWhite As long as I didn't cause you to smash your face on a glass coffee table or anything like that :-)

4:16 PM

@AaronBertrand Gosh no. painful memories

All surfaces are nicely padded here.

Sorry to bring it up. But when I visualized you falling off a chair, it was impossible to un-see.

And a calming shade of white.

No worries. I think, subconsciously, that's why I've never been back to Vegas.

Shame, because the conference itself is a beautiful thing.

Orlando is fun too

@AaronBertrand SQLSat?

No SQLintersection alternates - Orlando in the spring, Vegas in the fall

4:26 PM

Oh.

“We will work on fixing this issue as soon as we can,” then closed as won't fix. Nice touch, Microsoft. https://connect.microsoft.com/SQLServer/feedback/details/708622/gratuitous-incorrect-spatialguess-warning

Tweeted by BrentO on February 27, 2017 at 3:22 PM

Ha. He knows very well that Won't Fix often applies to the version it was reported against. It may well be fixed in a later version.

But I will support his right to troll Microsoft to my last breath :)

billinkc

Should change your handle to TrollSupporterDownUnder

3

MS could certainly do much better at respond to Connect items in place.

See what happens when I use the word "troll" ;)

4:38 PM

@TomV Good answer.

I know it's been said before, but I love how Connect cuts off lines of text.

Evan Carroll

I'm doing my first Udemy

@MaxVernon It is stupid and easily fixed. They just don't love Connect :(

@EvanCarroll finally coming to the dark side and learning how to use a real DBMS, eh?

I forget exactly how it is easily fixed, but I do recall it was trivial. Some HTML thing perhaps.

4:49 PM

@PaulWhite no kidding!

Evan Carroll

What?!

udemy.com/the-complete-guide-to-angular-2

@PaulWhite I can't imagine it would be beyond a couple of minutes of modifying the css. Although, it was probably designed to work on IE6.

Nothing works on IE6.

@EvanCarroll sorry, I couldn't resist.

A: SSIS changes SQL Server source data type from date to WSTR

5:03 PM

1

You are using SQLOLEDB.1 which is a pre-SQL 2008 version of the driver (and deprecated), and the date data type was introduced in SQL 2008. I suspect the provider isn't detecting the data type as it should, and you should probably switch to the more recent SQL Native client provider.

Forgot that link before.

billinkc

orly?

5:21 PM

@PaulWhite Nice answer to "Change query to improve operator estimates"

do you have a trick for generating those plans without the asker giving statistical data? do you just fake the rowcount for the stats?

0

A: Switching to RCSI

As I'm sure you know, just because a lot of people use NOLOCK does not make it a good idea - after all, if you go back far enough, a lot of people thought slavery, pesticides, asbestos, leaded paint and gasoline, etc. were all great ideas, too. NOLOCK has a performance benefit but not because i...

@JoeObbish Thanks. I was motivated by Matthew's poor answer there. Yes I just fake the row counts from the plan and guess at page counts.

@AaronBertrand Super!

Says the fastest reader on earth :-)

I'm half talking out of my ass but I wanted to be sure it was a wall of text

2

Thanks @billinkc

billinkc

Good catch on that one. I have learned something

5:27 PM

I disagreed with Paul saying the answer was good but you fixed it

@billinkc That is an excellent edit!

@AaronBertrand Too late I already installed railings on my chair.

heh heh

5:41 PM

@MaxVernon Actually the repro is sufficient. We don't need data.

@PaulWhite ok, you're the boss!

anyone have a good reference on the rules for preventing plan caching and/or reuse in SQL Server? I cannot use RECOMPILE

@MaxVernon I only know because he originally asked on #sqlhelp and I invited him to post a repro here. Adam Machanic and I are involved.

I was able to find only this: technet.microsoft.com/library/Cc966425

which is unsatisfactory in a few ways

ahhh

thanks for the heads-up, I'll not waste any time looking at it until one of you posts an answer.'

5:48 PM

@AaronBertrand have you measured him? I'm asking you because he's probably too mod-est to tell.

@ypercubeᵀᴹ I have not, and he probably is.

He is the moddest of the mods

@JoeObbish What do you need to do? It used to be the case that some ALTER TABLE statements prevented the whole batch being cached, but I think that was fixed. The 8k literal still works I think.

@PaulWhite The requirement is that any query involving a certain view cannot use a cached plan

@PaulWhite currently I'm defining an 8k literal in the view along with a few other tricks and it seems to work

but the solution looks very ugly and hacky

Indeed. That's because it is :)

so I was hoping to read lots of boring documentation to find something less hacky

6:01 PM

And the situation is such that OPTION (RECOMPILE) cannot be specified?

I would need to add it to the view definition

since it needs to apply to any query that an end user runs

I can explain why I think I need to do this if you are curious

Ah so the user queries are unpredictable and hence not suitable for a plan guide (to add the RECOMPILE hint).

Background information is always welcome.

it's a view to limit which data certain users can see

so for example, a provider of healthcare may only see his or her patients

so let's say one provider only gets to see 1 patient

and one gets to see 100 M patients

(those numbers do not make sense but bear with me)

rah! (bear impression)

the view uses one of the functions to check the logged in username

to get off topic, I believe that's the correct spelling

"bare with me" means for both of us to remove our clothing

6:05 PM

Yes please don't ask for that :)

I don't remember which, I think it's suser_name()

so let's say user 1 runs a query

sql server caches a query plan

one would expect very low estimates throughout the plan

then user 2 who can see millions of patients runs the same query

the cached plan is used and performance is terrible

Ok

@JoeObbish SQL Server Version?

@PaulWhite sql server 2014+, could be sql server 2016+ if I can produce a very good reason

I was just idly wondering how Row Level Security would interact with plan caching there.

@PaulWhite I haven't done a proof of concept. I've read through the documentation both times and it looks like it's mostly UI improvements for existing functionality

and it doesn't seem like anything we couldn't implement ourselves

but I suppose that it's possible there could be some interaction there...

6:12 PM

Yep I was just wondering. I haven't tested the scenario myself.

It's an interesting question. Let me think about it.

@PaulWhite Thanks! More than happy to do the legwork myself, just need to be pointed in a new direction

@PaulWhite I don't have the numbers in front of me but I remember not liking TVFs for filtering

ran into a lot of issues

also there's this: "RLS is compatible with both clustered and non-clustered columnstore indexes. However, because row-level security applies a function, it is possible that the optimizer may modify the query plan such that it does not use batch mode."

Yeah it's new. There will be stuff. It was just an idle thought.

@PaulWhite Don't get me wrong. It is the natural suggestion to make. I blame MS

Have you considered switching to PostgreSQL?

(sorry, joke)

I'm one of those people who generally doesn't want to use something if it is aggressively promoted towards me

so I have an unfounded negative opinion of PostgreSQL

6:21 PM

Ha ha yeah

@JoeObbish No, it's more than UI, but there are data leakage possibilities. mssqltips.com/sqlservertip/4005/…

And to be honest I have not done thorough analysis of plan impact either.

@AaronBertrand Thanks for the link. Mind clarifying a point in the article?

I can try

I mostly just regurgitate Books Online after filtering through Word's thesaurus and changing enough words to avoid lawsuits

5

When you said "It is important to note, too, that users outside of sysadmin, db_owner, db_ddladmin, and the table owner do not have direct access to DBCC SHOW_STATISTICS (since that could lead to information leakage), so estimates might be way off in some cases."

did you test that?

I was hoping that sql server just wouldn't let them use DBCC SHOW_STATISTICS

but somehow the plans would still use stats

through some magic

if that's not true that sounds like a dealbreaker

I don't recall exactly what I tested. I mean, it was a year and a half ago. :-)

But really, if you exposed estimates to end users (e.g. through execution plans) for queries like WHERE Salary > 100000 or WHERE Salary < [my salary], that could be bad.

Again, I forget exactly what is exposed and what sacrifices the optimizer has to make as a result. There should be two possible implementation modes - (a) I don't want unauthorized users to see this sensitive data in these other rows or (b) It would be more convenient if this set of users only ever saw these rows, but no big deal if they do somehow.

6:34 PM

@JoeObbish I will eat my hat if the last part is accurate.

The optimizer does not need the user to have show stats permission to load stats.

I would bet you are right

@JoeObbish Question about the issue at hand: So the users must write ad-hoc SQL against this view, they can't be constrained to stored procedures? I guess not, but want to confirm.

(also I keep a hat-shaped cake in the fridge just in case)

I probably worded that in a really crappy way, my main point was to be wary of numbers you might see in execution plans from a data leakage perspective, not from a performance perspective.

Which is sound, of course. RLS isn't really much of a security feature.

6:50 PM

@PaulWhite Right, can't be constrained to stored procedures

@JoeObbish And you can't force them to not schema-qualify the view, and not caching all ad-hoc plans isn't an option?

@PaulWhite The customer does the configuration that forces end users to use the views instead of the base tables. But we don't have any control over how they use the views. They can write any SELECT query they want. The customer might also schedule a report to run that runs the same query for multiple users.

what server setting did you have in mind for "not caching all ad-hoc plans isn't an option"?

Oh just TF 253 as a last resort kinda thing.

@PaulWhite Maybe I could have everyone use the DAC, apparently that doesn't use cached plans either ;)

Ha ha ha sure

6:57 PM

more seriously, I wasn't aware of TF 253

maybe?