The Heap™ – Consultancy ©®

6:57 AM

@McNets which edition?

2016

Database is a 2012 SQL server

10 hours ago, by McNets

Which is the price of one Oracle License for example for a 8 cores server?

ahhh

sorry

they have an online shop with prices: shop.oracle.com

@JackDouglas ok, thanks I'll have a look, I was curious .

7:02 AM

EE starts from about £8.5k per processor (but 'processor' <> socket or core)

oracle.com/us/corporate/contracts/…

^^^ that's the 'Core Factor Table'. If you are using Intel processor = 0.5 * core

@JackDouglas does it require client cal's as MS SQL?

@McNets you can license by named users instead but you don't need both

SQL Server has a 'per processor' licensing model (without CALs) too, doesn't it?

Stack Overflow would be expensive to run otherwise ;)

@JackDouglas ok, thanks

SE2 is cheaper but still expensive, and more crippled than it used to be

To all the W10 lovers in here: theregister.co.uk/2017/04/06/…

morning

@TomV if @srutzky can solve your issues, then I will gladly remove my answer. I added a comment to srutzky's question.

7:18 AM

@hot2use I'll let the bounty be auto awarded, your question has enough upvotes, you put in a lot of effort and your answer is certainly useful

I think it's a bit like the CX_PACKET issue where everybody started altering the MAX_DOP setting to reduce CX_PACKET waits instead of looking at the global picture.

More bounties can always be added if Solomon figures it out or allows me to figure it out

@McNets I absolutely love my Z-book

I'm not saying your servers aren't having issues, but it might boil down to : "Globally tune the server and the underlying hardware, and the issues might just be resolved."

BTW, are your servers running with the trace flag?

7:33 AM

@hot2use The thing is I don't see it on a single server, some are under a lot of stress, some really aren't

If I could only repro...

le sigh...

@TomV I feel your pain.

That itchy feeling that you want to know "WHY?"

I had a theory at some point that the transaction might not be executing any querys for a while (processing application logic inside the transaction) but tests with powershell indicate that I just always get the statement that last ran on the connection

Could Adam Machanics WhoisActive be of assistance? whoisactive.com

@TomV as in ASYNC_NETWORK_IO ?

as in

`begin transaction
run update statement
do lots of processing in the application
commit transaction`

Yup. Could be a good reason.

Could you have an AX user do some "normal" work on a copy of the database in the test environment to reproduce the workload?

7:46 AM

But in my tests I never get an empty blocking statement, the blocking statement is always the update that ran

Could you artificially slow down the storage? Like limit the speed on the SAN switch port to say 1GB instead of what you currently have? (Assuming you have a SAN switch infrastructure behind the SQL server). Or the equivalent on a NAS environment.

8:23 AM

@JackDouglas I'm sorry, we are talking and I've been required for a call conference

@TomV me too, but, do you know how to activate 'always' the graphic card?

@McNets In your BIOS, it's called something like "hybrid graphics" or something

However, I turned it on again, it the nvidia is a real battery drain

It would be a lot nicer if you could switch from the GUI

@TomV yes, I think so. is it no possible on Windows? Most part of the day I use it at the dockstation

I only found a way through the BIOS

hm, I had to roll back an edit on one of my answers for the first time

@TomV There is a graphics profiles (or something similar) that allows to activate it for certain applications

8:31 AM

someone decided that TRUNCATE was a DML statement, which I don't fully disagree, but then also that it was one of SELECT, INSERT, UPDATE, DELETE

@TomV I'll shout out real loud if I am able to reproduce the issue for you.

10:55 AM

On a correlated subquery I cannot use fields of the main query isn't it?

dbfiddle.uk/…

@McNets You need OUTER APPLY or CROSS APPLY for that construction to work.

@ypercubeᵀᴹ thanks

and also move the ON cindition inside the subquery. APPLY doesn't have ON

Oh, you already have it inside.

@ypercubeᵀᴹ ok

yep

And you need ORDER BY with TOP, so you get consistent results

11:11 AM

@ypercubeᵀᴹ it's true thanks

works fine dbfiddle.uk/…

I often tend to use a CTE with Row_Number() = 1 instead of consider an APPLY solution

heh

17 hours ago, by Paul White

I honestly don't know why people fall in love with ROW_NUMBER = 1 so much.

;)

11:38 AM

lol, Dynamics 365 on-prem becomes available

minimum requirements: 22 servers

3

11:50 AM

@TomV hummmm...I have 21 unused servers

12:18 PM

Admittedly that's for PROD & UAT but still

Q: PostgresSQL recursive query running slow with low load and almost no data

12:42 PM

Interesting question - identical plans with very different execution times

1

I'm building a simple ACL system and the datastore is Postgres 9.6 . Permissions are assigned to groups, which can have subgroups, and people are assigned to groups. I have a poor performing query responsible for retrieving all permissions related to a user, and I need your help to optimize/rewri...

vmware cpu shares, storage latency etc

I wouldn't know how to get wait stats on Postgres though

Is this a virtualised environment? If so, Are the guests running Windows? — Tom V 1 min ago

@dezso can you see from the plan if it's IO times or CPU times?

@TomV well, the last seqscan is already slower, but only gives a fraction of the whole difference

and I was also thinking VMs after seeing it, that's why I asked about the HW

the slowest is the bottommost hash, which is supposed to be an in-memory operation at 13 kB

If he's running windows I can tell him which counters to look at inside his VM

2:38 PM

@JackDouglas That seems fair and balanced. In the Fox News sense :)

You mean it isn't true that W10 will send your private files to MS in certain circumstances?

@JackDouglas No, I mean I found the obvious bias amusing.

Which isn't to say they don't have a point. I don't know enough about the details to say how 'bad' Windows 10 is compared with other modern operating systems (including the likes of iOS and Android).

I didn't spend hours on it - reading all the embedded links in detail, for example - but the collection in Basic mode seems OK to me. And crash dump reporting has always had a risk of including non-essential information as a side effect.

My phone creeps me out at times with the things it 'knows' about me and my habits. I can always stop using it of course.

3:00 PM

@JackDouglas @PaulWhite that's the price we pay for using non-free software ;)

and by non-free I do not mean that it costs money

3:35 PM

@PaulWhite El Reg are biassed against everyone, not just W10

They are especially biassed against Fox News

Ha!

3:51 PM

> “Sometimes when I’m writing JavaScript I want to throw up my hands and say “this is bull****!” but I can never remember what “this” refers to.” — Ben Halpern

4

Wilson

4:16 PM

@EvanCarroll Do you know if PostgreSQL has a long-transaction/multi-versioning mechanism, similar to ESRI's versioning or Oracle's Workspace Manager (OWM)? Related question/answer here: dba.stackexchange.com/a/169281/100880

I corrected the question/answer link.

4:29 PM

@Wilson I don't think so.

You can implement your own versioning scheme if you want to have this kind of "long" transactions.

Wilson

@ypercubeᵀᴹ Ok.

Q: SQL Server slow select from large Table

> This transaction has been open since July 2, 2001.
We call it “Edward.”
– http://thebuild.com/presentations/10-ways-to-kill-performance.pdf page 33

6

@Wilson that (^^^) was meant to be funny by the way. What happens if you use actual transactions that run that long.

Wilson

4:52 PM

@ypercubeᵀᴹ Haha. I don't have PostgreSQL, I'm just curious how this versioning business is done in the real world.

ErikE

5:24 PM

Is this a good candidate for migration to dba.se?

0

i have 2 Really Big sql server Database tables for IOT Project First TABLE IS Message (rows count 7,423,889,085 rows) CREATE TABLE [aymax].[Message]( [MessageId] [bigint] IDENTITY(1,1) NOT NULL, [ObjectId] [int] NOT NULL, [TimeStamp] [datetime] NOT NULL CONSTRAINT [DF__Message__Time...

sql sql-server sql-server-2014

I mean, how often do people work with 26-billion-row tables?

@ErikE - agreed

Good week for PG.

watching some of the new commits come in

CREATE TABLE itest3 (a smallint generated by default as identity (start with 7 increment by 5), b text);

Some really cool stuff here.

INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz');

Q: How to select a column that is not selectable

5:54 PM

0

I have a column in sql server that I need to select and I can not select but appear to exist. Is it a read only column or some other issue? Hopefully I added enough info here and I changed the name of my column and table to a generic name. SELECT t.name as TName, c.name AS ColName FROM sys.col...

sql-server tsql sql-server-2012

SO Question

@EvanCarroll what does that do?

0

A: What are Identity Columns?

How they actually work You can see how they're actually implemented now using the test suite's expected output. Some keys to take away from this. Inserting into a table with an identity column can now OVERRIDING USER VALUE for the identity column which forces a replacement of the conflicting ...

Think I should separate that from the spec question?

Perhaps, a new question, How are Identity Columns implemented in PostgreSQL 10?

How are they better than serial and a sequence?

@EvanCarroll bit premature I think — it might change before release

I think conceptually they're different. If the serial produces 10, and you want to override the row you have to delete and reinsert. Now you have sugar on the insert to force the insert overriding the identity.

You can have multiple sequences/serials on a table, but only one identity.

And, you can make an identity GENERATE BY ALWAYS so shy of having a special cause that OVERRIDES VALUES you can insert into the identity column.

@EvanCarroll the serial is just a default value — it isn't advanced if you provide a value

6:05 PM

I understand that, but providing a value is problematic in some situations.

seems just like sugar for serial primary key to me. not a bad thing but hardly revolutionaly

@EvanCarroll so what do you mean by "If the serial produces 10, and you want to override the row you have to delete and reinsert."?

@JackDouglas Most of it, yes. SERIAL is pg addition, IDENTITY is in the standard.

that's nice, but not sure it counts as really cool

more like really boring

This is not currently possible with serial, CREATE TABLE f ( a serial PRIMARY KEY, b text ); INSERT INTO f (a,b) VALUES (1,'foo'), (DEFAULT,'bar');

well, it errors, and for the wrong reason at the wrong time.

GENERATE BY ALWAYS would stop you from inserting into the column to begin with.

unless you put an overrides values clause in there.

6:15 PM

@EvanCarroll seems to me it errors for exactly the right reason at the right time

dbfiddle.uk/…

but you are allowed to be excited even if we aren't

depends on your intent. if you didn't want to have users inserting into the column, certainly not.

that's a duplicate key violation that happens after the column has been inserted into manually.

I think most users would want the error to happen if/when a user tries to manually insert into it.

I don't think I ever manually insert a value into a serial column. I want to be notified whenever that happens.

@JackDouglas this isn't currently allowed for serial, CREATE TABLE f ( a serial PRIMARY KEY, b text ); INSERT INTO f (a,b) VALUES (1,'foo'); INSERT INTO f (a,b) VALUES (1, 'bar');

it is allowed for identity columns with OVERRIDING USER VALUE

6:50 PM

I'm excited now

but only because it's time for me to go home :)

and Friday tomorrow

@ypercubeᵀᴹ looking forward to next friday though

next week will be tiring (but nice)

7:10 PM

@MaxVernon another idea for the timestamp(12) nonsense (sorry, question) is to use composite of 2 columns, eg.: DATETIME(0), BIGINT

No loss of accuracy, keep the date arithmetic and functions. With minor or major adjustments, depending on requirements.

DATEDIFF(day/minute/second, ..) would work as it is, DATEDIFF(microsecond, ...) would need special care.

Yah, agreed, @ypercube. I was also thinking of wrapping that functionality into a CLR user defined type. But, work, for free. :-(

7:46 PM

I'm voting to close this question as off-topic because it has been cross-posted at Database Administrators, where it has answers: dba.stackexchange.com/questions/169355/… Please do not cross-post. One question, in one site, is enough. — ypercubeᵀᴹ 1 min ago

(It's SO but doesn't hurt to downvote, VtC, VtD. Whatever you prefer ;)

Please delete my answer too, I'm on mobile...

@McNets I can't do that, I refer to it in mine ;)

@ypercubeᵀᴹ I still get the "consider adding a comment" thing when downvoting :)

Q: NOT (a=1 AND b=1) vs (a<>1 AND b<>1)

our HNQ question this week:

6

In the WHERE clause of a SQL query I would expect these two conditions to have the same behavior: NOT (a=1 AND b=1) vs a<>1 AND b<>1 The first condition behaves as expected, and while I epxect the second condition to do the same thing, it does not. This is very basic stuff, but ashamedly I...

sql condition

boooo

7:55 PM

noice

somebody should bounty it "not enough attention"

@TomV to give a heart attack to certain mod?

Nov 8 '16 at 0:22, by billinkc

best troll. 10/10 Would upvote again

Best troll I've witnessed in here so far, most definitely

I'm astonished it has only 2 answers so far. usually HNQ exposed questions attract more.

@TomV haha yeah, I had forgotten that. 21 answers and a gazillion points for KFC.

@ypercubeᵀᴹ it made his 20K mark IIRC

@ypercubeᵀᴹ HNQ? High National Question?

8:05 PM

hot network question

shows up in the sidebar on the right

ok thanks

I did a good job with some of my triggers today.

I deleted its.

@McNets It's like the scifi or worldbuilding weirdness I click on when the question looks funny, the rest of the network clicks those links to the dba question thinking the same "WTF"

8:21 PM

@TomV Yes, and lately I'm painted myself into a corner on some questions.

Talking about prices, if you think Oracle is expensive, I aquired two licences of part of this software materialise.com

14k per user a year each one

plus 3k if you want scripting module

8:34 PM

ouch

AVINENT - 3D Printing Technologies & Healthcare

ufff deleted

@MaxVernon

►

cooool

8:50 PM

yep

3d titanium printers

This ROWCOUNT thing is most counter-intuitive.

But UPDATE TOP(500) seems to be allowed

9:08 PM

what about ROWCOUNT?

Q: RowNum For T-SQL (Not TOP #)

1

Trying to run an update, but I want to step through it as it's quite a bit of data and don't want to blast it all at once. In oracle it's a bit easier to select the rows I want because you can include it in the WHERE clause, but T-SQL you include it in the select. I'm wanting to write it somethi...

t-sql update

@ypercube - the SET ROWCOUNT thing is occasionally useful, but always feels like a kludge to me.

"Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it."

2

@Max do you mind mentioning that in your answer?

@JoeObbish sure. However, they've been saying that since 2005 at least.

Thanks

9:12 PM

but, I agree - it's good to put that warning.;

also it could require a fair amount of log space depending on the old values and how full the pages are

but getting into that is probably too much

agreed. I did mention a bit about that without trying to get super technical.

super technical is so cool

lol, yes it is.

@McNets is UPDATE ... ORDER BY allowed?

9:17 PM

let me read again

The "Limitations and Restrictions" you quote says otherwise ;)

I predict one more round of delete-edit-undelete

It requires a WHERE clause

See example C (2)

No, WHERE is not strictly required. But UPDATE t SET ... ORDER BY a ; is not supported.

yes edited

So, in order to do: UPDATE TOP (500) t SET ... WHERE ... ORDER BY a ; you need:

UPDATE t SET ... FROM (SELECT TOP (500) pk FROM t WHERE ... ORDER BY a) upd WHERE upd.pk = t.pk ;

9:27 PM

now is correct, is it?

yes, looks valid syntax now]

ok

9:43 PM

I just rolled this back: dba.stackexchange.com/posts/57215/revisions

I can't see it

He deleted his answer.

But only after removing content and replacing it with:

> You don't like it, you don't get it. FU.

oh sensitive boy

Sooo. The United States looks to be going to war with Syria very soon.

Trump industries & friends needs a war?

9:55 PM

Apparently. I hate them all. We need gulags and communist revolution.

Tilerson just said plans are underway for regime change.

"Steps are Underway" mobile.twitter.com/davidmackau/status/850060417099587584/video/…

"No role for him to govern the Syrian people."

I see.

I think it makes sense. North Korea was never in the game plan. He just wanted to move all of his military equipment, still with dessert paint, into Europe. And, a force fit for invasion is clearly amassing. He thinks he's playing Risk.

BTW Evan, today I went to the Engineers University at Barcelone, the library hasn't SQL-86 book

That's cool =) good work for the hunt though. Spain looks like a wonderful place to be in the event I get to go draft-dodging.

Well, not all is good.

10:08 PM

Yea, I was watching some Franco rallies that were a bit concerning.

But you're welcome.

Franco dead on 1977

On Skeptics.SE there was a post today about opposition to a Muslim ban in the EU. Spain was the most opposed to the hypothetical ban.

Yes, but nobody wants them as neighbors.

Well this is not the place to talk about it.

A: How to conditionally stop a psql script (based on a variable value)?

True that, most of what I say gets deleted anyway so it is what it is.

0

PostgreSQL 10 PostgreSQL 10 brings conditionals to psql. This is no longer an issue. \if :db_to_run_on = 'dev_database' TRUNCATE the_most_important_table; \endif

@EvanCarroll You underestimate how much you say ;)

10:18 PM

Wouldn't know.. most of it is not here. =)

@ypercubeᵀᴹ You can vote to undelete it if you think it adds value. But as far as I can tell, all the value was in the comments.

@MaxVernon Are you around?

@PaulWhite No, it is rightfully deleted. I only posted here so you are aware of the offensive edit.

@ypercubeᵀᴹ Thanks (a flag would have worked too).

@JoeObbish yes, but I'm sitting outside having a beer.

10:23 PM

Well it's definitely not urgent. But I was looking for clarification on why that should be an answer

@PaulWhite do you plan to delete them?

@ypercubeᵀᴹ I don't often bother deleting comments on deleted posts.

though others can chime in

the question is here: dba.stackexchange.com/questions/169375/…

Oh, anything that provides the solution, no matter how short, should be posted as an answer, not a comment. At least in my opinion. @PaulW may have a different opinion...

Nope. Same view. If it's an answer, it's best in an answer so the community can vote/contribute to it.

I understand why people answer in comments sometimes, but I don't have to like it.

10:25 PM

Is my comment on that question an answer?

My point of view is that an answer would be saying where to store it instead of where not to store it

Me? I haven't looked yet. In the queue :)

Anyone!