Sorry, my bad judgement. Who needs facts when we got opinions, right?

Right!

Morning

is my opinion

@ypercubeᵀᴹ SQL Server can use 2PC in some cases using DTC

@TomV-trytopanswers.xyz Right. And Postgres has BDR which is a multi-master replication system. I'm pretty sure it uses 2PC.

Trying to format a utc date of 28/02/2020 05:00 (UTC) into 28.02.20 07:00 (Helsinki) but I can't figure out what I need to put in the tolocalestring parameters to get that output

Javascript btw ^^

Today's vaccine:
2 corona's bought
1 Mort Subite (sudden death) free

@TomV-trytopanswers.xyz toLocaleString('fi-FI', { timeZone: 'Europe/Helsinki' }) or something like that

Morning

@ypercubeᵀᴹ yeah I tried that, but I'm not even sure toLocaleString does what it's supposed to do in my environment

Javascript is just an epic clusterfuck of alike-but-not-the-same environments, ecmascript, javascript, works in this browser or not

The environment I'm in is using Rhino and not pure javascript

what a mess

Maybe the Rhino implementation just ignores the parameters, it keeps returning February 28, 2020 5:00:00 AM UTC

Morning

@TomV-trytopanswers.xyz This question should be posted on a different site: Stack Overflow

@TomV-trytopanswers.xyz I found this: stackoverflow.com/questions/2771609/…

check the moment.tz.format answer

@ypercubeᵀᴹ Yeah I saw that, but I have no idea how I could include moment.js into this framework (or any third party library for that matter)

I might write a java extension, which would work

Or just do what half of the answers on stackoverflow suggest, concatenate getyear, getmonth etc, and just add +2 to the hours

It seems like that is acceptable in the javascript world

@Vérace Postgres is working on adding Global Temporary Tables. Another feature (from No to Yes) on that list ;)

Not that I ever needed or used global temporary tables ...

I also see on that list: Permanent Global Temporary Tables. WTF is that? And how is it different than normal base tables?

@ypercubeᵀᴹ Azure SQL Database has a database setting for global temporary tables GLOBAL_TEMPORARY_TABLE_AUTO_DROP. Ordinarily, these are dropped when the last session stops referencing them. When this option is set, the global temporary tables stay around until explicitly dropped. Currently in public preview. cc: @Vérace

Normal tables don't get dropped when the service restarts. Global temporary tables (with auto drop off) are.

Fuzzing the line between permanent and temporary tables a bit.

Looks similar to creating a regular table in tempdb. It'll persist until you restart the server.

@AndriyM Yes I don't think you can do that directly in Azure SQL Database?

@PaulWhite9 Ah, I didn't know that. Maybe that's the reason why the setting was introduced in Azure, i.e. to let you have this kind of temporary table.

@Johnakahot2use I considered asking there, then common sense kicked in

@AndriyM Yes I believe so

@TomV-trytopanswers.xyz don't let them (sic) bully you from asking anything here ;)

So I guess SQL Server does support Permanent Global Temporary Tables?

Not that I really care about some random comparison list.

Certainly an odd name for a thing.

Like a cold hotdog

Morning all!

To move the discussion off databases for a while (yes, there is more to life than your own personal cosy RDMBS!), I'm going to Vienna with my new (and only ever...) fiancée and I'm looking for restaurant recommendations - it's only a short 4-day city break kinda thing, so only Austrian/Viennese cuisine please! We have Thai/Tex-Mex &c. here in Dublin, but no Austrian AFAICS.

What's an RDMBS?

Also I refuse to believe there is anything outside a database.

Personal and cosy or otherwise.

As one might say in Dublin, "RDMBS mee sister...!".

No idea what that means.

Have fun in Vienna

@Vérace Last (and only ever...) time I was in Vienna was in Christmas of 2007 so I do not remember much. There were these holiday things outside, where they sell hot wine (I know, crazy) and that's about it.

Oh and that it was quite cold.

Proper, nice cold, not the amateur cold they have in UK/Ireland ;)

@Vérace Schnitzel and Strudel :D

If you are into classic music, you can visit the Central Cemetery ;)

> Europe's second largest cemetery marks the final resting place for over 2.5 million people, including Beethoven, Brahms, Schubert and Strauss.

@TomV-trytopanswers.xyz good man

https://www.wien.info/en/sightseeing/sights/must-sees

@PaulWhite9 It's an oracle-ism. Basically in Oracle-speak a GTT is a table where the data items belonging to a transaction, session or whatever get dropped when the session closes. Can't remember the exact life-cycle now, my Oracle is getting pretty rusty. The key difference with SQL Server temp tables is that you can't create them without DDL privileges and the table itself is visible outside the transaction, and it is a shared object, although the data visibility is split by session.

Data visibility by session seems handy.

@ypercubeᵀᴹ Pere Lechaise is also a good cemetery to visit if you want to see tombs of famous people.

@PaulWhite9 It works OK - the table itself is a global artefact but the session can only see its own data.

Yes.

Just less convenient to use than they are in SQL Server. Idiomatic PL/SQL tends to use them a lot less than T-SQL does, but is rather heavier on cursor operations.

I use cursors rarely enough in T-SQL that I usually have to look up how to close and deallocate them properly when I do.

@PaulWhite9 Some of my best SO answers are to questions where I had to guess what the OP was actually asking.

@ConcernedOfTunbridgeWells nice to know!

🤦‍♂️ bought serial adaptor instead of vga adaptor

i bring shame to my family

didn't even really grok that they were different tbh

@PeterVandivier Just like USB and HDMI, which for some reason have similar-looking connectors again

Unrelated:

Damn I thought that was a question on dba

@TomV-trytopanswers.xyz not sure if ironic, but i'm replacing the serial->usb adaptor with a vga->hdmi 🤔🤷‍♂️

all this reminded me that I need to look for a RS-232 connector fo my old (pre-PC era) computer

Is there a way to measure slow in a SQL Server?

There's no such thing

I'm still bashing my head against this lovely database that seems to lose performance every now and then.

We've bashed the SQL Server 2016 into submission (acts like a SQL Server 2008 R2 again), but we are still having performance issues.

@PaulWhite9 you sir, get a star ;)

LEGACY_CARDINALITY_ESTIMATOR | ON

DBCC TRACEON(2371, -1)

COMPATIBILITY_LEVEL | 100

Statistics are being updated every 3 hours

RCSI is ON

have you tried turning it off and turning it on again?

The SQL Server? Gets booted every 4 weeks.

@ypercubeᵀᴹ Thanks!

I was looking at TF 2335

and TF 4199

But it was performant before the migration?

Very much so

But the data has grown by 10% in some tables.

Hi Experts, not able to pinpoint the issue on this one, basically I'm doing a "Forward Engineer to Database" on MySQL Workbench but hit the following error ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '

From 42 Mio rows to 50 Mio rows.

Executing SQL script in server
ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '
  CONSTRAINT `fk_Persona_Catalogo_Genero`
    FOREIGN KEY (`Catalogo_Genero_idC' at line 12
SQL Code:
        -- -----------------------------------------------------
        -- Table `examen1`.`persona`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `examen1`.`persona` (

I think we might be on the verge of having to perform a V2P.

But I can't seem to get any metrics inside SQL Server to support my case.

I have a CPU Ready on the 8 vCores of between 40ms and 100ms when performance degrades. But nada inside SQL Server. It just takes longer.

I'm trying to measure longer/slower.

Wait Stats during 5 mins when performance was slow:

-- 20200304 113000.000 (5 Min)
WaitType	Wait_S	Resource_S	Signal_S	WaitCount	Percentage	AvgWait_S	AvgRes_S	AvgSig_S
CXCONSUMER	553.24	545.77	7.47	4230	57.68	0.1308	0.1290	0.0018
CXPACKET	193.40	190.58	2.82	2089	20.16	0.0926	0.0912	0.0013
SOS_SCHEDULER_YIELD	168.09	0.06	168.04	162174	17.52	0.0010	0.0000	0.0010

Wait stats during 5 mins after moving virtual server to dedicated ESX host:

-- 20200304 140000.000 (5 Min)
WaitType	Wait_S	Resource_S	Signal_S	WaitCount	Percentage	AvgWait_S	AvgRes_S	AvgSig_S
CXCONSUMER	81.94	63.09	18.85	14131	44.41	0.0058	0.0045	0.0013
SOS_SCHEDULER_YIELD	45.88	0.03	45.84	77852	24.86	0.0006	0.0000	0.0006
CXPACKET	34.03	30.95	3.08	3437	18.44	0.0099	0.0090	0.0009
WRITELOG	7.29	5.99	1.30	5063	3.95	0.0014	0.0012	0.0003
ASYNC_NETWORK_IO	5.21	4.84	0.38	3987	2.83	0.0013	0.0012	0.0001
BACKUPIO	4.97	4.77	0.20	3043	2.69	0.0016	0.0016	0.0001

It looks better, but hard to pin-point.

I'm using Paul's (Randal) script https://www.sqlskills.com/blogs/paul/capturing-wait-statistics-period-time/

A case has been opened with Microsoft, but I'm not getting a lot of feedback recently.

@DRP it's better if you post a question on the site, when you have a lot of code.

The problem is 99% on the line that mysql complains or the line before.

@DRP So, did you "check the manual that corresponds to your MariaDB server version"? What was the outcome?

@mustaccio that "check the manual" is not very helpful really as an error message

I still haven't gone over the manual, as I'm still playing with the code... yup about to say that about the manual

I saw a similar post -> stackoverflow.com/questions/21798464/… but it was a (,) partly which was giving the issue

Additionally I'm already fighting with another manual (Go) , so was hoping to sort this out the hacky way

As a guess, that VISIBLE seems fishy.

are you wroking on MySQL or Maria?

MariaDB and MySQL Workbench

Let me take out visible

Yeah, but what is the database server version? select @@version ;

@ypercubeᵀᴹ that's not the error message, that's a suggestion. The error message is "You have an error in your SQL syntax near ' CONSTRAINT fk_Persona_Catalogo_Genero"

Invisible indexes is a MySQL-only feature (at least for now).

@mustaccio added in 8.0 ?

yes

@mustaccio it's a suggestion inside an error message ;)

True, and the error message seems fairly helpful to me.

@ypercubeᵀᴹ What is it? An amiga?

@ConcernedOfTunbridgeWells BIT-90 made in Taiwan

ZX-80, same CPU as Spectrum

@PaulWhite9 That may be true after you've been over the query but you wouldn't actually have a job without slow SQL.

sqlserverscience.com/documentation/…

@ConcernedOfTunbridgeWells I don't have a job!

Besides the quote was about a slow SQL Server, not slow SQL

SQL Server always runs at full speed

@PaulWhite9 Neither do I technically, yet here I am talking bollocks on forums while being paid to frig with somebody else's databases.

Living our best lives

@ypercubeᵀᴹ Interesting, a coleco knockoff.

@PaulWhite9 Living the dream, after a fashion.

Although that will change once we all flip over to IR35 and start having to work through brolly companies.

@PaulWhite9 Ok. I understand. The SQL Server will go as fast as it can. Agree.

So no hope in finding slow in SQL Server metrics?

wait stats kind of show you where the slow is, right?

They show you were SQL Server thinks the slow is.

Gotta go catch a train. :-)

Have a nice morning guys.

@Johnakahot2use 8vcores. Is your esx overcommited? Do you have Numa configured correctly?

@DRP 1. The constraint names can be whatever you choose. I don't see why you need to add a random character in the end, unless that's some Workbench bug. 2. You only need to remove the VISIBLE, not the indexes. Indexes are useful.

@TomV-trytopanswers.xyz yes 8 vCores. NUMA should be alright. Will have a look tomorrow.

Overcommitted? Could be.

@ypercubeᵀᴹ yeah you are right, I ended up taking out the index as I kept missing the VISIBLE lol. Removing just VISIBLE in fact is the answer.

Woop woop! Finally got that close vote priv 😎

music.youtube.com/watch?v=sM1nrFkLgsE&feature=share

twitter.com/computerfact/status/1180994818438242311

@Johnakahot2use sounds like it could be a vm issue

Noisy neighbors or vcpu spanning Numa. What's the VMware version?

Are you getting cpu ready from vtop or the charts?houseofbrick.com/cpu-ready-time-blog-part-ii

@PeterVandivier overseer mr overseer

Bleep bloop

@TomV-trytopanswers.xyz +1

That’s what I’m thinking too

@TomV-trytopanswers.xyz charts

@TomV-trytopanswers.xyz tell you tomorrow morning