The Heap™ – Consultancy ©®

ConcernedOfTunbridgeWells

12:28 AM

@MarkStoreySmith Want to make an appearance this weekend and lend a hand?

Ben Brocka

1:37 AM

@SimonRigharts yeah, I was thinking that from the way he phrased his question

"I know it won't break, I don't need a log!"

Simon Righarts

2:19 AM

Whoops. Just posted half of a spiel of an answer to a Sybase question before checking the date - one month ago

and the person who asked it hasn't been back since

Simon Righarts

2:43 AM

... and now I just realised I don't know if Sybase will do multiple index scans on a single table or not. Oh well, will investigate on Monday.

gbn

7:34 AM

Howdy

best

8:06 AM

hi

I've looked back at a question I asked and a different answer is more correct after working the problem, is it best to change my mark (can I?)

gbn

@best yes, you can. Happens often. Which is it please?

best

2

Q: Historical reporting from the database with Reporting Services?

We run a job tracking service as part of the company software. Reports are generated by SQL Server (Standard) Reporting Services usually daily but often as and when needed before meetings etc. As the jobs progress their status changes and the details of the reports change. Due to the legacy of...

really it should be concernedoftunbridgewells answer, the dates answer I chose first looked promising until we started working on it in practice

gbn

8:27 AM

@best fair enough, this happens

best

thanks, I'll change it a leave a comment explaing

Phil

9:20 AM

Got to love PHP reddit.com/r/lolphp/comments/ps6x5/0x0_wat

Simon Righarts

Evening gents

gbn

10:00 AM

Some fun:

1

Q: Can there be any beneficial reason to not to define a Primary Key on a table?

Are there any scenerious that could bring some benefits -in terms of performance or..?- to not to define a PRIMARY KEY on a SQL table?

sql-server tsql indexing primary-key

Mark Storey-Smith

@ConcernedOfTunbridgeWells I'm a bit (read alot!) behind schedule this week unfortunately.

Morning chaps

ConcernedOfTunbridgeWells

Morning

@MarkStoreySmith I'll see how far I get with installing it this weekend. Never set sharepoint up before, so I've really got no idea what I'm doing. I really know about 3/4 of fuckall about sharepoint.

Mark Storey-Smith

The install is a doddle, you'll have no problems

ConcernedOfTunbridgeWells

OK.

Mark Storey-Smith

It's just a bit of a monster when you start lifting the lid. Massive surface area

ConcernedOfTunbridgeWells

10:09 AM

@MarkStoreySmith I'm told there are a lot of things that open and shut on Sharepoint. I suppose it's about time that I got a working knowledge of it.

Mark Storey-Smith

For the purposes of what we're after, I think we can muddle through. Its the enterprise deployment side of it that's going to give me sleepless nights.

@ConcernedOfTunbridgeWells Exactly my thinking. It can do a lot, I need to be at least familiar with the capabilities if not expert in them

ConcernedOfTunbridgeWells

I guess I need to get maybe three or four things to work - SSRS integration, PerformancePoint, Publishing Office and Powerpivot for sharepoint.

Maybe work out how the web parts API works.

Mark Storey-Smith

@ConcernedOfTunbridgeWells Btw, I might switch from the SQLSkills perf tuning course I'm booked on to the BI course instead (sqlskills.com/T_ImmersionBI.asp)

That, or both

You've tweaked my interest in this BI malarky :)

ConcernedOfTunbridgeWells

Welcome to the dark side :)

BRB

Simon Righarts

He's doomed, someone drag him back while he's still rescuable!

ConcernedOfTunbridgeWells

10:16 AM

@MarkStoreySmith There's something to be said for it - with a background as a DBA you can probably move into it from the performance tuning side as well.

90% if the work is just glorified data monkeying, but it pays OK.

Mark Storey-Smith

I did IE1 last year so the natural choice initially was to do IE2 (perf tuning) but I've got some capabilities there already whereas the B.I. course would be good value end to end

Another string to the bow as it were

gbn

@SimonRigharts nah. less competition in the high end OLTP space then

ConcernedOfTunbridgeWells

The content of that course looks quite good. If they do it in any more than cursory depth you might get some mileage on it. There's a bit of material in there that I don't really know much about - sharepoint and some of the deeper SSIS stuff - almost all ETL work I've done in living memory has been done with stored procedure code.

Any idea what SQLSkills is like as a training provider?

Simon Righarts

Before I make an ass of myself, DENY overrides any GRANT (so a DENY at anywhere in your permissions always means no, but REVOKE nullifies a higher-level (wider-scope) GRANT but leaves the way open for a smaller-scope GRANT right?

or am I smoking the good pipe again?

(SQL Server incidentally)

gbn

"Maybe"

DENY + GRANT set a row in sys.database_permissions

REVOKE removes it

DENY and GRANT can be bypassed

because of ownership chaining

Think this sums it up:

3

A: SQL 2005 Permission Precedence

Never a simple answer... For a direct DELETE, a user in both roles won't be able to DELETEDENY always has precedence when permissions are checked For indirect via a stored procedure, the permissions may not be checked if both table and proc have the same owner. So both GRANT and DENY will be ig...

hang on. you answered too on that one

That is, DENY and REVOKE are explicit permissions. REVOKE is "no permissions"

Simon Righarts

10:34 AM

Yeah, my impression is that REVOKE means "remove this permission at this level" whereas DENY is "No. No exceptions"

Mark Storey-Smith

@gbn I'm not giving up OLTP! :)

Simon Righarts

That said I'm not a SQL Server expert

Mark Storey-Smith

@ConcernedOfTunbridgeWells Brilliant. Did IE1 last year

Simon Righarts

so an explicit DENT will always deny permissions, no matter what grants there are, whereas revoke works 'on the same level' as grant

do I have that right?

Mark Storey-Smith

That was Kimberley/Paul, the BI course is taught by someone else. But, can't imagine them taking on anyone questionable to teach under their company banner

gbn

10:37 AM

@SimonRigharts er... yes. REVOKE removes DENY or GRANT. You can DENY (= override) within a higher level GRANT. A high level DENY overried lower GRANT.

But

Simon Righarts

the but being ownership chaining?

gbn

GRANT on VIEW, this means a DENY on TableInView = ignored

yep

Simon Righarts

and now I'm not sure if my answer to the old question is correct. does a dbo always have all permissions to everything on a database?

gbn

yes

permissions are not checked for dbo

or sa

Simon Righarts

well yeah because sa is considered to be dbo on all databases

gbn

10:39 AM

well, sysadmin and db_owner aka CONTROL SERVER or CONTROL DATABASE (or CONTROL SCHEMA within a db)

for the last bit, dba.stackexchange.com/questions/6920/…

@MarkStoreySmith BTW, no answer from @MartinC via LI

Mark Storey-Smith

@gbn Strange. I was going to give him a buzz next week and grab a beer on route home one evening, will mention it.

ConcernedOfTunbridgeWells

@MarkStoreySmith From the course outline it touches on some SSIS stuff I don't know much about - never had to do any performance tuning on a SSIS package. Plus, the sharepoint material is probably relevant.

gbn

And I know 1 BI slot just popped up, coz the incumbent take my old slot...

Mark Storey-Smith

@gbn Were you booked in but cancelled?

Sorry, you mean ref MartinC?

gbn

@MarkStoreySmith yep. BI job in Malta. But they have London Offices

Mark Storey-Smith

10:46 AM

I'm on it!

Simon Righarts

11:08 AM

@gbn Sanity check, aisle two, please:

0

A: Adding Security to a legacy DB with Schema

That sounds reasonable (from the information provided). Also, one note - an explicit DENY at any level of the security heirarchy will always override any GRANT. This is different to a REVOKE, which nullifies a GRANT but doesn't do the "Haha, no, you're not getting that. Ever." effect of a DENY....

Also I just noticed my typo about half a page up here ("explicit DENT"). Oops.

gbn

2:46 PM

Interesting NoSQL consistency read

karlssonondatabases.blogspot.com/2012/02/…

Aaron Bertrand

This might be a candidate for DBA. I don't think serverfault is the best place for it.

0

Q: What causes this error: "There is insufficient system memory in resource pool 'internal' to run this query"?

I occasionally get this error message on one of my Tridion development machines: There is insufficient system memory in resource pool 'internal' to run this query. Unable to save Schema (tcm:0-0-0). A database error occurred while executing Stored Procedure "EDA_ORG_ITEMS_FINDUNIQUENESSCONF...

sql-server tridion

Though I'm not sure how the off-topic vote may sway if he does post execution plan and/or sample code.

As an aside, I wasn't the one who voted it off-topic (yet).

JNK

nice link @gbn

Mark Storey-Smith

@AaronBertrand The name of the stored procedure tweaks ones interest

JNK

man im getting sick of these shot-in-the-dark Qs

gbn

@JNK African or European?

All autogenerated code probably. Some big feck off In clause etc

Martin Smith

3:09 PM

I forsee some possible additional complaints about migrated questions

55

Q: Upcoming Reputation History Changes

Behind the scenes we've been hard at work making some changes to how we track reputation. Here are some of the issues we're aiming to address with these changes: Reputation Skew (your actual reputation doesn't match what's shown) Up/Down votes oddities (mostly with the daily rep cap, the magic...

discussion reputation rep-recalc

gbn

he he. I'm 2.5k higher on SO than I should be because of migrations

gbn

3:23 PM

@jcolebrand: can you über ping @AndriyM please? stackoverflow.com/users/297408/andriy-m

JNK

3:36 PM

why do you want Andriy?

gbn

to undelete his answer here stackoverflow.com/questions/9432995/…

JNK

can't you just ping him from another answer he made?

gbn

He was 2 mins ahead and one of his options was correct

JNK

well i undeleted it

i was the last vote

gbn

yeah, but that's clutter on SO

JNK

3:38 PM

and upvoted

gbn

ta

Andriy M

3:58 PM

Thanks, guys, for undeleting my answer

gbn

@ConcernedOfTunbridgeWells: do you have any knowledge (esp. performance) of NTFS mount points for SQL Server?

@AndriyM You're welcome

JNK

happy to help @AndriyM

good answer is good answer

Andriy M

And I don't know what über pinging means but I guess jcolebrand couldn't have done it, because I wasn't registered on dba until some minutes ago

@JNK, thanks

ConcernedOfTunbridgeWells

@gbn You mean shared over CIFS?

gbn

@ConcernedOfTunbridgeWells nah

a volume is mounted in an NTFS folder

ConcernedOfTunbridgeWells

4:01 PM

@gbn mounted in a unix style path?

gbn

er... something like symlinks

But reminds me more of what VAX/VMS used to have

serverwatch.com/tutorials/article.php/3687456/…

DTest

close the trash

-3

Q: email a database made by My SQL

I want to email a MYSQL database I created with all tables and queries to other person to check do I use the Import and Export Wizard or there are other ways?

mysql

ConcernedOfTunbridgeWells

What I meant is you mount the NTFS volume over a directory on another volume, a it like how mounts work on Unix

gbn

I've been asked. new company, trying to evolve SQL build standard

@ConcernedOfTunbridgeWells yep

ConcernedOfTunbridgeWells

@gbn I've never configured a server like that. Never seen any references to issues with it. In theory at least, it should only make a difference to the file opening process. Once it has a file handle there's no further overhead on the I/O.

gbn

4:05 PM

apparently, it's slower I've been told... by my SAN expert...

ConcernedOfTunbridgeWells

That's how it works on Unix, but I don't know for sure whether it works the same way on NTFS.

@gbn It could be. Why do they want to use mount points anyway?

gbn

@ConcernedOfTunbridgeWells It allows a SQL build to have the same paths etc

But the underlying folder my be mount pints

ConcernedOfTunbridgeWells

@gbn Are they trying to do everything through UNC paths?

gbn

Also good for multi-instance clustere

@ConcernedOfTunbridgeWells You run out of drive letters

ConcernedOfTunbridgeWells

I don't see how mounting the volumes against drive letters would preclude them from rolling out new cluster nodes or server instances.

They still have to explicitly mount the volumes, so I can't really see what they would save.

gbn

4:08 PM

bugger, see above

ConcernedOfTunbridgeWells

Unless they want to have some sort of global name space for all their .mdf/.ndf/.ldf files.

Mark Storey-Smith

@gbn We use mount points. No impact on perf whatsoever

gbn

@MarkStoreySmith OK, ta

ConcernedOfTunbridgeWells

So they want to do one volume per database file and mount all the volumes in a universal directory tree?

Or something like that.

gbn

@ConcernedOfTunbridgeWells kind of

Simplest form: ...\data ..\log ..\backup

Mark Storey-Smith

4:09 PM

Cluster/san aim is for one drive letter per instance as the root, typically 1GB carved from a mirrored LUN, all data luns mounted to that root drive

gbn

These folders can be one local volumne, a SAN, NAS etc

or 3 volumes mounted per folder

or \data\Db1 etc

So you build stays the same but you can have very different IO stacks underneath

ConcernedOfTunbridgeWells

@MarkStoreySmith Like I said before, on Unix there is a minor overhead on file open operations but once you have the file handle there's no further overhead.

gbn

eg single shitty RAID 5 for code monkeys

Whizzy RAID 10s per folder in prod

ConcernedOfTunbridgeWells

Never done it but if @MarkStoreySmith is to be believed there's no performance penalty, and I can't see any technical reason why there should be any performance penalty on anything but the file open operations, so (unless there's something funny going on behind the scenes) it makes sense that there's no performance overhead.

Digging stuff up from my very dimly remembered tutoring work on a 300 level operating systems paper back when I did my degree.

Mark Storey-Smith

blogs.msdn.com/b/pamitt/archive/2011/10/29/…

gbn

4:14 PM

@ConcernedOfTunbridgeWells yep, I'd like something concrete though. I've used them for years, but never beedn asked about performance

Mark Storey-Smith

That's a good reference for the plus points and the issues to be aware of

Aaron Bertrand

@gbn some people need to grow some skin. Especially when all you're doing is pointing out their mistakes.

ConcernedOfTunbridgeWells

On unix-oids, a file open has to traverse the mounts, which imposes a slight performance overhead, but no ongoing penalty once the file has been opened. No particular reason to assume anything different with Windows but that's the only basis I have ot make any observations.

gbn

@AaronBertrand not that bad. Just lack of experience in this area on his part. Long term, I'll hand over to an Ops team (new company, only 3 months old) but i want to lay groundwork

@ConcernedOfTunbridgeWells This is what he probly means. ta

Again

ConcernedOfTunbridgeWells

Some of the points in the MSDN article kind of make sense W.R.T. recovery times.

(i.e. if you have to check file systems you normally can't run a check until it's actually been mounted)

So if you have to mount - check - mount - check - mount this could be slower.

gbn

4:22 PM

@ConcernedOfTunbridgeWells also, his last high end OLTP was MySQL

With per table files for InnoDB, MySQL regularly recreates files when rebuilding index, OPTIMIZE etc. SQL Server opens and locks the .xDF files once

Assuming no auto close muppetry going on...

Mark Storey-Smith

@ConcernedOfTunbridgeWells The ideal is 1) Small LUN as root 2) Mount point for the system database, error log etc (default install stuff) 3) Separate mount points for user databases. That way you minimise the recovery time for the instance if a check has to be run on either root or system mount point

Or so the theory goes :)

ConcernedOfTunbridgeWells

@MarkStoreySmith Yes, that was roughly the impression I got from the article.

gbn

woohoo! Weekend starts now. Ciao folks

ConcernedOfTunbridgeWells

@gbn Have a good weekend.

Mark Storey-Smith

cheerio

Aaron Bertrand

4:32 PM

@gbn sorry, I was talking about dba.stackexchange.com/questions/13807/…

JNK

4:44 PM

wow @gbn is rudey mcruderson

jcolebrand

0

Q: MySql Cluster Built From Source Issue Installing MySQLi PHP Extension

I have successfully gotten the mysql cluster built after following this guide: http://tech.jocke.no/2011/03/13/mysql-cluster-how-to-with-load-balancing-failover/ I have since then installed apache and php on the same box. That is working. I run into troubles when trying to install the MySQLi e...

mysql linux php

Server Fault as a config issue? Stack Overflow as a tools issue? Unix and Linux or Ask Ubuntu as a Linux platform issue?

DTest

i flagged for SF before knowing the distro, so ubuntu in his case might be better /shrug His issue is more php connection than DBA

jcolebrand

Yeah, but which site is trickier

JNK

good Q here about order of operations behind the scenes in SS

1

Q: Select statement processing before Where clause in view

I have a statement that selects the substring of a charindex as follows: SELECT SUBSTRING(StringField, 5, CHARINDEX('ABC', StringField) - 5)... WHERE CHARINDEX('ABC', StringField) > 5 When I run the above statement in a select query, the results are returned just fine. When I run the abov...

sql database database-indexes

hopefully someone will post a better answer

Jack Douglas

5:20 PM

you guys seen this? :

57

Q: Upcoming Reputation History Changes

Behind the scenes we've been hard at work making some changes to how we track reputation. Here are some of the issues we're aiming to address with these changes: Reputation Skew (your actual reputation doesn't match what's shown) Up/Down votes oddities (mostly with the daily rep cap, the magic...

discussion reputation rep-recalc

might matter to us as migrations will be more noticable IIUC

Aaron Bertrand

Yes, scroll back 2 hours. :-)

DTest

@jcolebrand I think SO can be scratched off for sure. I'd ask SF mods if they'd like it or punt to ubuntu :)

Jack Douglas

@AaronBertrand oops, thanks :-)

jcolebrand

@JackDouglas thanks for that

ok, gone to lunch

jcolebrand

7:48 PM

wow, just because I went to lunch, everybody else got real quiet ... must be a case of the Fridays

Ben Brocka

8:30 PM

arg, it IS unreadable

jcolebrand

lol

DTest

rofl

Ben Brocka

8:53 PM

Ironically our logo is most often victim of poor color matching for some reason, like on the Mobile Site and in the SE site list...

4

Q: UX Stack Exchange logo is extremely hard to see in the mobile version header

This is a similar issue to the one affecting the SE profile pages, but affects the mobile version of the website. This fails the WCAG contrast checking algorithm, which recommends that the contrast ratio be at least 5:1. According to the OS X version of the Colour Contrast Analyser tool, the r...

bug design logo mobile

Aaron Bertrand

9:27 PM

If you're on the mobile version of an SE site, I don't think you need to be marketed to, you are clearly already a fanatic. Who cares how fancy the logo looks?

What I'd rather they spend time on is making it easier to format tabular results that are copied from, say, Management Studio. For this answer I spent more time formatting the output than I did on the rest of the question. That's wrong.

Ben Brocka

It's not an issue of fancy, it's unreadable and ugly

Aaron Bertrand

2

Q: How to Determine the input and output values of stored procedures?

I have hundreds of SPs and I would like to find out: input parameters with type output fields with type (not output parameters) Of course I could manually go through each one and write it down but where is the fun in that...No, literally where IS the fun in that :) Can this be done or does S...

stored-procedures parameter

But you got to the site already, who cares if you can read the logo? That's not what I'm there for.

Ben Brocka

@AaronBertrand I hope you realize you've just made our priorities look as if they are blatant UX/DBA stereotypes by the way

Aaron Bertrand

It's not what I meant. I'm saying a lot of people are making UX suggestions that are just not all that important and they're much more cosmetic than UX anyway.

It's like people on Connect begging Microsoft to make it easy to type something like SELECT (* EXCEPT column n) instead of typing out all of the columns except n

There are much more important things to worry about.

JMHO.

Ben Brocka

It's really ugly though and harms the presentation of the site, branding is important and making colors fit isn't too much to ask

DTest

9:33 PM

honestly, web pages should be just text, no graphics...who invented this thing anyway?

Ben Brocka

Plus with increasing mobile awareness it's more possible new users will be reaching the site on mobile, depends on how read-only the community is though, the mobile page really only works for reading

Aaron Bertrand

meh, I still don't think the logo is that important. Especially since on my phone it's like 8 pixels tall anyway. I can see "User Experience" at the top just fine, and that's the important part. I will admit that the logo on meta.ux looks a little bit better, but again, that doesn't impress me any more than the washed out one on the blue ux site.

I'm still not there because it has a fancy logo.

Ben Brocka

The readability issues are bigger, but the logo's a big eyesore and it's on every single page

Aaron Bertrand

9:50 PM

For the mobile version of the site, I'd prefer they just remove the logo and stick to the text, than spend time making the logo "better"...

Martin Smith

10:38 PM

@AaronBertrand Agree presenting tabular data is more hassle than it ought to be. For simple tables I normally do "Results to Text" and copy from there. Or for more exotic requirements I do "Results to Grid". Paste into Excel and make any required formatting changes there then paste from Excel to the ASCII table creator on this site. sensefulsolutions.com/2010/10/format-text-as-table.html

Aaron Bertrand

10:54 PM

I usually have results to grid and forget to switch to results to text. Then I copy and paste, and it looks nice in the HTML and then I swear at myself when I'm mucking with the spacing and tabs. I'm getting spoiled by SQL 2012 SSMS which has very powerful region editing features, and I expect every other UI to have them too. :-)

Transcript for

The Heap™ – Consultancy ©®